最新要闻

广告

手机

iphone11大小尺寸是多少？苹果iPhone11和iPhone13的区别是什么？

iphone11大小尺寸是多少？苹果iPhone11和iPhone13的区别是什么？

 警方通报辅警执法直播中被撞飞：犯罪嫌疑人已投案

警方通报辅警执法直播中被撞飞：犯罪嫌疑人已投案

家电

  1. 首页
  2. 移动通信
  3. 内容

通用表表达式

来源：博客园

通用表表达式

通用表表达式（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_salfrom departments d join (select department_id,avg(salary) avg_sal from employees  group by department_id) ds on d.department_id =ds.department_idwith 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_salfrom departments djoin department_avgon 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 是一个可选的代码块标签，可以用于 EXIT 语句退出指定的代码块，或者限定 变量的名称；DECLARE 是一个可选的声明部分，用于定义变量；BEGIN 和 END 之间是代码主 体，也就是主要的功能代码；所有的语句都使用分号（;）结束，END 之后的分号表示代码块结 束。

-- $$ 避免引号拼接 $$用于替换单引号（"），因为 PL/pgSQL 代码主体必须是字符串文本，意味着代码中所有的单引号都必须转义（重复写两次）。DO $$ DECLARE name text;BEGIN  name := "PL/pgSQL"; RAISE NOTICE "Hello %!", name;END $$;

代码子块

DO $$ <>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;beginid :=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 小于 3DO $$BEGIN  IF 2 > 3 THEN RAISE NOTICE "2 大于 3"; ELSE RAISE NOTICE "2 小于 3"; END IF;END $$;NOTICE: 2 小于 3DO $$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 等于 jDO

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: 1NOTICE: Loop: 3NOTICE: Loop: 5FOR 循环默认从小到大进行遍历REVERSE 表示从大到小遍历；BY 用于指定每次的增量，默认为 1。

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

[ <
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,KingNOTICE: Loop: Neena,KochharNOTICE: Loop: Lex,De HaanNOTICE: Loop: Alexander,HunoldNOTICE: 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 = 1NOTICE: x = 2NOTICE: x = 3NOTICE: x = 4NOTICE: x = 5NOTICE: x = 6DO $$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}

关键词：