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