5 PL/SQLでのTimesTen SQLの使用例

この章では、PL/SQLにおけるTimesTen SQLの密接な統合について詳細に検討するため、さらに例を示します。

PL/SQLでSELECT...INTO文を使用する例
INSERT文を使用する例
入力パラメータ、出力パラメータおよびバインド変数を使用する例
カーソルを使用する例
FORALLおよびBULK COLLECTを使用する例
EXECUTE IMMEDIATEを使用する例
RETURNING INTOを使用する例
AUTHID句を使用する例
システム・ビューを問い合せる例

PL/SQLでSELECT...INTO文を使用する例

単一行のデータを取得するには、SELECT... INTO文を使用します。1行も返さないか、または複数の行を返す問合せに対して、TimesTenはエラーを返します。

この項では、次の例を示します。

SELECT... INTOを使用した給与合計の取得
SELECT...INTOを使用した他のユーザーの表への問合せ

SELECT... INTOを使用した給与合計の取得

この例では、SELECT...INTO文を使用して、department_idが60の部門のすべての従業員の給与合計を計算します。

例5-1 SELECT... INTOを使用した給与合計の取得

Command> DECLARE
       >   v_sum_sal  NUMBER (10,2);
       >   v_dept_no  NUMBER NOT NULL := 60;
       > BEGIN
       >   SELECT SUM(salary) -- aggregate function
       >   INTO v_sum_sal FROM employees
       >   WHERE department_id = v_dept_no;
       >   DBMS_OUTPUT.PUT_LINE ('Sum is ' || v_sum_sal);
       >  END;
       >  /
Sum is 28800
 
PL/SQL procedure successfully completed.

SELECT...INTOを使用した他のユーザーの表への問合せ

次の例では、USER1およびUSER2の2人のユーザーで、1人のユーザーがSELECT...INTOを使用してもう1人のユーザーの表に問い合せる方法を示しています。

例5-2 SELECT...INTOを使用した他のユーザーの表への問合せ

次の権限を想定します。

grant create session to user1;
grant create session to user2;
grant create table to user1;
grant select on user1.test to user2;

USER1:

Command> create table test(name varchar2(20), id number);
Command> insert into test values('posey', 363);
1 row inserted.

USER2:

Command> declare
       >    targetid number;
       > begin
       >    select id into targetid from user1.test where name='posey';
       >    dbms_output.put_line('Target ID is ' || targetid);
       > end;
       > /
Target ID is 363
 
PL/SQL procedure successfully completed.

INSERT文を使用する 例

TimesTenでは、TimesTen DML文INSERT、UPDATE、DELETEおよびMERGEがサポートされています。この項では、INSERT文の例を示します。

例5-3 PL/SQLでINSERT文を使用する例

この例では、AS SELECT問合せ句を使用して表emp_copyを作成し、AUTOCOMMITをOFFに設定してemployee_idを増分するシーケンスを作成した後、PL/SQLのINSERT文を使用してデータ行を表emp_copyに挿入します。

Command> CREATE TABLE emp_copy AS SELECT * FROM employees;
107 rows inserted.
Command> SET AUTOCOMMIT OFF;

Command> CREATE SEQUENCE emp_copy_seq
       > START WITH 207
       > INCREMENT BY 1;

Command>  BEGIN
       >    INSERT INTO emp_copy
       >      (employee_id, first_name, last_name, email, hire_date, job_id,
       >       salary)
       >    VALUES (emp_copy_seq.NEXTVAL, 'Parker', 'Cores', 'PCORES', SYSDATE,
       >      'AD_ASST', 4000);
       >  END;
       >  /
 
PL/SQL procedure successfully completed.

続いて、行が挿入されたことを確認した後、トランザクションをロールバックします。

Command> SELECT * FROM EMP_COPY WHERE first_name = 'Parker';
< 207, Parker, Cores, PCORES, <NULL>, 2008-07-19 21:49:55, AD_ASST, 4000, 
<NULL>, <NULL>, <NULL> >
1 row found.
Command> ROLLBACK;
Command>  SELECT * FROM emp_copy WHERE first_name = 'Parker';
0 rows found.

ここで、INSERT文を再実行してから、PL/SQLでトランザクションをロールバックします。最後に、TimesTenによって行が挿入されていないことを確認します。

Command> BEGIN
       >   INSERT INTO emp_copy
       >     (employee_id, first_name, last_name, email, hire_date, job_id,
       >      salary)
       >   VALUES (emp_copy_seq.NEXTVAL, 'Parker', 'Cores', 'PCORES', SYSDATE,
       >           'AD_ASST',4000);
       > ROLLBACK;
       > END;
       > /
 
PL/SQL procedure successfully completed.
 
Command> SELECT * FROM emp_copy WHERE first_name = 'Parker';
0 rows found.

