通用表表达式

2023-03-08 21:00 由丨渍丨发表于 #数据库

通用表表达式

通用表表达式（Common Table Expression、CTE）是一个临时的查询结果或者临时表，可以在其他 SELECT、INSERT、UPDATE 以及 DELETE 语句中使用。通用表表达式只在当前语句中有效，类似于子查询。

使用 CTE 的主要好处包括：

提高复杂查询的可读性。CTE 可以将复杂查询模块化，组织成容易理解的结构。
支持递归查询。CTE 通过引用自身实现递归，可以方便地处理层次结构数据和图数据。

--临时表t 一个字段为n 括号里是表的内容
with t(n) as(
	select 1
)
select * from  t;

-- 也可以定义多个
with t(n) as(
	select 1
),
t2(m) as(
	select 1
)
select * from  t cross join t2;

相当于一个变量，可以重复使用, 后面的临时表可以应用前面临时表的变量

with t(n) as(
	select 1
),
t2(m) as(
	select n+1 from  t
)
select * from  t cross join t2;

with子句

--查询每个部门的平均薪资
select d.department_name ,ds.avg_sal
from departments d 
join (select department_id,avg(salary) avg_sal from employees  group by department_id) ds 
on d.department_id =ds.department_id



with department_avg(department_id,avg_sal) as (
		select department_id,avg(salary) avg_sal from employees  group by department_id
)
select d.department_name ,department_avg.avg_sal
from departments d
join department_avg
on d.department_id =department_avg.department_id;

递归

递归 CTE 允许在它的定义中进行自引用，理论上来说可以实现任何复杂的计算功能，最常用的场景就是遍历层次结构的数据和图结构数据。

WITH RECURSIVE cte_name AS(
 cte_query_initial -- 初始化部分
 UNION [ALL]
 cte_query_iterative -- 递归部分
) SELECT * FROM cte_name;

RECURSIVE 表示递归；
cte_query_initial 是初始化查询，用于创建初始结果集；
cte_query_iterative 是递归部分，可以引用 cte_name；
如果递归查询无法从上一次迭代中返回更多的数据，将会终止递归并返回结果。

一个经典的递归 CTE 案例就是生成数字序列：

with recursive t(n) as (
	select 1   -- 初始化
	union all     
	select n+1 from t  where n<10 -- 递归
)
select * from t

-- 组织递归举例
select * from employees;

with recursive emp_path(employee_id,emp_name,path_name) as (
	select employee_id ,first_name||last_name ,first_name||last_name from employees where manager_id isnull 
	union all 
	select e.employee_id ,e.first_name||e.last_name ,path_name||'-->'||e.first_name||e.last_name from employees e join emp_path p on (e.manager_id=p.employee_id)
)
select * from emp_path

PL/pgSQL 存储过程

除了标准 SQL 语句之外，PostgreSQL 还支持使用各种过程语言（例如 PL/pgSQL、C、PL/Tcl、 PL/Python、PL/Perl、PL/Java 等）创建复杂的过程和函数，称为存储过程（Stored Procedure）和自定义函数（User-Defined Function）。存储过程支持许多过程元素，例如控制结构、循环和复杂的计算。

代码块结构

[ <<label>> ]
[ DECLARE
 declarations ]
BEGIN
 statements;
 ...
END [ label ];

其中，label 是一个可选的代码块标签，可以用于 EXIT 语句退出指定的代码块，或者限定变量的名称；DECLARE 是一个可选的声明部分，用于定义变量；BEGIN 和 END 之间是代码主体，也就是主要的功能代码；所有的语句都使用分号（;）结束，END 之后的分号表示代码块结束。

-- $$ 避免引号拼接 $$用于替换单引号（'），因为 PL/pgSQL 代码主体必须是字符串文本，意味着代码中所有的单引号都必须转义（重复写两次）。

DO $$ 
DECLARE
 name text;
BEGIN 
 name := 'PL/pgSQL';
 RAISE NOTICE 'Hello %!', name;
END $$;

代码子块

DO $$ 
<<outer_block>>
DECLARE
 name text;
BEGIN 
 name := 'outer_block';
 RAISE NOTICE 'This is %', name;

	 DECLARE 
	 name text := 'sub_block';
	 BEGIN 
	 RAISE NOTICE 'This is %', name;
	 RAISE NOTICE 'The name FROM the outer block is %', outer_block.name;
	 END;
	
  RAISE NOTICE 'This is %', name;
 
END outer_block $$;

声明与赋值

1.变量

在使用变量之前，需要在代码的声明部分进行声明：

variable_name data_type [ NOT NULL ] [ { DEFAULT | := | = } expression ];

user_id integer;
quantity numeric(5) DEFAULT 0;
url varchar := 'http://mysite.com';

do $$
declare 
	id integer;
	price numeric(5,2) default 0.6;
	name text;
begin
	id :=1;
	name :='Postgresql';
	raise notice 'id : %' ,id;
	raise notice 'price : %' ,price;
	raise notice 'name : %' ,name;
end;
$$

