| Oracle TimesTen In-Memory Database PL/SQL開発者ガイド リリース11.2.1 B56057-01 |
|
 戻る |
 次へ |
TimesTenでPL/SQLを使用することのメリットの1つとして、PL/SQLの手続き型構成要素を柔軟かつ強力なTimesTen SQL言語と統合できるという点をあげることができます。
この章では、『Oracle Database PL/SQL言語リファレンス』のPL/SQLの概要に関する説明に示されている主要なPL/SQLプログラミング機能について概説します。 簡単な例から始めることにより、TimesTenにおけるPL/SQLの使用方法を学習できます。 特に注意書きがないかぎり、これらの例はTimesTenとOracleの場合と同じ結果になります。
TimesTen固有の考慮事項の詳細は、章の最後を参照してください。
主な機能は次のとおりです。
|
注意: 他に説明がないかぎり、このマニュアルの例では、TimesTenttIsqlユーティリティを使用します。 例の出力を表示するには、設定SET SERVEROUTPUT ONを使用します。 ttIsqlユーティリティの詳細は、『Oracle TimesTen In-Memory Databaseリファレンス』のttIsqlに関する説明を参照してください。 |
PL/SQLソース・プログラムの基本単位はブロック、つまり無名ブロックであり、関連する宣言および文をグループ化します。 Oracle TimesTen In-Memory Databaseでは、PL/SQLブロックがサポートされています。
PL/SQLブロックは、キーワードDECLARE、BEGIN、EXCEPTIONおよびENDで定義されます。 例2-1に、PL/SQLブロックの基本構成を示します。
|
注意: Oracle In-Memory Database Cacheを使用する場合、PL/SQLブロックはOracleにパススルーできません。 |
例2-1 PL/SQLブロックの構成
DECLARE --(optional) -- Variables, cursors, user-defined exceptions BEGIN --(mandatory) -- PL/SQL statements EXCEPTION --(optional) -- Actions to perform when errors occur END -- (mandatory)
PL/SQLプログラムでは、無名ブロックまたは名前付きブロックのいずれも定義できます。 次の例では、TimesTenデータベースのemployees表に問合せを発行して、PL/SQL変数にデータを返す無名ブロックを作成します。
Command> SET SERVEROUTPUT ON;
Command> DECLARE
> v_fname VARCHAR2 (20);
> BEGIN
> SELECT first_name
> INTO v_fname
> FROM employees
> WHERE employee_id = 100;
> DBMS_OUTPUT.PUT_LINE (v_fname);
> END;
> /
Steven
PL/SQL procedure successfully completed.
PL/SQLで変数および定数を定義した後、SQLとプロシージャ文の式を使用できる任意の場所でこれらを使用できます。
次に例を示します。
Command> DECLARE
> v_hiredate DATE;
> v_deptno NUMBER (2) NOT NULL := 10;
> v_location VARCHAR2 (13) := 'San Francisco';
> c_comm CONSTANT NUMBER := 1400;
%TYPE属性を使用すると、TimesTenの列定義または宣言済の別の変数に応じた変数を宣言できます。 たとえば、%TYPEを使用して、変数emp_lnameおよびmin_balanceを作成します。
Command> DECLARE
> emp_lname employees.last_name%TYPE;
> balance NUMBER (7,2);
> min_balance balance%TYPE:= 1000;
> BEGIN
> SELECT last_name INTO emp_lname FROM employees WHERE employee_id = 100;
> DBMS_OUTPUT.PUT_LINE (emp_lname);
> DBMS_OUTPUT.PUT_LINE (min_balance);
> END;
> /
King
1000
PL/SQL procedure successfully completed.
次の方法で、値を変数に代入できます。
代入演算子（:=）を使用します（例2-2）。
値を選択またはフェッチし、変数に入れます（例2-3）。
変数をOUTパラメータまたはIN OUTパラメータとしてサブプログラムに渡した後、サブプログラム内で値を代入します（例2-4）。
|
注意: これらの例で使用されているDBMS_OUTPUTパッケージは、TimesTenに付属しています。 このパッケージおよびその他の付属パッケージの詳細は、第9章「TimesTenが提供するPL/SQLパッケージ」を参照してください。 |
例2-2 代入演算子を使用した変数への値の代入
Command> DECLARE -- Assign values in the declarative section
> wages NUMBER;
> hours_worked NUMBER := 40; -- Assign 40 to hours_worked
> hourly_salary NUMBER := 22.50; -- Assign 22.50 to hourly_salary
> bonus NUMBER := 150; -- Assign 150 to bonus
> country VARCHAR2(128);
> counter NUMBER := 0; -- Assign 0 to counter
> done BOOLEAN;
> valid_id BOOLEAN;
> emp_rec1 employees%ROWTYPE;
> emp_rec2 employees%ROWTYPE;
> TYPE commissions IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
> comm_tab commissions;
> BEGIN -- Assign values in the executable section
> wages := (hours_worked * hourly_salary) + bonus;
> country := 'France';
> country := UPPER('Canada');
> done := (counter > 100);
> valid_id := TRUE;
> emp_rec1.first_name := 'Theresa';
> emp_rec1.last_name := 'Bellchuck';
> emp_rec1 := emp_rec2;
> comm_tab(5) := 20000 * 0.15;
> END;
> /
PL/SQL procedure successfully completed.
|
注意: この例ではレコードを使用しています。レコードとは、様々なデータ型のフィールドを持つ複合データ構造のことです。 例のように%ROWTYPE属性を使用すると、表内の行または問合せ結果セットの行を表すレコードを宣言できます。 レコードの詳細は、「PL/SQLの複合データ型」を参照してください。 |
例2-3 SELECT INTOを使用した変数への値の代入
従業員の給与の10%を選択して、bonus変数に入れます。
Command> DECLARE
> bonus NUMBER(8,2);
> emp_id NUMBER(6) := 100;
> BEGIN
> SELECT salary * 0.10 INTO bonus FROM employees
> WHERE employee_id = emp_id;
> DBMS_OUTPUT.PUT_LINE (bonus);
> END;
> /
2400
PL/SQL procedure successfully completed.
例2-4 サブプログラムのパラメータとしての変数への値の代入
変数new_salを宣言した後、変数をパラメータ（sal）としてプロシージャadjust_salaryに渡します。 プロシージャadjust_salaryでは、job_id='ST_CLERK'の従業員の平均給与を計算した後、salを更新します。 プロシージャの実行後、変数の値が表示され、変数が正しく更新されたことが示されます。
Command> DECLARE
> new_sal NUMBER(8,2);
> emp_id NUMBER(6) := 126;
> PROCEDURE adjust_salary (emp_id NUMBER, sal IN OUT NUMBER) IS
> emp_job VARCHAR2(10);
> avg_sal NUMBER(8,2);
> BEGIN
> SELECT job_id INTO emp_job FROM employees
> WHERE employee_id = emp_id;
> SELECT AVG(salary) INTO avg_sal FROM employees
> WHERE job_id = emp_job;
> DBMS_OUTPUT.PUT_LINE ('The average salary for ' || emp_job
> || ' employees: ' || TO_CHAR(avg_sal));
> sal := (sal + avg_sal)/2;
> DBMS_OUTPUT.PUT_LINE ('New salary is ' || sal);
> END;
> BEGIN
> SELECT AVG(salary) INTO new_sal FROM employees;
> DBMS_OUTPUT.PUT_LINE ('The average salary for all employees: '
> || TO_CHAR(new_sal));
> adjust_salary(emp_id, new_sal);
> DBMS_OUTPUT.PUT_LINE ('Salary should be same as new salary ' ||
> new_sal);
> END;
> /
The average salary for all employees: 6461.68
The average salary for ST_CLERK employees: 2785
New salary is 4623.34
Salary should be same as new salary 4623.34
PL/SQL procedure successfully completed.
|
注意: この例では、ブロック内にPL/SQLブロックをネストする機能が示されています。 外側の無名ブロックに、囲まれたプロシージャが含まれています。 このPROCEDURE文は、「PL/SQLのプロシージャおよびファンクション」で説明しているCREATE PROCEDUREとは異なります。後者はユーザー・スキーマに格納されたまま残るサブプログラムを作成します。 |
PL/SQLプログラミング言語では、PL/SQL文で使用できる組込みファンクションがサポートされています。 これらの組込みファンクションは、PL/SQL言語の一部である標準パッケージに含まれています。
Oracle TimesTen In-Memory Databaseでは、SQLの式で起動されるSQLファンクションがサポートされています。 PL/SQLを使用する際に、静的および動的なSQL文でSQLファンクションを使用します。
この後の2つの例では、PL/SQLの代入文と静的なSQL文の両方で、それぞれにファンクションRTRIMを使用します。 ファンクションの名前は同じ（RTRIM）ですが、最初の例ではPL/SQL組込みファンクションが起動され、2つ目の例ではTimesTen SQLファンクションが起動されます。
|
注意: PL/SQL組込みファンクションをTimesTen組込みSQLファンクションと混同しないようにしてください。後者の詳細は、『Oracle TimesTen In-Memory Databaseリファレンス』の組込みプロシージャに関する説明を参照してください。 |
例2-5 PL/SQL RTRIM組込みファンクションの使用
TimesTen PL/SQL RTRIM組込みファンクションを使用して、文字列から最右端のxおよびyの文字を削除します。 注意: RTRIMはPL/SQLの代入文で使用します。
Command> DECLARE p_var VARCHAR2(30);
> BEGIN
> p_var := RTRIM ('RTRIM Examplexxxyyyxyxy', 'xy');
> DBMS_OUTPUT.PUT_LINE (p_var);
> END;
> /
RTRIM Example
PL/SQL procedure successfully completed.
例2-6 SQL RTRIMファンクションの使用
TimesTen SQLファンクションRTRIMを使用して、文字列から最右端のxおよびyの文字を削除します。 RTRIMは静的なSQL文で使用することに注意してください。
Command> DECLARE tt_var VARCHAR2 (30);
> BEGIN
> SELECT RTRIM ('RTRIM Examplexxxyyyxyxy', 'xy')
> INTO tt_var FROM DUAL;
> DBMS_OUTPUT.PUT_LINE (tt_var);
> END;
> /
RTRIM Example
PL/SQL procedure successfully completed.
表2-1に、PL/SQL組込みファンクションとTimesTen SQLファンクションの違いをまとめます。
制御構造は、SQLに対する最も重要なPL/SQLの拡張機能です。 Oracle TimesTen In-Memory Databaseでは、Oracle Databaseと同じ制御構造がサポートされています。
ここで説明する制御構造のタイプは、次のとおりです。
TimesTen In-Memory DatabaseとOracle Databaseの両方で、CONTINUE文がサポートされています。
条件制御の例としては、IF-THEN-ELSE文およびCASE文があげられます。 例2-7では、IF-THEN-ELSE文を使用して、現在の給与を基に従業員の昇給を決定します。 また、CASE文を使用して、従業員のjob_idを基に実行する一連の処理を選択します。
例2-7 IF-THEN-ELSE文およびCASE文の使用
Command> DECLARE
> jobid employees.job_id%TYPE;
> empid employees.employee_id%TYPE := 115;
> sal employees.salary%TYPE;
> sal_raise NUMBER(3,2);
> BEGIN
> SELECT job_id, salary INTO jobid, sal from employees
> WHERE employee_id = empid;
> CASE
> WHEN jobid = 'PU_CLERK' THEN
> IF sal < 3000 THEN sal_raise := .12;
> ELSE sal_raise := .09;
> END IF;
> WHEN jobid = 'SH_CLERK' THEN
> IF sal < 4000 THEN sal_raise := .11;
> ELSE sal_raise := .08;
> END IF;
> WHEN jobid = 'ST_CLERK' THEN
> IF sal < 3500 THEN sal_raise := .10;
> ELSE sal_raise := .07;
> END IF;
> ELSE
> BEGIN
> DBMS_OUTPUT.PUT_LINE('No raise for this job: ' || jobid);
> END;
> END CASE;
> DBMS_OUTPUT.PUT_LINE ('Original salary ' || sal);
> -- Update
> UPDATE employees SET salary = salary + salary * sal_raise
> WHERE employee_id = empid;
> END;
> /
Original salary 3100
PL/SQL procedure successfully completed.
反復制御構造では、指定した条件がTRUEであるかぎり、文のシーケンスが繰り返し実行されます。 反復操作の実行にはループ構造が使用されます。
次の3つのタイプのループがあります。
基本ループ
FORループ
WHILEループ
基本ループは、全体にわたる条件を使用しないで反復処理を実行します。 FORループは、カウントに基づいて反復処理を実行します。 WHILEループは、条件に基づいて反復処理を実行します。
例2-8 WHILEループの使用
Command> CREATE TABLE temp (tempid NUMBER(6),
> tempsal NUMBER(8,2),
> tempname VARCHAR2(25));
Command> DECLARE
> sal employees.salary%TYPE := 0;
> mgr_id employees.manager_id%TYPE;
> lname employees.last_name%TYPE;
> starting_empid employees.employee_id%TYPE := 120;
> BEGIN
> SELECT manager_id INTO mgr_id
> FROM employees
> WHERE employee_id = starting_empid;
> WHILE sal <= 15000 LOOP -- loop until sal > 15000
> SELECT salary, manager_id, last_name INTO sal, mgr_id, lname
> FROM employees WHERE employee_id = mgr_id;
> END LOOP;
> INSERT INTO temp VALUES (NULL, sal, lname);
> -- insert NULL for tempid
> COMMIT;
> EXCEPTION
> WHEN NO_DATA_FOUND THEN
> INSERT INTO temp VALUES (NULL, NULL, 'Not found');
> -- insert NULLs
> COMMIT;
> END;
> /
PL/SQL procedure successfully completed.
Command> SELECT * FROM temp;
< <NULL>, 24000, King >
1 row found.
CONTINUE文はOracle 11gリリースで追加された文で、TimesTenでもサポートされています。 CONTINUE文を使用すると、ループ内の制御を新しい反復に移すことができます。
例2-9 CONTINUE文の使用
この例では、ループの10回の反復のそれぞれで、最初のv_totalの代入が実行されます。 2つ目のv_totalの代入は、ループの最初の5回の反復で実行されます。 CONTINUE文はループ内の制御を新しい反復に移すため、ループの最後の5回の反復では2つ目のv_totalの代入は実行されません。 最終的なv_totalの値は、70になります。
Command> DECLARE
> v_total SIMPLE_INTEGER := 0;
> BEGIN
> FOR i IN 1..10 LOOP
> v_total := v_total + i;
> DBMS_OUTPUT.PUT_LINE ('Total is : ' || v_total);
> CONTINUE WHEN i > 5;
> v_total := v_total + i;
> DBMS_OUTPUT.PUT_LINE ('Out of loop Total is: ' || v_total);
> END LOOP;
> END;
> /
Total is : 1
Out of loop Total is: 2
Total is : 4
Out of loop Total is: 6
Total is : 9
Out of loop Total is: 12
Total is : 16
Out of loop Total is: 20
Total is : 25
Out of loop Total is: 30
Total is : 36
Total is : 43
Total is : 51
Total is : 60
Total is : 70
PL/SQL procedure successfully completed.
TimesTenでは、ODBC、OCI、Pro*C/C++、JDBCまたはTimesTen TTClasses（C++）を使用したクライアント・アプリケーションからのPL/SQLの実行がサポートされています。
前述のとおり、ブロックとは、PL/SQLソース・プログラムの基本単位です。 無名ブロックも前述のとおりです。 一方、プロシージャおよびファンクション（サブプログラムとも呼ばれます）とは、指定した名前で定義されているPL/SQLブロックのことです。 （これらの定義および作成方法の詳細は、「PL/SQLのプロシージャおよびファンクション」を参照してください。）
TimesTenでは、PL/SQLプロシージャまたはファンクションは、無名ブロックで実行する必要があります。
次のファンクションについて考えてみます。
create or replace function mytest return number is begin return 1; end; /
TimesTenでは、次のようにmytestを実行できます。
Command> declare
> n number;
> begin
> n := mytest;
> end;
> /
PL/SQL procedure successfully completed.
Oracleでは、次のようにCALL文を使用するか、またはSQL文からmytestを実行できます。 これらの実行メカニズムは、TimesTenではサポートされていません。
CALL文の場合:
SQL> variable n number; SQL> call mytest() into :n; Call completed.
SELECT文の場合:
SQL> select mytest from dual;
MYTEST
----------
1
この項では、アプリケーションとPL/SQL間でのデータの受渡しに関し、次の内容について説明します。
詳細は、『Oracle Database PL/SQL言語リファレンス』のバインド引数に関する説明を参照してください。
バインド変数に:var表記を使用すると、アプリケーション（CまたはJavaアプリケーションなど）とPL/SQL間でバインド変数を受け渡すことができます。 バインド変数（ホスト変数と呼ばれる場合もあります）という用語は、これまでTimesTenで使用されてきたパラメータという用語と同様に使用し、アプリケーションのバインド変数とは、PL/SQLプロシージャまたはファンクションの仕様部で宣言されたパラメータに相当します。
次に、指定した従業員IDと一致する従業員の名前、および給与を取得するPL/SQLプロシージャをコールするときに、ttIsqlを使用する例を簡単に示します。この例では、ttIsqlは基本的にコール元のアプリケーションのように動作し、名前および給与はPL/SQLからの出力になります。
Command> VARIABLE b_name VARCHAR2 (25)
Command> VARIABLE b_sal NUMBER
Command> BEGIN
> query_emp (171, :b_name, :b_sal);
> END;
> /
PL/SQL procedure successfully completed.
Command> PRINT b_name
B_NAME : Smith
Command> PRINT b_sal
B_SAL : 7400
詳細な例については、「入力パラメータ、出力パラメータおよびバインド変数を使用する例」を参照してください。
プロシージャおよびファンクションの作成および定義方法の詳細は、「PL/SQLのプロシージャおよびファンクション」を参照してください。
これらの言語の例および詳細は、『Oracle TimesTen In-Memory Database C開発者ガイド』のパラメータのバインドに関する説明、および『Oracle TimesTen In-Memory Database Java開発者ガイド』のSQL文の準備および入力パラメータの設定に関する説明を参照してください。
パラメータ・モードは、PL/SQLサブプログラムの仕様部で宣言されたパラメータを入力、出力またはその両方のいずれに使用するかを定義します。 IN（デフォルト）、OUTおよびIN OUTの3つのパラメータ・モードがあります。
INパラメータでは、起動されるサブプログラムに値を渡すことができます。 サブプログラム内では、INパラメータは定数のように動作するため、値を代入することはできません。 INパラメータとして、定数、リテラル、初期化された変数または式を渡すことができます。
OUTパラメータは、サブプログラムのコール元に値を返します。 サブプログラム内では、OUTパラメータは変数のように動作します。 その値を変更して、代入後に値を参照できます。
IN OUTパラメータは初期値をサブプログラムに渡し、更新された値をコール元に返します。 パラメータに値を代入したり、その値を読み取ることができます。 一般的に、IN OUTパラメータは、文字列バッファ、または数値アキュムレータであり、サブプログラム内で読み取られた後に更新されます。 IN OUT仮パラメータに対応する実パラメータは、定数や式ではなく、変数である必要があります。
「入力パラメータ、出力パラメータおよびバインド変数を使用する例」を参照してください。
PL/SQLは、SQL言語を通じてTimesTenデータベースと密接に統合されています。 この項では、PL/SQLにおける次のSQL機能の使用について説明します。
PL/SQL内から、次の文を静的SQLとして実行できます。
DML文: INSERT、UPDATE、DELETEおよびMERGE
問合せ: SELECT
トランザクション制御: COMMITおよびROLLBACK
|
注意:
|
これらのSQL文の詳細は、『Oracle TimesTen In-Memory Database SQLリファレンス・ガイド』のSQL文に関する説明を参照してください。
次の例2-10に、問合せの実行方法を示します。 PL/SQLでTimesTen SQLを使用した例の詳細は、第5章「PL/SQLでのTimesTen SQLの使用例」を参照してください。
例2-10 SELECT...INTOを使用したデータの取得
単一行のデータを取得するには、SELECT... INTO文を使用します。 1行も返さないか、または複数の行を返す問合せに対して、TimesTenはエラーを返します。
この例では、HRスキーマのemployees表から、employee_id=100の従業員のhire_dateおよびsalaryを取得します。
Command> run selectinto.sql DECLARE v_emp_hiredate employees.hire_date%TYPE; v_emp_salary employees.salary%TYPE; BEGIN SELECT hire_date, salary INTO v_emp_hiredate, v_emp_salary FROM employees WHERE employee_id = 100; DBMS_OUTPUT.PUT_LINE(v_emp_hiredate || ' ' || v_emp_salary); END; / 1987-06-17 24000 PL/SQL procedure successfully completed.
次のような場合には、ネイティブ動的SQLを使用できます。
INSERT、UPDATE、DELETEなどのDML文を使用する場合
CREATE、ALTERなどのDDL文を使用する場合 （たとえば、ALTER SESSIONを使用すると、PL/SQL接続属性を変更できます。）
TimesTen組込みプロシージャをコールする場合
1つの使用例として、特に、実行時までSQL文の完全なテキストがわからない場合をあげることができます。 たとえば、コンパイル時に、SELECT文のWHERE句で使用する列の名前がわからないことがあります。 このような場合は、EXECUTE IMMEDIATE文を使用できます。
動的SQLのもう1つの使用例としては、DDLを使用する場合をあげることができます。DDLはPL/SQL内からは静的SQLで実行できません。
例2-11に、EXECUTE IMMEDIATEの例を示します。 その他の例の詳細は、「EXECUTE IMMEDIATEを使用する例」を参照してください。
例2-11 EXECUTE IMMEDIATE文を使用した表の作成
コンパイル時に表定義がわからない場合を考えてみます。 EXECUTE IMMEDIATE文を使用することで、実行時に表を作成できます。 この例では、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 *)
バルク・バインドは、PL/SQLからSQL文を実行する場合に使用される強力な機能であり、SQLとPL/SQL間での大量データの移動に使用されます。 （アプリケーション・プログラムからPL/SQLへのパラメータのバインドと混同しないでください。） バルク・バインドでは、ループを使用してFETCH、INSERT、UPDATEおよびDELETE操作を複数回実行するのではなく、単一の操作で値の配列をバインドします。 Oracle TimesTen In-Memory Databaseではバルク・バインドがサポートされており、これにより、パフォーマンスを大幅に向上できます。
FORALL文を使用して入力コレクションをバルク・バインドしてから、SQLエンジンに送信します。 BULK COLLECTを使用して、SQLから結果のバッチを取得します。 VARRAY、ネストした表、連想配列（索引付き表）などの任意のPL/SQLコレクション型に、バルク・コレクトできます。 （コレクションの詳細は、「コレクションの使用」を参照してください。）
FORALL文では%BULK_EXCEPTIONSカーソル属性およびSAVE EXCEPTIONS句を使用できます。 SAVE EXCEPTIONSを使用すると、挿入または更新の文で例外（制約エラーなど）が発生した場合でも文の実行を続行できます。 例外は配列に収集され、文の実行後に%BULK_EXCEPTIONSを使用して確認できます。 SAVE EXCEPTIONSを使用すると、FORALL文の実行中に例外が発生した場合でも、コレクション内のすべての行が処理されます。 文が終了するとエラーが発行され、1つ以上の例外が発生したことが示されます。 SAVE EXCEPTIONSを使用しなかった場合、FORALL文の実行中に例外が発行されると、文は即時に例外を返し、その他の行は処理されません。
これらの機能の詳細は、『Oracle Database PL/SQL言語リファレンス』のFORALLおよびBULK COLLECTの同時使用に関する説明を参照してください。
例2-12に、バルク・バインドおよびFORALL文の基本的な使用方法を示します。 バルク・バインドの例および詳細は、「FORALLおよびBULK COLLECTを使用する例」を参照してください。
例2-12 FORALL文の使用
次の例のPL/SQLプログラムでは、IDが100、102、104または110の従業員の給与を増やします。FORALL文でコレクションをバルク・バインドします。
Command> CREATE OR REPLACE PROCEDURE raise_salary (p_percent NUMBER) IS
> TYPE numlist_type IS TABLE OF NUMBER
> INDEX BY BINARY_INTEGER;
> v_id numlist_type; -- collection
> BEGIN
> v_id(1) := 100; v_id(2) := 102; v_id (3) := 104; v_id (4) := 110;
> -- bulk-bind the associative array
> FORALL i IN v_id.FIRST .. v_id.LAST
> UPDATE employees
> SET salary = (1 + p_percent/100) * salary
> WHERE employee_id = v_id (i);
> END;
> /
Procedure created.
raise_salaryプロシージャを実行する前に給与を検索します。
Command> SELECT salary FROM employees WHERE employee_id = 100 OR employee_id = 102 OR employee_id = 104 OR employee_id = 100; < 24000 > < 17000 > < 6000 > 3 rows found.
プロシージャを実行して、結果を確認します。
Command> EXECUTE raise_salary (10); PL/SQL procedure successfully completed. Command> SELECT salary FROM employees WHERE employee_id = 100 or employee_id = 102 OR employee_id = 104 OR employee_id = 100; < 26400 > < 18700 > < 6600 > 3 rows found. Command> ROLLBACK;
RETURNING INTO句（DML RETURNINGと呼ばれる場合もあります）をINSERT、UPDATEまたはDELETE文で使用すると、処理の影響を受けた行の（オプションでROWIDを含む）特定の列または式を返すことができます。 これにより、影響を確認したり挿入または更新後のROWIDが必要な場合などに、後続のSELECT文および個々のラウンドトリップが不要になります。
RETURNING INTO句は、動的SQL（EXECUTE IMMEDIATEを使用）と静的SQLのいずれでも使用できます。
PL/SQLのBULK COLLECT機能により、RETURNING INTO句では単一行からパラメータのセットまたはレコードに項目を返すか、または複数行からVARRAY、ネストした表または連想配列（索引付き表）などのPL/SQLコレクションに列を返すことができます。 この句のINTO部分のパラメータは、出力専用である必要があります。入力/出力は使用できません。 コレクションの詳細は、「コレクションの使用」を参照してください。 BULK COLLECTの詳細は、「FORALL操作およびBULK COLLECT操作」および「FORALLおよびBULK COLLECTを使用する例」を参照してください。
SQL構文およびTimesTenのRETURNING INTO句の制限については、『Oracle TimesTen In-Memory Database SQLリファレンス・ガイド』でINSERT、UPDATEおよびDELETEの説明の一部として説明されています。
「RETURNING INTOを使用する例」も参照してください。
RETURNING INTOの詳細は、『Oracle Database PL/SQL言語リファレンス』のRETURNING INTO句に関する説明を参照してください。
PL/SQLプログラムでSQL文を実行すると、そのSQL文は、他のプログラミング言語で記述したアプリケーションから実行された場合と同じ方法で、TimesTenで処理されます。 TimesTen SQLのすべての標準動作が適用されます。 これには、IMDB Cache環境で、PL/SQLからすべてのキャッシュ機能を使用できることも含まれます。 PL/SQLからキャッシュ・グループ内の表にアクセスする際は、これらの表に対する通常のルールが適用されます。 たとえば、動的キャッシュ・グループ内のキャッシュ・インスタンスに対してSELECT文を発行すると、インスタンスがOracle DatabaseからTimesTenに自動的にロードされる場合があります。
特に、この機能に関して次の点が重要です。
PL/SQLで静的SQLを使用する場合、アクセスされる表はすべてTimesTenに存在している必要があります。存在していない場合は、PL/SQLで正しくコンパイルされません。 次の例では、ABCがTimesTenに存在している必要があります。
begin insert into abc values(1, 'Y'); end;
IMDB Cache環境には、TimesTenパススルー機能を使用して、TimesTenからOracle Databaseに自動的にSQL文をルーティングする機能があります。 （パススルー機能の詳細は、『Oracle In-Memory Database Cacheユーザーズ・ガイド』のパススルー・レベルの設定に関する説明を参照してください。）
passthrough=1を指定した場合、アクセスされた表がTimesTenに存在しないときには、文はOracleにパススルーされます。 ただし、PL/SQLでは、動的SQLを使用して文を実行する必要があります。
前述の例を次のように書き換えて、passthrough=1を指定すると、このTimesTen PL/SQLブロックを使用してOracle DatabaseのABCにアクセスできます。
begin execute immediate 'insert into abc values(1, 'Y')'; end;
この場合、SQL文はコンパイル時には検査されないため、TimesTen PL/SQLでブロックをコンパイルできます。
PL/SQLはTimesTenで実行できますが、現在のリリースでは、TimesTenパススルー機能を使用してTimesTenからOracle DatabaseにPL/SQLブロックをルーティングすることはできません。 たとえば、passthrough=3を指定してIMDB Cacheを使用している場合、TimesTen接続で実行されるすべての文がOracle Databaseにルーティングされます。 この場合、PL/SQLブロックはアプリケーション・プログラムからは実行できません。これは、TimesTenでOracle DatabaseへのPL/SQLブロックの転送が試行されるものの、この転送はサポートされていないためです。 （passthrough=1の例では、ブロック全体ではなく、SQL文のみがOracleにルーティングされます。）
明示または暗黙のいずれのカーソルも、SELECT文の結果セットを処理するために使用されます。 プログラマは明示カーソルを宣言して、2つ以上のデータ行を返す問合せを管理できます。 PL/SQLは、明示カーソルと関連付けられていないSELECT文に対して、暗黙カーソルを宣言し、オープンします。
|
重要: TimesTenでは、トランザクションを終了する操作は、接続と関連付けられているすべてのカーソルをクローズすることに注意してください。 この操作には、COMMIT文またはROLLBACK文が含まれます。 また、PL/SQL内で実行されるDDL文も含まれます。これは、PL/SQLが有効な場合にはDDLCommitBehavior接続を0（ゼロ）に設定する必要があり、これにより、DDL文の自動コミットが発生するためです。 |
例2-13に、基本的なカーソルの使用方法を示します。 その他の例および詳細は、「カーソルを使用する例」を参照してください。 また、「PL/SQL REF CURSOR」も参照してください。
例2-13 カーソルを使用した従業員情報の取得
カーソルc1を宣言して、employee_idが120の従業員の姓、給与、入社日および職階を取得します。
Command> DECLARE
> CURSOR c1 IS
> SELECT last_name, salary, hire_date, job_id FROM employees
> WHERE employee_id = 120;
> --declare record variable that represents a row
> --fetched from the employees table
> employee_rec c1%ROWTYPE;
> BEGIN
> -- open the explicit cursor
> -- and use it to fetch data into employee_rec
> OPEN c1;
> FETCH c1 INTO employee_rec;
> DBMS_OUTPUT.PUT_LINE('Employee name: ' || employee_rec.last_name);
> CLOSE c1;
> END;
> /
Employee name: Weiss
PL/SQL procedure successfully completed.
プロシージャおよびファンクションとは、指定した名前で定義されているPL/SQLブロックのことです。 これらはサブプログラムともいいます。
スタンドアロンのサブプログラムは、CREATE PROCEDURE文またはCREATE FUNCTION文を使用して、データベース・レベルで作成します。
すでにサブプログラムが存在している場合にそのサブプログラムを置換するには、オプションでCREATE OR REPLACE PROCEDUREまたはCREATE OR REPLACE FUNCTIONを使用します。
プロシージャまたはファンクションを明示的にコンパイルするか、またはコンパイル・オプションを変更する場合は、ALTER PROCEDUREまたはALTER FUNCTIONを使用します。 （パッケージに含まれるプロシージャまたはファンクションをコンパイルし直すには、ALTER PACKAGE文を使用してパッケージを再コンパイルします。）
TimesTenでは、CREATE PROCEDUREおよびCREATE FUNCTIONの構文は、Oracleでサポートされている内容の一部です。 これらの文およびTimesTenのALTER PROCEDURE文およびALTER FUNCTION文の詳細は、『Oracle TimesTen In-Memory Database SQLリファレンス・ガイド』のSQL文に関する説明を参照してください。
|
注意:
|
次に、OUTパラメータを使用したプロシージャの作成例を示します。 その他の例の詳細は、第6章「スタンドアロンのサブプログラムを使用する例」を参照してください。
例2-14 OUTパラメータを使用したプロシージャの作成
Command> CREATE OR REPLACE PROCEDURE get_employee
> (p_empid in employees.employee_id%TYPE,
> p_sal OUT employees.salary%TYPE,
> p_job OUT employees.job_id%TYPE) IS
> BEGIN
> SELECT salary,job_id
> INTO p_sal, p_job
> FROM employees
> WHERE employee_id = p_empid;
> END;
> /
Procedure created.
Command> VARIABLE v_salary NUMBER
Command> VARIABLE v_job VARCHAR2(15)
Command> BEGIN
> GET_EMPLOYEE (120, :v_salary, :v_job);
> END;
> /
PL/SQL procedure successfully completed.
Command> PRINT
V_SALARY : 8000
V_JOB : ST_MAN
Command> SELECT salary, job_id FROM employees WHERE employee_id = 120;
< 8000, ST_MAN >
1 row found.
この項では、PL/SQLパッケージの作成および使用方法について説明します。
TimesTen付属のPL/SQLパッケージの詳細は、「TimesTen付属のPL/SQLパッケージ」を参照してください。
パッケージは、論理的に関連するPL/SQLデータ型、変数およびサブプログラムをグループ化したデータベース・オブジェクトです。 パッケージを指定した後、その本体を個別の手順で定義します。
パッケージ仕様部はパッケージに対するインタフェースで、パッケージの有効範囲外で参照できるパブリックなデータ型、変数、定数、例外、カーソルおよびサブプログラムを宣言します。 本体では、仕様部で宣言したオブジェクト以外に、カーソルの問合せ、サブプログラムのコードおよびパッケージ外のアプリケーションからは参照できないプライベートなオブジェクトを定義します。
TimesTenでは、パッケージ仕様部をパッケージ本体とは別にデータベースに格納します。 パブリックなプログラム・オブジェクトをコールまたは参照する他のスキーマ・オブジェクトは、パッケージ本体ではなく、パッケージ仕様部にのみ依存します。
|
注意: パッケージおよびパッケージ本体を作成する構文は、Oracleと同じです。ただし、OracleのマニュアルにはDBMSSTDX.SQLというスクリプトを実行する必要があることが記載されていますが、これはTimesTenには該当しません。 |
パッケージを作成してTimesTenデータベースに永続的に格納するには、CREATE PACKAGE文およびCREATE PACKAGE BODY文を使用します。
新しいパッケージを作成するには、次の手順を実行します。
CREATE PACKAGE文を使用してパッケージ仕様部を作成します。
パッケージ仕様部ではプログラム・オブジェクトを宣言できます。 このようなオブジェクトはパブリック・オブジェクトと呼ばれ、パッケージ内の他のオブジェクトだけでなくパッケージの外部からも参照できます。
すでにパッケージが存在している場合にそのパッケージ仕様部を置換するには、オプションでCREATE OR REPLACE PACKAGEを使用します。
CREATE PACKAGE BODY（またはCREATE OR REPLACE PACKAGE BODY）文を使用して、パッケージ本体を作成します。
パッケージ本体ではプログラム・オブジェクトを宣言および定義できます。
パッケージ仕様部で宣言したパブリック・オブジェクトを定義する必要があります。
プライベート・オブジェクトと呼ばれる追加のパッケージ・オブジェクトを宣言および定義できます。 プライベート・オブジェクトは、パッケージ内の他のオブジェクトからのみ参照できるよう、パッケージ仕様部ではなくパッケージ本体で宣言します。 パッケージの外部からは参照できません。
パッケージのメンバーであるプロシージャおよびファンクションを明示的にコンパイルするか、またはコンパイル・オプションを変更する場合は、ALTER PACKAGEを使用します。
CREATE PACKAGE、CREATE PACKAGE BODYおよびALTER PACKAGE文の詳細は、『Oracle TimesTen In-Memory Database SQLリファレンス・ガイド』のSQL文に関する説明を参照してください。
例2-15 パッケージの作成および使用
新しい従業員を雇用したときに従業員表に1行追加するケース、および従業員が退社したときに従業員表から1行削除するケースについて考えてみます。 次の例では、これらのタスクを実行する2つのプロシージャを作成して、1つのパッケージにまとめます。 パッケージには、特定の従業員よりも給与が高い従業員の数を返すファンクションも含まれています。 例では、この後ファンクションおよびプロシージャを実行して、結果を確認しています。
Command> CREATE OR REPLACE PACKAGE emp_actions AS
> PROCEDURE hire_employee (employee_id NUMBER,
> last_name VARCHAR2,
> first_name VARCHAR2,
> email VARCHAR2,
> phone_number VARCHAR2,
> hire_date DATE,
> job_id VARCHAR2,
> salary NUMBER,
> commission_pct NUMBER,
> manager_id NUMBER,
> department_id NUMBER);
> PROCEDURE remove_employee (emp_id NUMBER);
> FUNCTION num_above_salary (emp_id NUMBER) RETURN NUMBER;
> END emp_actions;
> /
Package created.
Command> -- Package body:
> CREATE OR REPLACE PACKAGE BODY emp_actions AS
> -- Code for procedure hire_employee:
> PROCEDURE hire_employee (employee_id NUMBER,
> last_name VARCHAR2,
> first_name VARCHAR2,
> email VARCHAR2,
> phone_number VARCHAR2,
> hire_date DATE,
> job_id VARCHAR2,
> salary NUMBER,
> commission_pct NUMBER,
> manager_id NUMBER,
> department_id NUMBER) IS
> BEGIN
> INSERT INTO employees VALUES (employee_id,
> last_name,
> first_name,
> email,
> phone_number,
> hire_date,
> job_id,
> salary,
> commission_pct,
> manager_id,
> department_id);
> END hire_employee;
> -- Code for procedure remove_employee:
> PROCEDURE remove_employee (emp_id NUMBER) IS
> BEGIN
> DELETE FROM employees WHERE employee_id = emp_id;
> END remove_employee;
> -- Code for function num_above_salary:
> FUNCTION num_above_salary (emp_id NUMBER) RETURN NUMBER IS
> emp_sal NUMBER(8,2);
> num_count NUMBER;
> BEGIN
> SELECT salary INTO emp_sal FROM employees
> WHERE employee_id = emp_id;
> SELECT COUNT(*) INTO num_count FROM employees
> WHERE salary > emp_sal;
> RETURN num_count;
> END num_above_salary;
> END emp_actions;
> /
Package body created.
Command> BEGIN
> /* call function to return count of employees with salary
> greater than salary of employee with employee_id = 120
> */
> DBMS_OUTPUT.PUT_LINE
> ('Number of employees with higher salary: ' ||
> TO_CHAR(emp_actions.num_above_salary(120)));
> END;
> /
Number of employees with higher salary: 33
PL/SQL procedure successfully completed.
33という数が正しいことを確認します。
Command> SELECT salary FROM employees WHERE employee_id = 120; < 8000 > 1 row found. Command> SELECT COUNT (*) FROM employees WHERE salary > 8000; < 33 > 1 row found.
ここで従業員を追加して、結果を確認します。 次に、従業員を削除して、その従業員がemployees表から削除されたことを確認します。
Command> BEGIN
> emp_actions.hire_employee(300,
> 'Belden',
> 'Enrique',
> 'EBELDEN',
> '555.111.2222',
> '31-AUG-04',
> 'AC_MGR',
> 9000,
> .1,
> 101,
> 110);
> END;
> /
PL/SQL procedure successfully completed.
Command> SELECT * FROM employees WHERE employee_id = 300;
< 300, Belden, Enrique, EBELDEN, 555.111.2222, 2004-08-31 00:00:00, AC_MGR, 9000
, .1, 101, 110 >
1 row found.
Command> BEGIN
> emp_actions.remove_employee (300);
> END;
> /
PL/SQL procedure successfully completed.
Command> SELECT * FROM employees WHERE employee_id = 300;
0 rows found.
ラップとは、PL/SQLソース・コードを隠す処理のことです。 PL/SQLソース・コードは、wrapユーティリティでラップできます。 wrapユーティリティは入力用SQLファイルを処理して、パッケージ仕様部、パッケージ本体、ファンクション、プロシージャなど、ファイル内のPL/SQL構成単位のみをラップします。
wrap_test.sqlというファイルを作成する例を考えてみます。ここでの目的は、wraptestというプロシージャを作成することです。 次に、wrapユーティリティを使用して、wrap_test.sqlを処理します。 プロシージャはソース・コードが隠された状態で作成され、正常に実行されます。 最後に、ALL_OBJECTSビューに問合せを発行して、ラップされているソース・コードを確認します。
$ cat wrap_test.sql
CREATE OR REPLACE PROCEDURE wraptest IS
TYPE emp_tab IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER;
all_emps emp_tab;
BEGIN
SELECT * BULK COLLECT INTO all_emps FROM employees;
FOR i IN 1..10
LOOP
DBMS_OUTPUT.PUT_LINE('Emp Id: ' || all_emps(i).employee_id);
END LOOP;
END;
/
$ wrap iname=wrap_test.sql
PL/SQL Wrapper: Release 11.1.0.7.0- Production on Thu Sep 11 23:27:04 2008
Copyright (c) 1993, 2009, Oracle. All rights reserved.
Processing wrap_test.sql to wrap_test.plb
$ cat wrap_test.plb
CREATE OR REPLACE PROCEDURE wraptest wrapped
a000000
1
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
7
109 124
88/TJ0ycbC+uGVlIpcLGCFnYCg8wg+nwf/Ydf3QC2vjqNGMUKbgh9iAYckXK5QNfzYzt+o6D
LS+DZ5zkzuVb3jmo7cYSTwk8NxVuvSQPILBOxv6IcXb88echYysoGXS006xKqkF95sO5A7zY
Pko3h+4fFD7wC2PvQxnuyiVWceKJGUJ7wPUWFCHDet1ym181AY0rd7oXR3tVh4h5d3RhLzNM
xKpGTRsHj7Al9eLe4pAutkqgVVDBveT5RrLRnKoGp79VjbFXinShf9huGTE9mnPh2CJgUw==
/
$ ttIsql SampleDatabase
Copyright (c) 1996-2009, Oracle. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.
connect "DSN=SampleDatabase";
Connection successful: ... PermSize=32;TypeMode=0;PLSQL_MEMORY_ADDRESS=20000000;
(Default setting AutoCommit=1)
Command> @wrap_test.plb
CREATE OR REPLACE PROCEDURE wraptest wrapped
a000000
1
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
7
109 124
88/TJ0ycbC+uGVlIpcLGCFnYCg8wg+nwf/Ydf3QC2vjqNGMUKbgh9iAYckXK5QNfzYzt+o6D
LS+DZ5zkzuVb3jmo7cYSTwk8NxVuvSQPILBOxv6IcXb88echYysoGXS006xKqkF95sO5A7zY
Pko3h+4fFD7wC2PvQxnuyiVWceKJGUJ7wPUWFCHDet1ym181AY0rd7oXR3tVh4h5d3RhLzNM
Command> SET SERVEROUTPUT ON;
Command> BEGIN
> wraptest ();
> END;
> /
Emp Id: 100
Emp Id: 101
Emp Id: 102
Emp Id: 103
Emp Id: 104
Emp Id: 105
Emp Id: 106
Emp Id: 107
Emp Id: 108
Emp Id: 109
PL/SQL procedure successfully completed.
Command> SELECT text FROM all_source WHERE name = 'WRAPTEST';
< PROCEDURE wraptest wrapped
a000000
1
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
7
109 124
88/TJ0ycbC+uGVlIpcLGCFnYCg8wg+nwf/Ydf3QC2vjqNGMUKbgh9iAYckXK5QNfzYzt+o6D
LS+DZ5zkzuVb3jmo7cYSTwk8NxVuvSQPILBOxv6IcXb88echYysoGXS006xKqkF95sO5A7zY
Pko3h+4fFD7wC2PvQxnuyiVWceKJGUJ7wPUWFCHDet1ym181AY0rd7oXR3tVh4h5d3RhLzNM
xKpGTRsHj7Al9eLe4pAutkqgVVDBveT5RrLRnKoGp79VjbFXinShf9huGTE9mnPh2CJgUw==
>
1 row found.
TimesTenでは、トランザクションを終了する操作は、接続と関連付けられているすべてのカーソルをクローズします。 これには次のものがあります。
任意のCOMMIT文またはROLLBACK文。
PL/SQLの任意のDDL文。 これは、PL/SQLが有効になっている場合（PLSQL接続属性が1に設定されている場合）、TimesTen DDLCommitBehavior接続属性はOracleモード（自動コミットDDL）を示す0に設定されている必要があるためです。
たとえば、次の使用例を考えてみます。ここでは、プロシージャ群を再コンパイルします。 最初にALTER PROCEDUREが実行されたときに、カーソル（pnamecurs）がクローズされるため、この例は正しく動作しません。
declare
cursor pnamecurs is select * from all_objects where object_name like 'MYPROC%';
begin
for rec in pnamecurs loop
execute immediate 'alter procedure ' || rec.object_name || ' compile';
end loop;
end;
/
かわりに、次のような処理を実行できます。 ここでは、すべてのプロシージャ名を内部表にフェッチした後、アクティブ・カーソルを使用しないで、これらのプロシージャ名に対してALTER PROCEDUREを実行します。
declare
cursor pnamecurs is select * from all_objects where object_name like 'MYPROC%';
type tbl is table of c%rowtype index by binary_integer;
myprocs tbl;
begin
open pnamecurs;
fetch pnamecurs bulk collect into myprocs;
close pnamecurs;
for i in 1..myprocs.count loop
execute immediate 'alter procedure ' || myprocs(i).object_name || ' compile';
end loop;
end;
/