入力パラメータ、出力パラメータおよびバインド変数を使用する例

この項の次の例では、PL/SQLの外部からのバインド変数(ホスト変数)を含む、IN、OUT、およびIN OUTパラメータを使用します。

INおよびOUTパラメータ
IN OUTパラメータの使用
連想配列の使用

INおよびOUTパラメータ

この例では、従業員情報を取得するプロシージャquery_empを作成して、employee_id値171をプロシージャに渡し、名前および給与を取得して2つのOUTパラメータに入れます。

例5-4 INパラメータおよびOUTパラメータの使用

Command> CREATE OR REPLACE PROCEDURE query_emp
       >   (p_id IN employees.employee_id%TYPE,
       >    p_name  OUT employees.last_name%TYPE,
       >    p_salary OUT employees.salary%TYPE) IS
       > BEGIN
       >   SELECT last_name, salary INTO p_name, p_salary
       >   FROM employees
       >   WHERE employee_id = p_id;
       > END query_emp;
       > /
 
Procedure created.

Command> -- Execute the procedure
       > DECLARE
       >  v_emp_name employees.last_name%TYPE;
       >  v_emp_sal  employees.salary%TYPE;
       > BEGIN
       >  query_emp (171, v_emp_name, v_emp_sal);
       >  DBMS_OUTPUT.PUT_LINE (v_emp_name || ' earns ' ||
       >   TO_CHAR (v_emp_sal, '$999,999.00'));
       > END;
       > /
Smith earns    $7,400.00
 
PL/SQL procedure successfully completed.

IN OUTパラメータの使用

電話番号を書式化する場合を考えてみます。次の例では、電話番号の桁のような10文字の文字列を受け取り、この書式が設定されていない文字列をIN OUTパラメータとしてプロシージャに渡します。プロシージャの実行が完了すると、IN OUTパラメータには書式化された電話番号の値が含まれます。

例5-5 IN OUTパラメータの使用

Command> CREATE OR REPLACE PROCEDURE format_phone
       >   (p_phone_no IN OUT VARCHAR2 ) IS
       > BEGIN
       >  p_phone_no := '('  || SUBSTR (p_phone_no,1,3) ||
       >                ') ' || SUBSTR (p_phone_no,4,3) ||
       >                '-'  || SUBSTR (p_phone_no,7);
       > END format_phone;
       > /
 
Procedure created.

バインド変数を作成し、プロシージャを実行して、結果を確認します。

Command> VARIABLE b_phone_no VARCHAR2 (15);
Command> EXECUTE :b_phone_no := '8006330575';
 
PL/SQL procedure successfully completed.
 
Command> PRINT b_phone_no;
B_PHONE_NO           : 8006330575
Command> BEGIN
       >   format_phone (:b_phone_no);
       > END;
       > /
 
PL/SQL procedure successfully completed.
 
Command> PRINT b_phone_no
B_PHONE_NO           : (800) 633-0575

連想配列の使用

次の例では、ttIsqlを使用して、NUMBER配列およびVARCHAR2配列を、PL/SQLの相当するOUT連想配列にバインドします。

関連情報については、「アプリケーションからの連想配列の使用」を参照してください。

例5-6 ttIsqlから連想配列へのバインド

次のSQL設定を想定します。

DROP TABLE FOO;
 
CREATE TABLE FOO (CNUM INTEGER,
                  CVC2 VARCHAR2(20));
 
INSERT INTO FOO VALUES ( null,
     'VARCHAR  1');
INSERT INTO FOO VALUES (-102,
     null);
INSERT INTO FOO VALUES ( 103,
     'VARCHAR  3');
INSERT INTO FOO VALUES (-104,
     'VARCHAR  4');
INSERT INTO FOO VALUES ( 105,
     'VARCHAR  5');
INSERT INTO FOO VALUES ( 106,
     'VARCHAR  6');
INSERT INTO FOO VALUES ( 107,
     'VARCHAR  7');
INSERT INTO FOO VALUES ( 108,
     'VARCHAR  8');
 
COMMIT;

次のPL/SQLパッケージ定義を想定します。これには、プロシージャP1の定義内の出力連想配列c1およびc2のそれぞれに使用される、INTEGER連想配列タイプNUMARRTYPおよびVARCHAR2連想配列タイプVCHARRTYPなどがあります。

CREATE OR REPLACE PACKAGE PKG1 AS
  TYPE NUMARRTYP IS TABLE OF INTEGER INDEX BY BINARY_INTEGER;
  TYPE VCHARRTYP IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;
 
  PROCEDURE P1(c1 OUT NUMARRTYP,c2 OUT VCHARRTYP);
 
END PKG1;
/
 
