兩種方式，建立有傳回值的DB2函數

函數場景：路徑資訊由若幹個機構編碼組成，且一個機構編碼是9位字元。要求：擷取路徑資訊，并且删除路徑中包含‘99’開頭的機構編碼。

從用戶端及伺服器端分别建立ignore99(pathinfo varchar(1800))函數

1、在DbVisualizer中建立實作上述功能的函數

1 --/
 2 CREATE FUNCTION ignore99 (pathinfo VARCHAR(1800)) RETURNS VARCHAR(1800) LANGUAGE SQL
 3 BEGIN ATOMIC
 4 DECLARE V_ORGCODE CHAR(9);--
 5 DECLARE I_NUMBER INT;--
 6 DECLARE I_INDEX INT;--
 7 DECLARE V_PATHSTR VARCHAR(1800);--
 8 
 9 set I_NUMBER=length(pathinfo)/9;--
10 set I_INDEX=0;--
11 set V_PATHSTR='';--
12 WHILE I_NUMBER>0 DO--
13     set V_ORGCODE=substr(pathinfo, I_INDEX*9+1, 9);--
14     IF V_ORGCODE not like '99%' THEN --
15     set V_PATHSTR = CONCAT(V_PATHSTR,V_ORGCODE);--
16     END IF;--
17     set I_INDEX = I_INDEX + 1;--
18     set I_NUMBER = I_NUMBER - 1;--
19 END WHILE;--
20     RETURN V_PATHSTR;--
21 END
22 /

在DbVisualizer建立函數的幾個注意事項

①在DbVisualizer建立函數

以"--/"開頭，以"/"結尾

②最後的end後面沒有";"

③傳入的參數varchar 型的要定義長度

④"BEGIN ATOMIC"表示下面的程式塊是一個事務

⑤變量的指派格式： set 變量名=變量值，要加"set"關鍵字

2、在Linux伺服器端，建立一個成功的DB2函數

①先建立Crate_func.sql檔案，将ignore99(pathinfo varchar(1800))寫入Crate_func.sql檔案

CREATE FUNCTION ignore99 (pathinfo VARCHAR(1800)) RETURNS VARCHAR(1800) LANGUAGE SQL
BEGIN ATOMIC
DECLARE V_ORGCODE CHAR(9);--
DECLARE I_NUMBER INT;--
DECLARE I_INDEX INT;--
DECLARE V_PATHSTR VARCHAR(1800);--

set I_NUMBER=length(pathinfo)/9;--
set I_INDEX=0;--
set V_PATHSTR='';--
WHILE I_NUMBER>0 DO--
    set V_ORGCODE=substr(pathinfo, I_INDEX*9+1, 9);--
    IF V_ORGCODE not like '99%' THEN --
    set V_PATHSTR = CONCAT(V_PATHSTR,V_ORGCODE);--
    END IF;--
    set I_INDEX = I_INDEX + 1;--
    set I_NUMBER = I_NUMBER - 1;--
END WHILE;--
    RETURN V_PATHSTR;--
END@

View Code

②執行Crate_func.sql檔案，指令：db2 -td@ -svf Crate_func.sql

③執行成功會顯示：DB20000I The SQL command completed successfully.

④注意事項：

.sql檔案以"@"結尾;

執行@之前需要連接配接資料庫db2 connect to 資料庫名稱；