複合 SQL (已編譯) 陳述式

¹ The FOR BIT DATA clause can be specified in any order with the other column constraints that follow. The FOR BIT DATA clause cannot be specified with string units CODEUNITS32 (SQLSTATE 42613).
² If data-type4 specifies a CURSOR built-in type or cursor-type-name, only NULL or cursor-value-constructor can be specified. Only DEFAULT NULL can be explicitly specified for array-type-name or row-type-name .
³ Only DEFAULT NULL can be explicitly specified for array-type-name.
⁴ Only DEFAULT NULL can be explicitly specified for row-type-name.
⁵ statement-name cannot be specified if cursor-parameter-declaration is specified.

說明

標籤定義程式碼區塊的標籤。如果指定開始標籤，則可以使用它來限定在複合陳述式中宣告的 SQL 變數，也可以在 LEAVE 陳述式上指定。如果指定結束標籤，則它必須與開始標籤相同。

ATOMIC 或 NOT ATOMIC

ATOMIC 指出如果複合陳述式中發生無法處理的異常狀況，則複合陳述式中的所有 SQL 陳述式都會回復。

Not ATOMIC 指出複合陳述式內未處理的異常狀況不會導致複合陳述式回復。

如果在動態準備的複合陳述式或不在模組內的 SQL 函數中指定 ATOMIC 關鍵字，則複合陳述式會作為複合 SQL (行內) 陳述式來處理。

在模組表格函數的函數主體中使用的複合陳述式只能定義為 NOT ATOMIC。

類型宣告

宣告複合陳述式本端的使用者定義資料類型。

指定本端使用者定義資料類型的名稱。名稱不能與現行複合陳述式中所宣告的任何其他類型相同 (SQLSTATE 42734)。不完整的 type-name 具有任何 CREATE TYPE 陳述式中說明的相同限制 (SQLSTATE 42939)。
陣列類型定義: 指定要與 type-name相關聯的陣列資料類型的屬性。如需語法元素的說明，請參閱 CREATE TYPE (陣列) 。 row-type-name 可以參照先前宣告且在現行複合 SQL (已編譯) 陳述式範圍內的宣告列類型。在錨定資料類型子句中指定的 variable-name 可以參照現行複合 SQL (已編譯) 陳述式範圍內的區域變數。
distinct-type-definition: 指定要與 type-name相關聯的弱類型化特殊類型的來源類型及選用資料類型限制。如需語法元素的完整說明，請參閱 "CREATE TYPE (distinct)"。在錨定-非橫列-data-type 子句中指定的 variable-name 可以參照現行複合 SQL (已編譯) 陳述式範圍內的區域變數。錨點 variable-name 或 column-name 的資料類型必須是內建資料類型。
列類型定義: 指定要與 type-name相關聯之列資料類型的欄位。如需語法元素的完整說明，請參閱 CREATE TYPE (列) 。在錨定-non-row-data-type 或錨定-row-data-type 子句中指定的 variable-name 可以參照現行複合 SQL (已編譯) 陳述式範圍內的區域變數。

SQL 變數宣告宣告複合陳述式本端的變數。

SQL 變數名稱定義區域變數的名稱。所有 SQL 變數名稱都會轉換為大寫。名稱不能與相同複合陳述式中的另一個 SQL 變數相同，也不能與參數名稱相同。 SQL 變數名稱不得與直欄名稱相同。如果 SQL 陳述式包含名稱與 SQL 變數及直欄參照相同的 ID ，則會將該 ID 解譯為直欄。如果在其中宣告變數的複合陳述式具有標籤，則可以使用標籤來限定變數的參照。例如，在具有標籤 C 的複合陳述式中宣告的變數 V 可以稱為 C.V.。

data-type4指定變數的資料類型。無法指定結構化類型或參照類型 (SQLSTATE 429BB)。

built-in-type指定內建資料類型。如需每一個內建資料類型的更完整說明 (但無法針對表格指定的 BOOLEAN 及 CURSOR 除外) ，請參閱 "CREATE TABLE"。在觸發程式、函數或獨立式陳述式 (SQLSTATE 429BB) 中使用的複合 SQL (已編譯) 陳述式中無法指定 XML 資料類型。在 SQL 程序主體中使用複合 SQL (已編譯) 陳述式時，可以指定 XML 資料類型。

