PostgreSQL 的存儲過程怎么寫

這篇文章主要講解了“PostgreSQL 的存儲過程怎么寫”，文中的講解內容簡單清晰，易于學習與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學習“PostgreSQL 的存儲過程怎么寫”吧！

1  支持了commit  和 rollback

2  幫助怎么從oracle 遷移到 postgresql 的便利性

3  符合更多傳統數據庫的用戶（ORACLE  SQL SERVER  MYSQL）

最近一段反思了一下為什么MYSQL中在大型應用程序沒有存儲過程這個詞，總結有三

1  MYSQL 本身不支持復雜的查詢語句 （我沒有說 mysql 8）

2  存儲過程是一段SQL語句的集合，處理一些程序處理比較困難和麻煩的功能，修改程序更加靈活

3   業務比較簡單，復雜的可以上移到程序層

那不支持存儲過程到底好不好，這的見仁見智了，在說下去估計就有人不歡喜了。

Ok 我們的回到PG的存儲過程，在PG11 之前也是沒有存儲過程的，但又一種變通的方式 create function 。PG 11 支持了比較完整的存儲過程，但網上的資料如果是中文的，屬于殘壁斷垣，英文的其實也沒有多少，估計大部分PG的使用環境還在 PG 9.4 , PG 10這個level。

關于存儲過程的優點：PG 官方也給了以下內容（其實這是存儲過程的優點）

 客戶端和服務端之間額外的傳輸將被取消.
 客戶端不需要也不用客戶端和服務端之間進行封裝或者傳輸的中間結果。
可以避免命令的多輪解析
在目前的PG11 中如果你要返回一個表的查詢內容，從現在11到PG12，目前存儲過程
是無法滿足這個需求的。目前如果要通過函數來完成在一段PLPGSQL中輸出某個表的
集合。

CREATE OR REPLACE function insert_data(type varchar(10), content text)
  RETURNS TABLE(  id int,  log_type varchar(10), log_content text,  insert_date timestamp)
 LANGUAGE plpgsql
AS $$
BEGIN

if( to_regclass('public.log_save') is null  ) then

CREATE TABLE public.log_save
(
    id serial,
    log_type character varying(10),
    log_content text,
    insert_date timestamp without time zone,
    PRIMARY KEY (id)
)
WITH (
    OIDS = FALSE,
    FILLFACTOR = 80,
    autovacuum_enabled = TRUE
);

ALTER TABLE public.log_save OWNER to admin;

end if;

insert into public.log_save (log_type,log_content,insert_date) value
(type,content,now());

RETURN QUERY select * from public.log_save;

END;
$$;

select insert_data ('simple','this is test log');
函數是通過 select 的方式來調用的。

上面是目前可以通過函數來完成調用表結果集的方式。

通過查閱資料，目前postgresql 的存儲過程和函數之間的區別可以總結成
1  存儲過程中可以包含commit  rollback
2  函數可以有return 返回值輸出
3  存儲過程支持 savepoint 功能

CREATE OR REPLACE PROCEDURE insert_D (type varchar(10),content text)

 LANGUAGE plpgsql

AS $$

BEGIN

if( to_regclass('public.log_save') is null  ) then

CREATE TABLE public.log_save

(

    id serial,

    log_type character varying(10),

    log_content text,

    insert_date timestamp without time zone,

    PRIMARY KEY (id)

)

WITH (

    OIDS = FALSE,

    FILLFACTOR = 80,

    autovacuum_enabled = TRUE

);

ALTER TABLE public.log_save OWNER to admin;

end if;

insert into public.log_save (log_type,log_content,insert_date) values (type,content,now());

rollback;

insert into public.log_save (log_type,log_content,insert_date) values (type,content,now());

commit;

--Drop table  public.log_save

END;

$$;

call insert_D('a','b');

select * from log_save;

當然在POSTGRESQL 11 開始有的存儲過程，在查看一些建議和資料的時候，有以下一些需要注意的地方

1  在創建trigger 中目前如果想在觸發后，如果執行函數或存儲過程，建議延續之前版本，繼續使用函數的方式

2  在有事務斷點，或者需要設置回滾或者保存點的大型的SQL 程序集的情況下，建議使用 PROCEDURE 

當然也有部分人對POSTGRESQL 的存儲過程提出了異議，說他根本沒有像其他的數據庫有事務性，我們來看下面一段存儲過程

CREATE OR REPLACE PROCEDURE check_now()
AS $$
DECLARE
    i int;
BEGIN
    FOR i in 1..5 LOOP
        RAISE NOTICE 'It is now: %', now();
        PERFORM txid_current();
        COMMIT;
        PERFORM pg_sleep(0.1);
    END LOOP;
END;
$$
LANGUAGE plpgsql;
結果如下，質疑在哪里，有些人認為在一個事務中表達的時間是一樣的，而不應該是
下面結果中的每運行一次就會更新一個時間。

個人覺得肯能是把 MYSQL 中的 NOW() 和 SYSDATE() 之間的事情搬弄到

PG中了。

我們在來一個存儲過程

CREATE OR REPLACE PROCEDURE check_now1()

AS $$

DECLARE

    i int;

BEGIN

    FOR i in 1..5 LOOP

        RAISE NOTICE 'It is now: %', now();

        PERFORM txid_current();

        RAISE NOTICE 'YES:i%',transaction_timestamp();

        PERFORM pg_sleep(0.1);

RAISE NOTICE 'NO:i%',statement_timestamp();

COMMIT;

    END LOOP;

END;

$$

LANGUAGE plpgsql;

你可以看到類似 MYSQL 中的  NOW() 與 SYSDATE() 之間的不同

目前PG的存儲過程，如果算上函數的情況下，其實PG的存儲過程和函數在使用中和 ORACLE  , SQL SERVER 在功能上是不相上下的，但其實不然，PG的存儲過程可能是使用 C 語言， PYTHON, 或其他可以接入的語言撰寫，我們可以發現很多的PG的系統函數都是使用 C語言撰寫的，那這樣的語言執行的速度一定是要比傳統的存儲過程要快，所以PG 在存儲過程上其實對程序員更友好，撰寫存儲過程可能是程序員的苦惱的地方，但如果換成他熟悉的語言，相比會輕松許多。

感謝各位的閱讀，以上就是“PostgreSQL 的存儲過程怎么寫”的內容了，經過本文的學習后，相信大家對PostgreSQL 的存儲過程怎么寫這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是億速云，小編將為大家推送更多相關知識點的文章，歡迎關注！