CREATE PROCEDURE

CREATE PROCEDURE文は、スタンドアロンのストアド・プロシージャを作成します。

必要な権限

CREATE PROCEDURE(所有者の場合)またはCREATE ANY PROCEDURE(非所有者の場合)。

TimesTen Scaleoutでの使用

この文は、TimesTen Scaleoutでサポートされています。

SQL構文

CREATE [OR REPLACE] PROCEDURE [Owner.]ProcedureName 
     [(arguments [IN|OUT|IN OUT][NOCOPY] DataType [DEFAULT expr][,...])]
     [InvokerRightsClause][AccessibleByClause] [DETERMINISTIC]
     {IS|AS} plsql_procedure_body

InvokerRightsClause::=
AUTHID {CURRENT_USER|DEFINER}

AccessibleByClause::=
ACCESSIBLE BY(accessor[,...])

accessor::= 
[UnitKind][Owner.]UnitName

InvokerRightsClause、AccessibleByClauseまたはDETERMINISTICは任意の順序で指定できます。

パラメータ

パラメータ	説明
`OR REPLACE`	プロシージャがすでに存在する場合に再作成するには、`OR REPLACE`を指定します。この句を使用して、プロシージャを削除および再作成せずに既存のプロシージャの定義を変更します。プロシージャを再作成すると、そのプロシージャはTimesTenで再コンパイルされます。
`ProcedureName`	プロシージャの名前。
`arguments`	引数またはパラメータの名前。プロシージャには0以上のパラメータを指定できます。パラメータを指定する場合は、パラメータのデータ型を指定する必要があります。データ型はPL/SQLデータ型である必要があります。
`[IN\|OUT\|IN OUT]`	パラメータのモード。 `IN`は読取り専用パラメータです。パラメータの値をプロシージャに渡すことはできますが、プロシージャはパラメータの値をプロシージャ外に渡したり、コール側PL/SQLブロックに戻したりすることはできません。パラメータの値は変更できません。 `OUT`は書込み専用パラメータです。値をプロシージャからコール側PL/SQLブロックに戻すには、`OUT`パラメータを使用します。パラメータに値を割り当てることができます。 `IN OUT`は読取り/書込みパラメータです。値をプロシージャに渡し、コール側プログラムに戻すことができます(元の変更されていない値またはプロシージャ内で設定された新しい値)。 `IN`がデフォルトです。
`NOCOPY`	可能なかぎり迅速にパラメータを渡すようにTimesTenに指示するには、`NOCOPY`を指定します。レコード、索引付き表、`OUT`または`IN OUT`パラメータへのVARRAYなどの大きな値を渡す場合に、パフォーマンスを向上できます。`IN`パラメータでは、常に`NOCOPY`が渡されます。
`DEFAULT` `expr`	パラメータの`DEFAULT`値を指定するには、この句を使用します。キーワード`DEFAULT`のかわりに`:=`を指定できます。
`InvokerRightsClause`	PL/SQLファンクションまたはプロシージャ内のSQL文が定義者権限で実行されるか実行者権限で実行されるかを指定できます。`AUTHID`の設定は、実行時にPL/SQLプロシージャまたはファンクションによって発行されるSQL文の名前解決および権限チェックに次のように影響を与えます。 `DEFINER`を指定すると、SQLの名前解決および権限チェックは、プロシージャまたはファンクションの所有者(定義者、つまりプロシージャまたはファンクションがあるスキーマの所有者)が実行しているものとして実行されます。`DEFINER`がデフォルトです。 `CURRENT_USER`を指定すると、SQLの名前解決および権限チェックは、現在のユーザー(実行者)が実行しているものとして実行されます。詳細は、『Oracle TimesTen In-Memory Databaseセキュリティ・ガイド』の「定義者権限および実行者権限(AUTHID句)」を参照してください。
`AccessibleByClause`	この句は、プロシージャを直接呼び出せる1つ以上のアクセッサ(PL/SQLユニット)を指定する場合に使用します。プロシージャにアクセスできるアクセッサのリストは、ホワイト・リストと呼ばれます。ホワイト・リストを使用すると、PL/SQLオブジェクトにセキュリティ・レイヤーを追加できます。特に、ホワイト・リストにあるオブジェクトのみにプロシージャへのアクセスを制限できます。 `AccessibleByClause`は、`CREATE` `PROCEDURE`文で1回のみ指定できます。構文: `ACCESSIBLE` `BY` `(accessor` `[,...])`
`accessor`	`AccessibleByClause`で使用されます。アクセッサは、プロシージャを呼び出せるPL/SQLユニットです。アクセサは、`AccessibleByClause`で複数回指定できます。構文: `[UnitKind][Owner.]UnitName`
`UnitKind`	`accessor`句(`AccessibleByClause`句の一部)で使用されます。プロシージャを呼び出せるPL/SQLユニットの種類を指定します。 `UnitKind`はオプションですが、指定する場合の有効なオプションは次のとおりです。 `FUNCTION` `PROCEDURE` `PACKAGE`
`[Owner.]UnitName`	`accessor`句(`AccessibleByClause`句の一部)で使用されます。プロシージャを呼び出せるPL/SQLユニットの名前を指定します。`UnitKind`を指定する場合、`UnitName`をこの種類のユニットの名前にする必要があります。たとえば、`UnitKind`に`PROCEDURE`を指定する場合、`UnitName`はプロシージャ名である必要があります。`UnitName`は必須です。 `Owner`は必要に応じて指定できます。`Owner`を指定する場合、`UnitName`がその所有者のスキーマ内に常駐する必要があります。`Owner`を指定しない場合、`UnitName`はプロシージャを含むスキーマ内にある必要があります。
`DETERMINISTIC`	パラメータに同じ値を指定してプロシージャをコールした場合に常に同じ結果値を返すように指定するには、`DETERMINISTIC`を指定します。
`IS\|AS`	`IS`または`AS`を指定して、プロシージャの本体を宣言します。
`plsql_procedure_body`	プロシージャの本体を指定します。

