Stored Procedure 入門

出自ProgWiki

增進 SQL 處理效率的一個很重要的方法就是使用 Stored Procedure ，一般商用企業級以上的資料庫系統都會內建這種功能。

以下是在 PostgreSQL 中建立一個 Stored Procedure 的最簡單例子：

-- 執行結果：執行 select * from mytable where table_id = id 並回傳所有符合的資料列
 
CREATE OR REPLACE FUNCTION pg_myfunc(id INTEGER)
RETURNS SETOF mytable AS
$BODY$DECLARE
    rs RECORD;
BEGIN
    FOR rs IN SELECT * FROM mytable WHERE table_id = id LOOP
        RETURN NEXT rs;
    END LOOP;
END;$BODY$
 
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION pg_myfunc(id INTEGER) OWNER TO somebody;

如同 Oracle PL/SQL一般，PostgreSQL也有自己的程序語言 PL/pgSQL，其中最常用到的幾種語法使用：

IF 判斷式

IF ... THEN
 
ELSIF ... THEN
 
ELSE
 
ENDIF;

FOR LOOP迴圈

FOR i IN 1 .. 10 LOOP
    ...
END LOOP;

當找不到任何符合的資料列時

SELECT * FROM mytable WHERE mytable_id = id;
IF NOT found THEN
    -- 找不到任何符合的資料列時執行這裡的程序
 
END IF;

全部的 Function 都建立完成了，接著就要開始使用已建立的 Stored Procedure。

執行 Functions 有兩種SQL命令可以使用：

   Function 沒有回傳值時使用 SELECT pg_myfunc();

   Function 有回傳值時使用 SELECT * FROM pg_myfunc();