Featured image of post StoreProcedure跟PSQL的筆記
筆記 程式

StoreProcedure跟PSQL的筆記

研究了一下Store Procedure跟PSQL，以此紀錄

參考網站

這篇文章是以Postgres SQL的語法為主，其他RDB的語法略有不同

什麼是Store Procedure

預存程序（Stored Procedure）是一種資料庫對象，它是一個預編譯的SQL程式碼，可以在資料庫中存儲並重複使用。它通常由一系列SQL語句和編程邏輯組成，可以接受輸入參數、返回輸出參數和結果集。存儲過程通常由數據庫管理員或開發人員編寫和維護，並且可以在數據庫中執行。

存儲過程有以下幾個優點：

  1. 提高了數據庫性能：存儲過程是預編譯的，一旦編譯就會存儲在數據庫中，執行速度快。此外，存儲過程可以減少客戶端與服務器之間的通信，從而降低網絡延遲，提高數據庫性能。
  2. 提高了數據安全性：存儲過程可以在數據庫層面上實現安全控制，可以限制某些用戶或角色的操作權限，從而保證數據的安全性。
  3. 代碼復用：存儲過程可以被多個應用程序或者腳本重複調用，從而實現代碼復用，減少重複編寫代碼的時間和工作量。
  4. 事務處理：存儲過程可以封裝事務處理邏輯，當多個SQL語句需要在一個事務中執行時，可以使用存儲過程將它們封裝在一個事務中，從而保證了數據的一致性和完整性。
  5. 簡化了複雜的操作：存儲過程可以將多個SQL語句組合成一個操作，從而簡化了複雜的操作，使得應用程序更易於開發和維護。

存儲過程可以實現很多功能，比如：

  1. 數據庫的備份和還原
  2. 數據庫的複製和同步
  3. 數據庫的日誌記錄和監控
  4. 數據庫的查詢優化
  5. 數據庫的數據加密和解密
  6. 數據庫的數據清洗和轉換
  7. 數據庫的業務邏輯實現
  8. 數據庫的存儲和檢索過程中的錯誤處理
  9. 數據庫的報表生成和匯出等。

Procedure與Function之差異

image-20230302110014148

在PostgreSQL中，function和procedure都是儲存運算邏輯的物件，它們在設計目的和使用方式上略有不同。

Function主要是用於執行一個特定的計算或邏輯，返回一個單一的值或一個結果集，並且可以在SELECT語句、WHERE語句等中使用。Function可以返回一個或多個結果集，但只能返回一個值作為輸出，因此它被稱為有返回值的Function。

Procedure主要用於執行一連串的操作，不返回結果集，通常是用來執行複雜的業務邏輯，如事務處理、資料庫管理等。Procedure可以執行INSERT、UPDATE、DELETE等數據操作，但是不能作為SELECT語句的一部分使用。Procedure不返回任何值或結果集，因此它被稱為無返回值的Procedure。

在設計時，需要根據實際需求選擇使用哪種物件。如果需要執行一個特定的計算或邏輯，並返回一個結果集，則使用Function。如果需要執行一連串的操作並且不需要返回結果集，則使用Procedure。

更直接的不同是，Procedure並沒有定義它自身的回傳值，連Returns void都沒有，而Function是有回傳值的，即使是Returns Void也是有回傳值

創建procedure的語法

1
2
3
4
5
6
7

create or replace function test()
LANGUAGE plpgsql
AS $$
BEGIN
END
$$;

image-20230303104439737

若加入RETURNS VOID則會回報錯誤

創建Function的語法

1
2
3
4
5
6
7

create or replace function test()
LANGUAGE plpgsql
AS $$
BEGIN
END
$$;

image-20230303104821763

若不指定回傳值，則會報錯誤function result type must be specified

定界符$$ (delimiter)

在SQL中，兩個美元符號（$$）用來定義一個區塊或一個標識符（identifier）。

例如，可以使用$$來.定義一個存儲過程或函數的主體。在$$之間的所有內容都被視為存儲過程或函數的主體，可以包含SQL語句、流程控制語句等。

另外，$$還可以用來定義一個標識符，例如，可以使用$$來定義一個變量或一個標識符的名稱。在$$之間的所有內容都被視為標識符的名稱，可以包含字母、數字和下劃線等字符。使用$$定義標識符時，可以幫助區分保留字和自定義的標識符，以及避免標識符中包含空格等特殊字符所帶來的問題。

