[PostgreSQL] 我的PL/pgSQL授課用的範例-進銷存(一)-進貨篇

 沒想到這裡我已有12年沒更新了. 現在重回來寫Blog看看, 慢慢再清理這裡的佈局和內容. 

走在這條資訊管理的不歸路, 不知不覺30年了, 一路全在業界打滾, 有得有失, 心境也隨著修行, 世事無常, 留下點微薄的經驗貢獻給需要的人.

全文連結在: https://oss-tw.blogspot.com/2021/02/postgresql-plpgsql.html

PostgreSQL-XA & DTP(分散式事務交易處理的概觀)

分享製作給 ...
對於想了解PostgreSQL Transaction 或是進階開發 Java EE 有關 JTA 的新人,
建議必要對該內容有個初步的認識.

(點圖大放後再保存)

撰寫 PL/pgSQL 函數快速指南: Part 1

PL/pgSQL 是 PostgreSQL 內置的可選預儲程序(Stored Procured)語言之一, 也是當中的效能與功能最強的。在這系列我們將學習如何撰寫 PL/pgSQL 的存儲函數/函式(Function)。

PLPGSQL FUNCTION 的構造

所有的 PL/pgSQL 函數遵循的結構, 看起來都如同下面的形式。

CREATE OR REPLACE FUNCTION fnsomefunc(numtimes integer, msg text)
RETURNS text AS
$
DECLARE
strresult text;
BEGIN
strresult := '';
IF numtimes > 0 THEN
    FOR i IN 1 .. numtimes LOOP
        strresult := strresult || msg || E'\r\n';
    END LOOP;
END IF;
RETURN strresult;
END;
$
LANGUAGE 'plpgsql' IMMUTABLE
SECURITY DEFINER
COST 10;

-- 我們呼叫這個函數它將回傳10次'hello there's'且回傳處理於單一的文本(text)欄位中. 
SELECT fnsomefunc(10, 'Hello there');

這個 PL/pgSQL 函數的基本構成如下:

1. 在函數介面宣告定義了引數(args)與返回的型別(type)。
2. 本體(body)的封裝部份, 當前 PostgreSQL (8+) 版本首選是 $$(dollar quoting)與使用著'單引號'。
3. 在主體(body)範圍: 變數宣告 DECLARE(declaration) 的區段是可選的。
4. 再者 BEGIN / END; 結構是定義該函數的內容。不同於 SQL 函數的選擇要求你參考到它們的順序位置例如 $1, $2, $3 等等。在 PL/pgSQL 中您能參考到它們的變數名稱。
5. 在主體(body)之後, 諸如所有的 PostgreSQL 函數，顯著的指出預儲程序(Stored Procured)語言和標記，並且指示應該如何被快取。在這種案例中，我們已顯著的標示 IMMUTABLE 這意味著不可改變輸出的函數，如果輸入的是相同的，可以預期是相同的。其它的選擇值是 STABLE - 這意味著它將不能被變更於一個查詢式輸入範圍中，VOLATILE 如此標示的函數帶有random()，CURRENT_TIMESTAMP 可以被預計變化輸出在相同的查詢式呼叫。
   更多參考: 34.6. 函數易變性範疇(Function Volatility Categories)
6. PostgreSQL 8.3 已投入有能力設定成本(costs)與估計行(rows)回傳的函數。對於標量函數的行不適用，我們這個簡單的例子也是同樣地。這個 COST 是相對於其他函數及預設為 100 ，除非你改變它。
7. 另外注意到該範例的最後有個快取模式的短句 SECURITY DEFINER，這意味著該函數是運行於符合擁有人權限背景下的函數。這意味著該函數可以安全的如同擁有人所能做的任何事情，即使這樣做的人運行的函數沒有這些權限。這部分適用於不僅要 PL/pgSQL 的函數，但任何。如果這一短句排除在外，之後該函數運行是以個人為安全背景。
   對於將從 SQL Server 來的使用者 - 這是類似的概念到 SQL Server 2005 - EXECUTE AS OWNER（leaving Security definer out is equivalent to EXECUTE As CALLER in sql server）。注意：SQL Server 2005 中有一個額外的選項，稱為使用 EXECUTE As 'user_name' 但這是 PostgreSQL 所缺乏的，使您能夠執行在一個命名的使用者, 而不需要是函數的擁有者。
   對於 MySQL 使用者們，SECURITY DEFINER 存在以及與工作或多或少相同的在PostgreSQL。
