本筆記旨在以範例說明 Function 和 Stored Procedure 的用法，詳細語法請務必參閱使用者手冊。
Function 可能會使用通用中譯「函數」；Stored Procedure 尚未有共識中譯，會採用「SP」縮寫，或「程序函數」。
本筆記由 PostgreSQL 台灣使用者社群提供，採 CC BY 4.0 授權。
初版來源：Stored Procedure與function的淺談 by Rubin.Sheu
協作專案：Jupyter notebook for PostgreSQL
開始囉！ 以下指令需要依序執行以達到說明的原始效果唷。 建議第二次閱讀再自行嘗試不同的執行方式。
# 每次載入都必須先執行！！！
# 載入 sql 延伸套件，每次都必須執行才能使用後續互動功能。
%load_ext sql
# 每次載入都必須先執行！！！
# 建立資料庫連線，請確認連線參數是否正確
# 本範例過程會真實影響資料庫內容，建議使用臨時性資料庫操作
# 使用 Binder 的話，請保持下面設定直接連線；否則請依你的資料庫連線資訊調整。
# postgresql://username:password@host/database
%sql postgresql://localhost/postgres
由於 Stored Procedure 功能在 PostgreSQL 11 之後才加入，故請執行下面指令檢查你的資料庫版本，以確保後續範例可以正常操作。
%sql SELECT version()
%sql SELECT now(); -- 日期處理
%sql SELECT ascii('x'); -- 文字處理
%sql SELECT to_number('12,454.8', '99G999D9S'); -- 文字轉數值
%%sql
CREATE FUNCTION fun_exp1()
returns integer as $$
select 1 as result;
$$ LANGUAGE sql;
# 你可以這樣呼叫它，當作欄位
%sql select fun_exp1()
# 也可以這樣，視為資料表
%sql select * from fun_exp1()
# 移除本段落產生的函數
%sql drop function fun_exp1
%%sql
create or replace function fun_exp2(x integer)
returns integer as $$
select x;
$$ language sql;
# 嘗試不給參數會發生錯誤
%sql select fun_exp2();
# 嘗試給參數
%sql select fun_exp2(3);
# 移除本段落產生的函數
%sql drop function fun_exp2
同名異式，可以根據業務需求分配相同名稱但不同參數的function
%%sql
-- 單一參數
create or replace function fun_exp1(x int)
returns integer as $$
select x as result;
$$ language sql;
-- 兩個參數
create or replace function fun_exp1(x int, y int)
returns integer as $$
select x * y as result;
$$ language sql;
-- 三個參數
create or replace function fun_exp1(x int, y int, z int)
returns integer as $$
select x * y * z as result;
$$ language sql;
嘗試呼叫
%sql select fun_exp1(1);
%sql select fun_exp1(1,2);
%sql select fun_exp1(1,2,3);
# 移除本段落產生的函數，多載函數只指定名稱時會出錯
%sql drop function fun_exp1
# 加入原宣告形式才能確定是要移除哪一個函數
%sql DROP FUNCTION fun_exp1(x int)
# 在一個指令中列舉要移除的函數
%sql DROP FUNCTION fun_exp1(x int, y int), fun_exp1(x int, y int, z int)
%%sql
-- 可以指定特定順序的參數給定數值
create or replace function fun_exp3(x integer default 0, y integer default 0)
returns integer as $$
select x - y;
$$ language sql;
%sql select fun_exp3(1); -- 預設值
%sql select fun_exp3(y:=1); -- 賦值
# 移除本段落產生的函數
%sql drop function fun_exp3
%%sql
create or replace function fun_exp4()
returns table(title varchar, pid int) as $$
select 'your-product',1;
$$ language sql;
# 回傳值
%sql select fun_exp4();
# 回傳資料表
%sql select * from fun_exp4();
# 移除本段落產生的函數
%sql drop function fun_exp4
%%sql
create or replace function fun_exp5(a int, b int)
returns int as $$
begin
return a + b;
end; $$
language plpgsql;
%sql select fun_exp5(1,100);
%%sql
/*客人資料表*/
drop table if exists exp_cust_tab;
create table exp_cust_tab(cid int, cname varchar, clevel varchar, cmemo varchar);
insert into exp_cust_tab
values
(1, '王小明', 'A', '常客'),
(2, '詹小傑', 'B', '偶爾來'),
(3, '陳曉娟', 'C', '態度惡劣'),
(4, '林阿胡', 'D', '新客人，不明');
# 檢查客人資料表內容
%sql select * from exp_cust_tab;
%%sql
create or replace function fun_exp6(cust_type varchar, amt int)
returns float as $$
declare price float;
begin
price :=amt;
if cust_type='A' then
price := price * 0.7;
elsif cust_type='B' then
price := price * 0.85;
elsif cust_type='C' then
price := price * 1.0;
else
price :=9999999;
end if;
return price;
end;
$$ language plpgsql;
%sql select fun_exp6('B',100);
# Function可以埋入基礎重點的商業邏輯
# 輔助軟體需求上的開發與維護
# 移除本段落產生的函數及資料表
%sql drop function fun_exp6
%sql drop table exp_cust_tab
定義上 Stored Procedure 為無回傳值的 Function，適用於幕後的資料程序控制，而非回傳資料內容。
%%sql
-- 建立第一個procedure
create or replace procedure sp_exp1()
as $$
begin
raise notice 'hello world';
end ;
$$
language plpgsql;
# 執行 sp_exp1()
%sql call sp_exp1()
# 移除 SP
%sql DROP PROCEDURE sp_exp1
已宣告的 SP，對程序之中所涉及的資料庫物件，並沒有強制的相依性。若資料庫結構改變，請自行同步變更 SP 內容。
%%sql
-- 交易紀錄表
drop table if exists exp_tran_tab;
create table exp_tran_tab(
oid serial,
cust_name varchar(10),
amt float,
odt timestamp default current_timestamp
);
# 新增一筆資料
%sql insert into exp_tran_tab(cust_name, amt) values('小陳', 10.3);
# 檢視內容
%sql select * from exp_tran_tab;
%%sql
-- 建立輸入交易資料的介面
create or replace procedure sp_alter_exp(cname varchar(10), amt float)
language sql
as $$
insert into exp_tran_tab(cust_name, amt)
values(cname, amt);
$$;
# 直接呼叫，可以透過procedure輸入資料，將資料邏輯整合進資料庫之中
%sql call sp_alter_exp('小王12345',100.09)
# 移除欄位
%sql alter table exp_tran_tab drop column cust_name;
# 呼叫，發生錯誤
%sql call sp_alter_exp('小王12345',100.09)
請回到前面 CREATE TABLE exp_tran_tab 的步驟，重新建立資料表，再進行下一步
# 修改格式
%sql alter table exp_tran_tab alter column amt type int;
# 呼叫 SP，數值失真
%sql call sp_alter_exp('小王12345',100.09)
%sql select * from exp_tran_tab;
# 移除本段測試物件
%sql DROP PROCEDURE sp_alter_exp
%sql DROP TABLE exp_tran_tab
%%sql
-- 帳務資料表
drop table if exists accounts;
create table accounts(
cname varchar,
balance int
);
insert into accounts
values('小陳',1000), ('小張',50);
# 查看內容
%sql select * from accounts;
%%sql
-- 轉帳程式A，先扣款，後匯款
create or replace procedure sp_exp2(cname_out varchar, amt int, cname_in varchar, waits int default 0)
language sql
as $$
update accounts
set balance = balance - amt
where cname = cname_out and balance >= amt;
select pg_sleep(waits);
update accounts
set balance = balance + amt
where cname = cname_in;
$$;
# 檢查餘額
%sql select * from accounts;
# 進行轉帳，等候二十秒，等候期間觀察餘額變化（執行上一步來觀察）
%sql call sp_exp2('小陳', 100, '小張',20);
%%sql
-- 轉帳程式B，先匯款，後扣款
create or replace procedure sp_exp3(cname_out varchar, amt int, cname_in varchar, waits int default 0)
language sql
as $$
update accounts
set balance = balance + amt
where cname = cname_in;
select pg_sleep(waits);
update accounts
set balance = balance - amt
where cname = cname_out and balance >= amt;
$$;
以下兩個 procedure 同時執行（連續按 shift+enter），一個等候三十秒，一個等候二十秒
%sql call sp_exp2('小陳', 100, '小張',20);
%sql call sp_exp3('小陳', 80, '小張',20);
# 移除本範例物件
%sql DROP TABLE accounts;
%sql DROP PROCEDURE sp_exp2;
%sql DROP PROCEDURE sp_exp3;
%%sql
-- 依客人狀態與師傅狀態決定是否付款
create table ramen_order
(
oid int,
cust_id varchar,
mast_id varchar,
cust_sta int,
mast_sta int
);
%%sql
-- 模擬訂單內容
insert into ramen_order
values
(1,'C01','M02',0,0),
(2,'C01','M02',1,2),
(3,'C01','M02',1,1),
(4,'C01','M02',2,2),
(5,'C01','M02',3,1);
# 查看訂單
%sql select * from ramen_order;
%%sql
-- 建立支付狀態對應表
drop table if exists pay_mod_mapping;
create table pay_mod_mapping(
pid int,
cust_sta int,
mast_sta int,
pay_mode int
);
-- 輸入對應的商業邏輯
insert into pay_mod_mapping values
(1,0,0,1),
(2,0,1,0),
(3,1,1,-1),
(4,2,1,-2);
# 查看內容
%sql select * from pay_mod_mapping;
%%sql
-- 用join直接映射出結果
select a.oid, a.cust_id, a.mast_id, b.pay_mode from ramen_order a
left join pay_mod_mapping b on a.cust_sta = b.cust_sta and a.mast_sta = b.mast_sta
where a.oid=1
%%sql
-- 可能遇到未定義的情況
select a.oid, a.cust_id, a.mast_id, b.pay_mode from ramen_order a
left join pay_mod_mapping b on a.cust_sta = b.cust_sta and a.mast_sta = b.mast_sta
where a.oid=2
%%sql
-- 可以用case輔助
select
a.oid, a.cust_id, a.mast_id,
case when b.pay_mode is null then 1 else b.pay_mode end
from ramen_order a
left join pay_mod_mapping b on a.cust_sta = b.cust_sta and a.mast_sta = b.mast_sta
where a.oid=6
%%sql
-- 用function包裝, 簡化常用查詢語句
create function fun_pay_mode(order_id int)
returns integer as $$
select
case when b.pay_mode is null then 1 else b.pay_mode end
from ramen_order a
left join pay_mod_mapping b on a.cust_sta = b.cust_sta and a.mast_sta = b.mast_sta
where a.oid=order_id
$$ language sql;
%%sql
-- 取得指定訂單支付狀態
select *, fun_pay_mode(oid) from ramen_order
%sql select fun_pay_mode(1);
%%sql
-- 移除本段範例物件
drop table pay_mod_mapping;
drop function fun_pay_mode;