FORALLおよびBULK COLLECTを使用する例

TimesTenでは、バルク・バインディングと、FORALL文およびBULK COLLECT機能がサポートされています。

この項で取り上げる例は次のとおりです。

SQL%BULK_ROWCOUNTでのFORALLの使用

%BULK_ROWCOUNTカーソル属性は、FORALL文とともに使用することが前提の複合構造です。

この属性は連想配列(索引付き表)のように動作します。属性のi番目の要素には、INSERT文のi回目の実行で処理された行の数が格納されます。i回目の実行がどの行にも影響しなかった場合、%BULK_ROWCOUNT(i)は0(ゼロ)を戻します。

このことを次の例に示します。

Command> DECLARE
           TYPE num_list_type IS TABLE OF NUMBER
              INDEX BY BINARY_INTEGER;
         v_nums num_list_type;
         BEGIN
           v_nums (1) := 1;
           v_nums (2) := 3;
           v_nums (3) := 5;
           v_nums (4) := 7;
           v_nums (5) := 11;
             FORALL i IN v_nums.FIRST .. v_nums.LAST
              INSERT INTO num_table (n) VALUES (v_nums (i));
            FOR i IN v_nums.FIRST .. v_nums.LAST
            LOOP
               DBMS_OUTPUT.PUT_LINE ('Inserted '||
                 SQL%BULK_ROWCOUNT (i) || ' row (s)' ||
                 ' on iteration  ' || i );
            END LOOP;
         END;
         /
Inserted 1 row (s) on iteration  1
Inserted 1 row (s) on iteration  2
Inserted 1 row (s) on iteration  3
Inserted 1 row (s) on iteration  4
Inserted 1 row (s) on iteration  5
 
PL/SQL procedure successfully completed.

問合せでのBULK COLLECT INTOの使用

PL/SQLでSELECT文とともにBULK COLLECTを使用して、カーソルを使用しないで行を取得します。

この例では、departments表から指定した場所のすべての行を選択して、ネストした表に入れた後、FOR LOOPを使用してデータを出力します。

Command> CREATE OR REPLACE PROCEDURE get_departments (p_loc NUMBER) IS
            TYPE dept_tab_type IS
         TABLE OF departments%ROWTYPE;
           v_depts dept_tab_type;
          BEGIN
            SELECT * BULK COLLECT INTO v_depts
           FROM departments
          where location_id = p_loc;
          FOR i IN 1 .. v_depts.COUNT
          LOOP
             DBMS_OUTPUT.PUT_LINE (v_depts(i).department_id
               || ' ' || v_depts (i).department_name);
          END LOOP;
          END;
          /
 
Procedure created.

次のようにプロシージャを実行して、結果を確認します。

Command> EXECUTE GET_DEPARTMENTS (1700);
10 Administration
30 Purchasing
90 Executive
100 Finance
110 Accounting
120 Treasury
130 Corporate Tax
140 Control And Credit
150 Shareholder Services
160 Benefits
170 Manufacturing
180 Construction
190 Contracting
200 Operations
210 IT Support
220 NOC
230 IT Helpdesk
240 Government Sales
250 Retail Sales
260 Recruiting
270 Payroll
 
PL/SQL procedure successfully completed.
 
Command> SELECT department_id, department_name FROM departments WHERE
         location_id = 1700;
< 10, Administration >
< 30, Purchasing >
< 90, Executive >
< 100, Finance >
< 110, Accounting >
< 120, Treasury >
< 130, Corporate Tax >
< 140, Control And Credit >
< 150, Shareholder Services >
< 160, Benefits >
< 170, Manufacturing >
< 180, Construction >
< 190, Contracting >
< 200, Operations >
< 210, IT Support >
< 220, NOC >
< 230, IT Helpdesk >
< 240, Government Sales >
< 250, Retail Sales >
< 260, Recruiting >
< 270, Payroll >
21 rows found.

カーソルでのBULK COLLECT INTOの使用

この例では、カーソルを使用して、指定したlocation_id値を持つ行を部門表からバルク・コレクトします。

結果は、前述の「問合せでのBULK COLLECT INTOの使用」の項と同じです。