除了基本的 SQL 数据类型之外，PL/pgSQL 还支持基于表的字段或行或者其他变量定义变量：

myrow tablename%ROWTYPE;
myfield tablename.columnname%TYPE;

常量

DO $$ 
DECLARE
 PI CONSTANT NUMERIC := 3.14159265;
 radius NUMERIC;
BEGIN 
 radius := 1.0;
 RAISE NOTICE 'The area is %', PI * radius * radius;
END $$;
NOTICE: The area is 3.1415926500

if语句

IF 语句可以基于条件选择性执行操作， PL/pgSQL 提供了三种形式的 IF 语句。

IF … THEN … END IF
IF … THEN … ELSE … END IF
IF … THEN … ELSIF … THEN … ELSE … END IF

DO $$
BEGIN 
 IF 2 > 3 THEN
 RAISE NOTICE '2 大于 3';
 END IF;
 IF 2 < 3 THEN
 RAISE NOTICE '2 小于 3';
 END IF;
END $$;
NOTICE: 2 小于 3



DO $$
BEGIN 
 IF 2 > 3 THEN
 RAISE NOTICE '2 大于 3';
 ELSE
 RAISE NOTICE '2 小于 3';
 END IF;
END $$;
NOTICE: 2 小于 3


DO $$
DECLARE
 i integer := 3;
 j integer := 3;
BEGIN 
 IF i > j THEN
 RAISE NOTICE 'i 大于 j';
 ELSIF i < j THEN
 RAISE NOTICE 'i 小于 j';
 ELSE
 RAISE NOTICE 'i 等于 j';
 END IF;
END $$;
NOTICE: i 等于 j
DO

case语句

DO $$
DECLARE
 i integer := 3;
BEGIN 
 CASE i
 WHEN 1, 2 THEN
 RAISE NOTICE 'one or two';
 WHEN 3, 4 THEN
 RAISE NOTICE 'three or four';
 ELSE
 RAISE NOTICE 'other value';
 END CASE;
END $$;



DO $$
DECLARE
 i integer := 3;
BEGIN 
 CASE 
 WHEN i BETWEEN 0 AND 10 THEN
 RAISE NOTICE 'value is between zero and ten';
 WHEN i BETWEEN 11 AND 20 THEN
 RAISE NOTICE 'value is between eleven and twenty';
 ELSE
 RAISE NOTICE 'other value';
 END CASE;
END $$;

循环语句

PostgreSQL 提供 4 种循环执行命令的语句：LOOP、WHILE、FOR 和 FOREACH 循环，以及循环控制的 EXIT 和 CONTINUE 语句。

loop

EXIT 语句用于退出循环。
CONTINUE 表示忽略后面的语句

DO $$
DECLARE
 i integer := 0;
BEGIN 
 LOOP
 EXIT WHEN i = 5;
 i := i + 1;
 RAISE NOTICE 'Loop: %', i;
 END LOOP;
END $$;

while

DO $$
DECLARE
 i integer := 0;
BEGIN 
 WHILE i < 5 LOOP
 i := i + 1;
 RAISE NOTICE 'Loop: %', i;
 END LOOP;
END $$;

FOR 循环

DO $$
BEGIN 
 FOR i IN 1..5 BY 2 LOOP
 RAISE NOTICE 'Loop: %', i;
 END LOOP;
END $$;

NOTICE: Loop: 1
NOTICE: Loop: 3
NOTICE: Loop: 5


FOR 循环默认从小到大进行遍历
REVERSE 表示从大到小遍历；BY 用于指定每次的增量，默认为 1。

遍历查询结果集的 FOR 循环如下

[ <<label>> ]
FOR target IN query LOOP
 statements
END LOOP [ label ];

DO $$
DECLARE
 emp record;
BEGIN 
 FOR emp IN (SELECT * FROM employees LIMIT 5) LOOP
 	RAISE NOTICE 'Loop: %,%', emp.first_name, emp.last_name;
 END LOOP;
END $$;

NOTICE: Loop: Steven,King
NOTICE: Loop: Neena,Kochhar
NOTICE: Loop: Lex,De Haan
NOTICE: Loop: Alexander,Hunold
NOTICE: Loop: Bruce,Ernst

FOREACH 循环 ( 数组 )

DO $$
DECLARE
 x int;
BEGIN
 FOREACH x IN ARRAY (ARRAY[[1,2,3],[4,5,6]])
 LOOP
 RAISE NOTICE 'x = %', x;
 END LOOP;
END $$;
NOTICE: x = 1
NOTICE: x = 2
NOTICE: x = 3
NOTICE: x = 4
NOTICE: x = 5
NOTICE: x = 6

DO $$
DECLARE
 x int[];
BEGIN
 FOREACH x SLICE 1 IN ARRAY (ARRAY[[1,2,3],[4,5,6]])
 LOOP
 RAISE NOTICE 'row = %', x;
 END LOOP;
END $$;
NOTICE: row = {1,2,3}
NOTICE: row = {4,5,6}

热门相关：仙城纪仗剑高歌小妈妈的朋友新妈妈的礼物：年糕李秀与情夫