通用表表达式
通用表表达式
通用表表达式(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}