明示的カーソルは主にクエリステートメントの処理に使用され、特にクエリ結果が複数のレコードである場合に有効です。
適用対象
この内容はOceanBaseデータベースEnterprise Editionにのみ適用されます。OceanBaseデータベースCommunity EditionはMySQLモードのみを提供します。
明示カーソルの呼び出し形式
明示的なカーソルを宣言した後、同一のサブプログラムまたはプログラムパッケージ内で定義することができます。また、明示的なカーソルを同時に宣言して定義することも可能です。
明示的なカーソルを宣言するだけの構文は以下のとおりです：
CURSOR cursor_name [ parameter_list ] RETURN return_type;
カーソルを定義する構文は以下のとおりです：
CURSOR cursor_name [ parameter_list ] [ RETURN return_type ]
IS select_statement;
明示カーソルの処理手順
明示カーソルの処理には4つのステップがあります：
カーソルを定義する
カーソルを開く
カーソルデータを取得する
カーソルを閉じる
カーソルを定義する
カーソル名と、それに対応する SELECT ステートメントを定義します。
構文は以下のとおりです：
CURSOR cursor_name[(parameter[, parameter]...)] IS select_statement;
カーソルパラメータは入力パラメータのみであり、その構文は以下のとおりです：
parameter_name [IN] datatype [{:= | DEFAULT} expression]
注意
データ型を指定する際には、長さ制約を使用することはできません。NUMBER(4)、CHAR(10) などはすべて誤りです。
カーソルを開く
OPEN ステートメントは、カーソルを開き、カーソルに対応する SELECT ステートメントを実行し、そのクエリ結果をワークエリアに格納します。また、ポインタはワークエリアの先頭を指し、カーソル結果セットを識別します。カーソルクエリステートメントに FOR UPDATE オプションが含まれている場合、OPEN ステートメントはデータベーステーブル内のカーソル結果セットに対応するデータ行をロックします。
構文は以下のとおりです：
OPEN cursor_name[([parameter =>] value[, [parameter =>] value]...)];
カーソルにパラメータを渡す際には、関数パラメータと同じ値渡し方法、すなわち位置表記法と名前表記法を使用できます。PLプログラムでは、OPEN ステートメントを使用してカーソルを複数回開くことはできません。
カーソルデータを取得する
FETCH ステートメントを使用して結果セット内のデータ行を検索し、指定された出力変数に格納します。
構文は以下のとおりです：
FETCH cursor_name INTO {variable_list | record_variable };
/* カーソルを使用して一括でデータを取得することができます */
FETCH { cursor | cursor_variable | :host_cursor_variable }
{ into_clause | BULK COLLECT INTO { collection | :host_array }
[, { collection | :host_array } ]... [ LIMIT numeric_expression ] } ;
通常、FETCH ステートメントは LOOP ステートメント内で使用され、アクティブセット内のすべてのレコードが処理されるまで、そのレコードに対してループ処理を行います。カーソルプロパティ %NOTFOUND を使用して終了条件を検出します。
カーソルを閉じる
CLOSE ステートメントは、カーソルを閉じるために使用されます。
構文は以下のとおりです：
CLOSE cursor_name;
カーソル結果セットのデータを取得し処理した後、カーソルが占有するシステムリソースを解放し、カーソルのワークエリアを無効にするため、適時にカーソルを閉じる必要があります。この時点で、FETCH ステートメントを使用してデータを取得することはできなくなります。閉じたカーソルは、OPEN ステートメントを使用して再度開くことができます。
明示カーソルを使用してレコードセットの各行をループで取得する
以下の手順では、明示カーソルを使用してレコードセットの各行をループで取得する方法を示します。
まず宣言部でカーソルを宣言します。以下の2つの方法を参照してください。
CURSOR cursor_name IS query; --方法1 CURSOR cursor_name(para_name data_type) IS query; --方法2宣言部で、カーソルが返す行データを格納するための変数を宣言します。
record_name cursor_name%ROWTYPE;実行部でカーソルを開きます。以下の2つの方法を参照してください。
OPEN cursor_name; --方法1 OPEN cursor_name(para_name); --方法2実行部で、カーソルから行データを取得します。毎回1行ずつ、以下の形式の
LOOPステートメントと組み合わせて使用することで、複数行のデータを取得できます。LOOP FETCHcursor_name INTO record_name; EXIT WHEN cursor_name%NOTFOUND; statement; [ statement; ]... END LOOP;カーソルを閉じます。
CLOSE cursor_name;
例：明示カーソルを使用してレコードセットの各行をループで取得する。
obclient> CREATE TABLE ware (
w_id int
, w_ytd decimal(12,2)
, w_tax decimal(4,4)
, w_name varchar(10)
, w_street_1 varchar(20)
, w_street_2 varchar(20)
, w_city varchar(20)
, w_state char(2)
, w_zip char(9)
, unique(w_name, w_city)
, primary key(w_id)
);
Query OK, 0 rows affected
obclient> INSERT INTO ware VALUES(1, 1200, .1868, 'W_NAME_1', 'jTNkXKWXOdh',
'lf9QXTXXGoF04IZBkCP7', 'srRq15uvxe5', 'GQ', 506811111);
Query OK, 1 row affected
obclient> INSERT INTO ware VALUES(2, 1200, .0862, 'W_NAME_2', 'xEdT1jkENtbLwoI1Zb0',
'NT0j4RCQ4OqrS', 'vlwzndw2FPrO', 'XR', 063311111);
Query OK, 1 row affected
obclient> CREATE OR REPLACE PACKAGE pkg_ware_mgmt as
PROCEDURE sp_record_print(p_w_id IN ware.w_id%type);
END;/
Query OK, 0 rows affected
obclient> CREATE OR REPLACE PACKAGE BODY pkg_ware_mgmt
AS
PROCEDURE sp_record_print_by_record(p_record IN ware%ROWTYPE)
AS
BEGIN
dbms_output.put_line('Print a record :');
dbms_output.put_line('W_ID : ' || p_record.w_id
|| ', W_YTD : ' || p_record.w_ytd
|| ', W_TAX : ' || p_record.w_tax
|| ', W_NAME : ' || p_record.w_name
|| ', W_STREET_1 : ' || p_record.w_street_1
|| ', W_STREET_2 : ' || p_record.w_street_2
|| ', W_CITY : ' || p_record.w_city
|| ', W_STATE : ' || p_record.w_state
|| ', W_ZIP : ' || p_record.w_zip )
;
dbms_output.put_line('');
END;
PROCEDURE sp_record_print(p_w_id IN ware.w_id%TYPE )
IS
CURSOR c1(cp_w_id ware.w_id%TYPE ) IS SELECT * FROM ware WHERE w_id = cp_w_id;
r_ware ware%ROWTYPE;
BEGIN
OPEN c1(p_w_id);
dbms_output.put_line('Open a cursor with a parameter [ ' || p_w_id || ' ].');
LOOP
FETCH c1 INTO r_ware ;
dbms_output.put_line('Fetch the cursor one time.');
EXIT WHEN c1%NOTFOUND ;
sp_record_print_by_record(r_ware);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Raise an unkown exception !');
END ;
END;
/
Query OK, 0 rows affected
obclient>SET SERVEROUTPUT ON;
Query OK, 0 rows affected
obclient>CALL pkg_ware_mgmt.sp_record_print(2);
Query OK, 0 rows affected
Open a cursor with a parameter [ 2 ].
Fetch the cursor one time.
Print a record :
W_ID : 2, W_YTD : 1200, W_TAX : .0862, W_NAME : W_NAME_2, W_STREET_1 : xEdT1jkENtbLwoI1Zb0, W_STREET_2 : NT0j4RCQ4OqrS, W_CITY : vlwzndw2FPrO, W_STATE : XR, W_ZIP : 63311111
Fetch the cursor one time.