この章では、PL/SQLにおけるTimesTen SQLの密接な統合について詳細に検討するため、さらに例を示します。
単一行のデータを取得するには、SELECT... INTO
文を使用します。1行も返さないか、または複数の行を返す問合せに対して、TimesTenはエラーを返します。
この項では、次の例を示します。
この例では、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.
次の例では、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.
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
パラメータを使用します。
この例では、従業員情報を取得するプロシージャ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.
電話番号を書式化する場合を考えてみます。次の例では、電話番号の桁のような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
文の結果セットを処理します。
この項で取り上げる例は次のとおりです。
これらの例のカーソル属性については、『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.
例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.
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操作」で説明しているように、TimesTenでは、バルク・バインドとFORALL
文およびBULK COLLECT
機能がサポートされています。
この項で取り上げる例は次のとおりです。
%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.
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.
例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.
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.
「PL/SQLでの動的SQL(EXECUTE IMMEDIATE文)」に記載のとおり、TimesTenでは、EXECUTE IMMEDIATE
文がサポートされています。この項では、TimesTenでPL/SQLアプリケーションを開発する際の検討事項を次の例で説明します。
コンパイル時に表定義がわからない場合を考えてみます。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 *)
この例では、ファンクション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
文を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.
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.
この例では、EXECUTE IMMEDIATE
文を使用して、TimesTen SELECT FIRST
n
文を実行します。この構文はTimesTenに固有です。
この項では、RETURNING INTO
句を使用する次の2つの例を示します。
概要は、「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.
次の例では、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.
この項では、変更を1つだけ加えてスクリプトをttIsql
で2回実行します(最初は実行者権限のAUTHID CURRENT_USER
でPL/SQLプロシージャを定義し、次に定義者権限のAUTHID DEFINER
で定義します)。関連情報については、「定義者権限および実行者権限」を参照してください。
スクリプトでは、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;
/
...
この例では、実行者権限を使用してプロシージャを定義した場合の結果について説明しています。ツール・ユーザー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;
この例では、定義者権限を使用してプロシージャを定義した場合の結果について説明しています。ツール・ユーザー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 権限はすべてのユーザーにあります。 |