アプリケーション開発の手引き


SQL プロシージャーの例

このセクションでは、 SQL プロシージャー本体に表示される各ステートメントの使用方法を示した例が記載されています。これらおよび他の SQL プロシージャーの例については (SQL プロシージャーを呼び出すクライアント・アプリケーションを含む)、以下のようなディレクトリーを参照してください。

UNIX オペレーティング・システム
$HOME/sqllib/samples/sqlproc。ここで $HOME は、 DB2 インスタンス・ディレクトリーの位置を表します。

Windows 32 ビット オペレーティング・システム
%DRIVE%\sqllib\samples\sqlproc。ここで %DRIVE% は、 DB2 のインストール先のドライブを表します。

例 1: CASE ステートメント: 以下の SQL プロシージャーは、CASE ステートメントの使用方法を示したものです。このプロシージャーでは、入力パラメーターとして ID 番号および従業員のランク付けを受け取ります。 CASE ステートメントは、ランク付けごとに異なった UPDATE ステートメントを使用して従業員の給与およびボーナスを変更します。

    CREATE PROCEDURE UPDATE_SALARY
    (IN employee_number CHAR(6), IN rating INT)
    LANGUAGE SQL
    BEGIN
      DECLARE not_found CONDITION FOR SQLSTATE '02000';
      DECLARE EXIT HANDLER FOR not_found
        SIGNAL SQLSTATE '02444';
      CASE rating
        WHEN 1 THEN
          UPDATE employee
          SET salary = salary * 1.10, bonus = 1000
          WHERE empno = employee_number;
        WHEN 2 THEN
          UPDATE employee
          SET salary = salary * 1.05, bonus = 500
          WHERE empno = employee_number;
        ELSE
          UPDATE employee
          SET salary = salary * 1.03, bonus = 0
          WHERE empno = employee_number;
       END CASE;
   END

例 2: ネストされた IF および WHILE ステートメントを使った複合ステートメント: 以下の例では、ネストされた IF ステートメント、 WHILE ステートメント、および割り当てステートメントを含んだ複合ステートメントを示します。また、この例では、エラー・コードのクラスに対して SQL 変数、カーソル、およびハンドラーを宣言する方法も示します。

このプロシージャーは、部門番号を入力パラメーターとして受け取ります。プロシージャー本体の WHILE ステートメントは、部門の各従業員の給与およびボーナスを取り出します。 WHILE ステートメント内の IF ステートメントは、各従業員の労働年数および現在の給与に応じて給与を更新します。この部門に存在するすべての従業員のレコードが処理された場合、従業員レコードを検索する FETCH ステートメントは SQLSTATE 20000 を受け取ります。 not_found 条件ハンドラーによって WHILE ステートメントの検索条件が偽になり、WHILE ステートメントの実行は終了します。

   CREATE PROCEDURE BUMP_SALARY_IF (IN deptnumber SMALLINT) 
   LANGUAGE SQL
   BEGIN
      DECLARE v_salary DOUBLE;
      DECLARE v_years SMALLINT;
      DECLARE v_id SMALLINT;
      DECLARE at_end INT DEFAULT 0;
      DECLARE not_found CONDITION FOR SQLSTATE '02000';
      -- CAST salary as DOUBLE because SQL procedures do not support DECIMAL
      DECLARE C1 CURSOR FOR
        SELECT id, CAST(salary AS DOUBLE), years
        FROM staff;
      DECLARE CONTINUE HANDLER FOR not_found
        SET at_end = 1;
      OPEN C1;
      FETCH C1 INTO v_id, v_salary, v_years;
      WHILE at_end = 0 DO
        IF (v_salary < 2000 * v_years)
          THEN UPDATE staff
            SET salary = 2150 * v_years
            WHERE id = v_id;
          ELSEIF (v_salary < 5000 * v_years)
            THEN IF (v_salary < 3000 * v_years)
              THEN UPDATE staff
                SET salary = 3000 * v_years
                WHERE id = v_id;
            ELSE UPDATE staff
              SET salary = v_salary * 1.10
              WHERE id = v_id;
            END IF;
          ELSE UPDATE staff
            SET job = 'PREZ'
            WHERE id = v_id;
        END IF;
        FETCH C1 INTO v_id, v_salary, v_years;
      END WHILE;
      CLOSE C1;
   END