1
2
3
4
5
6
7
8

CREATE FUNCTION myfunc(x INT, y INT) RETURNS INT AS $$
BEGIN
    RETURN x + y;
END;
$$ LANGUAGE plpgsql;
-- 在這個例子中，$$ 用來定義函數的主體。CREATE FUNCTION 指令創建了一個名為 myfunc 的函數，它接受兩個整數參數 x 和 y，返回它們的和。 RETURN 指令用於返回函數的值。

-- 在這裡，$ 用作定界符，它定義了函數的主體。如果不使用定界符，則在函數主體中使用分號 ; 可能會導致錯誤。使用定界符可以幫助區分不同的 SQL 區塊，從而更好地組織和管理 SQL 代碼。

使用Terminal操作Postgres SQL

有兩種方式，一種是用psql的Shell，另一種則是用powershell

使用psql的Shell

打開psql

image-20230301141105566

輸入相應的資訊

image-20230301141442266

1677641123421

使用powerShell

首先先下載Postgres SQL，接著在把環境變數加入至電腦中，路徑大概會如下

1

C:\Program Files\PostgreSQL\14\bin

image-20230301112209470

即可在Termianl中操作postgres SQL

如以下指令

查看當前Psql有什麼Database

1

psql -l

連結進入Database

1
2
3
4
5
6

psql [dbname]
psql -U [role] [dbname]  # 以 postgres 的 role 登入 postgres 的 database

psql -U postgres # 使用 postgres 的 role 登入 的 postgres 這個 database
psql -U aaronchen minicare_demo  # 以 aaronchen 的 role 登入 minicare_demo 這個 database
psql -U postgres -d minicare_demo


1
2

psql -h localhost -p 5432 -U postgres school
## psql -h localhost -p 5432 -U <your_username> <your_database>

image-20230301112342437

1677641123421

(與之對應的Server設定)

連結成功後的畫面

image-20230301114731154

可以輸入

1

\dt

來查看該database底下的table

image-20230301114853633

在 psql 中，\ 符號是一個特殊的字元，稱為 “psql 內部命令前綴”。當您在 psql 終端機中輸入以 \ 開頭的命令時，psql 將解釋這個命令，並執行對應的操作。

以下是幾個常用的 psql 內部命令：

  • \c: 切換到另一個數據庫
  • \dt: 列出當前數據庫中的所有表格
  • \d table_name: 查看指定表格的結構
  • \q: 退出 psql 終端機
  • \! clear: 清空psql 終端機畫面

當您想要使用 psql 內部命令時，必須在命令前加上 \ 符號。例如，要列出當前數據庫中的所有表格，您可以輸入 \dt。

如果您想要輸入以 \ 開頭的普通文本，而不是 psql 內部命令，則必須在 \ 前面再加上一個 \ 符號。例如，如果您想要在 SQL 陳述句中使用反斜線（\）字元，則必須將其寫成 \\。

請注意，在某些情況下，psql 內部命令前綴 \ 可能會和 SQL 語句的關鍵字混淆。例如，CREATE TABLE 陳述句中的 TABLE 關鍵字後面如果不加引號可能會被 psql 認為是一個內部命令。為避免這種情況，建議在使用關鍵字時加上引號，例如 "TABLE"。\

image-20230301114853633

基本上這個terminal就是我們的Query Tool，所以是可以在這邊做CRUD的，如下

image-20230301115221834

常用的psql 指令

  1. \q - 退出 psql 命令行界面。
  2. \c <database_name> - 連接到指定的數據庫。
  3. \dt - 列出當前數據庫中的所有表格。
  4. \d <table_name> - 列出指定表格的詳細信息，包括列名、類型和約束等。
  5. \du - 列出所有用戶帳號的詳細信息，包括用戶名、權限和群組等。
  6. \dp - 列出所有表格的權限設置。
  7. \timing - 啟用或禁用查詢執行時間的顯示。
  8. \set - 列出或設置 psql 的配置選項。
  9. \i <file_path> - 從指定的檔案載入 SQL 命令。
  10. \e - 打開外部編輯器編輯當前緩衝區中的 SQL 命令。
  11. \! clear: 清空psql 終端機畫面
  12. \dt: 查看所有表格
  13. \df: 查看資料庫中的所有函數以及Stored Procedure
  14. \ef: 編輯現有的函數，可能你寫錯了還是啥的 ☆修改完後，存檔退出記事本，進到terminal要加上 \g 才會把這個修改go出去，不然你怎麼改都是沒有用ㄉ，血與淚的教訓Q_Q
  15. \sf: 查看更詳細的函數內容，s stand for show