BOOLEAN: 若為布林。
CURSOR: 若為游標。

錨定資料類型識別另一個物件，用來決定 SQL 變數的資料類型。錨點物件的資料類型具有相同的限制，適用於直接指定資料類型，或如果是列，則適用於建立列類型。

錨點資料類型至

指出使用錨點資料類型來指定資料類型。

variable-name識別 SQL 變數、SQL 參數或廣域變數。所參照變數的資料類型用作 SQL variable-name的資料類型。

表格名稱. 直欄名稱識別現有表格或視圖的直欄名稱。直欄的資料類型用作 SQL-variable-name的資料類型。

列表格名稱或視圖名稱

指定一列欄位，其名稱及資料類型基於 table-name 所識別表格或 view-name所識別視圖的直欄名稱及直欄資料類型。 SQL-variable-name 的資料類型是未命名的列類型。

列/ 游標變數名稱

指定一列欄位，其名稱及資料類型基於 cursor-variable-name所識別之游標變數的欄位名稱及欄位資料類型。指定的游標變數必須是下列其中一個元素 (SQLSTATE 428HS):

具有高度類型化游標資料類型的 SQL 變數或廣域變數
具有弱類型化游標資料類型的 SQL 變數或廣域變數，該變數已使用 CONSTANT 子句建立或宣告，並在其中指定所有結果直欄的 select-statement 。

如果游標變數的游標類型未使用具名列類型強烈 rtype ，則 SQL-variable-name 的資料類型是未命名的列類型。

陣列類型名稱

指定使用者定義陣列類型的名稱。陣列資料類型可以是本端宣告的資料類型、綱目資料類型或模組資料類型。

cursor-type-name

指定游標類型的名稱。游標資料類型可以是綱目資料類型或模組資料類型。

distinct-type-name

指定特殊類型的名稱。特殊資料類型可以是綱目資料類型或模組資料類型。宣告變數的長度、精準度及小數位數分別為特殊類型之來源類型的長度、精準度及小數位數。

row-type-name

指定使用者定義列類型的名稱。列資料類型可以是本端宣告的資料類型、綱目資料類型或模組資料類型。變數的欄位是列類型的欄位。

DEFAULT 或 CONSTANT

指定參照複合 SQL (已編譯) 陳述式時 SQL 變數的值。如果都未指定，則 SQL 變數的預設值為空值。如果指定 array-type-name 或 row-type-name ，則只能明確指定 DEFAULT NULL。

預設

定義 SQL 變數的預設值。當參照複合 SQL (已編譯) 陳述式時，會起始設定變數。預設值必須與變數的資料類型指派相容。

常數

指定 SQL 變數具有無法變更的固定值。使用 CONSTANT 定義的 SQL 變數無法用作任何指派作業的目標。固定值必須與變數的資料類型指派相容。

NULL

指定 NULL 作為 SQL 變數的預設值。

常數指定常數作為 SQL 變數的預設值。如果 data-type4 指定 CURSOR 內建類型或游標類型名稱，則無法指定常數 (SQLSTATE 42601)。

cursor-value-constructorcursor-value-constructor 指定與 SQL 變數相關聯的 select-statement 。將 cursor-value-constructor 指派給游標變數，會定義該游標變數的基礎游標。

cursor-parameter-declaration指定游標的輸入參數，包括每一個參數的名稱及資料類型。只有在 cursor-value-constructor (SQLSTATE 428HU) 中也指定 select-statement 時，才能指定具名輸入參數。

參數名稱將 cursor 參數命名為 select-statement內的 SQL 變數。名稱不能與游標的任何其他參數名稱相同。也應該選擇名稱以避免在 select-statement中使用任何直欄名稱，因為在參數名稱之前會先解析直欄名稱。

data-type5指定在 select-statement內使用的游標參數的資料類型。無法指定結構化類型及參照類型 (SQLSTATE 429BB)。

built-in-type

