カーソルとカーソル変数

  カーソル



特定の文を処理する情報を保存しておきます。
PL/SQL 内で固定の SELECT 構文を示します( 変数使用は可能 )

明示カーソルおよび暗黙カーソルという呼び名があります。

○ 暗黙カーソル
     PL/SQLでは、1行のみを返す問合せを含む、一連の行にあるすべてのSQLデータ操作文に対して
     暗黙的にカーソルが宣言されます(SQL カーソル)。
     Oracle ドキュメント : SQL カーソル

○ 明示カーソル
     ループを使って複数行を返す問合せの場合は、カーソルを明示的に宣言する



カーソル属性
どの明示カーソルおよびカーソル変数にも%FOUND、%ISOPEN、%NOTFOUND
および%ROWCOUNTの4つの属性があります




  暗黙カーソル属性の使用例



  
PROCEDURE 暗黙カーソル
(
	PM_STRING IN VARCHAR2
)

/**********************************************************/
/* 変数の定義 */
/**********************************************************/
AS

/**********************************************************/
/* 処理開始 */
/**********************************************************/
BEGIN
	DBMS_OUTPUT.PUT_LINE('デバッグ:開始');
	DBMS_OUTPUT.PUT_LINE('デバッグ:引数:'||PM_STRING);

	UPDATE 社員マスタ SET 更新日 = sysdate WHERE 社員コード = PM_STRING;
	IF SQL%FOUND THEN  -- 更新が実行された
		DBMS_OUTPUT.PUT_LINE('対象者あり');
	ELSE
		DBMS_OUTPUT.PUT_LINE('対象者なし');
	END IF;

/**********************************************************/
/* 一番外側のブロックの例外処理 */
/**********************************************************/
EXCEPTION
	WHEN OTHERS THEN
		DBMS_OUTPUT.PUT_LINE('例外発生:'||SQLCODE||':'||SQLERRM);
END;
  

%ISOPENカーソル属性は、暗黙カーソルの場合は常にFALSE になります
%NOTFOUND属性 は、%FOUND 属性の逆です。

以下は、SELECT INTO による %FOUND の使用例

  
PROCEDURE 暗黙カーソル
(
	PM_STRING IN VARCHAR2
)

/**********************************************************/
/* 変数の定義 */
/**********************************************************/
AS
	WK_DATE DATE;

/**********************************************************/
/* 処理開始 */
/**********************************************************/
BEGIN
	DBMS_OUTPUT.PUT_LINE('デバッグ:開始');
	DBMS_OUTPUT.PUT_LINE('デバッグ:引数:'||PM_STRING);

	/**********************************************************/
	/* 行が戻されないと、例外が発生する */
	/**********************************************************/
	BEGIN
		SELECT 更新日 INTO WK_DATE
		   FROM 社員マスタ WHERE 社員コード = PM_STRING;
	EXCEPTION
		WHEN OTHERS THEN
			NULL;
	END;
	IF SQL%FOUND THEN
		DBMS_OUTPUT.PUT_LINE('対象者あり:' || WK_DATE);
	ELSE
		DBMS_OUTPUT.PUT_LINE('対象者なし');
	END IF;

/**********************************************************/
/* 一番外側のブロックの例外処理 */
/**********************************************************/
EXCEPTION
	WHEN OTHERS THEN
		DBMS_OUTPUT.PUT_LINE('例外発生:'||SQLCODE||':'||SQLERRM);
END;
  

%ROWCOUNT属性は、これまでに影響を受けた行数なので、以下のようにして使用します

  
PROCEDURE 暗黙カーソル

/**********************************************************/
/* 変数の定義 */
/**********************************************************/
AS

/**********************************************************/
/* 処理開始 */
/**********************************************************/
BEGIN
	DBMS_OUTPUT.PUT_LINE('デバッグ:開始');

	UPDATE 社員マスタ SET 更新日 = sysdate;
	DBMS_OUTPUT.PUT_LINE('更新行数:' || SQL%ROWCOUNT);

/**********************************************************/
/* 一番外側のブロックの例外処理 */
/**********************************************************/
EXCEPTION
	WHEN OTHERS THEN
		DBMS_OUTPUT.PUT_LINE('例外発生:'||SQLCODE||':'||SQLERRM);
END;
  



  暗黙カーソル属性の使用上の注意点

1)
SELECT INTO文が複数の行を戻した場合、例外TOO_MANY_ROWSが発生して、
%ROWCOUNTは、問合せを満たす行の実数ではなく、1になります。

2)
%ROWCOUNT属性の値は直前に実行されたSQL文を参照しています。
属性の値を保存して後で使用する場合は、値をローカル変数に直接代入してください。

3)
%ROWCOUNT属性はトランザクションの状態には無関係です。
ロールバックが実行されても、%ROWCOUNTの値が元に戻ることはありません。

4)
%NOTFOUND属性は、SELECT INTO文と組み合せて使用しても効果がありません。
例外処理を考慮した上で、%FOUND で記述します( 前述の例 )