8. 幾乎所有的函數你可以寫在 PostgreSQL 抑或 SQL 中, PL/pgSQL 或者某些其它的預儲程序語言能夠使用 遞迴(recursion). 我們將在這系列的的另一個主題中示範。

有條件限制的邏輯

PL/pgSQL 對條件限制的邏輯式是成對的結構. 在上面的範例中我們看到了簡單的 IF THEN。同樣也存在於 IF .. ELSIF ..ELSIF END IF, IF ..ELSE ..END IF。我們將在上面的示例上做一個改變。

CREATE OR REPLACE FUNCTION fnsomefunc(numtimes integer, msg text)
RETURNS text AS
$
DECLARE
strresult text;
BEGIN
strresult := '';
IF numtimes = 42 THEN
    strresult := '你說得對！';
ELSIF numtimes > 0 AND numtimes < 100 THEN
    FOR i IN 1 .. numtimes LOOP
        strresult := strresult || msg || E'\r\n';
    END LOOP;
ELSE
    strresult := '你不能這樣做。麻煩不要濫用我們的慷慨。';
    IF numtimes <= 0 THEN
        strresult := strresult || '你是一個 bozo。';
    ELSIF numtimes > 1000 THEN
        strresult := strresult || ' 我不知道你以為你是誰。 
                    你的意向失去了控制。';
    END IF;
END IF;
RETURN strresult;
END;
$
LANGUAGE 'plpgsql' IMMUTABLE;

SELECT fnsomefunc(42, 'Hello there');
SELECT fnsomefunc(200, 'Hello there');
SELECT fnsomefunc(5000, 'Hello there'); 

控制流程

在上面的範例中，我們看到了不同的 FOR LOOP(迴圈) - 在以下是列出其他基本控制結構。第2部分中，我們將深入探討使用其中的一些。

在 PL/pgSQL 中提供的基本控制流結構是：

• FOR somevariable IN (1 ...someendnumber) 迴圈 .. END 迴圈;
• FOR somevariable IN REVERSE someendnumber .. 1 BY somestep 迴圈 .. END 迴圈;
• FOR somevariable IN (somesqlquery) 迴圈 ..RETURN NEXT; .. END 迴圈;
• 迴圈 ..logic statements EXIT .. EXIT WHEN .. CONTINUE WHEN .. END 迴圈;
• WHILE ... 迴圈 ... END 迴圈;
• EXCEPTION WHEN .... WHEN ..
• 介紹在 PostgreSQL 8.3 RETURN QUERY 迴圈能被置於任何的結構或是單獨裡。

本文譯自:

http://www.postgresonline.com/journal/index.php?/archives/58-guid.html

PL/pgSQL：呼叫函數、SQL查詢與回傳值處理

更新:2007-09-09
對映章節:

內容:

使用 PL/pgSQL 不外乎就是撰寫資料庫的預儲程序(Stored Procedure)、函數(Function)、觸發器(Trigger) 等。設計這些內部程序，其中除了迴圈、真假值判斷、回傳等等特別需求的語法外，主要的內容還是由各種 SQL 查詢命令或是呼叫其他已存在的函數所構成。由於使用 PL/pgSQL 呼叫其他函數或執行SQL命令時，多半是要等待回傳值、資料列並更進一步處理，所以與平時用前端程式或SQL命令列對函數與SQL指令的操作上，有比較不同的習慣性用法。要了解在 PL/pgSQL 處理各種呼叫查詢，必須從回傳值處理的角度去深入。

以下是幾個常用處理函數和SQL命令回傳值的方法：

1. 將回傳資料列指向 rs 變數﹝將 rs 定義為 record 類型﹞

   SELECT * INTO rs FROM mytable;
   
   然後可讀取回傳資料列的各欄位內容：
   
   rs.id
   rs.name
   rs.address
   ...
   
   註：此 SELECT INTO 使用方法很特別，並非是你想的那樣，請參考下文說明。
   
   
   

2. 只回傳單獨欄位內容並指向 addr 變數﹝將 addr 定義成與 address 欄位類型相同﹞

   
   SELECT address INTO addr FROM mytable;
   
   
   

3. 拋棄所有查詢和函數的回傳值

   EXECUTE myfunction();
   PERFORM myfunction();
   
   註：EXECUTE 和 PERFORM 的詳細差異不在本文討論範圍，請參考官方說明文件。
   

一般來說，在 PL/pgSQL 之中我們還是可以照常使用 SELECT、UPDATE、DELETE 等指令，差別在於執行查詢命令時『有無回傳值』。另外，以下有幾點，是使用 PL/pgSQL 呼叫及執行任何 SQL 命令時該注意的重點：


