通用表運算式

通用表運算式（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}

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/546261.html

標籤：PostgreSQL

上一篇：bucket表：數倉存算分離中CU與DN解綁的關鍵

下一篇：明解Redis（一）概述