源地址:DB2存儲過程-基礎詳解

關鍵字: 存儲過程

學習 DB2 9.5 SQL Procedural Language，包括變量、條件、處理程序聲明、控制流和迭代語句以及錯誤處理機制。

您將：

學習 SQL PL 的基本要素。
理解如何聲明變量、條件和處理程序。
學習控制流語句。
學習游標處理和如何返回結果集。
理解錯誤處理機制。

簡介

DB2 SQL Procedural Language（SQL PL）是 SQL Persistent Stored Module 語言標準的一個子集。該標準結合了 SQL 訪問數據的方便性和編程語言的流控制。通過 SQL PL 當前的語句集合和語言特性，可以用 SQL 開發綜合的、高級的程序，例如函數、存儲過程和觸發器。這樣便可以將業務邏輯封裝到易于維護的數據庫對象中，從而提高數據庫應用程序的性能。

SQL PL 支持本地和全局變量，包括聲明和賦值，還支持條件語句和迭代語句、控制語句的轉移、錯誤管理語句以及返回結果集的方法。這些話題將在本教程中討論。

變量聲明

SQL 過程允許使用本地變量賦予和獲取 SQL 值，以支持所有 SQL 邏輯。在 SQL 過程中，在代碼中使用本地變量之前要先進行聲明。

清單 1 中的圖演示了變量聲明的語法：
清單 1. 變量聲明的語法

Sql代碼

.-,-----------------.
V |
|--DECLARE----SQL-variable-name-+------------------------------->
.-DEFAULT NULL------.
>--+-data-type--+-------------------+-+-------------------------|
| '-DEFAULT--constant-' |

					           .-,-----------------.
V                   |
|--DECLARE----SQL-variable-name-+------------------------------->
.-DEFAULT NULL------.
>--+-data-type--+-------------------+-+-------------------------|
|            '-DEFAULT--constant-' |

SQL-variable-name 定義本地變量的名稱。該名稱不能與其他變量或參數名稱相同，也不能與列名相同。

圖 1 顯示了受支持的 DB2 數據類型：

DEFAULT 值 – 如果沒有指定，在聲明時將賦值為 NULL。

下面是變量聲明的一些例子：

DECLARE v_salary DEC(9,2) DEFAULT 0.0;
DECLARE v_status char(3) DEFAULT ‘YES’;
DECLARE v_descrition VARCHAR(80);
DECLARE v1, v2 INT DEFAULT 0;

請注意，從 DB2 version 9.5 開始才支持在一個 DECLARE 語句中聲明多個相同數據類型的變量。

數組數據類型

SQL 過程從 9.5 版開始支持數組類型的變量和參數。要定義一個數組類型的變量，需要先在數據庫中創建該類型，然后在過程或應用程序中聲明它。數組是臨時的值，可以在存儲過程和應用程序中操縱它，但是不能將它存儲到表中。

DB2 支持以下創建數組數據類型的語法：

清單 2. 創建數組數據類型的語法

Sql代碼

>>-CREATE TYPE—array-type-name--AS--| data-type |--ARRAY--[---------->
.-2147483647-------.
>--+------------------+--]-------------------------------------><
'-integer-constant-'

>>-CREATE TYPE—array-type-name--AS--| data-type |--ARRAY--[---------->
.-2147483647-------.
>--+------------------+--]-------------------------------------><
'-integer-constant-'

數組類型的名稱應該用模式加以限定，并且在當前服務器上應該是惟一的。LONG VARCHAR、LONG VARGRPAHIC、XML 和用戶定義類型不能作為數組元素的數據類型。

下面是數組類型的例子：

Sql代碼

CREATE TYPE numbers as INTEGER ARRAY[100];
CREATE TYPE names as VARCHAR(30) ARRAY[];
CREATE TYPE MYSCHEMA.totalcomp as DECIMAL(12,2) ARRAY[];

CREATE TYPE numbers as INTEGER ARRAY[100];
CREATE TYPE names as VARCHAR(30) ARRAY[];
CREATE TYPE MYSCHEMA.totalcomp as DECIMAL(12,2) ARRAY[];

請注意，整數 “constant” 指定數組的最大基數，它是可選的。數組元素可以通過 ARRAY-VARIABLE(subindex) 來引用，其中 subindex 必須介于 1 到數組的基數之間。

