03_Function_Procedure.ipynb

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