指定內建資料類型。如需每一種內建資料類型的更完整說明，請參閱 "CREATE TABLE"。無法指定 BOOLEAN 及 CURSOR 內建類型 (SQLSTATE 429BB)。

錨定-非列資料類型識別另一個物件，用來決定游標參數的資料類型。錨點物件的資料類型具有適用於直接指定資料類型的相同限制。

錨點資料類型至

指出使用錨點資料類型來指定資料類型。

variable-name

識別區域 SQL 變數、SQL 參數或廣域變數。所參照變數的資料類型用作游標參數的資料類型。

表格名稱. 直欄名稱

識別現有表格或視圖的直欄名稱。直欄的資料類型用作游標參數的資料類型。

distinct-type-name

指定特殊類型的名稱。如果指定 distinct-type-name 但沒有綱目名稱，則會透過在 SQL 路徑中搜尋綱目來解析該特殊類型。

保留能力指定是否因為確定作業而無法關閉游標。如需相關資訊，請參閱「宣告游標」。預設值是 WITHOut HOLE。

沒有保留: 不會因為確定作業而導致游標關閉。
WITH HOLD: 維護多個工作單元的資源。防止因確定作業而關閉游標。

select 陳述式指定游標的 SELECT 陳述式。如需相關資訊，請參閱 "select-statement"。如果 cursor-value-constructor中包含 cursor-parameter-declaration ，則 select 陳述式不得包含任何區域 SQL 變數或常式 SQL 參數 (SQLSTATE 42704)。

陳述式名稱指定游標的備妥 select-statement 。如需備妥陳述式的說明，請參閱 "PREPARE"。目標游標變數不得具有高度 rtype 使用者定義的游標類型 (SQLSTATE 428HU)。如果指定 statement-name ，則不得在 cursor-value-constructor 中指定具名輸入參數 (SQLSTATE 428HU)。

RESULT_SET_LOCATOR VARYING

指定結果集定位器變數的資料類型。

條件宣告宣告條件名稱具有選用關聯 SQLSTATE 值。

指定條件的名稱。條件名稱在其宣告所在的複合陳述式中必須是唯一的，排除該複合陳述式內巢狀的複合陳述式中的任何宣告 (SQLSTATE 42734)。條件名稱只能在宣告它的複合陳述式內參照，包括該複合陳述式內巢狀的任何複合陳述式 (SQLSTATE 42737)。
SQLSTATE 值的條件字串常數: 指定與條件相關聯的 SQLSTATE。字串常數必須指定為以單引號括住的五個字元，且 SQLSTATE 類別 (前兩個字元) 不能是 '00'。如果未指定此子句，則條件沒有相關聯的 SQLSTATE 值。

陳述式-宣告宣告複合陳述式本端的一或多個名稱清單。 statement-name 中的每一個名稱不得與相同複合陳述式中宣告的任何其他陳述式名稱相同。

return-codes-declaration宣告稱為 SQLSTATE 及 SQLCODE 的特殊變數，這些變數會自動設為處理 SQL 陳述式之後所傳回的值。 SQLSTATE 及 SQLCODE 變數只能在最外層複合陳述式當有巢狀複合 SQL (已編譯) 陳述式時; 例如在 SQL 程序主體中宣告。每個 SQL 程序只能宣告這些變數一次。

declare-cursor-statement在程序主體中宣告內建游標。使用 SQL-variable-declaration 陳述式來宣告使用者定義游標資料類型的變數。

每一個宣告游標在宣告它的複合陳述式中必須具有唯一名稱，不包括該複合陳述式內巢狀的複合陳述式中的任何宣告 (SQLSTATE 42734)。游標只能從宣告它的複合陳述式內參照，包括該複合陳述式內巢狀的任何複合陳述式 (SQLSTATE 34000)。

請使用 OPEN 陳述式來開啟游標，並使用 FETCH 陳述式來讀取使用游標的列。若要將結果集從 SQL 程序傳回至用戶端應用程式，必須使用 WITH RETURN 子句來宣告游標。下列範例會傳回一個結果集給用戶端應用程式:

   CREATE PROCEDURE RESULT_SET()
     LANGUAGE SQL
     RESULT SETS 1
     BEGIN
       DECLARE C1 CURSOR WITH RETURN FOR
         SELECT id, name, dept, job
           FROM staff;
       OPEN C1;
   END