現在可以在 SQL 過程中使用這個數據類型：

清單 3. 在過程中使用數組數據類型

Sql代碼

CREATE PROCEDURE PROC_VARRAY_test (out mynames names)
BEGIN
DECLARE v_pnumb numbers;
SET v_pnumb = ARRAY[1,2,3,5,7,11];
SET mynames(1) =’MARINA’;
…
END

CREATE PROCEDURE PROC_VARRAY_test (out mynames names)
BEGIN
DECLARE v_pnumb numbers;
SET v_pnumb = ARRAY[1,2,3,5,7,11];
SET mynames(1) =’MARINA’;
…
END

DB2 支持一些操作數組的方法。例如，函數 CARDINALITY(myarray) 返回一個數組中元素的個數。

賦值

SQL PL 提供了 SET 語句來為變量和數組元素賦值。

下面是一個 SET 語句的簡化的語法：

SET variable_name = value/expression/NULL;

這個變量名可以是一個本地變量、全局變量或數組元素的名稱。

下面是一些例子：

清單 4. SET 語句的例子

SET var1 = 10;
            SET total = (select sum(c1) from T1);
            SET var2 = POSSTR(‘MYTEST’,’TEST’);
            SET v_numb(10) = 20;  -- assign value of 20 to the 10^th
            element
            of the  array v_numb
            SET v_numb = ARRAY[1,2,3,4];  -- fill up array with values

為變量賦值的其他方法有：

VALUES INTO
            SELECT (or FETCH) INTO

下面的例子演示了這些方法的使用：

清單 5. VALUE INTO 和 SELECT INTO 的例子

VALUES 2 INTO v1;
            VALUES ‘TEST’ INTO var2;
            SELECT SUM(c1) INTO var1 FROM T1;
            SELECT POSSTR(‘MYTEST’,’TEST’) INTO v1 FROM SYSIBM.SYSDUMMY1;

專用寄存器

專用寄存器（special register） 是 DBA 定義的一個存儲塊，供一個應用程序過程使用。寄存器中的值可以在 SQL 語句或 SQL PL 語句中訪問和引用。在 IBM DB2 database for Linux, UNIX, and Windows Information Center 可以找到所有的專用寄存器（參見參考資料）。

最常用的專用寄存器有：

CURRENT DATE
CURRENT TIME
CURRENT TIMESTAMP
CURRENT USER
CURRENT PATH

所有這些寄存器都可以通過在名稱中加下劃線來引用。例如，CURRENT_DATE。

下面的過程返回當前日期和時間：

清單 6. 返回當前日期和時間的過程

CREATE PROCEDURE get_datetime (out cdate date, out ctime time )
            P1: BEGIN
            VALUES CURRENT DATE INTO cdate;
            VALUES CURRENT TIME INTO ctime;
            END P1

執行后，該過程返回：

Name	Input	Output
            cdate		 2008-08-28
            ctime		 13:47:41

有些專用寄存器的值可以通過 SET 語句來更新。例如，為了更新正在訪問的模式，需要像下面這樣更改專用寄存器 CURRENT SCHEMA。

SET CURRENT_SCHEMA = MYSCHEMA

若要更改默認函數路徑，則需要更新專用寄存器 CURRENT PATH。

游標

聲明

SQL PL 提供 DECLARE cursor 語句來定義一個游標，并提供其他語句來支持返回其他結果集和游標處理。

下面是游標聲明的語法：

清單 7. 游標聲明的語法

 >>-DECLARE--cursor-name
            --CURSOR---------->
            >--FOR--+-select-statement
            -+-------------><
               .-WITHOUT HOLD-.   
            |--+--------------+---------------------------------------------|
               '-WITH HOLD----'   
               .-WITHOUT RETURN-------------.   
            |--+----------------------------+-------------------------------|
               |              .-TO CALLER-. |   
               '-WITH RETURN--+-----------+-'
               
                                  '-TO CLIENT-'

Select-statement 是一條有效的 SQL SELECT 語句。可以指定 FOR UPDATE 子句，以便將游標用于定位更新或刪除。

