| Oracle® TimesTen In-Memory Database PL/SQL開発者ガイド リリース18.1 E98629-04 |
|
 前 |
 次 |
この章では、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, 128 > < 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, 128 > < 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(7).name
|| ' Value: ' || v_ttConfigs(7).value);
END;
/
Name: CommitBufferSizeMax Value: 10
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.
この項では、システム・ビューを問い合せる例を示します。
例5-23 システム・ビュー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権限はすべてのユーザーにあります。 |