Command> CREATE OR REPLACE PROCEDURE get_departments2 (p_loc NUMBER) IS
           CURSOR cur_dept IS
            SELECT * FROM departments
            WHERE location_id = p_loc;
          TYPE dept_tab_type IS TABLE OF cur_dept%ROWTYPE;
          v_depts dept_tab_type;
         BEGIN
           OPEN cur_dept;
           FETCH cur_dept BULK COLLECT INTO v_depts;
           CLOSE cur_dept;
         FOR i IN 1 .. v_depts.COUNT
          LOOP
            DBMS_OUTPUT.PUT_LINE (v_depts (i).department_id
            || ' ' || v_depts (i).department_name );
         END LOOP;
         END;
         /
 
Procedure created.
 
Command> EXECUTE GET_DEPARTMENTS2 (1700);
10 Administration
30 Purchasing
90 Executive
100 Finance
110 Accounting
120 Treasury
130 Corporate Tax
140 Control And Credit
150 Shareholder Services
160 Benefits
170 Manufacturing
180 Construction
190 Contracting
200 Operations
210 IT Support
220 NOC
230 IT Helpdesk
240 Government Sales
250 Retail Sales
260 Recruiting
270 Payroll
 
PL/SQL procedure successfully completed.

BULK COLLECTでのSAVE EXCEPTIONSの使用

SAVE EXCEPTIONSを使用すると、UPDATE、INSERTまたはDELETE文で例外が発生した後でもその文の実行を続行できます。文が終了するとエラーが発行され、1つ以上の例外が発生したことが示されます。例外は配列に収集され、文の実行後に%BULK_EXCEPTIONSを使用して確認できます。

次の例では、job_id列で一部の新しい値が大きすぎるため、PL/SQLにより事前定義された例外が発生します。FORALL文の後、SQL%BULK_EXCEPTIONS.COUNTは2を戻し、SQL%BULK_EXCEPTIONSのコンテンツは、(7, 01401)および(13, 01401)となり、エラー番号とエラーが検出された行番号を示します。エラー・メッセージを取得するには、SQL%BULK_EXCEPTIONS(i).ERROR_CODEの負の値をエラー・レポート・ファンクションSQLERRMに渡します(負の値が必要)。

次のスクリプトはttIsqlを使用して実行されます。

-- create a temporary table for this example
CREATE TABLE emp_temp AS SELECT * FROM employees;
 
DECLARE
   TYPE empid_tab IS TABLE OF employees.employee_id%TYPE;
   emp_sr empid_tab;
-- create an exception handler for ORA-24381
   errors NUMBER;
   dml_errors EXCEPTION;
   PRAGMA EXCEPTION_INIT(dml_errors, -24381);

BEGIN
   SELECT employee_id
      BULK COLLECT INTO emp_sr FROM emp_temp
      WHERE hire_date < '1994-12-30';
-- add '_SR' to the job_id of the most senior employees
   FORALL i IN emp_sr.FIRST..emp_sr.LAST SAVE EXCEPTIONS
      UPDATE emp_temp SET job_id = job_id || '_SR'
      WHERE emp_sr(i) = emp_temp.employee_id;
-- If any errors occurred during the FORALL SAVE EXCEPTIONS,
-- a single exception is raised when the statement completes.
 
EXCEPTION
-- Figure out what failed and why
   WHEN dml_errors THEN
      errors := SQL%BULK_EXCEPTIONS.COUNT;
      DBMS_OUTPUT.PUT_LINE
         ('Number of statements that failed: ' || errors);
      FOR i IN 1..errors LOOP
         DBMS_OUTPUT.PUT_LINE('Error #' || i || ' occurred during '||
                 'iteration #' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
         DBMS_OUTPUT.PUT_LINE('Error message is ' ||
         SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
   END LOOP;
END;
/
 
DROP TABLE emp_temp;

結果は次のとおりです。

Number of statements that failed: 2
Error #1 occurred during iteration #7
Error message is ORA-01401: inserted value too large for column
Error #2 occurred during iteration #13
Error message is ORA-01401: inserted value too large for column
 
PL/SQL procedure successfully completed.