附註: 若要處理結果集，您必須使用其中一個 Db2® 呼叫層次介面 (Db2 呼叫層次介面)、開放式資料庫連線功能 (ODBC)、Java™ 資料庫連線功能 (JDBC) 或內嵌式 SQL for Java (SQLJ) 應用程式設計介面。

如需宣告游標的相關資訊，請參閱「宣告游標」。

程序宣告

宣告複合陳述式的本端程序。本端程序的定義不包括 CREATE PROCEDURE (SQL) 陳述式中任何可能選項的規格。選項預設為 CREATE PROCEDURE (SQL) 陳述式的選項，但 MODIFIES SQL DATA 除外。程序的資料存取層次會自動判定為處理 SQL 程序主體所需的最低層次。

procedure-name

定義本端程序的名稱。必須指定沒有任何資格的名稱 (SQLSTATE 42601)。在現行複合陳述式中，由 procedure-name 及宣告參數數目組成的程序簽章必須是唯一的。在其中巢狀現行複合陳述式的外部複合陳述式不能包含具有相同名稱的程序。

參數宣告

指定本端程序的參數。如需語法元素的說明，請參閱 CREATE PROCEDURE (SQL) 。參數資料類型可以是現行複合陳述式範圍內本端宣告的資料類型。

SQL-procedure-body

指定作為 SQL 程序主體的 SQL 陳述式。 SQL-procedure-body 中參照的名稱可以參照先前宣告且在宣告本端程序之複合陳述式範圍內的已宣告物件 (例如已宣告變數、資料類型及程序)。

handler-declaration指定 處理程式，以及在複合陳述式中發生異常狀況或完成狀況時要執行的一組一個以上 SQL-procedure-statements 。SQL-procedure-statement 是處理程式接收控制時執行的陳述式。

對於在其中宣告處理程式之複合陳述式 (包括任何巢狀複合陳述式) 內的 處理程式宣告 集之後的執行的持續時間 SQL-procedure-statements 集，處理程式被認為是作用中的。

條件處理程式有三種類型:

繼續: 順利呼叫處理程式之後，會將控制項傳回給 SQL 陳述式，該陳述式接在引發異常狀況的陳述式之後。如果引發異常狀況的錯誤是 FOR、IF、 CASE、WHILE 或 REPEAT 陳述式 (但不是其中之一內的 SQL-procedure-陳述式) ，則控制會傳回 END FOR、END IF、END CASE、END WHILE 或 END REPEAT 之後的陳述式。
EXIT: 順利呼叫處理程式之後，會將控制項傳回宣告處理程式的複合陳述式結尾。
復原: 在呼叫處理程式之前，會回復在複合陳述式中所做的任何 SQL 變更。順利呼叫處理程式之後，會將控制項傳回宣告處理程式的複合陳述式結尾。如果指定 UNDO ，則宣告處理程式的複合陳述式必須是 ATOMIC。

導致啟動處理程式的條件定義在處理程式宣告中，如下所示:

specific-condition-value指定處理程式是 特定條件處理程式。

SQLSTATE VALUEstring-constant: 指定呼叫處理程式的 SQLSTATE。 SQLSTATE 值的前兩個字元不能是 '00'。; 指定要呼叫處理程式的條件名稱。條件名稱必須先前定義在條件宣告中，或它必須識別存在於現行伺服器的條件。

一般條件值指定處理程式是 一般條件處理程式。

SQLEXCEPTION: 指定在發生異常狀況時呼叫處理程式。異常狀況由 SQLSTATE 值代表，其前兩個字元不是 '00'、'01' 或 '02'。
SQLWARNING: 指定在發生警告狀況時呼叫處理程式。警告狀況由 SQLSTATE 值代表，其前兩個字元為 '01'。
找不到: 指定在發生 NOT FOUND 條件時呼叫處理程式。 NOT FOUND 條件由前兩個字元為 '02' 的 SQLSTATE 值表示。

SQL-procedure-statement指定 SQL 程序陳述式。