例 3: グローバル一時表および結果セット付きのネストされた SQL プロシージャーの使用:

ASSOCIATE RESULT SET LOCATOR および ALLOCATE CURSOR ステートメントを使用して、呼び出し先 SQL プロシージャー temp_table_insert から呼び出し元 SQL プロシージャー temp_table_create に結果セットを戻す方法を以下の例に示します。この例では、呼び出し先 SQL プロシージャーが呼び出し元 SQL プロシージャーによって作成されたグローバル一時表を使用できることも示されています。

この例では、クライアント・アプリケーションまたは別の SQL プロシージャーが temp_table_create を呼び出します。そして、temp_table_create はグローバル一時表 SESSION.TTT を作成してから temp_table_insert を呼び出します。

SESSION.TTT グローバル一時表を使用するため、 temp_table_insert には DECLARE GLOBAL TEMPORARY TABLE ステートメントが含まれています。これは、 temp_table_create が SESSION.TTT を作成するために発行するステートメントと同様のものです。 temp_table_create が発行するステートメントとの違いは、 temp_table_insert の DECLARE GLOBAL TEMPORARY TABLE ステートメントがいつも偽である IF に含まれていることにあります。 IF ステートメントは、 DB2 がグローバル一時表を 2 度目に作成しないようにしますが、 SQL プロシージャーが後続するステートメントでグローバル一時表を使用するのを可能にします。

別の SQL プロシージャーで作成されたグローバル一時表から結果セットを戻すには、 temp_table_insert は DECLARE CURSOR ステートメントを新しい範囲内で発行する必要があります。 temp_table_insert は、新しい効力範囲の要求を満たす DECLARE CURSOR および OPEN CURSOR ステートメントを複合 SQL ブロックで発行します。 SQL プロシージャーが終了する前にカーソルはクローズされないので、 DB2 は結果セットを呼び出し元 temp_table_create に戻します。

呼び出し先 SQL プロシージャーから結果セットを受け入れるため、 temp_table_create は、 temp_table_insert を結果セットの発信元であることを識別する ASSOCIATE RESULT SET LOCATOR ステートメントを発行します。それから、temp_table_create は、結果セット・ロケーターの ALLOCATE CURSOR ステートメントを発行して結果セットをオープンします。 ALLOCATE CURSOR ステートメントが正常に実行されると、 SQL プロシージャーは通常どおり結果セットで作業を行えます。この例では、temp_table_create は結果セットからすべての行を取り出し、出力パラメーターに列の値を追加します。
注:グローバル一時表を使用する SQL プロシージャーで CREATE PROCEDURE ステートメントを発行する前に、ユーザー一時表スペースを作成する必要があります。ユーザー一時表スペースを作成するには、以下の SQL ステートメントを発行します。

   CREATE USER TEMPORARY TABLESPACE ts1
      MANAGED BY SYSTEM USING ('ts1file');