WITHOUT HOLD/WITH HOLD 選項定義 COMMIT 操作之后的游標狀態（open/close）。默認情況下為 WITHOUT HOLD。如果使用了 WITH HOLD 選項定義一個游標，那么在 COMMIT 操作之后，該游標保持 OPEN 狀態。在 ROLLBACK 操作之后，所有游標都將被關閉。

下面是一個顯式聲明游標的例子，它可以用于過程中后面的迭代處理：

清單 8. 游標聲明的例子

DECLARE mycur1 CURSOR
            FOR SELECT e.empno, e.lastname, e.job
            FROM employee e, department d
            WHERE e.workdept = d.deptno
            AND deptname =’PLANNING’;

雖然 SQL 語句不能包含參數占位符，但是它可以引用在游標之前聲明的本地變量。例如：

清單 9. 使用本地變量的游標聲明

            DECLARE v_dept CHAR(3) DEAFULT ‘ ‘;
            DECLARE myres_set CURSOR
            FOR SELECT empno, lastname, job, salary, comm.
            FROM employee
            WHERE workdept = v_dept;

游標和結果集

在 SQL 過程中，除了迭代結果集中的行以外，游標還可以做更多的事情。游標還可用于將結果集返回給調用程序或其他過程。

WITHOUT RETURN/WITH return 選項指定游標的結果表是否用于作為從一個過程中返回的結果集。
WITH RETURN TO CALLER 選項指定將來自游標的結果集返回給調用者，后者可以是另一個過程或一個客戶機應用程序。這是默認選項。
WITH RETURN TO CLIENT 選項指定將來自游標的結果集返回給客戶機應用程序，繞過任何中間的嵌套過程。

若要從一個過程中返回結果集，需要：

創建一個過程，創建時指定 DYNAMIC RESULT SETS 子句。
聲明游標，聲明時指定 WITH RETURN 子句。
打開該游標，并使之保持 open 狀態。

如果關閉該游標，則結果集將不能返回給調用者應用程序。

清單 10 演示了一個游標的聲明，該游標從一個過程中返回一個結果集：

清單 10. 返回一個結果集的游標的聲明

CREATE PROCEDURE emp_from_dept()
            DYNAMIC RESULT SETS 1
            P1: BEGIN
            DECLARE c_emp_dept CURSOR WITH RETURN
            FOR SELECT empno, lastname, job, salary, comm.
            FROM employee
            WHERE workdept = ‘E21’;
            OPEN c_emp_dept;
            END P1

游標處理

為了在一個過程中處理一個游標的結果，需要做以下事情：

在存儲過程塊的開頭部分 DECLARE 游標。
打開該游標。
將游標的結果取出到之前已聲明的本地變量中（隱式游標處理除外，在下面的 FOR 語句中將對此加以解釋）。
關閉該游標。（注意：如果現在不關閉游標，當過程終止時將隱式地關閉游標）。

條件語句

SQL PL 中支持兩種類型的條件語句 — IF 語句和 CASE 語句。

IF 語句

通過 IF 語句可以根據一個條件的狀態來實現邏輯的分支。IF 語句支持使用可選的 ELSEIF 子句和默認的 ELSE 子句。END IF 子句是必需的，它用于表明 IF 語句的結束。

清單 11 展示了一個示例 IF 語句。

清單 11. IF 語句示例

   IF years_of_serv > 30 THEN
            SET gl_sal_increase = 15000;
            ELSEIF years_of_serv > 20 THEN
            SET gl_sal_increase = 12000;
            ELSE
            SET gl_sal_increase = 10000;
            END IF;

回頁首

CASE 語句

SQL PL 支持兩種類型的 CASE 語句，以根據一個條件的狀態實現邏輯的分支：

simple CASE 語句用于根據一個字面值進入某個邏輯。
searched CASE 語句用于根據一個表達式的值進入某個邏輯。

清單 12 顯示了使用 searched CASE 語句的一個存儲過程的例子。

清單 12. 使用 searched CASE 語句的存儲過程