CREATE OR REPLACE PACKAGE BODY PKG1 AS
 
  CURSOR CUR1 IS SELECT CNUM, CVC2 FROM FOO;
 
  PROCEDURE P1(c1 OUT NUMARRTYP,c2 OUT VCHARRTYP) IS
  BEGIN
    IF NOT CUR1%ISOPEN  THEN
      OPEN CUR1;
    END IF;
    FOR i IN 1..8 LOOP
      FETCH CUR1 INTO c1(i), c2(i);
      IF CUR1%NOTFOUND THEN
        CLOSE CUR1;
        EXIT;
      END IF;
    END LOOP;
  END P1;
 
END PKG1;

これで、ttIsqlはPKG1.P1をコールし、配列をP1出力連想配列にバインドして、これらの連想配列のコンテンツを出力します。

Command> var c1[10] number;
Command> var c2[10] varchar2(20);
Command> print;
C1                   : ARRAY [ 10 ] (Current Size 0)
C2                   : ARRAY [ 10 ] (Current Size 0)
Command> BEGIN PKG1.P1(:c1, :c2); END;
> /
 
PL/SQL procedure successfully completed.
 
Command> print
C1                   : ARRAY [ 10 ] (Current Size 8)
C1[1] : <NULL>
C1[2] : -102
C1[3] : 103
C1[4] : -104
C1[5] : 105
C1[6] : 106
C1[7] : 107
C1[8] : 108
C2                   : ARRAY [ 10 ] (Current Size 8)
C2[1] : VARCHAR  1
C2[2] : <NULL>
C2[3] : VARCHAR  3
C2[4] : VARCHAR  4
C2[5] : VARCHAR  5
C2[6] : VARCHAR  6
C2[7] : VARCHAR  7
C2[8] : VARCHAR  8

カーソルを使用する 例

「PL/SQLプログラムでのカーソルの使用」で説明しているように、TimesTenではカーソルがサポートされています。カーソルを使用して、SELECT文の結果セットを処理します。

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

値のフェッチ
%ROWCOUNT属性および%NOTFOUND属性の使用
カーソルFORループの使用

これらの例のカーソル属性については、『Oracle Database PL/SQL言語リファレンス』の明示的なカーソル属性に関する説明を参照してください。

値の フェッチ

この項では、カーソルから値をフェッチする方法について、値をレコードにフェッチする方法も含めて、例を示します。

例5-7 カーソルからの値のフェッチ

次の例では、カーソルを使用して、employees表から、department_idが30であるemployee_idおよびlast_nameを選択します。2つの変数が宣言され、カーソルからフェッチされた値を保持すると、FETCH文はループ内の行を1つずつ取得してすべての行を取得します。カーソルに残りの行がなくなると%NOTFOUNDカーソル属性が表示され、実行は停止します。

%NOTFOUNDは、INSERT、UPDATE、またはDELETE文がどの行にも影響しなかったか、またはSELECT INTO文で行が戻されなかった場合にTRUEを生成します。

Command>  DECLARE
       >    CURSOR c_emp_cursor IS
       >      SELECT employee_id, last_name FROM employees
       >      WHERE department_id = 30;
       >    v_empno  employees.employee_id%TYPE;
       >    v_lname  employees.last_name%TYPE;
       >  BEGIN
       >    OPEN c_emp_cursor;
       >    LOOP
       >     FETCH c_emp_cursor INTO v_empno, v_lname;
       >    EXIT WHEN c_emp_cursor%NOTFOUND;
       >    DBMS_OUTPUT.PUT_LINE (v_empno || ' ' || v_lname);
       >    END LOOP;
       >    CLOSE c_emp_cursor;
       >  END;
       >  /

114 Raphaely
115 Khoo
116 Baida
117 Tobias
118 Himuro
119 Colmenares

例5-8 レコードへの値のフェッチ

この例は例5-7と似ており、同じ結果を返しますが、値をPL/SQLの変数ではなくPL/SQLのレコードにフェッチします。

Command> DECLARE
       >   CURSOR c_emp_cursor IS
       >     SELECT employee_id, last_name FROM employees
       >     WHERE department_id = 30;
       >   v_emp_record  c_emp_cursor%ROWTYPE;
       > BEGIN
       >   OPEN c_emp_cursor;
       >   LOOP
       >     FETCH c_emp_cursor INTO v_emp_record;
       >   EXIT WHEN c_emp_cursor%NOTFOUND;
       >   DBMS_OUTPUT.PUT_LINE (v_emp_record.employee_id || ' ' |
       >     v_emp_record.last_name);
       >   END LOOP;
       >   CLOSE c_emp_cursor;
       > END;
       > /

114 Raphaely
115 Khoo
116 Baida
117 Tobias
118 Himuro
119 Colmenares
 
