ストアドプロシージャは、SQLステートメントとオプションの制御フロー文のプリコンパイル済みコレクションであり、PLはそれを単一のユニットとして処理します。ストアドプロシージャは他のストアドプロシージャを参照することができ、複数の変数を返すこともできます。
機能の適用範囲
この内容はOceanBaseデータベースEnterprise Editionにのみ適用されます。OceanBaseデータベースCommunity EditionはMySQLモードのみを提供します。
ストアドプロシージャを作成する構文は次のとおりです：
CREATE [OR REPLACE] PROCEDURE Procedure_name
[ (argment [ { IN | IN OUT }] Type,
argment [ { IN | OUT | IN OUT } ] Type ]
[ AUTHID DEFINER | CURRENT_USER ]
{ IS | AS }
delarification_block
BEGIN
procedure_body
EXCEPTION
exception_handler
END;
パラメータなしのストアドプロシージャの例は次のとおりです：
obclient> CREATE TABLE loghistory
(userid VARCHAR2(20),
logdate DATE DEFAULT SYSDATE);
Query OK, 0 rows affected
obclient> CREATE OR REPLACE PROCEDURE userlogin
IS
BEGIN
INSERT INTO loghistory (userid) VALUES (USER);
END;
/
Query OK, 0 rows affected
ストアドプロシージャの呼び出し
ストアドプロシージャの作成が完了したら、ユーザーは権限付与を経てOBClient、OceanBase Developer Center、またはサードパーティ開発ツールから呼び出して実行できます。
例：
obclient> SELECT * FROM loghistory;
Empty set
obclient> BEGIN
userlogin;
END;
/
Query OK, 0 rows affected
obclient> SELECT * FROM loghistory;
+--------+-----------+
| USERID | LOGDATE |
+--------+-----------+
| HR | 27-SEP-20 |
+--------+-----------+
1 row in set
obclient> COMMIT;
Query OK, 0 rows affected
サブプログラム属性
各サブプログラム属性は、サブプログラムの宣言内で1回のみ記述でき、任意の順序で配置できます。属性はサブプログラムの IS または AS キーワードの前に置かれます。この属性はネストされたサブプログラムでは使用できません。
パッケージ内では ACCESSIBLE BY 属性のみが使用できます。独立サブプログラムは、その宣言内で以下の属性を持つことができます。
AUTHID属性ACCESSIBLE BY句
AUTHID
ストアドプロシージャを定義する際に AUTHID 句を定義することで、実行時にストアドプロシージャに付与されるユーザーの権限を取得できます。権限タイプは以下の2種類です：
AUTHID DEFINER（定義者権限）：デフォルトでは、ストアドプロシージャの権限は所有者から得られます。AUTHID CURRENT_USER（呼び出し元権限）：実行時に現在のセッションユーザーに付与される権限であり、これは現在ログインしているユーザーと同じまたは異なる場合があります（ALTER SESSION SET CURRENT_SCHEMAにより呼び出し元のスキーマを変更できます）。
例：HR ユーザーがストアドプロシージャ userlogin を作成し、AUTHID DEFINER を指定します。
obclient> CREATE OR REPLACE PROCEDURE userlogin
AUTHID DEFINER
IS
BEGIN
INSERT INTO loghistory (userid) VALUES (USER);
END;
/
Query OK, 0 rows affected
HR ユーザーが userlogin の実行権限を他のユーザーに付与すると、scott ユーザーはストアドプロシージャを実行できます。
obclient> SELECT * FROM loghistory;
+--------+-----------+
| USERID | LOGDATE |
+--------+-----------+
| HR | 27-SEP-20 |
+--------+-----------+
obclient>GRANT EXECUTE ON userlogin TO PUBLIC;
Query OK, 0 rows affected
scott ユーザーには hr.loghistory へのアクセス権限がありませんが、ストアドプロシージャ userlogin を実行できます。
obclient> SELECT USER FROM DUAL;
+-------+
| USER |
+-------+
| SCOTT |
+-------+
1 row in set
obclient> SELECT * FROM loghistory;
OBE-00942: table or view 'SCOTT.LOGHISTORY' does not exist
obclient> SELECT * FROM hr.loghistory;
OBE-00942: table or view does not exist
obclient> BEGIN
hr.userlogin;
END;
/
Query OK, 0 rows affected
obclient> COMMIT;
Query OK, 0 rows affected
scott ユーザーが HR ユーザーが作成したストアドプロシージャ userlogin を実行して hr.loghistory に正常に挿入できたことから、scott ユーザーがこのストアドプロシージャを実行する際には HR の権限を持っていることが示されます。
obclient> SELECT USER FROM DUAL;
+------+
| USER |
+------+
| HR |
+------+
1 row in set
obclient> SELECT * FROM hr.loghistory;
+--------+-----------+
| USERID | LOGDATE |
+--------+-----------+
| HR | 27-SEP-20 |
| SCOTT | 27-SEP-20 |
+--------+-----------+
2 rows in set
AUTHID CURRENT_USER 句についてです。HR ユーザーが AUTHID CURRENT_USER 句を使用してストアドプロシージャ userlogin を再定義します。
obclient> CREATE OR REPLACE PROCEDURE userlogin
AUTHID CURRENT_USER
IS
BEGIN
INSERT INTO loghistory (userid) VALUES (USER);
END;
/
Query OK, 0 rows affected
scott ユーザーが再度 userlogin を実行するとエラーが発生します。これは scott には hr.loghistory テーブルへのアクセス権限がないためです。scott ユーザーがテーブル loghistory を作成すると、実行は成功し、データは実際に scott.loghistory に挿入されます。
obclient> SELECT USER FROM DUAL;
+-------+
| USER |
+-------+
| SCOTT |
+-------+
1 row in set
obclient> BEGIN
hr.userlogin;
END;
/
OBE-00942: table or view 'SCOTT.LOGHISTORY' does not exist
obclient>ALTER SESSION SET current_schema=hr;
Query OK, 0 rows affected
obclient> BEGIN
hr.userlogin;
END;
/
OBE-00942: table or view does not exist
obclient>ALTER SESSION SET current_schema=scott;
Query OK, 0 rows affected
obclient> CREATE TABLE loghistory
(userid VARCHAR2(20),
logdate date default sysdate);
Query OK, 0 rows affected
obclient> BEGIN
hr.userlogin;
END;
/
Query OK, 0 rows affected
obclient> COMMIT;
Query OK, 0 rows affected
obclient> SELECT * FROM loghistory;
+--------+-----------+
| USERID | LOGDATE |
+--------+-----------+
| SCOTT | 27-SEP-20 |
+--------+-----------+
1 row in set
ACCESSIBLE BY
ACCESSIBLE BY は、オブジェクトの呼び出し元を制約できます。
アクセス可能なユニットを明示的にリストアップするのがアクセサーリストです。アクセサーリストはサブプログラムパッケージ上で定義できます。パッケージは自身が定義したアクセサーリスト（存在する場合）をチェックします。このリストは、内部サブプログラムの不必要な使用を防ぐため、サブプログラムへのアクセスを制限するだけで、アクセスを拡張することはできません。例えば、パッケージを2つのパッケージに再構成することはサポートされていません。一つのパッケージで少数のアクセスを制限し、もう一つのパッケージで公開アクセスを提供するという方法は認められていません。
ACCESSIBLE BY 句は、オブジェクト型、オブジェクト型本体、パッケージ、およびサブプログラムの宣言に使用できます。
ACCESSIBLE BY 句は、以下のSQL文で使用できます：
ALTER TYPE文CREATE FUNCTION文プロシージャ作成文
パッケージ宣言作成文
CREATE TYPE文CREATE TYPE BODY文
構文は以下のとおりです：
unit_kind:
FUNCTION { $$[0] = SP_FUNCTION; }
| PROCEDURE { $$[0] = SP_PROCEDURE; }
| PACKAGE_P { $$[0] = SP_PACKAGE; }
| TRIGGER { $$[0] = SP_TRIGGER; }
| TYPE { $$[0] = SP_TYPE; }
;
accessor:
pl_schema_name
{
malloc_non_terminal_node($$, parse_ctx->mem_pool_, T_SP_ACCESSOR, 2, NULL, $1);
}
| unit_kind pl_schema_name
{
ParseNode *accessor_kind = NULL;
malloc_terminal_node(accessor_kind, parse_ctx->mem_pool_, T_SP_ACCESSOR_KIND);
accessor_kind->value_ = $1[0];
malloc_non_terminal_node($$, parse_ctx->mem_pool_, T_SP_ACCESSOR, 2, accessor_kind, $2);
}
;
accessor_list:
accessor_list ',' accessor
{
malloc_non_terminal_node($$, parse_ctx->mem_pool_, T_LINK_NODE, 2, $1, $3);
}
| accessor
{
$$ = $1;
}
;
accessible_by:
ACCESSIBLE BY '(' accessor_list ')'
{
ParseNode *accessor_list = NULL;
merge_nodes(accessor_list, parse_ctx->mem_pool_, T_SP_ACCESSOR_LIST, $4);
malloc_non_terminal_node($$, parse_ctx->mem_pool_, T_SP_ACCESSIBLE_BY, 1, accessor_list);
}
;
自律トランザクション
OceanBaseデータベースは自律トランザクションをサポートしています。自律トランザクションは親トランザクションから独立して存在し、単独でコミットまたはロールバックされても親トランザクションに影響を与えません。
以下の例に従ってオブジェクトを作成します：
-- ログテーブルを作成
CREATE TABLE logtable(
username VARCHAR2(20),
date_time DATE,
message VARCHAR2(60)
);
-- 一時テーブルを作成
CREATE TABLE demotable( N number);
-- 自律トランザクションを持つプロシージャを作成
CREATE OR REPLACE PROCEDURE log_message(p_message varchar2)
AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO logtable VALUES ( user, sysdate, p_message );
COMMIT;
END log_message;
次の操作手順は以下のとおりです：
log_messageを呼び出してログを書き込みます。トランザクション内で
demotableにデータを挿入します。再度
log_messageを呼び出して、もう一つログを書き込みます。現在のトランザクションをロールバックします。
obclient> SELECT * FROM logtable;
Empty set
obclient> SELECT * FROM demotable;
Empty set
obclient> BEGIN
Log_message ('About to insert into demotable.');
INSERT INTO demotable VALUES (1);
Log_message ('Rollback the transaction.');
ROLLBACK;
END;
/
Query OK, 0 rows affected
obclient> SELECT * FROM logtable;
+----------+-----------+---------------------------------+
| USERNAME | DATE_TIME | MESSAGE |
+----------+-----------+---------------------------------+
| HR | 28-SEP-20 | About to insert into demotable. |
| HR | 28-SEP-20 | Rollback the transaction. |
+----------+-----------+---------------------------------+
2 rows in set
上記の例では、demotable への挿入操作がロールバックされました。自律トランザクションを持つストアドプロシージャ log_message は、自身のトランザクションのみをコミットし、ログテーブルにデータを書き込みました。
ストアドプロシージャ log_message を再作成し、自律トランザクション属性を削除して、データベースの動作を確認します。
obclient> CREATE OR REPLACE PROCEDURE log_message(p_message varchar2)
AS
BEGIN
INSERT INTO logtable VALUES ( user, sysdate, p_message );
COMMIT;
END log_message;
/
Query OK, 0 rows affected
同じストアドプロシージャを再度実行します：
obclient> SELECT * FROM logtable;
Empty set
obclient> SELECT * FROM demotable;
Empty set
obclient> BEGIN
Log_message ('About to insert into demotable.');
INSERT INTO demotable VALUES (1);
Log_message ('Rollback the transaction.');
ROLLBACK;
END;
/
Query OK, 0 rows affected
obclient> SELECT * FROM logtable;
+----------+-----------+---------------------------------+
| USERNAME | DATE_TIME | MESSAGE |
+----------+-----------+---------------------------------+
| HR | 28-SEP-20 | About to insert into demotable. |
| HR | 28-SEP-20 | Rollback the transaction. |
+----------+-----------+---------------------------------+
2 rows in set
obclient>SELECT * FROM demotable;
+------+
| N |
+------+
| 1 |
+------+
1 row in set
2回の実行を比較すると、2回目の実行では demotable 上のトランザクションはロールバックされませんでした。自律トランザクションを使用しない場合、すべての変更は単一のトランザクション内で行われます。そのため、2回目に log_message プロシージャを呼び出す際、その中の commit ステートメントにより、INSERT INTO demotable VALUES (1) を含むトランザクション全体がコミットされます。