標籤

指定 SQL 程序陳述式的標籤。在 SQL 程序陳述式清單中，標籤必須是唯一的，包括清單內巢狀的任何複合陳述式。請注意，非巢狀的複合陳述式可以使用相同的標籤。 SQL 程序陳述式清單可在許多 SQL 控制陳述式中使用。

SQL 陳述式

所有可執行的 SQL 陳述式，但下列項目除外:

ALTER
CONNECT
建立
DESCRIBE
DISCONNECT
DROP
清除事件監視器
FREE LOCATOR
GRANT
REFRESH TABLE
RELEASE (僅限連線)
RENAME TABLE
RENAME TABLESPACE
REVOKE
SET CONNECTION
SET INTEGRITY
SET PASSTHRU
SET SERVER OPTION
TRANSFER OWNERSHIP

下列可執行陳述式在獨立式複合 SQL (已編譯) 陳述式中不受支援，但在 SQL 函數、SQL 程序或觸發程式內使用的複合 SQL (已編譯) 陳述式中受支援:

建立索引、表格或視圖
DECLARE GLOBAL TEMPORARY TABLE
DROP (DROP) 索引、表格或視圖
GRANT
ROLLBACK

在獨立式複合 SQL (已編譯) 陳述式內呼叫的任何巢狀陳述式中，也不支援 ROLLBACK 陳述式。

在複合 SQL (已編譯) 陳述式中支援下列陳述式 (非可執行陳述式):

ALLOCATE CURSOR
ASSOCIATE LOCATORS

規則

ATOMIC 複合陳述式不能為巢狀。
下列規則適用於處理程式宣告:
- 處理程式宣告不能多次包含相同的 condition-name 或 SQLSTATE 值，也不能包含代表相同 SQLSTATE 值的 condition-name 。
- 其中在複合陳述式中宣告兩個以上條件處理程式:
  - 沒有兩個處理程式宣告可以指定相同的一般條件種類 (SQLEXCEPTION、SQLWARNING、NOT FOUND)。
  - 沒有兩個處理程式宣告可以指定相同的特定條件作為 SQLSTATE 值或代表相同值的 condition-name 。
- 當處理程式是異常狀況或完成條件最適當的處理程式時，即會啟動處理程式。最適當的處理程式是根據下列考量來決定:
  - 處理程式宣告 H 的範圍是 SQL-procedure-statement 清單，它遵循出現 H 的複合陳述式內所包含的處理程式宣告。這表示 H 的範圍不包括條件處理程式 H主體中包含的陳述式，暗示條件處理程式無法處理其本身主體內產生的條件。同樣地，對於在相同複合陳述式中宣告的任何兩個處理程式 H1 及 H2 ， H1 將不會處理在 H2及 H2 的主體將不會處理 H1主體中產生的條件。
  - 內部範圍中宣告的 specific-condition-value 或 general-condition-value C 的處理程式優先於含括範圍中宣告的 C 的另一個處理程式。
  - 當在相同範圍中宣告條件 C 的特定處理程式，以及也會處理 C 的一般處理程式時，特定處理程式優先於一般處理程式。
  - 在相同範圍中宣告沒有相關聯 SQLSTATE 值之模組條件的處理程式及 SQLSTATE 45000 的處理程式時，模組條件的處理程式優先於 SQLSTATE 45000 的處理程式。
  如果發生沒有適當處理程式的異常狀況，則包含失敗陳述式的 SQL 程序會因無法處理的異常狀況而終止。如果發生沒有適當處理程式的完成狀況，則會繼續執行下一個 SQL 陳述式。
在進行確定或回復作業之後，不支援在 SQL 程序中參照資料類型 XML 的變數或參數，而不先指派新值給這些變數 (SQLSTATE 560CE)。
使用錨點資料類型: 錨點資料類型無法參照下列物件 (SQLSTATE 428HS): 暱稱、類型化表格、類型化視圖、與表示式型索引相關聯的統計視圖、宣告的暫存表格、與相關聯的列定義弱度類型化游標、具有與資料庫字碼頁或資料庫對照不同的字碼頁或對照的物件。
如果在動態準備或執行的複合 SQL (已編譯) 陳述式中使用具名參數標記，則每個參數標記名稱都必須是唯一的 (SQLSTATE 42997)。