ここで、ts1 はユーザー一時表スペースの名前を表し、 ts1file は表スペースによって使用されるコンテナー名を表します。

   CREATE PROCEDURE temp_table_create(IN parm1 INTEGER, IN parm2 INTEGER,
      OUT parm3 INTEGER, OUT parm4 INTEGER)
      LANGUAGE SQL 
      BEGIN
         DECLARE loc1 RESULT_SET_LOCATOR VARYING;
         DECLARE total3,total4 INTEGER DEFAULT 0;
         DECLARE rcolumn1, rcolumn2 INTEGER DEFAULT 0;
         DECLARE result_set_end INTEGER DEFAULT 0;
         DECLARE CONTINUE HANDLER FOR NOT FOUND, SQLEXCEPTION, SQLWARNING
         BEGIN
           SET result_set_end = 1;
         END;
         --Create the temporary table that is used in both this SQL procedure
         --and in the SQL procedure called by this SQL procedure.
         DECLARE GLOBAL TEMPORARY TABLE ttt(column1 INT, column2 INT)
            NOT LOGGED;
         --Insert rows into the temporary table.
         --The result set includes these rows.
         INSERT INTO session.ttt(column1, column2) VALUES ( parm1+1, parm2+1);
         INSERT INTO session.ttt(column1, column2) VALUES ( parm1+2, parm2+2);
         --Make a nested call to the 'temp_table_insert' SQL procedure.
         CALL temp_table_insert(parm1, parm2);
         --Issue the ASSOCIATE RESULT SET LOCATOR statement to
         --accept a single result set from 'temp_table_insert'.
         --If 'temp_table_insert' returns multiple result sets,
         --you must declare one locator variable (for example,
         --ASSOCIATE RESULT SET LOCATOR(loc1, loc2, loc3) for each result set.
         ASSOCIATE RESULT SET LOCATOR(loc1) WITH PROCEDURE temp_table_insert;
         --The ALLOCATE statement is similar to the OPEN statement.
         --It makes the result set available in this SQL procedure.
         ALLOCATE cursor1 CURSOR FOR RESULT SET loc1;
         --Insert rows into the temporary table.
         --The result set does not include these rows.
         INSERT INTO session.ttt(column1, column2) VALUES ( parm1+5, parm2+5);
         INSERT INTO session.ttt(column1, column2) VALUES ( parm1+6, parm2+6);
         SET result_set_end = 0;
         --Fetch the columns from the first row of the result set.
         FETCH FROM cursor1 INTO rcolumn1, rcolumn2;
         WHILE (result_set_end = 0) DO
           SET total3 = total3 + rcolumn1;
           SET total4 = total4 + rcolumn2;
           --Fetch columns from the result set for the
           --next iteration of the WHILE loop.
           FETCH FROM cursor1 INTO rcolumn1, rcolumn2;
         END WHILE;
         CLOSE cursor1;
         SET parm3 = total3;
         SET parm4 = total4;
      END @
   CREATE PROCEDURE temp_table_insert (IN parm1 INTEGER, IN parm2 INTEGER )
      LANGUAGE SQL
      BEGIN
         DECLARE result_set_end INTEGER DEFAULT 0;
         DECLARE CONTINUE HANDLER FOR NOT FOUND BEGIN
           SET result_set_end = 1;
         END;
         --To use a temporary table that is created by a different stored
         --procedure, include a DECLARE GLOBAL TEMPORARY TABLE statement
         --inside a condition statement that always evaluates to false.
         IF (1 = 0) THEN
           DECLARE GLOBAL TEMPORARY TABLE ttt(column1 INT, column2 INT)
              NOT LOGGED;
         END IF;
         --Insert rows into the temporary table.
         --The result set includes these rows.
         INSERT INTO session.ttt(column1, column2) VALUES ( parm1+3, parm2+3);
         INSERT INTO session.ttt(column1, column2) VALUES ( parm1+4, parm2+4);
         --To return a result set from the temporary table, issue
         --the DECLARE CURSOR statement inside a new scope, such as
         --a compound SQL statement (BEGIN...END block).
         --Issue the DECLARE CURSOR statement after the DECLARE
         --GLOBAL TEMPORARY TABLE statement.
         BEGIN
            --The WITH RETURN TO CALLER clause causes the SQL procedure
            --to return its result set to the calling procedure.
            DECLARE cur1 CURSOR WITH RETURN TO CALLER
               FOR SELECT * FROM session.ttt;
            --To return a result set, open a cursor without closing the cursor.
           OPEN cur1 ;
         END;
      END


[ ページのトップ | 前ページ | 次ページ | 目次 | 索引 ]