ステートメントとは、SQL文とオプションの制御フロー文をプリコンパイルした集合であり、PLはこれを単一のユニットとして処理します。1つのステートメントは他のステートメントを参照することができ、複数の変数を返すことも可能です。
適用対象
この内容は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つのパッケージに再構成することはサポートされていません。1つのプログラムは少数のアクセスを制限し、もう1つのプログラムは公開アクセスを提供するために使用されます。
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) を含むトランザクション全体がコミットされます。