PL/SQL procedure successfully completed.

%ROWCOUNT属性および%NOTFOUND属性の使用

例5-9に、%ROWCOUNTカーソル属性と%NOTFOUNDカーソル属性(例5-7および例5-8で説明済)の使用方法を示します。

例5-9 %ROWCOUNT属性および%NOTFOUND属性の使用

この例は例5-8と同じ結果になりますが、例に示すとおり、ループ内の終了条件に%ROWCOUNTカーソル属性と%NOTFOUND属性を使用します。

%ROWCOUNTは、INSERT、UPDATE、またはDELETE文で影響を受けた行の数、またはSELECT...INTO文やFETCH...INTO文で戻された行の数を生成します。

Command> DECLARE
       >   CURSOR c_emp_cursor IS
       >     SELECT employee_id, last_name FROM employees
       >     WHERE department_id = 30;
       >   v_emp_record  c_emp_cursor%ROWTYPE;
       > BEGIN
       > OPEN c_emp_cursor;
       > LOOP
       >   FETCH c_emp_cursor INTO v_emp_record;
       >   EXIT WHEN c_emp_cursor%ROWCOUNT > 10 OR c_emp_cursor%NOTFOUND;
       >   DBMS_OUTPUT.PUT_LINE (v_emp_record.employee_id || ' ' ||
       >     v_emp_record.last_name);
       >   END LOOP;
       >   CLOSE c_emp_cursor;
       >  END;
       >  /
114 Raphaely
115 Khoo
116 Baida
117 Tobias
118 Himuro
119 Colmenares
 
PL/SQL procedure successfully completed.

カーソルFORループの 使用

TimesTenのPL/SQLでは、次の例に示すとおり、カーソルFORループがサポートされています。

例5-10 カーソルFORループの使用

この例では、PL/SQLは暗黙的にemp_recordを宣言します。OPEN文およびCLOSE文は必要ありません。結果は上記の例5-9と同じです。

Command> DECLARE
       >   CURSOR c_emp_cursor IS
       >    SELECT employee_id, last_name FROM employees
       >    WHERE department_id = 30;
       > BEGIN
       >   FOR emp_record IN c_emp_cursor
       >    LOOP
       >      DBMS_OUTPUT.PUT_LINE (emp_record.employee_id || ' ' ||
       >         emp_record.last_name);
       >    END LOOP;
       > END;
       > /
114 Raphaely
115 Khoo
116 Baida
117 Tobias
118 Himuro
119 Colmenares
 
PL/SQL procedure successfully completed.

例5-11 副問合せでのカーソルFORループの使用

この例では、副問合せを使用したFORループを示します。結果は上記の例5-9および例5-10と同じです。

Command> BEGIN
       >  FOR emp_record IN (SELECT employee_id, last_name FROM
       >   employees WHERE department_id = 30)
       >  LOOP
       >    DBMS_OUTPUT.PUT_LINE (emp_record.employee_id || ' ' ||
       >      emp_record.last_name);
       >   END LOOP;
       > END;
       > /
114 Raphaely
115 Khoo
116 Baida
117 Tobias
118 Himuro
119 Colmenares
 
PL/SQL procedure successfully completed.

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

「FORALL操作およびBULK COLLECT操作」で説明しているように、TimesTenでは、バルク・バインドとFORALL文およびBULK COLLECT機能がサポートされています。

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

SQL%BULK_ROWCOUNTでのFORALLの使用
問合せでのBULK COLLECT INTOの使用
カーソルでのBULK COLLECT INTOの使用
BULK COLLECTでのSAVE EXCEPTIONSの使用

SQL%BU LK_ROWCOUNTでのFORALLの使用

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

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

このことを例5-12に示します。

例5-12 SQL%BULK_ROWCOUNTでのFORALL文の使用

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を使用して、カーソルを使用しないで行を取得します。

例5-13 問合せでのBULK COLLECT INTOの使用

この例では、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の使用

例5-14では、カーソルを使用して表から行をバルク・コレクトします。

例5-14 カーソルでのBULK COLLECT INTOの使用

この例では、カーソルを使用して、指定したlocation_id値を持つ行を部門表からバルク・コレクトします。結果は上記の例5-13と同じです。

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を使用して確認できます。

例5-15 BULK COLLECTでのSAVE 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.

EXECUTE IMMEDIATEを使用する 例

「PL/SQLでの動的SQL(EXECUTE IMMEDIATE文)」に記載のとおり、TimesTenでは、EXECUTE IMMEDIATE文がサポートされています。この項では、TimesTenでPL/SQLアプリケーションを開発する際の検討事項を次の例で説明します。