任何命令所回傳的值不能隨便忽略

在過去使用前端外部程式去執行 SQL 命令，你可以忽略不管回傳值的問題，就算有回傳任何資料，我們也可以省略不處理它；但在 PL/pgSQL 中就有所不同了，尤其在『觸發器(Trigger) 』函數的設計中，更是不可放任回傳值不處理，所以，通常在觸發器中我們如果要拋棄回傳值，請使用 PERFORM 。


SELECT INTO 的不同

在 PL/pgSQL 與平時使用 SELECT INTO 是完全不同的意義，後者是建立一個新的資料表，並將所查尋到的數個資料列寫入此資料表。而在 PL/pgSQL 中只是取得查詢資料列到自訂變數之中，並不會建立新的資料表。

延伸閱讀(Link):
PostgreSQL 的 Stored Procedure 簡易實例
PostgreSQL 觸發器(Trigger) 入門
Stored Procedure 實例：資料分頁

Stored Procedure 案例：動態排列資料

在資料整理時，我們常碰到有些資料需要往前移動，有些則要往後移動，尤其在資料的顯示流程規劃上就常常要使用到這種 功能。可是傳統資料庫的排序功能，不外乎就是使用日期、時間、資料編號做為排序依據，然後利用 ORDER BY 命令達成資料排序的目的，除此之外，我們無法動態調整資料列的先後次序。

一種解決辦法是為每個資料列增加新的欄位﹝例子中取名為mytable_after﹞，用來記錄該筆資料列的前一筆資料編號。所以我們在讀取資料時就使用該欄位記錄的值，逐一列出每筆資料。操作流程大致上是：

讀取第一筆資料 A﹝mytable_after 為 0﹞

→ 讀取欄位 mytable_after = A 的第二筆資料 B

→ 重覆操作直到找不到下一筆資料

此方法讀取過程繁複，若交由外部程式去做，將會不停的發送 SELECT 命令給 SQL Server，可想而之，其效能必定差矣。所以，我們可以建立 PostgreSQL 預儲程序，讓 PostgreSQL 在內部操作處理，然後直接回傳已排序完成的資料流。

PostgreSQL 動態排列實作例子：

CREATE OR REPLACE FUNCTION mytable_dynlist()
  RETURNS SETOF mytable AS
$BODY$DECLARE
    rscount Integer;
   rsnext Integer;
   rs record;
BEGIN

    select count(*) INTO rscount from mytable;
    rsnext = 0;
   FOR i IN 1 .. rscount LOOP
       SELECT * INTO rs from mytable where mytable_after = rsnext LIMIT 1;
        rsnext = rs.mytable_id;
        RETURN NEXT rs;
    END LOOP;
END;$BODY$
 LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION mytable_dynlist() OWNER TO somebody;

當 資料不多時，這種在資料庫內部處理動態排列很省事，在前端的程式也很好寫，但是，資料量一大，這種方式會造成 SQL Server 相當大的負擔。所以，當資料量多或是在前端程式有做多台機器的負載平衡，還是建議在前端先將所有要排序的資料下載後，在前端的程式做排序處理。

延伸閱讀(Link):
PostgreSQL 的 Stored Procedure 簡易實例
Stored Procedure 實例：資料分頁

Stored Procedure 實例：資料分頁

資料分頁的功能常見於 Web 應用程式，許多人應該對這功能的程式撰寫不陌生，本文嘗試利用 Stored Procedure 的方式完成這工作，我們將在 PostgreSQL 內部建立一個 Function 做 SELECT 並預先處理分頁，最後再輸出給前端程式。

PostgreSQL 資料分頁 Stored Procedure 實作例子：

CREATE OR REPLACE FUNCTION mytable_list(ps bigint, page bigint)
  RETURNS SETOF mytable AS
$BODY$DECLARE
rs mytable;
BEGIN
FOR rs IN select * from mytable LIMIT ps OFFSET ps*(page-1) LOOP
        RETURN NEXT rs;
    END LOOP;
END;$BODY$
 LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION mytable_list(ps bigint, page bigint) OWNER TO somebody;

引數說明：

ps - 設定每一頁的資料列數量

page - 指定目前讀取的頁碼

延伸閱讀(Link):
PostgreSQL 的 Stored Procedure 簡易實例

PostgreSQL 的 Stored Procedure 簡易實例

增進 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();