5)
SQL集計関数をコールするSELECT INTO文は、常に値またはNULLを戻します。
このような文の後では、%FOUND や %NOTFOUND属性の値は決まっているので、
属性をチェックする意味がありません。



  カーソル変数 ( REF CURSOR )

カーソル変数は、結果セットへのポインタに類似しています。
特定の問合せと結合されないため柔軟性があり、プロシージャの引数として使用する事ができます。
また、文字列を動的に実行する場合にも威力を発揮します。

※ カーソル変数を作成するには、REF CURSOR型を定義してから、その型のカーソル変数を宣言します。
※ ( 事前定義のSYS_REFCURSOR型を使用することもできます )

  
PROCEDURE 外部カーソル変数
(
	引数カーソル IN SYS_REFCURSOR
)
AS

	社員_REC 社員マスタ%ROWTYPE; 

BEGIN

	FETCH 引数カーソル INTO 社員_REC;
	if 引数カーソル%NOTFOUND then
		RETURN;
	end if;
	DBMS_OUTPUT.PUT_LINE( '外部:' || 社員_REC.社員コード || ' : ' || 社員_REC.氏名);

/**********************************************************/
/* 一番外側のブロックの例外処理 */
/**********************************************************/
EXCEPTION
	WHEN OTHERS THEN
		DBMS_OUTPUT.PUT_LINE('例外発生:'||SQLCODE||':'||SQLERRM);
END;
  

  
PROCEDURE カーソル変数
(
	PM_STRING IN VARCHAR2
)

/**********************************************************/
/* 変数の定義 */
/**********************************************************/
AS
	WK_VALUE	VARCHAR2(2000);

	社員_REC 社員マスタ%ROWTYPE; 
	TYPE カーソル型 IS REF CURSOR;
	検索cur カーソル型;

/**********************************************************/
/* 内部プロシージャ */
/**********************************************************/
PROCEDURE 内部カーソル変数
(
	引数カーソル IN カーソル型
)
AS
	社員_REC 社員マスタ%ROWTYPE; 
BEGIN

	FETCH 引数カーソル INTO 社員_REC;
	if 引数カーソル%NOTFOUND then
		RETURN;
	end if;
	DBMS_OUTPUT.PUT_LINE( 社員_REC.社員コード || ' : ' || 社員_REC.氏名);
END;

/**********************************************************/
/* 処理開始 */
/**********************************************************/
BEGIN
	DBMS_OUTPUT.PUT_LINE('デバッグ:開始');
	DBMS_OUTPUT.PUT_LINE('デバッグ:引数:'||PM_STRING);

	/**********************************************************/
	/* 固定 SELECT ( 但しカーソル変数自体は他の検索にも使える )*/
	/**********************************************************/
	OPEN 検索cur for SELECT * FROM 社員マスタ
		WHERE 社員コード >= PM_STRING;
	LOOP
		FETCH 検索cur INTO 社員_REC;
		EXIT when 検索cur%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE( 社員_REC.社員コード || ' : ' || 社員_REC.氏名);
	END LOOP;
	CLOSE 検索cur;

	/**********************************************************/
	/* 動的 SELECT */
	/**********************************************************/
	WK_VALUE := 'SELECT * FROM 社員マスタ WHERE 社員コード >= :仮引数';
	OPEN 検索cur for WK_VALUE using PM_STRING;
	LOOP
		FETCH 検索cur INTO 社員_REC;
		EXIT when 検索cur%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE( 社員_REC.社員コード || ' : ' || 社員_REC.氏名);
	END LOOP;
	CLOSE 検索cur;

	/**********************************************************/
	/* 内部プロシージャ呼び出し */
	/**********************************************************/
	WK_VALUE := 'SELECT * FROM 社員マスタ WHERE 社員コード >= :仮引数';
	OPEN 検索cur for WK_VALUE using PM_STRING;
	LOOP
		内部カーソル変数( 検索cur );
		EXIT when 検索cur%NOTFOUND;
	END LOOP;
	CLOSE 検索cur;

	/**********************************************************/
	/* 外部プロシージャ呼び出し */
	/**********************************************************/
	WK_VALUE := 'SELECT * FROM 社員マスタ WHERE 社員コード >= :仮引数';
	OPEN 検索cur for WK_VALUE using PM_STRING;
	LOOP
		外部カーソル変数( 検索cur );
		EXIT when 検索cur%NOTFOUND;
	END LOOP;
	CLOSE 検索cur;

/**********************************************************/
/* 一番外側のブロックの例外処理 */
/**********************************************************/
EXCEPTION
	WHEN OTHERS THEN
		DBMS_OUTPUT.PUT_LINE('例外発生:'||SQLCODE||':'||SQLERRM);
END;
  










  infoboard   管理者用   
このエントリーをはてなブックマークに追加





フリーフォントWEBサービス
SQLの窓WEBサービス

SQLの窓フリーソフト

素材

一般WEBツールリンク

SQLの窓

フリーソフト

JSライブラリ