説明

AccessibleByClause:
- コンパイラはAccessibleByClauseの構文の妥当性をチェックしますが、アクセッサが存在することはチェックしません。このため、所有者のスキーマにまだ存在しているアクセッサを定義できます。
- プロシージャを呼び出すと、コンパイラはまず呼出しの通常のアクセス権チェックを実行します。いずれかのチェックに失敗すると、呼出し元がアクセッサであっても呼出しは失敗します。呼出し時の通常のアクセス権チェックがすべて成功し、このプロシージャにAccessibleByClauseがない場合、呼出しは成功します。プロシージャにAccessibleByClauseがある場合、呼出し元がアクセッサである場合にのみ呼出しは成功します。
プロシージャを作成または置き換えた場合、プロシージャに付与された権限は同じままです。オブジェクトを削除して再作成した場合、元のオブジェクトに付与されていたオブジェクト権限は削除されます。
PL/SQLプロシージャのネームスペースは、TimesTen組込みプロシージャとは異なります。PL/SQLプロシージャは、TimesTen組込みプロシージャと同じ名前で作成できます。
次の句はTimesTenではサポートされません。
- call_spec句
- AS EXTERNAL句
レプリケートされた環境で、CREATE PROCEDURE文はレプリケートされません。詳細は、『Oracle TimesTen In-Memory Databaseレプリケーション・ガイド』の「既存のアクティブ・スタンバイ・ペアでの新しいPL/SQLオブジェクトの作成」および「既存のクラシック・レプリケーション・スキームへのPL/SQLオブジェクトの追加」を参照してください。

例

Accessible By句の使用

この例では、ProtectedProcプロシージャを作成し、ACCESSIBLE BY句を使用してCallingProcプロシージャへのアクセスを制限します。CallingProcプロシージャはまだ存在しません。次に、ProtectedProcプロシージャをコールするCallingProcプロシージャを作成します。CallingProcプロシージャは、ACCESSIBLE BY句で指定されているため正常に作成されます。例では、ProtectedProcプロシージャを直接コールしようとしてエラーが発生します。最終的にProtectedProcプロシージャを参照するAnotherCallingProcプロシージャを作成しようとしますが、AnotherCallingProcプロシージャはホワイト・リストに存在しません。コンパイル・エラーが発生します。

この例を示すステップ:

ACCESSIBLE BY句を指定して、ProtectedProcプロシージャを作成します。CallingProcプロシージャは、ホワイト・リストにあります。まだ存在しません。

Command> CREATE OR REPLACE PROCEDURE ProtectedProc
           ACCESSIBLE BY (CallingProc)
         AS
         BEGIN
           DBMS_OUTPUT.PUT_LINE ('ProtectedProc');
         END;
         /
          
Procedure created.

ProtectedProcプロシージャを参照して、CallingProcプロシージャを作成します。

Command> CREATE OR REPLACE PROCEDURE CallingProc
         AS
         BEGIN
           DBMS_OUTPUT.PUT_LINE ('CallingProc');
           ProtectedProc;
         END;
         /
         
Procedure created.

CallingProcプロシージャをコールします。プロシージャは正常に実行されます。

Command> SET SERVEROUTPUT ON
Command> exec CallingProc;
CallingProc
ProtectedProc
 
PL/SQL procedure successfully completed.

ProtectedProcプロシージャの直接のコールを試行します。アクセス権限が不十分なため、エラーがスローされます。

Command> exec ProtectedProc;
 8503: ORA-06550: line 1, column 7:
PLS-00904: insufficient privilege to access object PROTECTEDPROC
 8503: ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
The command failed.

ProtectedProcプロシージャを参照するAnotherCallingProcプロシージャを作成します。AnotherCallingProcがホワイト・リスト内にない(ProtectedProcのACCESSIBLE BY句に含まれない)ため、コンパイル・エラーが発生します。

Command> CREATE OR REPLACE PROCEDURE AnotherCallingProc
         AS
         BEGIN
           DBMS_OUTPUT.PUT_LINE ('AnotherCallingProc');
           ProtectedProc;
         END;
         /
 
