學(xué)習(xí) DB2 9.5 SQL Procedural Language，包括變量、條件、處理程序聲明、控制流和迭代語(yǔ)句以及錯(cuò)誤處理機(jī)制。
您將：
- 學(xué)習(xí) SQL PL 的基本要素。
- 理解如何聲明變量、條件和處理程序。
- 學(xué)習(xí)控制流語(yǔ)句。
- 學(xué)習(xí)游標(biāo)處理和如何返回結(jié)果集。
- 理解錯(cuò)誤處理機(jī)制。
簡(jiǎn)介
DB2 SQL Procedural Language（SQL PL）是 SQL Persistent Stored Module 語(yǔ)言標(biāo)準(zhǔn)的一個(gè)子集。該標(biāo)準(zhǔn)結(jié)合了 SQL 訪問(wèn)數(shù)據(jù)的方便性和編程語(yǔ)言的流控制。通過(guò) SQL PL 當(dāng)前的語(yǔ)句集合和語(yǔ)言特性，可以用 SQL 開(kāi)發(fā)綜合的、高級(jí)的程序，例如函數(shù)、存儲(chǔ)過(guò)程和觸發(fā)器。這樣便可以將業(yè)務(wù)邏輯封裝到易于維護(hù)的數(shù)據(jù)庫(kù)對(duì)象中，從而提高數(shù)據(jù)庫(kù)應(yīng)用程序的性能。
SQL PL 支持本地和全局變量，包括聲明和賦值，還支持條件語(yǔ)句和迭代語(yǔ)句、控制語(yǔ)句的轉(zhuǎn)移、錯(cuò)誤管理語(yǔ)句以及返回結(jié)果集的方法。這些話題將在本教程中討論。
變量聲明
SQL 過(guò)程允許使用本地變量賦予和獲取 SQL 值，以支持所有 SQL 邏輯。在 SQL 過(guò)程中，在代碼中使用本地變量之前要先進(jìn)行聲明。
清單 1 中的圖演示了變量聲明的語(yǔ)法：
清單 1. 變量聲明的語(yǔ)法
- .-,
- V |
- |
-
- .-DEFAULT NULL
- >
- | '-DEFAULT--constant-' |
-
.-,-----------------.
V |
|--DECLARE----SQL-variable-name-+------------------------------->
.-DEFAULT NULL------.
>--+-data-type--+-------------------+-+-------------------------|
| '-DEFAULT--constant-' |
SQL-variable-name 定義本地變量的名稱。該名稱不能與其他變量或參數(shù)名稱相同，也不能與列名相同。
圖 1 顯示了受支持的 DB2 數(shù)據(jù)類型：
DEFAULT 值 – 如果沒(méi)有指定，在聲明時(shí)將賦值為 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;
請(qǐng)注意，從 DB2 version 9.5 開(kāi)始才支持在一個(gè) DECLARE 語(yǔ)句中聲明多個(gè)相同數(shù)據(jù)類型的變量。
數(shù)組數(shù)據(jù)類型
SQL 過(guò)程從 9.5 版開(kāi)始支持?jǐn)?shù)組類型的變量和參數(shù)。要定義一個(gè)數(shù)組類型的變量，需要先在數(shù)據(jù)庫(kù)中創(chuàng)建該類型，然后在過(guò)程或應(yīng)用程序中聲明它。數(shù)組是臨時(shí)的值，可以在存儲(chǔ)過(guò)程和應(yīng)用程序中操縱它，但是不能將它存儲(chǔ)到表中。
DB2 支持以下創(chuàng)建數(shù)組數(shù)據(jù)類型的語(yǔ)法：
清單 2. 創(chuàng)建數(shù)組數(shù)據(jù)類型的語(yǔ)法
- >>-CREATE TYPE—array-type-name
-
- .-2147483647
- >
- '-integer-constant-'
>>-CREATE TYPE—array-type-name--AS--| data-type |--ARRAY--[---------->
.-2147483647-------.
>--+------------------+--]-------------------------------------><
'-integer-constant-'
數(shù)組類型的名稱應(yīng)該用模式加以限定，并且在當(dāng)前服務(wù)器上應(yīng)該是惟一的。LONG VARCHAR、LONG VARGRPAHIC、XML 和用戶定義類型不能作為數(shù)組元素的數(shù)據(jù)類型。
下面是數(shù)組類型的例子：
- 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[];
請(qǐng)注意，整數(shù) “constant” 指定數(shù)組的最大基數(shù)，它是可選的。數(shù)組元素可以通過(guò) ARRAY-VARIABLE(subindex) 來(lái)引用，其中 subindex 必須介于 1 到數(shù)組的基數(shù)之間。
現(xiàn)在可以在 SQL 過(guò)程中使用這個(gè)數(shù)據(jù)類型：
清單 3. 在過(guò)程中使用數(shù)組數(shù)據(jù)類型
- 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 支持一些操作數(shù)組的方法。例如，函數(shù) CARDINALITY(myarray) 返回一個(gè)數(shù)組中元素的個(gè)數(shù)。
賦值
SQL PL 提供了 SET 語(yǔ)句來(lái)為變量和數(shù)組元素賦值。
下面是一個(gè) SET 語(yǔ)句的簡(jiǎn)化的語(yǔ)法：
SET variable_name = value/expression/NULL;
|
這個(gè)變量名可以是一個(gè)本地變量、全局變量或數(shù)組元素的名稱。
下面是一些例子：
清單 4. SET 語(yǔ)句的例子
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 10th
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 定義的一個(gè)存儲(chǔ)塊，供一個(gè)應(yīng)用程序過(guò)程使用。寄存器中的值可以在 SQL 語(yǔ)句或 SQL PL 語(yǔ)句中訪問(wèn)和引用。在 IBM DB2 database for Linux, UNIX, and Windows Information Center 可以找到所有的專用寄存器（參見(jiàn) 參考資料 ）。
最常用的專用寄存器有：
- CURRENT DATE
- CURRENT TIME
- CURRENT TIMESTAMP
- CURRENT USER
- CURRENT PATH
所有這些寄存器都可以通過(guò)在名稱中加下劃線來(lái)引用。例如，CURRENT_DATE。
下面的過(guò)程返回當(dāng)前日期和時(shí)間：
清單 6. 返回當(dāng)前日期和時(shí)間的過(guò)程
CREATE PROCEDURE get_datetime (out cdate date, out ctime time )
P1: BEGIN
VALUES CURRENT DATE INTO cdate;
VALUES CURRENT TIME INTO ctime;
END P1
|
執(zhí)行后，該過(guò)程返回：
Name Input Output
cdate 2008-08-28
ctime 13:47:41
|
有些專用寄存器的值可以通過(guò) SET 語(yǔ)句來(lái)更新。例如，為了更新正在訪問(wèn)的模式，需要像下面這樣更改專用寄存器 CURRENT SCHEMA。
SET CURRENT_SCHEMA = MYSCHEMA
|
若要更改默認(rèn)函數(shù)路徑，則需要更新專用寄存器 CURRENT PATH。
游標(biāo)
聲明
SQL PL 提供 DECLARE cursor 語(yǔ)句來(lái)定義一個(gè)游標(biāo)，并提供其他語(yǔ)句來(lái)支持返回其他結(jié)果集和游標(biāo)處理。
下面是游標(biāo)聲明的語(yǔ)法：
清單 7. 游標(biāo)聲明的語(yǔ)法
>>-DECLARE--cursor-name
--CURSOR---------->
>--FOR--+-select-statement
-+-------------><
.-WITHOUT HOLD-.
|--+--------------+---------------------------------------------|
'-WITH HOLD----'
.-WITHOUT RETURN-------------.
|--+----------------------------+-------------------------------|
| .-TO CALLER-. |
'-WITH RETURN--+-----------+-'
'-TO CLIENT-'
|
Select-statement 是一條有效的 SQL SELECT 語(yǔ)句。可以指定 FOR UPDATE 子句，以便將游標(biāo)用于定位更新或刪除。
WITHOUT HOLD/WITH HOLD 選項(xiàng)定義 COMMIT 操作之后的游標(biāo)狀態(tài)（open/close）。默認(rèn)情況下為 WITHOUT HOLD。如果使用了 WITH HOLD 選項(xiàng)定義一個(gè)游標(biāo)，那么在 COMMIT 操作之后，該游標(biāo)保持 OPEN 狀態(tài)。在 ROLLBACK 操作之后，所有游標(biāo)都將被關(guān)閉。
下面是一個(gè)顯式聲明游標(biāo)的例子，它可以用于過(guò)程中后面的迭代處理：
清單 8. 游標(biāo)聲明的例子
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 語(yǔ)句不能包含參數(shù)占位符，但是它可以引用在游標(biāo)之前聲明的本地變量。例如：
清單 9. 使用本地變量的游標(biāo)聲明
DECLARE v_dept CHAR(3) DEAFULT ‘ ‘;
DECLARE myres_set CURSOR
FOR SELECT empno, lastname, job, salary, comm.
FROM employee
WHERE workdept = v_dept;
|
游標(biāo)和結(jié)果集
在 SQL 過(guò)程中，除了迭代結(jié)果集中的行以外，游標(biāo)還可以做更多的事情。游標(biāo)還可用于將結(jié)果集返回給調(diào)用程序或其他過(guò)程。
- WITHOUT RETURN/WITH return 選項(xiàng)指定游標(biāo)的結(jié)果表是否用于作為從一個(gè)過(guò)程中返回的結(jié)果集。
- WITH RETURN TO CALLER 選項(xiàng)指定將來(lái)自游標(biāo)的結(jié)果集返回給調(diào)用者，后者可以是另一個(gè)過(guò)程或一個(gè)客戶機(jī)應(yīng)用程序。這是默認(rèn)選項(xiàng)。
- WITH RETURN TO CLIENT 選項(xiàng)指定將來(lái)自游標(biāo)的結(jié)果集返回給客戶機(jī)應(yīng)用程序，繞過(guò)任何中間的嵌套過(guò)程。
若要從一個(gè)過(guò)程中返回結(jié)果集，需要：
- 創(chuàng)建一個(gè)過(guò)程，創(chuàng)建時(shí)指定 DYNAMIC RESULT SETS 子句。
- 聲明游標(biāo)，聲明時(shí)指定 WITH RETURN 子句。
- 打開(kāi)該游標(biāo)，并使之保持 open 狀態(tài)。
如果關(guān)閉該游標(biāo)，則結(jié)果集將不能返回給調(diào)用者應(yīng)用程序。
清單 10 演示了一個(gè)游標(biāo)的聲明，該游標(biāo)從一個(gè)過(guò)程中返回一個(gè)結(jié)果集：
清單 10. 返回一個(gè)結(jié)果集的游標(biāo)的聲明
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
|
游標(biāo)處理
為了在一個(gè)過(guò)程中處理一個(gè)游標(biāo)的結(jié)果，需要做以下事情：
- 在存儲(chǔ)過(guò)程塊的開(kāi)頭部分 DECLARE 游標(biāo)。
- 打開(kāi)該游標(biāo)。
- 將游標(biāo)的結(jié)果取出到之前已聲明的本地變量中（隱式游標(biāo)處理除外，在下面的 FOR 語(yǔ)句中將對(duì)此加以解釋）。
- 關(guān)閉該游標(biāo)。（注意：如果現(xiàn)在不關(guān)閉游標(biāo)，當(dāng)過(guò)程終止時(shí)將隱式地關(guān)閉游標(biāo)）。
條件語(yǔ)句
SQL PL 中支持兩種類型的條件語(yǔ)句 — IF 語(yǔ)句和 CASE 語(yǔ)句。
IF 語(yǔ)句
通過(guò) IF 語(yǔ)句可以根據(jù)一個(gè)條件的狀態(tài)來(lái)實(shí)現(xiàn)邏輯的分支。IF 語(yǔ)句支持使用可選的 ELSEIF 子句和默認(rèn)的 ELSE 子句。END IF 子句是必需的，它用于表明 IF 語(yǔ)句的結(jié)束。
清單 11 展示了一個(gè)示例 IF 語(yǔ)句。
清單 11. IF 語(yǔ)句示例
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 語(yǔ)句
SQL PL 支持兩種類型的 CASE 語(yǔ)句，以根據(jù)一個(gè)條件的狀態(tài)實(shí)現(xiàn)邏輯的分支：
- simple CASE 語(yǔ)句用于根據(jù)一個(gè)字面值進(jìn)入某個(gè)邏輯。
- searched CASE 語(yǔ)句用于根據(jù)一個(gè)表達(dá)式的值進(jìn)入某個(gè)邏輯。
清單 12 顯示了使用 searched CASE 語(yǔ)句的一個(gè)存儲(chǔ)過(guò)程的例子。
清單 12. 使用 searched CASE 語(yǔ)句的存儲(chǔ)過(guò)程
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
|
迭代語(yǔ)句
SQL PL 支持一些重復(fù)執(zhí)行某個(gè)邏輯的方法，包括簡(jiǎn)單的 LOOP、WHILE 循環(huán)、REPEAT 循環(huán)和 FOR 循環(huán)：
- LOOP 循環(huán) -- 簡(jiǎn)單的循環(huán)
- L1: LOOP
- SQL statements;
- LEAVE L1;
- END LOOP L1;
- WHILE 循環(huán) -- 進(jìn)入前檢查條件
- WHILE condition
- DO
- SQL statements
- END WHILE;
- REPEAT 循環(huán) -- 退出前檢查條件
- REPEAT
- SQL statements;
- UNTIL condition
- END REPEAT;
- FOR 循環(huán) -- 結(jié)果集上的隱式循環(huán)
- FOR loop_name AS
- SELECT … FROM
- DO
- SQL statements;
- END FOR;
請(qǐng)注意，F(xiàn)OR 語(yǔ)句不同于其他的迭代語(yǔ)句，因?yàn)樗糜诘粋€(gè)定義好的結(jié)果集中的行。
為了演示這些循環(huán)技巧的使用，我們來(lái)編寫(xiě)一個(gè)過(guò)程，該過(guò)程從一個(gè) EMPLOYEE 表中獲取每個(gè)雇員的姓氏、工作年限和年齡，并將其插入到新表 REPORT_INFO_DEPT 中，這些信息分別被聲明為 lname varchar(15)、hiredate date 和 birthdate date。
請(qǐng)注意，使用一個(gè)簡(jiǎn)單的 SQL 語(yǔ)句也可以做同樣的事情，但是在這個(gè)例子中我們使用 3 種不同的循環(huán)語(yǔ)句。
清單 13. 簡(jiǎn)單的循環(huán)例子
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
|
現(xiàn)在，我們使用 WHILE 循環(huán)語(yǔ)句來(lái)做同樣的事情。
清單 14. WHILE 循環(huán)的例子
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 循環(huán)非常類似于 WHILE 循環(huán)，只不過(guò)條件是在最后檢查的（因此，它實(shí)際上是一個(gè) UNTIL 循環(huán)）。
現(xiàn)在，我們使用包含 FOR 循環(huán)語(yǔ)句的一個(gè)過(guò)程來(lái)填充 REPORT_INFO_DEPT 表。
清單 15. FOR 循環(huán)的例子
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
|
請(qǐng)注意，最后一個(gè)過(guò)程沒(méi)有打開(kāi)游標(biāo)、從游標(biāo)中取數(shù)據(jù)或關(guān)閉游標(biāo) — 所有這些都是由 FOR 循環(huán)語(yǔ)句隱式進(jìn)行的。而且，可以引用循環(huán)中隱式地獲取的值，使用循環(huán)名稱限定列（例如dept_loop.lastname ）— 而不必使用本地變量來(lái)存儲(chǔ)這些值。
異常處理機(jī)制
DECLARE 有名稱的條件
SQL PL 允許為給定的 SQLSTATE 聲明用戶命名的條件，以用于之后的錯(cuò)誤處理。條件名稱在整個(gè)復(fù)合語(yǔ)句中必須是惟一的，并且只能在聲明它的復(fù)合語(yǔ)句中引用它。
清單 16 顯示了聲明一個(gè)有名稱的條件的語(yǔ)法。
清單 16. 聲明一個(gè)有名稱的條件的語(yǔ)法
|--DECLARE--condition-name
--CONDITION--FOR---------------------->
.-VALUE-.
.-SQLSTATE--+-------+-.
>--+---------------------+--string-constant
---------------------|
|
下面是條件聲明的例子：
DECLARE FOREIGN_KEY_VIOLATION CONDITION FOR SQLSTATE ‘23503’;
DECLARE overflow CONDITION FOR SQLSTATE '22003';
|
DECLARE 條件處理程序
如果發(fā)生一個(gè)錯(cuò)誤，存儲(chǔ)過(guò)程的行為是根據(jù)條件處理程序來(lái)決定的。在一個(gè)存儲(chǔ)過(guò)程中，可以為一個(gè)普通的或有名稱的條件和特定的 SQLSTATE 聲明一個(gè)或多個(gè)條件處理程序。當(dāng)一個(gè) SQL 語(yǔ)句產(chǎn)生一個(gè) SQLEXCEPTION 或 SQLWARNING（SQLCODE <> 0）時(shí)，控制被轉(zhuǎn)移到為一個(gè)聲明的處理程序中，以獲取普通的異常或特定的 SQLSTATE 值。
清單 17 顯示了受支持的處理程序聲明的語(yǔ)法。
清單 17. 處理程序聲明的語(yǔ)法
|--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
-----------------------'
|
下面是演示它如何工作的一些例子。在下面的過(guò)程中，如果 UPDATE 語(yǔ)句失敗，則控制被轉(zhuǎn)移到 EXIT 處理程序。結(jié)果，該過(guò)程被終止，但是它的輸出參數(shù)包含 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
|
請(qǐng)注意，SQLCODE 和 SQLSTATE 應(yīng)該被顯式地聲明為本地變量。
清單 19 演示了一個(gè)場(chǎng)景，在此場(chǎng)景中，當(dāng)一個(gè)給定的錯(cuò)誤發(fā)生時(shí)，不是退出過(guò)程，而是繼續(xù)某個(gè)動(dòng)作。為了理解這個(gè)例子，請(qǐng)注意表 TAB1 被定義為（col1 int, col2 char(5)），在默認(rèn)情況下，如果發(fā)生值溢出，DB2 不是截短一個(gè)值，而是產(chǎn)生 SQLSTATE ‘22001’。
清單 19. 具有 CONTINUE 處理程序的過(guò)程
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’ 作為輸入?yún)?shù) new_status 的值調(diào)用這個(gè)過(guò)程，那么在 INSERT 語(yǔ)句執(zhí)行期間會(huì)產(chǎn)生 SQLSTATE ‘22001’，控制被轉(zhuǎn)移到 CONDITION HANDLER。結(jié)果，v_trunc 指示符將被設(shè)置為 2，新行將被插入到 TAB1 表中，插入時(shí)對(duì) COL2 列使用了截短后的值，該過(guò)程最終成功完成。
強(qiáng)制發(fā)出異常 -- SIGNAL SQLSTATE
SQL PL 支持發(fā)出一個(gè)錯(cuò)誤或警告條件。這導(dǎo)致一個(gè)具有指定 SQLSTATE 的錯(cuò)誤或警告被返回，同時(shí)返回的還有可選的消息文本。清單 20 顯示了 SIGNAL 語(yǔ)句的語(yǔ)法。
清單 20. SIGNAL 語(yǔ)句的語(yǔ)法
>>-SIGNAL------------------------------------------------------->
.-VALUE-.
>--+-SQLSTATE--+-------+--+-sqlstate-string-constant
-+-+-------->
| '-variable-name
------------' |
'-condition-name
------------------------------------'
>--+------------------------+----------------------------------><
'|--+-SET MESSAGE_TEXT-- = --diagnostic-string-expression
-+------|
|
可以以包含 5 個(gè)字符的字符串常量的形式發(fā)出一個(gè)用戶定義的 SQLSTATE。它必須以數(shù)字 7、8 或 9 或者字母 I 到 Z 開(kāi)始。
還可以發(fā)出一個(gè)特定的條件名稱，但是必須在包含 SIGNAL 語(yǔ)句的復(fù)合語(yǔ)句中聲明它，如下面的清單所示。
DECLARE condition overflow for SQLSTATE ‘22001’;
….
SIGNAL overflow SET MESSAGE_TEXT = ‘Too many characters, truncated’;
|
清單 21 演示了一個(gè) SIGNAL 語(yǔ)句的使用。
清單 21. SIGNAL 語(yǔ)句的使用
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
|
在本教程中，您學(xué)習(xí)了用于編寫(xiě)過(guò)程、用戶定義函數(shù)和觸發(fā)器的 SQL Procedural Language。您學(xué)習(xí)了 SQL Procedure Language 的所有基本要素，包括變量聲明和賦值、語(yǔ)法和使用以及用于控制過(guò)程邏輯的流程的條件語(yǔ)句和迭代語(yǔ)句。您還學(xué)習(xí)了如何使用錯(cuò)誤處理和結(jié)果集。這使您能夠構(gòu)建可集成到數(shù)據(jù)庫(kù)應(yīng)用程序中的定制的、復(fù)雜的業(yè)務(wù)邏輯。