| Oracle® TimesTen In-Memory Database PL/SQL開発者ガイド 11gリリース2 (11.2.2) B66723-04 |
|
 前 |
 次 |
TimesTenでPL/SQLを使用することのメリットの1つとして、PL/SQLの手続き型構成要素を柔軟かつ強力なTimesTen SQL言語と統合できるという点をあげることができます。
この章では、『Oracle Database PL/SQL言語リファレンス』のPL/SQLの概要に関する説明に示されている主要なPL/SQLプログラミング機能について概説します。簡単な例から始めることにより、TimesTenにおけるPL/SQLの使用方法を学習できます。特に注意書きがないかぎり、これらの例はTimesTenとOracle Databaseの場合と同じ結果になります。
TimesTen固有の考慮事項の詳細は、章の最後を参照してください。アプリケーションがTimesTenと一般的に対話する方法、また特にPL/SQLと対話する方法の概要については、「TimesTenのPL/SQLコンポーネントと操作」を参照してください。
この章の主な内容は次のとおりです。
|
注意: 他に説明がないかぎり、このマニュアルの例では、TimesTenttIsqlユーティリティを使用します。例の出力を表示するには、設定SET SERVEROUTPUT ONを使用します。ttIsqlの詳細は、『Oracle TimesTen In-Memory Databaseリファレンス』のttIsqlに関する説明を参照してください。 |
PL/SQLソース・プログラムの基本単位はブロック、つまり無名ブロックであり、関連する宣言および文をグループ化します。TimesTenではPL/SQLブロックをサポートしています。
PL/SQLブロックは、キーワードDECLARE、BEGIN、EXCEPTIONおよびENDで定義します。例2-1に、PL/SQLブロックの基本構成を示します。
|
注意: TimesTen Application-Tier Database Cache (TimesTen Cache)を使用する場合、PL/SQLブロックはOracle Databaseにパススルーできません。(「TimesTen Cacheを使用したTimesTen PL/SQL」も参照してください。) |
例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プログラムでは、無名ブロックまたは名前付きブロックのいずれも定義できます。次の例では、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に付属しています。このパッケージおよびその他の付属パッケージの詳細は、第8章「TimesTenが提供するPL/SQLパッケージ」を参照してください。 |
例2-2 代入演算子を使用した変数への値の代入
Command> DECLARE -- Assign values in the declarative section
> wages NUMBER;
> hours_worked NUMBER := 40;
> hourly_salary NUMBER := 22.50;
> bonus NUMBER := 150;
> country VARCHAR2(128);
> counter NUMBER := 0;
> 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 := 'Amy';
> emp_rec1.last_name := 'Feiner';
> 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文は、CREATE PROCEDURE文とは異なります(後者は、ユーザーのスキーマ内に格納されたままとなるサブプログラムを作成する文で、詳細は「PL/SQLプロシージャおよびファンクション」を参照)。 |
ほとんどのSQLファンクションは、PL/SQLから直接コールすることができます。次に示す最初の例では、ファンクションRTRIMを、PL/SQL代入文内でPL/SQLファンクションとして使用しています。2番目の例では、このファンクションは、静的SQL文内のSQLファンクションとして使用しています。
例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.
TimesTenでのSQLファンクションの詳細は、『Oracle TimesTen In-Memory Database SQLリファレンス』の式に関する説明を参照してください。PL/SQLでのSQLファンクションのサポートの詳細は、『Oracle Database PL/SQL言語リファレンス』のPL/SQL式でのSQLファンクションに関する説明を参照してください。
制御構造は、SQLに対するPL/SQLの拡張機能です。TimesTenでは、Oracle Databaseと同じ制御構造がサポートされています。
この項では、次の制御構造について説明します。
条件制御の例としては、IF-THEN-ELSEおよびCASE構造があげられます。例2-7では、IF-THEN-ELSE構造を使用して、現在の給与を基に従業員の昇給を決定します。また、従業員のjob_idに基づいて実行する処理のコースを選択するために、CASE構造も使用します。
例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文を使用すると、ループ内の制御を新しい反復に移すことができます。
例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、ODP.NETまたはTimesTen TTClasses(C++)を使用したクライアント・アプリケーションからのPL/SQLの実行がサポートされています。
前述のとおり、ブロックとは、PL/SQLソース・プログラムの基本単位です。無名ブロックも前述のとおりです。一方、プロシージャおよびファンクションとは、指定した名前で定義されているPL/SQLブロックのことです。これらの定義および作成方法の詳細は、「PL/SQLのプロシージャおよびファンクション」を参照してください。
TimesTenでは、スタンドアロンのPL/SQLプロシージャまたはファンクション(CREATE PROCEDUREまたはCREATE FUNCTIONで作成)、またはパッケージの一部を、無名ブロックまたはCALL文を使用して実行できます。(CALL構文の詳細は、『Oracle TimesTen In-Memory Database SQLリファレンス』のCALLに関する説明を参照してください。)
次のファンクションについて考えてみます。
create or replace function mytest return number is begin return 1; end;
TimesTenでは、次のいずれかの方法でmytestを実行できます。
無名ブロック:
Command> variable n number;
Command> begin
> :n := mytest();
> end;
> /
PL/SQL procedure successfully completed.
Command> print n;
N : 1
CALL文:
Command> variable n number; Command> call mytest() into :n; Command> print n; N : 1
Oracle Database では、次のようにSQL文からmytestを実行することもできます。この実行メカニズムは、TimesTenではサポートされていません。
SELECT文:
SQL> select mytest from dual;
MYTEST
----------
1
|
注意: ユーザー独自のプロシージャは、同じ名前を持つTimesTenの組込みプロシージャよりも優先されますが、そのような名前の競合は避けることをお薦めします。 |
この項では、アプリケーションと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仮パラメータに対応する実パラメータは、定数や式ではなく、変数である必要があります。
|
注意: TimesTenは、連想配列のバインディングを(ただしVARRAYおよびネストした表を除く)、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.
EXECUTE IMMEDIATE文を使用すると、ネイティブの動的SQLを使用して次のことを実行できます。
INSERT、UPDATEまたはDELETEなどのDML文を実行します。
CREATEまたはALTERなどのDDL文を実行します。たとえば、ALTER SESSIONを使用して、PL/SQL初期接続属性を変更できます。
PL/SQL無名ブロックを実行します。
PL/SQLストアド・プロシージャまたはファンクションをコールします。
TimesTen組込みプロシージャをコールする場合。(詳細は、『Oracle TimesTen In-Memory Databaseリファレンス』の組込みプロシージャに関する説明を参照してください。)
1つの使用例として、実行時までSQL文の完全なテキストがわからない場合をあげることができます。たとえば、コンパイル時に、SELECT文のWHERE句で使用する列の名前がわからないことがあります。このような場合は、EXECUTE IMMEDIATE文を使用できます。
別の使用例として、DDLの使用が挙げられますが、DDLはPL/SQL内からは静的SQLで実行できません。
結果セットを戻すTimesTen組込みプロシージャをコールするには、レコード型を作成し、BULK COLLECTを使用したEXECUTE IMMEDIATEを使用して、結果を配列にフェッチします。
例2-11で、EXECUTE IMMEDIATEの一連の簡単な例を示します。その他の例の詳細は、「EXECUTE IMMEDIATEを使用する例」を参照してください。
詳細は、『Oracle Database PL/SQL言語リファレンス』のEXECUTE IMMEDIATE文に関する説明を参照してください。
|
注意:
|
例 2-11 EXECUTE IMMEDIATEを使用したPL/SQLの実行
この例では、EXECUTE IMMEDIATE文でPL/SQLを使用する一連の例を示します。(これらの例は、互いに無関係です。)
表を作成し、PL/SQLブロック内でその表に対してDML文を実行します(USING句で入力パラメータを指定します)。次に、表を選択して結果を表示します。
Command> create table t(i int);
Command> declare
> i number := 1;
> begin
> execute immediate 'begin insert into t values(:j);end;' using i;
> end;
> /
PL/SQL procedure successfully completed.
Command> select * from t;
< 1 >
1 row found.
PL/SQLプロシージャfooを作成したら、PL/SQLブロックでそのプロシージャを実行します(USING句で入力パラメータを指定します)。
Command> create or replace procedure foo(message varchar2) is
> begin
> dbms_output.put_line(message);
> end;
> /
Procedure created.
Command> begin
> execute immediate 'begin foo(:b);end;' using 'hello';
> end;
> /
hello
PL/SQL procedure successfully completed.
PL/SQLプロシージャmyprintを作成したら、CALL文でそのプロシージャを実行します(USING句で入力パラメータを指定します)。
Command> declare
> a number := 1;
> begin
> execute immediate 'call myprint(:b)' using a;
> end;
> /
myprint procedure got number 1
PL/SQL procedure successfully completed.
動的SQLの使用上の注意
通常、EXECUTE IMMEDIATEを使用して実行するコードは、Oracle Databaseの場合と同様、外部のPL/SQLブロックと同じ環境を共有します。特に次の点に注意してください。(これらは、EXECUTE IMMEDIATEだけでなく、DBMS_SQLを使用する場合にも該当します。)
EXECUTE IMMEDIATEで実行されるSQLおよびPL/SQLは、外部のブロックと同じトランザクションで実行されます。
EXECUTE IMMEDIATE文の実行中に発生した例外は、外部のブロックに伝播されます。そのため、EXECUTE IMMEDIATE文の実行時に発生するエラー・スタックに関するエラーは、外部のブロック内に表示されます。これは、DBMS_UTILITY.FORMAT_ERROR_STACKなどのプロシージャで便利です。
EXECUTE IMMEDIATE文でPL/SQLブロックを実行する前に発生したエラー・スタックに関するエラーは、たとえばDBMS_UTILITY.FORMAT_ERROR_STACKを使用してそのブロック内に表示されます。
EXECUTE IMMEDIATE文を実行する実行環境は、外部のブロックの場合と同じです。PL/SQLおよびTimesTenパラメータ、REF CURSOR状態、およびEXECUTE IMMEDIATE文からのパッケージ状態は、外部のブロック内に表示されます。
バルク・バインドは、PL/SQLからSQL文を実行する場合に使用される強力な機能であり、SQLとPL/SQL間での大量データの移動に使用されます。(アプリケーション・プログラムからPL/SQLへのパラメータのバインドとは異なります。)バルク・バインドでは、ループを使用してFETCH、INSERT、UPDATE、およびDELETE操作を複数回実行するのではなく、単一の操作で値の配列をバインドします。TimesTenではバルク・バインドがサポートされており、これにより、パフォーマンスを大幅に向上できます。
FORALL文を使用して入力コレクションをバルク・バインドしてから、SQLエンジンに送信します。BULK COLLECTを使用して、SQLから結果のバッチを取得します。VARRAY、ネストした表、連想配列(索引付き表)などの任意のPL/SQLコレクション型に、バルク・コレクトできます。コレクションの詳細は、「PL/SQLでのコレクションの使用」を参照してください。
FORALL文では%BULK_EXCEPTIONSカーソル属性およびSAVE EXCEPTIONS句を使用できます。SAVE EXCEPTIONSを使用すると、UPDATE、INSERTまたはDELETE文で例外(制約エラーなど)が発生した後でも実行を続行できます。例外は配列に収集され、文の実行後に%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.
RETURNING INTO句(DML RETURNINGと呼ばれる場合もあります)をINSERT、UPDATEまたはDELETE文で使用すると、処理の影響を受けた行の(オプションでROWIDを含む)特定の列または式を返すことができます。これにより、影響を確認したり、挿入または更新後のROWIDが必要な場合などに、後続のSELECT文および個々のラウンドトリップが不要になります。
RETURNING INTO句は、動的SQL(EXECUTE IMMEDIATEを使用)または静的SQLのいずれかで使用できます。
PL/SQL BULK COLLECT機能を使用すると、この句は、単一の行から一連のパラメータまたはレコードのいずれかにアイテムを返すことも、複数の行からVARRAY、ネストした表、または連想配列(索引付き表)などのPL/SQLコレクションに列を返すこともできます。句のINTO部分のパラメータには、入力/出力ではなく、出力のみを指定する必要があります。コレクションの詳細は、「PL/SQLでのコレクションの使用」を参照してください。BULK COLLECTについては、「FORALL操作およびBULK COLLECT操作」および「FORALLおよびBULK COLLECTを使用する例」を参照してください。
TimesTenでのSQL構文およびRETURNING INTO句の制限事項については、『Oracle TimesTen In-Memory Database SQLリファレンス』のINSERT、UPDATE、およびDELETEに関する説明を参照してください。
「RETURNING INTOを使用する例」も参照してください。
DML RETURNINGの詳細は、『Oracle Database PL/SQL言語リファレンス』のRETURNING INTO句に関する説明を参照してください。
PL/SQLプログラムでSQL文を実行すると、そのSQL文は、他のプログラミング言語で記述したアプリケーションから実行された場合と同じ方法で、TimesTenで処理されます。TimesTen SQLのすべての標準動作が適用されます。これには、TimesTen Cache環境で、PL/SQLからすべてのキャッシュ機能を使用できることも含まれます。PL/SQLからキャッシュ・グループ内の表にアクセスする際は、これらの表に対する通常のルールが適用されます。たとえば、動的キャッシュ・グループ内のキャッシュ・インスタンスに対してSELECT文を発行すると、インスタンスがOracle DatabaseからTimesTenに自動的にロードされる場合があります。
この機能に関する次の点には、特に注意してください。
PL/SQLで静的SQLを使用する場合、アクセス対象の表がTimesTenに存在しないとPL/SQLが正常にコンパイルされません。次の例では、TimesTenにABCが存在する必要があります。
begin insert into abc values(1, 'Y'); end;
TimesTen Cache環境には、TimesTenパススルー機能を使用して、TimesTenからOracle Databaseに自動的にSQL文をルーティングする機能があります。(パススルー機能の詳細は、『Oracle TimesTen Application-Tier Database Cacheユーザーズ・ガイド』のパススルー・レベルの設定に関する説明を参照してください。)
passthrough=1を指定しておくと、アクセス対象の表がTimesTenに存在しない場合に、文をOracle Databaseにパススルーできます。ただし、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でブロックをコンパイルできます。
TimesTenではPL/SQLを実行できますが、現在のリリースではTimesTenパススルー機能を使用してTimesTenからOracle DatabaseにPL/SQLブロックをルーティングすることはできません。たとえば、TimesTen Cacheをpassthrough=3とともに使用した場合、TimesTen接続で実行される文は、ほとんどの環境のOracle Databaseにルーティングされます。このシナリオでは、TimesTenはPL/SQLブロックをOracle Databaseに転送しようとしますが、これはサポートされていないため、アプリケーション・プログラムからPL/SQLブロックを実行できない場合があります。(passthrough=1の例では、ブロック全体ではなく、SQL文のみがOracle Databaseにルーティングされています。)
|
重要: PL/SQLプロシージャおよびファンクションは、定義者権限または実行者権限で次のどのキャッシュ操作も実行できます。
PL/SQLプロシージャまたはファンクションが、実行者権限( 「定義者権限および実行者権限」を参照してください。 |
TimesTen supportsは、LOB(ラージ・オブジェクト)をサポートしています。これには、CLOB(文字LOB)、NCLOB(各国語LOB)、およびBLOB(バイナリLOB)などがあります。
PL/SQL言語機能は、特に記述がないかぎり、Oracle Databaseと同様にTimesTenでもLOBをサポートされています。
この項では、LOBの簡単な概要とPL/SQLでの使用方法に関して、次の内容について説明します。
|
注意: データベースの文字セットがTIMESTEN8に設定されている場合、TimesTenはCLOBをサポートしていません。 |
次の情報も参照できます。
TimesTenでのLOBに関する詳細は、『Oracle TimesTen In-Memory Database SQLリファレンス・ガイド』のLOBのデータ型に関する説明を参照してください。
LOBを使用したプログラミングに関する、TimesTen機能に特化されない一般的な情報については、『Oracle Database SecureFilesおよびラージ・オブジェクト開発者ガイド』
LOBには、ラージ・バイナリ・オブジェクト(BLOB)または文字オブジェクト(CLOBまたはNCLOB)があります。TimesTenでは、BLOBのサイズは最大16MB、CLOBまたはNCLOBのサイズは最大4MBです。TimesTenのLOBには、特に記述がないかぎり、基本的にOracle Databaseと同じ機能があります。(「TimesTenにおけるLOBとOracle DatabaseにおけるLOBの相違点」を参照してください。)
LOBは、永続的または一時的のいずれかに設定できます。永続LOBは、データベースのLOB列に存在します。一時LOBはアプリケーション内のみに存在します。
PL/SQLでは、LOBは、LOBロケータおよびLOB値で構成されています。ロケータは、値へのハンドルとして機能する不透明構造です。LOBをパラメータとして渡すなどの操作で、アプリケーションがLOBを使用する場合、それは実際の値を渡すのではなく、ロケータを渡すことになります。
|
重要: LOBロケータを使用するAPIを介ししてLOBを操作すると、TimesTen一時領域を使用することになります。このような操作を非常に多くの回数実行すると、TimesTen一時データ領域サイズの拡大が必要になる場合があります。『Oracle TimesTen In-Memory Databaseリファレンス』のTempSizeに関する説明を参照してください。 |
LOBを更新するには、トランザクションで、LOBを含む行が排他的にロックされている必要があります。これを行うには、SELECT ... FOR UPDATE文でLOBを選択します。その結果、ロケータは書込み可能になります。簡単なSELECT文の場合、ロケータは読取り専用です。読取り専用および書込み可能ロケータは次のように動作します。
読取り専用ロケータには読取り一貫性があり、そのロケータが存続する間は、LOBが選択された時点のコンテンツのみが表示されることを意味します。これには、LOBが選択される前に同じトランザクション内でLOBに対して行われた、コミットされていない更新も含まれることに注意してください。
書込み可能なロケータは、そのロケータを介して書込みが行われるたびに、データベースの最新のデータで更新されます。そのため、各書込みはLOBの最新データに対して行われ、これには他のロケータを介して行われた更新内容も含まれます。
次の例で、同一のLOBに対する2つの書込み可能なロケータの動作を説明します。
LOB列に、"XY"が含まれています。
更新用にロケータL1を選択します。
更新用にロケータL2を選択します。
L1を使用してオフセット1で"Z"を書き込みます。
ロケータL1を使用して読み取ります。これにより、"ZY"が戻されます。
ロケータL2を使用して読み取ります。L2は、書込みに使用されるまで読取り一貫性を保持しているため、これにより"XY"が戻されます。
L2を使用してオフセット2で"W"を書き込みます。
ロケータL2を使用して読み取ります。これにより、"ZW"が戻されます。このロケータは、前の手順の書込みが行われる前に、最新データ("ZY")で更新されていたことになります。
PL/SQLブロックは、それ自身で使用する一時LOBを明示的に作成することができます。TimesTenでは、このようなLOBの存続期間は、(TimesTenにおけるLOBロケータの存続期間と同様)そのLOBが作成されたトランザクションの終了時を超えて延長されることはありません。
一時LOBは、TimesTenで暗黙的に作成されることもあります。たとえば、SELECT文によって追加の文字列と連結されたLOBが選択される場合、連結データを含むようTimesTenでは一時LOBを暗黙的に作成します。一時LOBはサーバー側のオブジェクトであることに注意してください。TimesTenには、クライアント側のLOBという概念はありません。
一時LOBは、TimesTen一時データ領域に格納されます。
一時LOBの作成方法については、『Oracle TimesTen In-Memory Database PL/SQLパッケージ・リファレンス』のCREATETEMPORARYプロシージャに関する説明を参照してください。
次の点に注意してください。
TimesTenのLOB実装とOracle Databaseでの実装との主な違いは、TimesTenの場合、LOBロケータはトランザクションが終了すると無効になる点です。すべてのLOBロケータは、明示的か暗黙的かにかかわらず、コミットまたはロールバック後に無効化されます。これには、TimesTen DDLCommitBehaviorがOracle Database動作を示す0 (デフォルト)に設定されている場合のすべてのDDL文の終了後が含まれ、PL/SQLユーザーは常に該当します。
TimesTenは、BFILE、SecureFiles、LOBの配列の読取りおよび書込み、またはLOBのコールバック・ファンクションをサポートしていません。
TimesTenでは、DBMS_LOB FRAGMENTプロシージャはサポートされていないため、データをLOBの中間に書き込むには、以前のデータを上書きするしかありません。LOBの中間にデータを挿入し、それに応じて以前のデータをLOB内のその時点よりも上位に移動する機能はありません。同様に、TimesTenでは、LOBの中間からデータを削除するには、以前のデータをゼロまたはNullデータで上書きする方法しかありません。LOBの中間からデータを削除し、それに応じて以前のデータをLOB内のその時点よりも下位に移動する機能はありません。どちらの場合にも、TimesTenではLOBのサイズは変化しませんが、指定のオフセットから、LOBで利用できる容量が書き込むデータよりも少ない場合は例外です。(Oracle Databaseでは、上書きしてLOBのサイズを変更しないモード、または挿入するか削除してLOBのサイズを変更するモードの機能があります。)
TimesTenは、LOBの配列のバインドをサポートしていません。
TimesTenは、LOBのバッチ処理をサポートしていません。
BLOBに関して、TimesTenの16進リテラルの使用法に違いがあります。『Oracle TimesTen In-Memory Database SQLリファレンス』の定数に関する項でHexadecimalLiteralの説明を参照してください。
次では、CLOBの基本的な使用方法について説明します。次のように、BLOB列(ここでは使用しません)およびCLOB列を含むように定義され、移入された表を想定します。
Command> create table t1 (a int, b blob, c clob); Command> insert into t1(a,b,c) values(1, 0x123451234554321, 'abcde'); 1 row inserted. Command> commit;
次のように、表からCLOBを選択して表示します。
Command> declare
> myclob clob;
> begin
> select c into myclob from t1 where a=1;
> dbms_output.put_line('CLOB selected from table t1 is: ' || myclob);
> end;
> /
CLOB selected from table t1 is: abcde
PL/SQL procedure successfully completed.
次では、commit文でトランザクションを終了した後に一時CLOBを再度表示してみると、その時点でLOBロケータが無効になっていることが示されています。
Command> declare
> myclob clob;
> begin
> select c into myclob from t1 where a=1;
> dbms_output.put_line('CLOB selected from table t1 is: ' || myclob);
> commit;
> dbms_output.put_line('CLOB after end of transaction is: ' || myclob);
> end;
> /
1806: invalid LOB locator specified
8507: ORA-06512: at line 8
CLOB selected from table t1 is: abcde
The command failed.
TimesTenは、LOBデータを操作するためのDBMS_LOBパッケージのサブプログラムをサポートしています。
これらのサブプログラムのリストと説明については、このドキュメントの「DBMS_LOB」を参照してください。詳細は、『Oracle TimesTen In-Memory Database PL/SQLパッケージ・リファレンス』のDBMS_LOBに関する説明を参照してください。
パススルーLOBは、TimesTenを介してアクセスされるOracle DatabaseのLOBであり、TimesTen LOBとして公開され、TimesTen LOBとほぼ同様にTimesTenでサポートされますが、次の点に注意してください。
TimesTen LOBのサイズ制限は、パススルーLOBの記憶域には適用されませんが、バインディングには適用されます。また、DBMS_LOB.COPYなどで、パススルーLOBがTimesTen LOBにコピーされる場合、そのコピーにもサイズ制限が適用されます。
パススルーLOBのサイズがTimesTen LOBのサイズ制限より大きい場合、パススルーLOBをTimesTen LOBにコピーしようとするとエラーが発生します。
TimesTenローカルLOBと同様、パススルーLOBのロケータは、トランザクションが終了すると無効になります。
パススルーLOBのDBMS_LOBサポートについては、このドキュメントの「DBMS_LOB」を参照してください。
明示または暗黙のいずれのカーソルも、SELECT文の結果セットの処理に使用されます。プログラマは明示カーソルを宣言して、複数のデータ行を戻す問合せを管理できます。PL/SQLは、明示カーソルと関連付けられていないSELECT文に対して、暗黙カーソルを宣言し、オープンします。
|
重要: TimesTenでは、トランザクションを終了する操作は、接続と関連付けられているすべてのカーソルをクローズすることに注意してください。これには、COMMIT文またはROLLBACK文が該当します。また、PL/SQLが有効な場合に実行されたDDL文もこれに該当します(PL/SQLが有効な場合はDDLCommitBehavior接続を0(Oracle Databaseの動作)に設定する必要があるため)。このため、DDL文は自動コミットされます。詳細は、「TimesTenにおける相違点: トランザクション動作」を参照してください。 |
例2-13に、基本的なカーソルの使用方法を示します。その他の例および詳細は、「カーソルを使用する例」を参照してください。また、「PL/SQL REF CURSOR」も参照してください。
例2-13 カーソルを使用した従業員情報の取得
カーソルc1を宣言して、従業員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 Databaseでサポートされている内容の一部です。これらの文およびTimesTenのALTER PROCEDURE文およびALTER FUNCTION文の詳細は、『Oracle TimesTen In-Memory Database SQLリファレンス』のSQL文に関する説明を参照してください。
また、「PL/SQLのプロシージャおよびファンクションを実行する方法」も参照してください。
|
注意:
|
例2-14 OUTパラメータを使用したプロシージャの作成と実行
この例では、OUTパラメータを使用するプロシージャを作成し、無名ブロックでプロシージャを実行して、OUT値を表示します。プロシージャは、従業員IDを入力として受け取り、従業員の給与とジョブIDを出力します。
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.
|
注意: 前の例で示されている無名ブロックを使用するかわりに、CALL文を使用することもできます。
Command> CALL GET_EMPLOYEE(120, :v_salary, :v_job); |
例2-15 ファンクションの作成とコール
この例では、入力として従業員IDを指定して従業員の給与を戻すファンクションを作成し、このファンクションをコールして戻された結果を表示します。
Command> CREATE OR REPLACE FUNCTION get_sal
> (p_id employees.employee_id%TYPE) RETURN NUMBER IS
> v_sal employees.salary%TYPE := 0;
> BEGIN
> SELECT salary INTO v_sal FROM employees
> WHERE employee_id = p_id;
> RETURN v_sal;
> END get_sal;
> /
Function created.
Command> variable n number;
Command> call get_sal(100) into :n;
Command> print n;
N : 24000
|
注意: 前の例で示されているCALL文を使用するかわりに、無名ブロックを使用することもできます。
Command> begin
> :n := get_sal(100);
> end;
> /
|
TimesTenは、PL/SQLプロシージャ、ファンクション、パッケージなどのデータベース・オブジェクトのプライベートおよびパブリック・シノニム(エイリアス)をサポートしています。シノニムは、オブジェクト名およびオブジェクト所有者をマスクしたり、SQL文を単純化する際によく使用されます。
次のように、スキーマ内にプロシージャfooのプライベート・シノニムを作成します。
CREATE SYNONYM synfoo FOR foo;
次のように、パブリック・シノニムを作成します。
CREATE PUBLIC SYNONYM pubfoo FOR foo;
プライベート・シノニムは特定のユーザーのスキーマ内に存在し、表、ビューおよび順序などのデータベース・オブジェクトと同じネームスペースを共有します。プライベート・シノニムの名前は、同じスキーマ内の表などのオブジェクト名と同じにすることはできません。
パブリック・シノニムは、特定のスキーマに属しておらず、すべてのユーザーからアクセス可能で、プライベート・オブジェクトと同じ名前を使用できます。
シノニムを使用するには、その基になるオブジェクトへの適切なアクセス権限を持っている必要があります。シノニムの作成や削除に必要な権限については、「PL/SQL文およびPL/SQL操作に必要な権限」を参照してください。
シノニムに関する一般的な情報については、『Oracle TimesTen In-Memory Databaseオペレーション・ガイド』のシノニムの理解に関する説明を参照してください。CREATE SYNONYMおよびDROP SYNONYM文の詳細は、『Oracle TimesTen In-Memory Database SQLリファレンス』のSQL文に関する説明を参照してください。
例2-16 プロシージャでのシノニムの使用
次の例では、USER1は、スキーマにプロシージャを作成し、そのパブリック・シノニムを作成します。次に、USER2は、パブリック・シノニムを使用してプロシージャを実行します。次の状況を想定しています。
USER1には、CREATE SESSION権限、CREATE PROCEDURE権限、およびCREATE PUBLIC SYNONYM権限が付与されています。
USER2には、CREATE SESSION権限、EXECUTE ANY PROCEDURE権限が付与されています。
どちらのユーザーもデータベースに接続しています。
USER2は、SET SERVEROUTPUT ON設定を使用しています。
USER1:
Command> create or replace procedure test is
> begin
> dbms_output.put_line('Running the test');
> end;
> /
Procedure created.
Command> create public synonym pubtest for test;
Synonym created.
USER2:
Command> begin
> pubtest;
> end;
> /
Running the test
PL/SQL procedure successfully completed.
この項では、PL/SQLパッケージの作成および使用方法について説明します。
TimesTenに付属のPL/SQLパッケージの詳細は、第8章「TimesTenが提供するPL/SQLパッケージ」を参照してください。
パッケージは、論理的に関連するPL/SQLデータ型、変数およびサブプログラムをグループ化したデータベース・オブジェクトです。パッケージを指定した後、その本体を個別の手順で定義します。
パッケージ仕様部はパッケージに対するインタフェースで、パッケージの有効範囲外で参照できるパブリックなデータ型、変数、定数、例外、カーソルおよびサブプログラムを宣言します。本体では、仕様部で宣言したオブジェクト以外に、カーソルの問合せ、サブプログラムのコードおよびパッケージ外のアプリケーションからは参照できないプライベートなオブジェクトを定義します。
TimesTenでは、パッケージ仕様部をパッケージ本体とは別にデータベースに格納します。パブリックなプログラム・オブジェクトをコールまたは参照する他のスキーマ・オブジェクトは、パッケージ本体ではなく、パッケージ仕様部にのみ依存します。
|
注意: パッケージおよびパッケージ本体を作成する構文は、Oracle Databaseと同様ですが、Oracle Databaseのマニュアルに記載されているDBMSSTDX.SQLというスクリプトの実行については、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-17 パッケージの作成および使用
新しい従業員を雇用したときに従業員表に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.
TimesTenは、PL/SQLプロシージャ、ファンクション、パッケージなどのデータベース・オブジェクトのプライベートおよびパブリック・シノニム(エイリアス)をサポートしています。シノニムは、オブジェクト名およびオブジェクト所有者をマスクしたり、SQL文を単純化する際によく使用されます。
スキーマ内でパッケージfoopkgのプライベート・シノニムを作成する場合:
CREATE SYNONYM synfoopkg FOR foopkg;
foopkgのパブリック・シノニムを作成する場合:
CREATE PUBLIC SYNONYM pubfoopkg FOR foopkg;
また、「プロシージャおよびファンクションのシノニムの使用」および「PL/SQL文およびPL/SQL操作に必要な権限」も参照してください。
|
注意: パッケージの各メンバー・サブプログラムに対するシノニムを作成することはできません。次は有効です。 create or replace public synonym pubtestpkg for testpkg; 次は無効です。 create or replace public synonym pubtestproc for testpkg.testproc; |
ラップとは、PL/SQLソース・コードを隠す処理のことです。入力用SQLファイルを処理して、パッケージ仕様部、パッケージ本体、ファンクション、プロシージャなど、ファイル内のPL/SQLユニットのみをラップするwrapユーティリティで、PL/SQLのソース・コードをラップすることができます。
次の例では、ファイルwrap_test.sqlを使用してwraptestという名前のプロシージャを定義して検討します。その後wrapユーティリティを使用してwrap_test.sqlを処理します。このプロシージャは、ソース・コード非表示として作成され、正常に実行されます。最終的に、ALL_OBJECTSビューが問い合せられ、ラップされたソース・コードが表示されます。
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;
/
次の例で、"$"はUNIXのプロンプト、"Command>"はttIsqlのプロンプトを表し、ユーザー入力は太字で表示されます。
$ wrap iname=wrap_test.sql PL/SQL Wrapper: Release 11.2.0.2.0- Production on Wed Sep 14 12:59:27 2011 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 124 12c YZ6L0v2ntFaqttW8hSJD5IHIYccwg+nwNfZqfHQCv/9kMJyznwdLh8FepNXpWS1fzVBDkTke LWlhFdFzCMfmmJ5GGrCwrqgngEhfRpq7ck5Dzsf7sDlnQeE3QGmb/yu9Dec1+JO2kOMlx3dq BuC7fR2f5sjDtBeDXiGCC0kJ5QBVregtoBckZNO9MoiWS4w0jF6T1CPY0Aoi/KUwxC8S8I8n amF5xGQDCYTDajs77orIGEqtX747k0YAO+r1e9adGUsVgZK1ONcTM/+Wit+LYKi7b03eJxdB +aaKn/Lh / $ ttisql sampledb_1122 Copyright (c) 1996-2011, Oracle. All rights reserved. Type ? or "help" for help, type "exit" to quit ttIsql. connect "DSN=sampledb_1122"; Connection successful: DSN=sampledb_1122;UID=myuserid;DataStore=.../install/info/DemoDataStore/ sampledb_1122;DatabaseCharacterSet=US7ASCII;ConnectionCharacterSet=US7ASCII;DRIVER =.../install/lib/libtten.so;PermSize=40;TempSize=32;TypeMode=0; (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 124 12c YZ6L0v2ntFaqttW8hSJD5IHIYccwg+nwNfZqfHQCv/9kMJyznwdLh8FepNXpWS1fzVBDkTke LWlhFdFzCMfmmJ5GGrCwrqgngEhfRpq7ck5Dzsf7sDlnQeE3QGmb/yu9Dec1+JO2kOMlx3dq BuC7fR2f5sjDtBeDXiGCC0kJ5QBVregtoBckZNO9MoiWS4w0jF6T1CPY0Aoi/KUwxC8S8I8n amF5xGQDCYTDajs77orIGEqtX747k0YAO+r1e9adGUsVgZK1ONcTM/+Wit+LYKi7b03eJxdB +aaKn/Lh Procedure created. 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 124 12c YZ6L0v2ntFaqttW8hSJD5IHIYccwg+nwNfZqfHQCv/9kMJyznwdLh8FepNXpWS1fzVBDkTke LWlhFdFzCMfmmJ5GGrCwrqgngEhfRpq7ck5Dzsf7sDlnQeE3QGmb/yu9Dec1+JO2kOMlx3dq BuC7fR2f5sjDtBeDXiGCC0kJ5QBVregtoBckZNO9MoiWS4w0jF6T1CPY0Aoi/KUwxC8S8I8n amF5xGQDCYTDajs77orIGEqtX747k0YAO+r1e9adGUsVgZK1ONcTM/+Wit+LYKi7b03eJxdB +aaKn/Lh > 1 row found.
TimesTenでは、トランザクションを終了する操作は、接続と関連付けられているすべてのカーソルをクローズします。これには次のものがあります。
COMMIT文またはROLLBACK文
PL/SQLユーザーの場合は、DDL文
これは、PL/SQLが有効になっている場合(PLSQL初期接続属性が1に設定されている場合)、TimesTen DDLCommitBehavior一般接続属性はOracle Databaseの動作(自動コミット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;