Warning: Procedure created with compilation errors.
 
Command> SHOW ERRORS
Errors for PROCEDURE ANOTHERCALLINGPROC:
 
LINE/COL ERROR
-------- -----------------------------------------------------------------
5/1      PL/SQL: Statement ignored
5/1      PLS-00904: insufficient privilege to access object PROTECTEDPROC

アクセサ句の使用

この例は、アクセッサ句を一連のステップで使用する方法を示しています。

SampleUser1ユーザーとSampleUser2ユーザーを作成し、両方のユーザーにADMIN権限を付与します。

Command> CREATE USER SampleUser1 IDENTIFIED BY SampleUser1;
 
User created.
 
Command> CREATE USER SampleUser2 IDENTIFIED BY SampleUser2;
 
User created.
Command> GRANT ADMIN TO SampleUser1, SampleUser2;

ACCESSIBLE BY句を指定して、SampleUser1.ProtectedProcプロシージャを作成します。CallingProcプロシージャは、所有者なしでホワイト・リスト内に指定されます。CallingProcプロシージャの所有者は、ACCESSIBLE BY句を持つプロシージャの所有者と同じスキーマ内に存在すると想定されます。したがって、CallingProcはSampleUser1スキーマ内にあるとみなされます。
```
Command> CREATE OR REPLACE PROCEDURE SampleUser1.ProtectedProc
         ACCESSIBLE BY (CallingProc)
         AS
         BEGIN
           DBMS_OUTPUT.PUT_LINE ('SampleUser1 ProtectedProc');
         END;
         /
 
Procedure created.
```

SampleUser1として接続します。SampleUser1.ProtectedProcプロシージャを参照して、CallingProcプロシージャを作成します。

Command> Connect adding "uid=SampleUser1;pwd=SampleUser1PW" as SampleUser1;
Connection successful:
DSN=database1;UID=SampleUser1;DataStore=/scratch/sampleuser1/database1;
DatabaseCharacterSet=AL32UTF8;ConnectionCharacterSet=AL32UTF8;
PermSize=128;
(Default setting AutoCommit=1)

sampleuser1: Command> CREATE OR REPLACE PROCEDURE CallingProc
                      AS
                      BEGIN
                        DBMS_OUTPUT.PUT_LINE ('SampleUser1 CallingProc');
                        ProtectedProc;
                      END;
                      /
 
Procedure created.

SampleUser1接続から、CallingProcプロシージャをコールします。コールは成功します。

sampleuser1: Command> SET SERVEROUTPUT ON
sampleuser1: Command> exec CallingProc;
SampleUser1 CallingProc
SampleUser1 ProtectedProc
 
PL/SQL procedure successfully completed.

SampleUser2に接続します。SampleUser1.ProtectedProcプロシージャを参照して、CallingProcプロシージャを作成します。コンパイル・エラーが発生します。

SampleUser1: Command> connect adding "uid=Sampleuser2;pwd=SampleUser2PW"
                      as SampleUser2;
Connection successful:
DSN=database1;UID=Sampleuser2;DataStore=/scratch/sampleuser2/database1;
DatabaseCharacterSet=AL32UTF8;ConnectionCharacterSet=AL32UTF8;
PermSize=128;
(Default setting AutoCommit=1)

sampleuser2: Command> CREATE OR REPLACE PROCEDURE CallingProc
                      AS
                      BEGIN
                        DBMS_OUTPUT.PUT_LINE ('SampleUser2 CallingProc');
                        SampleUser1.ProtectedProc;
                      END;
                      /
 
Warning: Procedure created with compilation errors.
 
sampleuser2: Command> SHOW ERRORS
Errors for PROCEDURE CALLINGPROC:
 
LINE/COL ERROR
-------- -----------------------------------------------------------------
5/1      PL/SQL: Statement ignored
5/1      PLS-00904: insufficient privilege to access object PROTECTEDPROC

SampleUser1接続に切り替えます。ProtectedProcプロシージャを再作成します。

sampleuser2: Command> use SampleUser1
sampleuser1: Command> CREATE OR REPLACE PROCEDURE ProtectedProc
                      ACCESSIBLE BY (CallingProc, SampleUser2.CallingProc)
                      AS
                      BEGIN
                        DBMS_OUTPUT.PUT_LINE ('SampleUser1 ProtectedProc');
                      END;
                      /
 
Procedure created.

SampleUser2接続から、CallingProcプロシージャをコールします。SampleUser2.CallingProcはSampleUser1.ProtectedProcプロシージャのホワイト・リストにあり、実行が成功します。
```
sampleuser1: Command> use SampleUser2;
sampleuser2: Command> SET SERVEROUTPUT ON
sampleuser2: Command> exec CallingProc
SampleUser2 CallingProc
SampleUser1 ProtectedProc
 
PL/SQL procedure successfully completed.
```

CREATE PROCEDURE文を使用した情報の取得

プロシージャquery_empを作成して、従業員に関する情報を取得します。employee_id 171をプロシージャに渡し、2つのOUTパラメータに対してlast_nameおよびsalaryを取得します。

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.