開始之前
關於本系列
這個包含 6 篇 DB2 SQL Procedure Developer 教程的系列涵蓋了 SQL Procedural Language（SQL PL）的所有基本結構和方法，並展示了如何在存儲過程、用戶定義函數和觸發器，包括錯誤處理和部署中使用 SQL PL。本系列還描述了一些 DB2 9.5 高級特性，例如樂觀鎖（optimistic locking）、分層查詢（hIErarchical query）和已聲明的全局臨時表（declared global temporary table）。此外，本系列還提供了關於如何調用存儲過程以及如何在過程和函數之間共享數據的具體內容。本系列還介紹了 DB2 開發工具，包括 IBM Data Studio。這些教程為該考試的每個部分都提供了堅實的基礎。但是，這些教程不應該作為准備該考試的惟一資料。
關於本教程
在本教程中，學習 DB2 9.5 SQL Procedural Language，包括變量、條件、處理程序聲明、控制流和迭代語句以及錯誤處理機制。本教程是一個包含 6 篇教程的系列中的第一篇教程，該系列可用於准備 DB2 9.5 SQL Procedure Developer 考試 735。
目標
在本教程中，您將：
學習 SQL PL 的基本要素。
理解如何聲明變量、條件和處理程序。
學習控制流語句。
學習游標處理和如何返回結果集。
理解錯誤處理機制。
先決條件
要參加 DB2 9.5 SQL Procedure Developer 考試，必須已經通過 DB2 9 Family Fundamentals 考試（Exam 730）。可以使用 “DB2 Family Fundamentals 教程系列” 准備那個考試（參見 參考資料）。它是一個非常受歡迎的系列，已經幫助很多人理解 DB2 產品家族的基本知識。
為了使用本教程，您應該擁有關系數據庫的工作原理以及數據庫和數據庫編程方面的背景知識。
系統需求
無需安裝 DB2 9.5 也可完成本教程。但是，為了充分利用本教程，您可以下載免費試用版的 DB2 Express-C 與本教程結合使用。
簡介
DB2 SQL Procedural Language（SQL PL）是 SQL Persistent Stored Module 語言標准的一個子集。該標准結合了 SQL 訪問數據的方便性和編程語言的流控制。通過 SQL PL 當前的語句集合和語言特性，可以用 SQL 開發綜合的、高級的程序，例如函數、存儲過程和觸發器。這樣便可以將業務邏輯封裝到易於維護的數據庫對象中，從而提高數據庫應用程序的性能。
SQL PL 支持本地和全局變量，包括聲明和賦值，還支持條件語句和迭代語句、控制語句的轉移、錯誤管理語句以及返回結果集的方法。這些話題將在本教程中討論。
變量聲明
SQL 過程允許使用本地變量賦予和獲取 SQL 值，以支持所有 SQL 邏輯。在 SQL 過程中，在代碼中使用本地變量之前要先進行聲明。
清單 1 中的圖演示了變量聲明的語法：
清單 1. 變量聲明的語法
.-,-----------------.
V |
|--DECLARE----SQL-variable-name-+------------------------------->
.-DEFAULT NULL------.
>--+-data-type--+-------------------+-+-------------------------|
| '-DEFAULT--constant-' |
SQL-variable-name 定義本地變量的名稱。該名稱不能與其他變量或參數名稱相同，也不能與列名相同。
圖 1 顯示了受支持的 DB2 數據類型：
圖 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. 創建數組數據類型的語法
>>-CREATE TYPE—array-type-name--AS--| data-type |--ARRAY--[---------->
.-2147483647-------.
>--+------------------+--]-------------------------------------><
'-integer-constant-'
數組類型的名稱應該用模式加以限定，並且在當前服務器上應該是惟一的。LONG VARCHAR、LONG VARGRPAHIC、XML 和用戶定義類型不能作為數組元素的數據類型。
下面是數組類型的例子：
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. 在過程中使用數組數據類型
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 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 定義的一個存儲塊，供一個應用程序過程使用。寄存器中的值可以在 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
現在，我們使用 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
