ヘッダーをスキップ
Oracle TimesTen In-Memory Database PL/SQL開発者ガイド
リリース11.2.1
B56057-01
  目次
目次		 索引
索引
戻る
戻る 		 次へ
次へ		  

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

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

次の例があります。

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

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

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

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

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.

INSERT文を使用する例

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

例5-2 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プログラムでのカーソルの使用」で説明しているように、Oracle TimesTen In-Memory Databaseではカーソルがサポートされています。 カーソルを使用して、SELECT文の結果セットを処理します。

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

値のフェッチ

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

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

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

%NOTFOUNDは、INSERT、UPDATEまたはDELETE文がどの行にも影響しなかったか、またはSELECT INTO文で行が返されなかった場合にTRUEを生成します。 詳細は、『Oracle Database PL/SQL言語リファレンス』の%NOTFOUND属性に関する説明を参照してください。

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-4 レコードへの値のフェッチ

この例は例5-3と似ており、同じ結果を返しますが、値を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-5に、%ROWCOUNTカーソル属性と%NOTFOUNDカーソル属性（例5-3および例5-4で説明済）の使用方法を示します。

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

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

%ROWCOUNTは、INSERT、UPDATEまたはDELETE文で影響を受けた行の数、またはSELECT...INTO文やFETCH...INTO文で返された行の数を生成します。 詳細は、『Oracle Database PL/SQL言語リファレンス』のSQL%ROWCOUNT属性に関する説明を参照してください。

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-6 カーソルFORループの使用

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

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-7 副問合せでのカーソルFORループの使用

この例では、副問合せを使用したFORループを示します。 結果は例5-6と同じです。

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操作」で説明しているように、Oracle TimesTen In-Memory Databaseでは、バルク・バインドとFORALL文およびBULK COLLECT機能がサポートされています。

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

SQL%BULK_ROWCOUNTでのFORALLの使用

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

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

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

例5-8 SQL%BULKROWCOUNTでの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-9 問合せでの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-10では、カーソルを使用して表から行をバルク・コレクトします。

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

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

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.

EXECUTE IMMEDIATEを使用する例

「PL/SQLでの動的SQL（EXECUTE IMMEDIATE文）」で説明しているように、TimesTenでは、EXECUTE IMMEDIATE文がサポートされています。 この項では、EXECUTE IMMEDIATEを使用してPL/SQL接続属性を変更したり、TimesTen組込みプロシージャをコールする方法など、TimesTenでのPL/SQLアプリケーション開発を検討するために、さらに例を説明します。

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

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

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.

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

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

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.

例5-13 TimesTen固有の構文でのEXECUTE IMMEDIATEの使用

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

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.

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

Oracle TimesTen In-Memory Databaseでは、ttConfigurationのようなTimesTen固有の組込みプロシージャが数多くサポートされています。 EXECUTE IMMEDIATE文をCALLとともに使用すると、これらの組込みプロシージャをコールできます。

たとえば、組込みプロシージャttConfigurationをコールするには、PL/SQLのレコード型を作成した後、SELECT INTOを実行してそのレコード型に値を入れます。 ttConfigurationでは2つ以上の行が返されるため、BULK COLLECTを使用します。

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

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.

RETURNING INTOを使用する例

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

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

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

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

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および姓をスクリプトの実行前にも表示します。

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つのスクリプトを1箇所のみ変更して2回実行します。1回目は実行者権限を使用するAUTHID CURRENT_USERを指定し、2回目は定義者権限を使用するAUTHID DEFINERを指定して、PL/SQLプロシージャを定義します。 詳細は、「定義者権限および実行者権限」を参照してください。

スクリプトでは、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;

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

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

例5-15 AUTHID CURRENT_USERの使用

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

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;

例5-16 AUTHID DEFINERの使用

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

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;
  戻る
戻る 		 次へ
次へ
  目次
目次		 索引
索引