注意事項

XML 指派: 對資料類型 XML 之參數及變數的指派由參照完成。
將 CALL 陳述式中資料類型 XML 的參數傳遞至 SQL 程序是透過參照來完成。依參照傳遞 XML 值時，會直接從 XML 引數使用任何輸入節點樹狀結構。此直接用法會保留所有內容，包括文件順序、原始節點身分及所有母項內容。

範例

範例 1: 輸出單字 'Hello' 的簡式獨立式複合陳述式:

   SET SERVEROUTPUT ON;
   BEGIN
       CALL DBMS_OUTPUT.PUT_LINE ( 'Hello' );
   END

範例 2: 計算 staff 中記錄數並輸出結果的簡式獨立式複合陳述式:

   SET SERVEROUTPUT ON;
   BEGIN
       DECLARE v_numRecords INTEGER DEFAULT 1;
    SELECT COUNT(*) INTO v_numRecords FROM staff;

    CALL DBMS_OUTPUT.PUT_LINE (v_numRecords);
   END

範例 3: 使用執行下列動作的複合 SQL (已編譯) 陳述式建立程序:

宣告 SQL 變數
宣告游標以傳回由 IN 參數決定之部門中員工的薪資。在 SELECT 陳述式中，將 salary 直欄的資料類型從 DECIMAL 強制轉型為 DOUBLE。
宣告 NOT FOUND (檔案結尾) 條件的 EXIT 處理程式，其將值 '6666' 指派給 OUT 參數 medianSalary
在 SQL 變數 numRecords 中選取給定部門中的員工數目
從 WHILE 迴圈中的游標提取列，直到已擷取 50% + 1 個員工為止
傳回中位數薪資

   CREATE PROCEDURE DEPT_MEDIAN
     (IN deptNumber SMALLINT, OUT medianSalary DOUBLE)
     LANGUAGE SQL
     BEGIN
       DECLARE v_numRecords INTEGER DEFAULT 1;
       DECLARE v_counter INTEGER DEFAULT 0;
       DECLARE c1 CURSOR FOR
         SELECT CAST(salary AS DOUBLE) FROM staff
           WHERE DEPT = deptNumber
           ORDER BY salary;
       DECLARE EXIT HANDLER FOR NOT FOUND
         SET medianSalary = 6666;
   -- initialize OUT parameter
         SET medianSalary = 0;
         SELECT COUNT(*) INTO v_numRecords FROM staff
           WHERE DEPT = deptNumber;
         OPEN c1;
         WHILE v_counter < (v_numRecords / 2 + 1) DO
           FETCH c1 INTO medianSalary;
           SET v_counter = v_counter + 1;
         END WHILE;
         CLOSE c1;
     END

範例 4: 下列範例說明假設因 RESIGNAL 而從另一個條件啟動 UNDO 處理程式時的執行流程:

   CREATE PROCEDURE A()
   LANGUAGE SQL
   CS1: BEGIN ATOMIC
     DECLARE C CONDITION FOR SQLSTATE '12345';
     DECLARE D CONDITION FOR SQLSTATE '23456';

     DECLARE UNDO HANDLER FOR C    
     H1: BEGIN
       -- Perform rollback after error, perform final cleanup, and exit        
       -- procedure A.                

       -- ...                

       -- When this handler completes, execution continues after        
       -- compound statement CS1; procedure A will terminate.    
     END;

     -- Perform some work here ...    
     CS2: BEGIN
       DECLARE CONTINUE HANDLER FOR D
       H2: BEGIN
         -- Perform local recovery, then forward the error            
         -- condition to the outer handler for additional
         -- processing.

         -- ...

         RESIGNAL C; -- will activate UNDO handler H1; execution
                     -- WILL NOT return here.  Any local cursors
                     -- declared in H2 and CS2 will be closed.
       END;

       -- Perform some more work here ...

       -- Simulate raising of condition D by some SQL statement
       -- in compound statement CS2:
       SIGNAL D; -- will activate H2
     END;
   END