CREATE PROCEDURE sal_increase_lim1 (empid CHAR(6))
            BEGIN
            DECLARE years_of_serv INT DEFAULT 0;
            DECLARE v_incr_rate DEC(9,2) DEFAULT 0.0;
            SELECT YEAR(CURRENT DATE) - YEAR(hiredate)
            INTO years_of_serv
            FROM empl1
            WHERE empno = empid;
            CASE
            WHEN  years_of_serv > 30 THEN
            SET v_incr_rate = 0.08;
            WHEN  years_of_serv > 20 THEN
            SET v_incr_rate = 0.07;
            WHEN  years_of_serv > 10 THEN
            SET v_incr_rate = 0.05;
            ELSE
            SET v_incr_rate = 0.04;
            END CASE;
            UPDATE empl1
            SET salary = salary+salary*v_incr_rate
            WHERE empno = empid;
            END

迭代語句

SQL PL 支持一些重復執行某個邏輯的方法，包括簡單的 LOOP、WHILE 循環、REPEAT 循環和 FOR 循環：

LOOP 循環 -- 簡單的循環
- L1: LOOP
- SQL statements;
- LEAVE L1;
- END LOOP L1;

WHILE 循環 -- 進入前檢查條件
- WHILE condition
- DO
- SQL statements
- END WHILE;

REPEAT 循環 -- 退出前檢查條件
- REPEAT
- SQL statements;
- UNTIL condition
- END REPEAT;

FOR 循環 -- 結果集上的隱式循環
- FOR loop_name AS
- SELECT … FROM
- DO
- SQL statements;
- END FOR;

請注意，FOR 語句不同于其他的迭代語句，因為它用于迭代一個定義好的結果集中的行。

為了演示這些循環技巧的使用，我們來編寫一個過程，該過程從一個 EMPLOYEE 表中獲取每個雇員的姓氏、工作年限和年齡，并將其插入到新表 REPORT_INFO_DEPT 中，這些信息分別被聲明為 lname varchar(15)、hiredate date 和 birthdate date。

請注意，使用一個簡單的 SQL 語句也可以做同樣的事情，但是在這個例子中我們使用 3 種不同的循環語句。

清單 13. 簡單的循環例子

CREATE PROCEDURE LEAVE_LOOP (DEPTIN char(3), OUT p_counter INTEGER)
            Ll: BEGIN
            DECLARE v_at_end , v_counter INTEGER DEFAULT 0;
            DECLARE v_lastname VARCHAR(15);
            DECLARE v_birthd, v_hired DATE;
            DECLARE c1 CURSOR
            FOR SELECT lastname, hiredate, birthdate FROM employee
            WHERE WORKDEPT = deptin;
            DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_at_end = 1;
            OPEN c1;
            FETCH_LOOP: LOOP
            FETCH c1 INTO v_lastname, v_hired, v_birthd;
            IF v_at_end <> 0 THEN    -- loop until last row of the cursor
            LEAVE FETCH_LOOP;
            END IF;
            SET v_counter = v_counter + 1;
            INSERT INTO REPORT_INFO_DEPT
            values(v_lastname, v_hired, v_birthd);
            END LOOP FETCH_LOOP;
            SET p_counter = v_counter;
            END Ll

現在，我們使用 WHILE 循環語句來做同樣的事情。

清單 14. WHILE 循環的例子

CREATE PROCEDURE DEPT_REPT (DEPTIN char(3), OUT p_counter INTEGER)
            Pl: BEGIN
            DECLARE v_at_end , v_counter INTEGER DEFAULT 0;
            DECLARE v_lastname VARCHAR(15);
            DECLARE v_birthd, v_hired DATE;
            DECLARE c1 CURSOR
            FOR SELECT lastname, hiredate, birthdate FROM employee
            WHERE WORKDEPT = deptin;
            DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_at_end = 1;
            OPEN c1;
            FETCH c1 INTO v_lastname, v_hired, v_birthd;
            WHILE (v_at_end = 0)
            DO
            INSERT INTO REPORT_INFO_DEPT
            values(v_lastname, v_hired, v_birthd);
            SET v_counter = v_counter + 1;
            FETCH c1 INTO v_lastname, v_hired, v_birthd;
            END WHILE;
            SET p_counter = v_counter;
            END P1

REPEAT 循環非常類似于 WHILE 循環，只不過條件是在最后檢查的（因此，它實際上是一個 UNTIL 循環）。

現在，我們使用包含 FOR 循環語句的一個過程來填充 REPORT_INFO_DEPT 表。

清單 15. FOR 循環的例子

