PL/SQLのプロシージャおよびファンクション

プロシージャおよびファンクションの作成

TimesTenでは、スタンドアロン・サブプログラム(ストアド・プロシージャまたはファンクション)を、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文を参照してください。

ノート:

ALTERは、TimesTen ScaleoutのPL/SQLオブジェクトではサポートされていません。

プロシージャおよびファンクションの実行

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の組込みプロシージャよりも優先されますが、そのような名前の競合は避けることをお薦めします。

この例では、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);

次の例では、入力として従業員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;

プライベート・シノニムは特定のユーザーのスキーマ内に存在し、表、ビューおよび順序などのデータベース・オブジェクトと同じネームスペースを共有します。プライベート・シノニムの名前は、同じスキーマ内の表などのオブジェクト名と同じにすることはできません。

パブリック・シノニムは、特定のスキーマに属しておらず、すべてのユーザーからアクセス可能で、プライベート・オブジェクトと同じ名前を使用できます。

シノニムを使用するには、その基になるオブジェクトへの適切なアクセス権限を持っている必要があります。シノニムの作成や削除に必要な権限については、『Oracle TimesTen In-Memory Databaseセキュリティ・ガイド』のPL/SQL文および操作の権限を参照してください。

シノニムに関する一般的な情報は、『Oracle TimesTen In-Memory Databaseオペレーション・ガイド』のシノニムの理解を参照してください。CREATE SYNONYM文およびDROP SYNONYM文については、『Oracle TimesTen In-Memory Database SQLリファレンス』のSQL文を参照してください。

次の例では、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.

TimesTenでのプロシージャおよびファンクションの使用上の留意事項

TimesTenでPL/SQLのプロシージャおよびファンクションを使用する場合は、次の使用上の留意事項を知っておいてください。

レプリケーションを使用する場合、PL/SQLファンクション、プロシージャおよびパッケージのCREATE文などのPL/SQL DDL文はレプリケートされません。これに対処する手順については、『Oracle TimesTen In-Memory Databaseレプリケーション・ガイド』の既存のアクティブ・スタンバイ・ペアでの新しいPL/SQLの作成および既存のクラシック・レプリケーション・スキームへのPL/SQLオブジェクトの追加を参照してください。
キャッシュを使用する場合: Oracle Database内にあるPL/SQLプロシージャまたはファンクションをパススルーによってTimesTenでコールすることはできません。TimesTenで実行できるようにするには、プロシージャおよびファンクションをTimesTenで定義する必要があります。(「TimesTenのPL/SQLとキャッシュ」も参照してください。)
PL/SQLおよびデータベース・オブジェクトの名前: TimesTenでは、PL/SQLオブジェクト(プロシージャ、ファンクションおよびパッケージ)に、非ASCIIまたは引用符で囲まれた大文字以外の名前を使用できません。また、PL/SQLオブジェクトの名前を引用符で囲んだ場合、後続の空白はサポートされていません。さらに、PL/SQLに渡される表およびビューなどオブジェクトの名前が引用符で囲まれている場合、後続の空白はそのまま削除されます。
定義者権限および実行者権限によって、PL/SQLプロシージャまたはファンクションで使用されるSQLオブジェクトへのアクセスが決定されます。『Oracle TimesTen In-Memory Databaseセキュリティ・ガイド』の定義者権限および実行者権限(AUTHID句)を参照してください。
プロシージャまたはファンクションのコンパイル中にエラーが発生した場合の情報の入手方法については、ttIsqlでのエラーの表示を参照してください。