Help us understand the problem. What is going on with this article?

【Oracle】SELECT文の実行結果(NULL)とPL/SQLでの戻り値取得結果(NO_DATA_FOUNDエラー)が違っていた件

More than 1 year has passed since last update.

【概要】

下記のようなデータを1件も取得しないNULL_TESTファンクションをSELECT文の実行結果とPL/SQLで実行した結果が予想と異なっていました。

・SELECT文で実行  →  NULLを取得する
・PL/SQLで実行   →  NO_DATA_FOUNDエラーが発生する

11.2.0で検証しています。
なぜこのような結果になるのか謎。同じ結果になると思っていたが、Oracleの仕様なのかな・・・。

【準備】

-- テスト用テーブル作成
CREATE TABLE NULL_TEST_TABLE (col1 VARCHAR(10));

-- テスト用テーブルにデータ挿入
INSERT INTO NULL_TEST_TABLE (col1) VALUES ('abc'); 

-- 1件もデータを取得しないテスト用ファンクション作成
CREATE OR REPLACE FUNCTION NULL_TEST
  RETURN VARCHAR2
IS
  func_res VARCHAR2(10);
BEGIN
  SELECT COL1 INTO func_res FROM NULL_TEST_TABLE WHERE COL1 = 'xyz';
  RETURN func_res;
END;
/

【実行結果】

■NULLを取得するパターン
SELECT NULL_TEST() FROM DUAL;

/* 実行結果
null
*/
■NO_DATA_FOUNDエラーが生じるパターン その1
BEGIN
  DBMS_OUTPUT.PUT_LINE(NULL_TEST());
END;
/

/* 実行結果
エラー・レポート -
ORA-01403: データが見つかりません。
ORA-06512: "SYS.NULL_TEST", 行6
ORA-06512: 行2
01403. 00000 -  "no data found"
*Cause:    No data was found from the objects.
*Action:   There was no data from the objects which may be due to end of fetch.
*/
■NO_DATA_FOUNDエラーが生じるパターン その2
DECLARE
  res VARCHAR2(10);
BEGIN
  res := NULL_TEST();
  DBMS_OUTPUT.PUT_LINE(res);
END;
/

/* 実行結果
エラー・レポート -
ORA-01403: データが見つかりません。
ORA-06512: "SYS.NULL_TEST", 行6
ORA-06512: 行4
01403. 00000 -  "no data found"
*Cause:    No data was found from the objects.
*Action:   There was no data from the objects which may be due to end of fetch.
*/

【例外処理を追加してエラーを回避】

■NO_DATA_FOUNDエラーが生じるパターン その1
SET SERVEROUTPUT ON
BEGIN
  DBMS_OUTPUT.PUT_LINE(NULL_TEST());
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('例外としてキャッチされました。');
END;
/

/* 実行結果
例外としてキャッチされました。
*/
■NO_DATA_FOUNDエラーが生じるパターン その2
SET SERVEROUTPUT ON
DECLARE
  res VARCHAR2(10);
BEGIN
  res := NULL_TEST();
  DBMS_OUTPUT.PUT_LINE(res);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('例外としてキャッチされました。');
END;
/

/* 実行結果
例外としてキャッチされました。
*/

【おまけのコーナー】

OracleのDBは好き。他のDBはほとんど触ったことないが・・・。

Nobu12
ユニークで奇抜な面白いアイディアのプログラミングをしたい!
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away