預存程序 - AnalyticDB

預存程序（SQL stored procedures），通常指資料庫中存放在一起的一系列SQL。您在使用預存程序時，可以指定預存程序的名字並使用參數（parameters），並在合適的時候調用預存程序實現相同或者不同的業務。本文將介紹預存程序的使用方法。

背景資訊

AnalyticDB PostgreSQL 6.0版中，函數（Function）雖然可以實現大部分預存程序功能，但是使用函數代替預存程序的方式會產生額外的工作量，且函數內部不支援事務控制，無法完全代替預存程序功能。因此AnalyticDB PostgreSQL 7.0版新增了預存程序功能。

在7.0版本，您可以直接在預存程序中控制事務。Oracle等傳統數倉的業務遷移至AnalyticDB PostgreSQL版時，可以直接使用預存程序功能，不需要改造業務SQL。

預存程序的特性

AnalyticDB PostgreSQL 7.0版的核心版本升級至PostgreSQL 12，可以較好地支援預存程序能力。通過合理地使用預存程序，您可以在業務開發中可以獲得以下收益：

預存程序整合了一些列SQL，並分隔不同業務的SQL。這種特性使得預存程序易於維護，極大地提升資料庫開發人員的效率。
調用預存程序非常簡單，資料庫開發人員可以高效地在不同業務情境中複用預存程序。
不同的預存程序可以賦予不同的使用者權限，協助提升資料庫使用的安全性。

注意事項

暫不支援在DMS上使用預存程序，如需使用預存程序，建議您在用戶端工具psql上進行操作。

文法及參數介紹

具體資訊，請參見CREATE PROCEDURE。

預存程序與函數的區別

AnalyticDB PostgreSQL 6.0版的函數（Function）可以實現大部分的預存程序功能，通常建議您使用函數來實現預存程序業務，但是預存程序仍然是許多AnalyticDB PostgreSQL版使用者、及PostgreSQL從業者們期待已久的功能，具體原因主要為：

預存程序使用CREATE PROCEDURE文法，並通過CALL調用，符合SQL文法標準，減少使用者從其它支援預存程序資料庫的業務遷移至AnalyticDB PostgreSQL版的工作量。
預存程序支援內部開啟事務塊，進行事務提交（Commit）或者交易回復（Rollback），而函數無此功能，只能整體提交或復原一個事務。
預存程序無類似Function的傳回值，但是可以通過output參數擷取返回結果。

樣本

樣本一

本樣本中將建立一個預存程序，對其中一個事務進行提交，另外一個事務進行復原。

建立預存程序，並在預存程序中控制不同事務。

CREATE PROCEDURE proc() LANGUAGE plpgsql
AS $$
  BEGIN
    CREATE TABLE table_a (id int);
    INSERT INTO table_a VALUES(1);
    COMMIT;

    CREATE TABLE table_b (cid int);
    INSERT INTO table_b VALUES(1);
    ROLLBACK;
  END;
$$;

調用預存程序。
```
 CALL proc();
```
查看預存程序執行結果。
在psql中執行\d查看當前庫中的所有表，返回資訊如下：
```
                List of relations
 Schema |  Name   | Type  |   Owner    | Storage
--------+---------+-------+------------+---------
 public | table_a | table | adbpgadmin | heap
(1 row)
```
查看錶table_a：
```
SELECT * FROM table_a;
```
返回資訊如下：
```
 id
----
  1
(1 row)
```
通過上述預存程序執行結果可以看到表table_a相關事務提交，成功建表並寫入資料；而表table_b相關的事務在預存程序中被復原。

樣本二

本樣本將分別在預存程序和函數中執行一個迴圈任務並回收表的儲存空間，以體現預存程序和函數在提交事物方面的區別。

預存程序提供了事務提交能力，您可以在預存程序執行過程中對事務進行提交。以下樣本，預存程序可以實現在迴圈任務期間及時回收資料表空間：

CREATE PROCEDURE run_procedure()  LANGUAGE plpgsql
AS $$
 BEGIN
    FOR i in 1..10 LOOP
        INSERT INTO t VALUES(i);-- 在表t上執行一系列任務，樣本中為INSERT任務。
        TRUNCATE t;
        COMMIT;  -- 在迴圈內提交TRUNCATE任務，回收表t的實體儲存體空間。
    END LOOP;
  END;
$$;

函數無法在執行過程中進行事務提交，僅能在函數執行完成後作為一個事務一次性提交。以下樣本，函數無法在迴圈任務期間回收資料表空間：

CREATE FUNCTION run_function() RETURNS void LANGUAGE plpgsql
AS $$
 BEGIN
    FOR i in 1..10 LOOP
        INSERT INTO t VALUES(i);-- 在表t上執行一系列任務，樣本中為INSERT任務。
        TRUNCATE t;
        -- COMMIT; 不支援事務提交。
    END LOOP;
  END;
$$;

AnalyticDB：預存程序

背景資訊

預存程序的特性

注意事項

文法及參數介紹

預存程序與函數的區別

樣本

樣本一

樣本二

相關文檔