dt, du, df的d代表Describe的縮寫，用以顯示資料庫的資料

Stored Procedure的實戰

首先創造表格

1
2
3
4
5

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE
);

插入資料

1
2
3
4
5

INSERT INTO users (name, email)
VALUES ('John Doe', '[email protected]'),
       ('Jane Doe', '[email protected]'),
       ('Bob Smith', '[email protected]');

返回值為空的Function

先創建一個無關緊要的stored procedure來感受一下它的作用吧，我們創造一個function，用來返回今天的日期，語法如下

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

CREATE OR REPLACE FUNCTION  -- 創建stored procedure的起手式。
get_current_date() -- 函式名稱
RETURNS DATE  -- 該函式的返回值，若未指定則為Return Null
AS -- 用以分明函數聲明與函數體，AS上面的是函數聲明，AS下面的是函數體
$$  -- 定界符，參照上方說明
BEGIN 
  RETURN CURRENT_DATE; -- 函數體
END;
$$  -- 定界符，參照上方說明
LANGUAGE plpgsql; -- 不加這個就是默認使用SQL語言，只能使用一些比較簡單的語法，加了這個之後就可以作流程控制、循環、異常處理等等...

接著呼叫它

1

SELECT get_current_date();

demo

回傳特定資料的function

該function會返回參數值的使用者的相關資訊

1
2
3
4
5
6
7

CREATE OR REPLACE FUNCTION get_user_info(user_id INTEGER)
  RETURNS TABLE (name VARCHAR(255), email VARCHAR(255)) AS
$$
BEGIN
  RETURN QUERY SELECT users.name, users.email FROM users WHERE id = user_id;
END;
$$ LANGUAGE plpgsql;

demo

更新資料的function

1
2
3
4
5
6
7
8

CREATE OR REPLACE FUNCTION public.update_user_info(user_id integer, name character varying, email character varying)
 RETURNS void
 LANGUAGE plpgsql
AS $$
BEGIN
  UPDATE users AS u SET name = $2, email = $3 WHERE u.id = $1;
END;
$$

變數宣告

:=來進行變數宣告，=在postgresSQL比較像是用來比較

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

CREATE OR REPLACE FUNCTION my_function(param1 integer, param2 integer)
RETURNS integer AS $$
DECLARE
    var1 integer := 0;
    var2 integer := 0;
BEGIN
    var1 := param1 + 1;
    var2 := param2 + 2;
    RETURN var1 * var2;
END;
$$ LANGUAGE plpgsql;

WHILE 語法

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

CREATE OR REPLACE PROCEDURE public.factorial(IN n INT, OUT result BIGINT)
LANGUAGE plpgsql
AS $$
DECLARE 
    i INT := 1;
BEGIN
    result := 1;
    WHILE i <= n LOOP
        result := result * i;
        i := i + 1;
    END LOOP;
END;
$$;

IF語法

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

CREATE OR REPLACE PROCEDURE example_if_proc (a INT)
AS
BEGIN
  IF a < 0 THEN
    DBMS_OUTPUT.PUT_LINE('a is negative');
  ELSIF a = 0 THEN
    DBMS_OUTPUT.PUT_LINE('a is zero');
  ELSE
    DBMS_OUTPUT.PUT_LINE('a is positive');
  END IF;
END;

將SELECT出來的值儲存在一變數中

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

CREATE OR REPLACE PROCEDURE public.get_customer_info(
    IN customer_id INTEGER,
    OUT customer_name VARCHAR(50),
    OUT customer_email VARCHAR(50)
)
LANGUAGE plpgsql
AS $$
BEGIN
    SELECT name, email INTO customer_name, customer_email
    FROM customers
    WHERE id = customer_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Customer not found';
    END IF;
END;
$$;

將資訊打印出來(print)

1
2
3
4
5
6
7
8
9

CREATE OR REPLACE procedure sayHello()
 RETURNS void
 LANGUAGE plpgsql
AS $$
DECLARE
BEGIN
        RAISE NOTICE 'hello word:';
END;
$$
Licensed under CC BY-NC-SA 4.0