CREATE PROCEDURE DEPT_REPT1 (DEPTIN char(3), OUT p_counter INT)
            P1:BEGIN
            DECLARE v_counter INT DEFAULT 0;
            FOR dept_loop AS
            SELECT lastname, hiredate, birthdate FROM employee
            WHERE WORKDEPT = deptin
            DO
            INSERT INTO REPORT_INFO_DEPT values
            (dept_loop.lastname,dept_loop.hiredate,dept_loop.birthdate);
            SET v_counter = v_counter + 1;
            END FOR;
            SET p_counter = v_counter;
            END P1

請注意，最后一個過程沒有打開游標、從游標中取數據或關閉游標 — 所有這些都是由 FOR 循環語句隱式進行的。而且，可以引用循環中隱式地獲取的值，使用循環名稱限定列（例如dept_loop.lastname ）— 而不必使用本地變量來存儲這些值。

異常處理機制

DECLARE 有名稱的條件

SQL PL 允許為給定的 SQLSTATE 聲明用戶命名的條件，以用于之后的錯誤處理。條件名稱在整個復合語句中必須是惟一的，并且只能在聲明它的復合語句中引用它。

清單 16 顯示了聲明一個有名稱的條件的語法。

清單 16. 聲明一個有名稱的條件的語法

|--DECLARE--condition-name
            --CONDITION--FOR---------------------->
                           .-VALUE-.                      
               .-SQLSTATE--+-------+-.                    
            >--+---------------------+--string-constant
            ---------------------|

下面是條件聲明的例子：

DECLARE FOREIGN_KEY_VIOLATION CONDITION FOR SQLSTATE ‘23503’;
            DECLARE overflow CONDITION FOR SQLSTATE '22003';

DECLARE 條件處理程序

如果發生一個錯誤，存儲過程的行為是根據條件處理程序來決定的。在一個存儲過程中，可以為一個普通的或有名稱的條件和特定的 SQLSTATE 聲明一個或多個條件處理程序。當一個 SQL 語句產生一個 SQLEXCEPTION 或 SQLWARNING（SQLCODE <> 0）時，控制被轉移到為一個聲明的處理程序中，以獲取普通的異常或特定的 SQLSTATE 值。

清單 17 顯示了受支持的處理程序聲明的語法。

清單 17. 處理程序聲明的語法

|--DECLARE--+-CONTINUE-+--HANDLER--FOR-------------------------->
                        +-EXIT-----+                 
                        '-UNDO-----'                 
            >--+-specific-condition-value
            -+--| SQL-procedure-statement |----|
               '-general-condition-value
            --'                                
            WHERE specific-condition-value
               .-,----------------------------------------.   
               V             .-VALUE-.                    |   
            |----+-SQLSTATE--+-------+--string-constant
            -+-+-----------------|
                 '-condition-name
            -----------------------'

下面是演示它如何工作的一些例子。在下面的過程中，如果 UPDATE 語句失敗，則控制被轉移到 EXIT 處理程序。結果，該過程被終止，但是它的輸出參數包含 SQLCODE 和 SQLSTATE 的值。

清單 18. 返回 SQLCODE 和 SQLSTATE 的處理程序的例子

CREATE PROCEDURE simple_error
            (IN new_job CHAR(8), IN p_empno CHAR(6),
            OUT p_state_out CHAR(5),OUT p_code_out INT)
            SPECIFIC simple_error1
            BEGIN
            DECLARE SQLCODE INT DEFAULT 0;
            DECLARE SQLSTATE CHAR(5) DEFAULT ‘00000’;
            DECLARE EXIT HANDLER FOR SQLEXCEPTION
            SELECT SQLSTATE, SQLCODE
            INTO p_sqlstate_out, p_sqlcode_out
            FROM SYSIBM.SYSDUMMY1;
            UPDATE EMPLOYEE
            SET job = new_job
            WHERE empno = p_empno;
            END

請注意，SQLCODE 和 SQLSTATE 應該被顯式地聲明為本地變量。

清單 19 演示了一個場景，在此場景中，當一個給定的錯誤發生時，不是退出過程，而是繼續某個動作。為了理解這個例子，請注意表 TAB1 被定義為（col1 int, col2 char(5)），在默認情況下，如果發生值溢出，DB2 不是截短一個值，而是產生 SQLSTATE ‘22001’。