EXECUTE IMMEDIATEを使用した表の作成
単一行問合せでのEXECUTE IMMEDIATEの使用
EXECUTE IMMEDIATEを使用した接続属性の変更
EXECUTE IMMEDIATEを使用したTimesTen組込みプロシージャのコール
TimesTen固有の構文を持つEXECUTE IMMEDIATEの使用

EXECUTE IMMEDIATEを使用した表の作成

コンパイル時に表定義がわからない場合を考えてみます。EXECUTE IMMEDIATE文を使用することで、実行時に表を作成できます。この例では、EXECUTE IMMEDIATE文を使用して表を作成するプロシージャを表示します。プロシージャはパラメータとして渡された表の名前および列定義を使用して実行され、その後、表の作成が確認されます。

例5-16 EXECUTE IMMEDIATEを使用した表の作成

Command> CREATE OR REPLACE PROCEDURE create_table
       >   (p_table_name VARCHAR2, p_col_specs VARCHAR2) IS
       > BEGIN
       >   EXECUTE IMMEDIATE 'CREATE TABLE ' || p_table_name
       >
       > || ' (' || p_col_specs|| ' )';
       > END;
       > /
 
Procedure created.

プロシージャを実行して、表が作成されていることを確認します。

Command> BEGIN
       > create_table ('EMPLOYEES_NAMES', 'id NUMBER (4)
       >  PRIMARY KEY, name VARCHAR2 (40)');
       > END;
       > /
 
PL/SQL procedure successfully completed.
 
Command> DESCRIBE employees_names;
 
Table USER.EMPLOYEES_NAMES:
  Columns:
   *ID                              NUMBER (4) NOT NULL
    NAME                            VARCHAR2 (40) INLINE
 
1 table found.
(primary key columns are indicated with *)

単一行問合せでのEXECUTE IMMEDIATEの使用

この例では、ファンクションget_empは従業員レコードを取得します。ファンクションを実行して、v_emprecに結果を戻します。

例5-17 単一行問合せでのEXECUTE IMMEDIATEの使用

Command> CREATE OR REPLACE FUNCTION get_emp (p_emp_id NUMBER)
       >   RETURN employees%ROWTYPE IS
       >   v_stmt VARCHAR2 (200);
       >   v_emprec employees%ROWTYPE;
       > BEGIN
       >   v_stmt:= 'SELECT * FROM EMPLOYEES '||
       >   'WHERE employee_id = :p_emp_id';
       >   EXECUTE IMMEDIATE v_stmt INTO v_emprec USING p_emp_id;
       >   RETURN v_emprec;
       > END;
       > /
 
Function created.
 
Command> DECLARE
       >   v_emprec employees%ROWTYPE := GET_EMP (100);
       > BEGIN
       >  DBMS_OUTPUT.PUT_LINE ('Employee: ' || v_emprec.last_name);
       > END;
       > /
Employee: King
 
PL/SQL procedure successfully completed.

EXECUTE IMMEDIATEを使用した接続属性の変更

この例では、EXECUTE IMMEDIATE文をALTER SESSIONとともに使用してPLSQL_OPTIMIZE_LEVEL設定を変更し、変更前および変更後にttConfiguration組込みプロシージャをコールして結果を確認します。(次の例では、EXECUTE IMMEDIATE文の内部からttConfigurationをコールします。)このプロシージャの詳細は、『Oracle TimesTen In-Memory Databaseリファレンス』のttConfigurationに関する説明を参照してください。

例5-18 EXECUTE IMMEDIATEを使用したPLSCOPE_SETTINGSの変更

Command> call ttconfiguration;
...
< PLSCOPE_SETTINGS, IDENTIFIERS:NONE >
< PLSQL, 1 >
< PLSQL_CCFLAGS, <NULL> >
< PLSQL_CODE_TYPE, INTERPRETED >
< PLSQL_CONN_MEM_LIMIT, 100 >
< PLSQL_MEMORY_ADDRESS, 0x10000000 >
< PLSQL_MEMORY_SIZE, 32 >
< PLSQL_OPTIMIZE_LEVEL, 2 >
< PLSQL_TIMEOUT, 30 >
...
54 rows found.
 
Command> begin
       > execute immediate 'alter session set PLSQL_OPTIMIZE_LEVEL=3';
       > end;
       > /
PL/SQL procedure successfully completed.
 
Command> call ttconfiguration;
...
< PLSCOPE_SETTINGS, IDENTIFIERS:NONE >
< PLSQL, 1 >
< PLSQL_CCFLAGS, <NULL> >
< PLSQL_CODE_TYPE, INTERPRETED >
< PLSQL_CONN_MEM_LIMIT, 100 >
< PLSQL_MEMORY_ADDRESS, 0x10000000 >
< PLSQL_MEMORY_SIZE, 32 >
< PLSQL_OPTIMIZE_LEVEL, 3 >
< PLSQL_TIMEOUT, 30 >
...
54 rows found.

EXECUTE IMMEDIATEを使用したTimesTen組込みプロシージャのコール

PL/SQLでは、CALL構文を使用したEXECUTE IMMEDIATE文を使用してTimesTen組込みプロシージャをコールできます。

たとえば、組込みプロシージャttConfigurationをコールして、その出力結果セットを戻すには、PL/SQLレコード型を作成し、BULK COLLECTを使用したEXECUTE IMMEDIATEを使用して、結果セットを配列にフェッチします。

TimesTen組込みプロシージャの詳細は、『Oracle TimesTen In-Memory Databaseリファレンス』の組込みプロシージャに関する説明を参照してください。

例5-19 EXECUTE IMMEDIATEを使用したttConfigurationのコール

Command> DECLARE
       >   TYPE ttConfig_record IS RECORD
       >      (name varchar2(255), value varchar2 (255));
       >   TYPE ttConfig_table IS TABLE OF ttConfig_record;
       > v_ttConfigs ttConfig_table;
       > BEGIN
       >  EXECUTE IMMEDIATE  'CALL ttConfiguration'
       >   BULK COLLECT into v_ttConfigs;
       >  DBMS_OUTPUT.PUT_LINE ('Name: ' || v_ttConfigs(1).name
       >    || ' Value: ' || v_ttConfigs(1).value);
       > end;
       > /
Name: CacheGridEnable Value: 0
 
PL/SQL procedure successfully completed.

TimesTen固有の構文を持つEXECUTE IMMEDIATEの使用

この例では、EXECUTE IMMEDIATE文を使用して、TimesTen SELECT FIRST n文を実行します。この構文はTimesTenに固有です。

例5-20 TimesTen固有の構文を持つEXECUTE IMMEDIATEの使用

Command> DECLARE v_empid NUMBER;
       > BEGIN
       >   EXECUTE IMMEDIATE 'SELECT FIRST 1 employee_id FROM employees'
       >    INTO v_empid;
       >  DBMS_OUTPUT.PUT_LINE ('Employee id: ' || v_empid);
       > END;
       > /
Employee id: 100
 
PL/SQL procedure successfully completed.

RETURNING INTOを使用する 例

この項では、RETURNING INTO句を使用する次の2つの例を示します。

レコードでのRETURNING INTO句の使用
RETURNING INTO句でのBULK COLLECT INTOの使用

概要は、「RETURNING INTO句」を参照してください。

レコードでのRETURNING INTO句の使用

次の例では、ttIsqlを使用して、RETURNING INTO句でレコードにデータを戻すSQLスクリプトを実行します。この例では、指定した従業員を昇給させ、その従業員の名前および新しい給与をレコードに入れた後、そのレコードからデータを出力します。参照用に元の給与を表示してから、スクリプトを実行します。

例5-21 レコードでのRETURNING INTO句の使用

Command> SELECT SALARY,LAST_NAME FROM EMPLOYEES WHERE EMPLOYEE_ID = 100;
< 24000, King >
1 row found.

Command> run ReturnIntoWithRecord.sql;
 
CREATE TABLE emp_temp AS SELECT * FROM employees;
107 rows inserted.
 
DECLARE
   TYPE EmpRec IS RECORD (last_name employees.last_name%TYPE,
                          salary employees.salary%TYPE);
   emp_info EmpRec;
   emp_id NUMBER := 100;
BEGIN
   UPDATE emp_temp SET salary = salary * 1.1
      WHERE employee_id = emp_id
      RETURNING last_name, salary INTO emp_info;
   DBMS_OUTPUT.PUT_LINE
      ('Just gave a raise to ' || emp_info.last_name ||
       ', who now makes ' || emp_info.salary);
   ROLLBACK;
END;
/
 
Just gave a raise to King, who now makes 26400
 
PL/SQL procedure successfully completed.

RETURNING INTO句でのBULK COLLECT INTOの使用

次の例では、ttIsqlを使用して、BULK COLLECTを指定したRETURNING INTO句でネストした表(PL/SQLコレクション型)にデータを戻すSQLスクリプトを実行します。例では、指定した部門のすべての従業員を削除した後、従業員ID用のネストした表および姓用のネストした表を使用して、削除した各従業員の従業員IDおよび姓を出力します。参照用に、その部門の従業員のIDおよび姓をスクリプトの実行前にも表示します。

例5-22 RETURNING INTO句でのBULK COLLECT INTOの使用

Command> select employee_id, last_name from employees where department_id=30;
< 114, Raphaely >
< 115, Khoo >
< 116, Baida >
< 117, Tobias >
< 118, Himuro >
< 119, Colmenares >
6 rows found.
Command> run ReturnIntoWithBulkCollect.sql;
 
CREATE TABLE emp_temp AS SELECT * FROM employees;
107 rows inserted.
 
DECLARE
   TYPE NumList IS TABLE OF employees.employee_id%TYPE;
   enums NumList;
   TYPE NameList IS TABLE OF employees.last_name%TYPE;
   names NameList;
BEGIN
   DELETE FROM emp_temp WHERE department_id = 30
     RETURNING employee_id, last_name
     BULK COLLECT INTO enums, names;
   DBMS_OUTPUT.PUT_LINE
      ('Deleted ' || SQL%ROWCOUNT || ' rows:');
   FOR i IN enums.FIRST .. enums.LAST
   LOOP
      DBMS_OUTPUT.PUT_LINE
         ('Employee #' || enums(i) || ': ' || names(i));
   END LOOP;
END;
/
Deleted 6 rows:
Employee #114: Raphaely
Employee #115: Khoo
Employee #116: Baida
Employee #117: Tobias
Employee #118: Himuro
Employee #119: Colmenares
 
PL/SQL procedure successfully completed.

AUTHID句を使用する例

この項では、変更を1つだけ加えてスクリプトをttIsqlで2回実行します(最初は実行者権限のAUTHID CURRENT_USERでPL/SQLプロシージャを定義し、次に定義者権限のAUTHID DEFINERで定義します)。関連情報については、「定義者権限および実行者権限」を参照してください。

AUTHIDのスクリプト例

スクリプトでは、1人のツール・ベンダーと2人のツール・ユーザー(brandXおよびbrandY)の、3人のユーザーが作成されていることを想定しています。各ユーザーには、必要に応じて、CREATE SESSION権限、CREATE PROCEDURE権限およびCREATE TABLE権限が付与されています。また、use username;構文を使用してusernameとしてデータベースに接続できるよう、次の設定が想定されています。

connect adding "uid=toolVendor;pwd=pw" as toolVendor;
connect adding "uid=brandX;pwd=pw" as brandX;
connect adding "uid=brandY;pwd=pw" as brandY;

スクリプトでは、次の操作を実行します。

ツール・ベンダーとして、プロシージャprintInventoryStatisticsを作成します。
3つのユーザー・スキーマのそれぞれに同じmyInventoryという名前の表を作成し、それぞれの表に一意のデータを移入します。
それぞれのツール・ユーザーとして、プロシージャを実行します。

2回のスクリプト実行の結果の違いは、実行者権限と定義者権限の違いを示しています。

次に、実行者権限で実行する場合のスクリプトを示します。

use toolVendor;
create table myInventory (name varchar2(100), inventoryCount tt_integer);
insert into myInventory values('butter', 1);

create or replace procedure printInventoryStatistics authid current_user is
 inventoryCount pls_integer;
begin
 select count(*) into inventoryCount from myInventory;
 dbms_output.put_line('Total items in inventory: ' || inventoryCount);
 for currentItem in (select * from myInventory) loop
   dbms_output.put_line(currentItem.name || ' ' || currentItem.inventoryCount);
 end loop;
end;
/
grant execute on printInventoryStatistics to brandX;
grant execute on printInventoryStatistics to brandY;
 
use brandX;
create table myInventory (name varchar2(100), inventoryCount tt_integer);
insert into myInventory values('toothpaste', 100);
set serveroutput on
execute toolVendor.printInventoryStatistics;
 
use brandY;
create table myInventory (name varchar2(100), inventoryCount tt_integer);
insert into myInventory values('shampoo', 10);
set serveroutput on
execute toolVendor.printInventoryStatistics;

定義者権限で実行する場合の違いは、プロシージャ定義のAUTHID句を変更することのみです。

...
create or replace procedure printInventoryStatistics authid definer is
 inventoryCount pls_integer;
begin
 select count(*) into inventoryCount from myInventory;
 dbms_output.put_line('Total items in inventory: ' || inventoryCount);
 for currentItem in (select * from myInventory) loop
   dbms_output.put_line(currentItem.name || ' ' || currentItem.inventoryCount);
 end loop;
end;
/
...

AUTHID CURRENT_USERの使用

この例では、実行者権限を使用してプロシージャを定義した場合の結果について説明しています。ツール・ユーザーbrandXおよびbrandYがprintInventoryStatisticsプロシージャを実行すると、それぞれのユーザー専用の(実行者の)myInventory表のデータが表示されることに注意してください。

例5-23 AUTHID CURRENT_USERの使用

Command> run invoker.sql
 
use toolVendor;
create table myInventory (name varchar2(100), inventoryCount tt_integer);
insert into myInventory values('butter', 1);
1 row inserted.
 
create or replace procedure printInventoryStatistics authid current_user is
 inventoryCount pls_integer;
begin
 select count(*) into inventoryCount from myInventory;
 dbms_output.put_line('Total items in inventory: ' || inventoryCount);
 for currentItem in (select * from myInventory) loop
   dbms_output.put_line(currentItem.name || ' ' || currentItem.inventoryCount);
 end loop;
end;
/

Procedure created.
 
grant execute on printInventoryStatistics to brandX;
grant execute on printInventoryStatistics to brandY;
 
use brandX;
create table myInventory (name varchar2(100), inventoryCount tt_integer);
insert into myInventory values('toothpaste', 100);
1 row inserted.
set serveroutput on;
 
execute toolVendor.printInventoryStatistics;
Total items in inventory: 1
toothpaste 100
 
PL/SQL procedure successfully completed.
 
use brandY;
create table myInventory (name varchar2(100), inventoryCount tt_integer);
insert into myInventory values('shampoo', 10);
1 row inserted.
set serveroutput on;
 
execute toolVendor.printInventoryStatistics;
Total items in inventory: 1
shampoo 10
 
PL/SQL procedure successfully completed.

次のコマンドを使用して、すべての接続を終了します。

Command> disconnect all;

AUTHID DEFINERの使用

この例では、定義者権限を使用してプロシージャを定義した場合の結果について説明しています。ツール・ユーザーbrandXおよびbrandYがprintInventoryStatisticsを実行すると、ツール・ベンダー(定義者)に属するmyInventoryのデータが表示されることに注意してください。

例5-24 AUTHID DEFINERの使用

Command> run definer.sql
 
use toolVendor;

create table myInventory (name varchar2(100), inventoryCount tt_integer);
insert into myInventory values('butter', 1);
1 row inserted.
 
create or replace procedure printInventoryStatistics authid definer is
 inventoryCount pls_integer;
begin
 select count(*) into inventoryCount from myInventory;
 dbms_output.put_line('Total items in inventory: ' || inventoryCount);
 for currentItem in (select * from myInventory) loop
   dbms_output.put_line(currentItem.name || ' ' || currentItem.inventoryCount);
 end loop;
end;
/
 
Procedure created.
 
grant execute on printInventoryStatistics to brandX;
grant execute on printInventoryStatistics to brandY;
 
use brandX;
create table myInventory (name varchar2(100), inventoryCount tt_integer);
insert into myInventory values('toothpaste', 100);
1 row inserted.
set serveroutput on;
 
execute toolVendor.printInventoryStatistics;
Total items in inventory: 1
butter 1
 
PL/SQL procedure successfully completed.
 
use brandY;
create table myInventory (name varchar2(100), inventoryCount tt_integer);
insert into myInventory values('shampoo', 10);
1 row inserted.
set serveroutput on;
 
execute toolVendor.printInventoryStatistics;
Total items in inventory: 1
butter 1
 
PL/SQL procedure successfully completed.

この場合、brandXおよびbrandYはそれぞれプロシージャを介してtoolVendor.myInventory表にアクセスできますが、直接にはアクセスできないことを理解しておくことも有用です。定義者権限を主にこのように使用することによって、プロシージャの処理を介して表または他のSQLオブジェクトに明確に制限付きでアクセスできるようになります。

Command> use brandX;
brandx: Command> select * from toolVendor.myInventory;
15100: User BRANDX lacks privilege SELECT on TOOLVENDOR.MYINVENTORY
The command failed.

brandx: Command> use brandY;
brandy: Command> select * from toolVendor.myInventory;
15100: User BRANDY lacks privilege SELECT on TOOLVENDOR.MYINVENTORY
The command failed.

次のコマンドを使用して、すべての接続を終了します。

Command> disconnect all;

システム・ビューを問い合せる 例

この項では、システム・ビューを問い合せる例を示します。

例5-25 システム・ビューUSER_SOURCEの問合せ

この例では、USER_SOURCEシステム・ビューを問い合せて、例5-4のプロシージャquery_empのソース・コードを確認します。(この例を実行する前に、このプロシージャを作成する必要があります。)

Command> SELECT SUBSTR (text, 1, LENGTH(text)-1)
       > FROM user_source
       > WHERE name = 'QUERY_EMP' AND type = 'PROCEDURE';

このコードでは、次の出力が生成されます。

< PROCEDURE query_emp >
< (p_id IN employees.employee_id%TYPE, >
<  p_name OUT employees.last_name%TYPE, >
<  p_salary OUT employees.salary%TYPE) IS >
< BEGIN >
< SELECT last_name, salary INTO p_name, p_salary >
< FROM employees >
< WHERE employee_id = p_id; >
< END query_emp; >
9 rows found.

注意:

他のUSER_*システム・ビューと同様に、USER_SOURCEシステム・ビューのSELECT権限はすべてのユーザーにあります。