DB2 SQL PL

DB2 SQL PL

[DB2]テンプレート:Category SQL

概要

• ストアドプロシージャ（SQL）を作成するには
• SQL PL
• ルーチンの開発
• システム定義ルーチン
• SQLルーチン
• SQL ルーチンまたは外部ルーチンを使用する場合の判断
• SQLプロシージャ
• CREATE PROCEDURE
• CREATE FUNCTION

ステートメント

CALL ステートメント

• プロシージャーまたは外部プロシージャーを呼び出します。

CASE ステートメント

• 複数の条件に基づいて実行パスを選択します。

CASE ステートメントを CASE 式と混同しないでください。

simple-case-statement-when-clause

  CASE v_workdept
    WHEN'A00'
      THEN UPDATE department
      SET deptname = 'DATA ACCESS 1';
    WHEN 'B01'
      THEN UPDATE department
      SET deptname = 'DATA ACCESS 2';
    ELSE UPDATE department
      SET deptname = 'DATA ACCESS 3';
  END CASE

searched-case-statement-when-clause

  CASE
    WHEN v_workdept = 'A00'
      THEN UPDATE department
      SET deptname = 'DATA ACCESS 1';
    WHEN v_workdept = 'B01'
      THEN UPDATE department
      SET deptname = 'DATA ACCESS 2';
    ELSE UPDATE department
      SET deptname = 'DATA ACCESS 3';
  END CASE

コンパウンド SQL (コンパイル済み) ステートメント

• 変数、条件、カーソル、およびハンドラーに関する、ローカルな有効範囲を指定して実行する一連の SQL ステートメント。

FOR ステートメント

• 表の行ごとに、ステートメントまたはステートメントのグループを実行

例

BEGIN ATOMIC
    DECLARE fullname CHAR(40);
    FOR vl AS
      SELECT firstnme, midinit, lastname FROM employee
        DO
        SET fullname = lastname CONCAT ','
          CONCAT firstnme CONCAT ' ' CONCAT midinit;
        INSERT INTO tnames VALUES (fullname);
    END FOR;
  END

IF ステートメント

• 条件の評価に基づいて実行パスを選択します。

例

 CREATE PROCEDURE UPDATE_SALARY_IF
    (IN employee_number CHAR(6), INOUT rating SMALLINT)
    LANGUAGE SQL
    BEGIN
      DECLARE not_found CONDITION FOR SQLSTATE '02000';
      DECLARE EXIT HANDLER FOR not_found
        SET rating = -1;
      IF rating = 1
        THEN UPDATE employee
        SET salary = salary * 1.10, bonus = 1000
        WHERE empno = employee_number;
      ELSEIF rating = 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 IF;
    END

ITERATE ステートメント

• 制御のフローがラベル付きループの最初に戻ります。

例

  CREATE PROCEDURE ITERATOR()
    LANGUAGE SQL
    BEGIN
      DECLARE v_dept CHAR(3);
      DECLARE v_deptname VARCHAR(29);
      DECLARE v_admdept CHAR(3);
      DECLARE at_end INTEGER DEFAULT 0;
      DECLARE not_found CONDITION FOR SQLSTATE '02000';
      DECLARE c1 CURSOR FOR
        SELECT deptno, deptname, admrdept
        FROM department
        ORDER BY deptno;
      DECLARE CONTINUE HANDLER FOR not_found
        SET at_end = 1;
      OPEN c1;
      ins_loop:
      LOOP
        FETCH c1 INTO v_dept, v_deptname, v_admdept;
        IF at_end = 1 THEN
          LEAVE ins_loop;
        ELSEIF v_dept = 'D11' THEN
          ITERATE ins_loop;
        END IF;
        INSERT INTO department (deptno, deptname, admrdept)
        VALUES ('NEW', v_deptname, v_admdept);
      END LOOP;
      CLOSE c1;
    END

LEAVE ステートメント

• プログラム制御をループまたはコンパウンド・ステートメントの外側に移動させます。

RETURN ステートメント

• ルーチンから戻るために使用されます。 SQL 関数またはメソッドの場合、関数またはメソッドの結果を返します。 SQL プロシージャーの場合、オプションで整数状況値が戻されます。

例

 BEGIN
  ...
    GOTO FAIL;
  ...
    SUCCESS: RETURN 0;
    FAIL: RETURN -200;
  END

SET 変数ステートメント

• 変数に値を割り当てます。 このステートメントは、トランザクションの制御下にありません。

例

行の給与の列を 50000 に設定

SET NEW_VAR.SALARY = 50000;
--または
SET (NEW_VAR.SALARY) = (50000);

行の給与と歩合の列を、 それぞれ 50000 および 8000 に設定

SET NEW_VAR.SALARY = 50000, NEW_VAR.COMM = 8000;
--または
SET (NEW_VAR.SALARY, NEW_VAR.COMM) = (50000, 8000);

行の給与と歩合の列を、 更新される行に関連した部門の従業員の平均給与および平均歩合にそれぞれ設定

  SET (NEW_VAR.SALARY, NEW_VAR.COMM)
     = (SELECT AVG(SALARY), AVG(COMM)
          FROM EMPLOYEE E
          WHERE E.WORKDEPT = NEW_VAR.WORKDEPT);

変数 P_SALARY を 10 % ずつ増加

SET P_SALARY = P_SALARY + (P_SALARY * .10)

数値 2.71828183 および 3.1415926 を、配列変数 SPECIALNUMBERS の最初のエレメントと 10 番目のエレメントに割り当て

  SET SPECIALNUMBERS[1] = 2.71828183;
  SET SPECIALNUMBERS[10] = 3.14159265;

値を連想配列変数 CAPITALS に割り当てます。この変数は配列タイプ CAPITALSARRAY として宣言

  SET CAPITALS['British Columbia'] = 'Victoria';
  SET CAPITALS['Alberta'] = 'Edmonton';
  SET CAPITALS['Manitoba'] = 'Winnipeg';
  SET CAPITALS['Canada'] = 'Ottawa';

SIGNAL ステートメント

• エラーまたは警告条件を通知するために使用されます。 これを使用すると、指定した SQLSTATE とオプションのメッセージ・テキストが、 エラーまたは警告とともに戻されます。

WHILE ステートメント

• エラーまたは警告条件を通知するために使用されます。 これを使用すると、指定した SQLSTATE とオプションのメッセージ・テキストが、 エラーまたは警告とともに戻されます。

例

  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;
      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