清單 19. 具有 CONTINUE 處理程序的過程

CREATE PROCEDURE proc1 (IN num int, IN new_status varchar(10))
            P1: BEGIN
            DECLARE SQLCODE INTEGER default 0;
            DECLARE SQLSTATE CHAR(5) default ‘ ‘;
            DECLARE v_trunc INTEGER default 0;
            DECLARE overflow CONDITION FOR SQLSTATE '22001';
            DECLARE CONTINUE HANDLER FOR overflow
            BEGIN
            INSERT INTO tab1 VALUES (num, substr (new_sataus,1,5));
            SET v_trunc = 2;
            END;
            INSERT INTO tab1 VALUES(num, new_status);
            RETURN v_trunc;
            END P1

如果以 ‘Too many’ 作為輸入參數 new_status 的值調用這個過程，那么在 INSERT 語句執行期間會產生 SQLSTATE ‘22001’，控制被轉移到 CONDITION HANDLER。結果，v_trunc 指示符將被設置為 2，新行將被插入到 TAB1 表中，插入時對 COL2 列使用了截短后的值，該過程最終成功完成。

強制發出異常 -- SIGNAL SQLSTATE

SQL PL 支持發出一個錯誤或警告條件。這導致一個具有指定 SQLSTATE 的錯誤或警告被返回，同時返回的還有可選的消息文本。清單 20 顯示了 SIGNAL 語句的語法。

清單 20. SIGNAL 語句的語法

>>-SIGNAL------------------------------------------------------->
                           .-VALUE-.                                   
            >--+-SQLSTATE--+-------+--+-sqlstate-string-constant
            -+-+-------->
               |                      '-variable-name
            ------------' |   
               '-condition-name
            
            ------------------------------------'   
            >--+------------------------+----------------------------------><
              '|--+-SET MESSAGE_TEXT-- = --diagnostic-string-expression
            -+------|

可以以包含 5 個字符的字符串常量的形式發出一個用戶定義的 SQLSTATE。它必須以數字 7、8 或 9 或者字母 I 到 Z 開始。

還可以發出一個特定的條件名稱，但是必須在包含 SIGNAL 語句的復合語句中聲明它，如下面的清單所示。

DECLARE condition overflow for SQLSTATE ‘22001’;
            ….
            SIGNAL overflow SET MESSAGE_TEXT = ‘Too many characters, truncated’;

清單 21 演示了一個 SIGNAL 語句的使用。

清單 21. SIGNAL 語句的使用

CREATE PROCEDURE sign_test (IN num int, IN new_status varchar(10))
            P1: BEGIN
            DECLARE SQLCODE INTEGER default 0;
            DECLARE SQLSTATE CHAR(5) default '';
            IF length (new_status) > 5 THEN
            SIGNAL SQLSTATE '72001' SET MESSAGE_TEXT = 'INPUT VALUE TOO LONG';
            END IF;
            INSERT  INTO  TAB1 VALUES (num, new_status);
            END P1

在本教程中，您學習了用于編寫過程、用戶定義函數和觸發器的 SQL Procedural Language。您學習了 SQL Procedure Language 的所有基本要素，包括變量聲明和賦值、語法和使用以及用于控制過程邏輯的流程的條件語句和迭代語句。您還學習了如何使用錯誤處理和結果集。這使您能夠構建可集成到數據庫應用程序中的定制的、復雜的業務邏輯。

posted on 2011-04-18 17:33 luis 閱讀(534) 評論(0) 編輯收藏引用

只有注冊用戶登錄后才能發表評論。
【推薦】100%開源！大型工業跨平臺軟件C++源碼提供，建模，組態！



網站導航: 博客園 IT新聞 BlogJava 博問 Chat2DB 管理

2025年5月

日

一

二

三

四

五

六

常用鏈接

留言簿(3)

隨筆分類

隨筆檔案

文章分類

感悟！奮斗！(2)

文章檔案

2009年7月 (2)

友情鏈接

個人主頁
Yi Lu's Homepage UMass Amherst

源地址:DB2存儲過程-基礎詳解

常用鏈接

留言簿(3)

隨筆分類

隨筆檔案

文章分類

文章檔案

友情鏈接

搜索

最新評論

閱讀排行榜

評論排行榜