« ちょいと迷惑だったストアドファンクション | トップページ | 一年近くアップデートしてない"Dashboard de Aquarium"ですが・・ »

2008年9月13日 (土)

ちょいと迷惑だったストアドファンクション(つづき)

前回の続きです。

ところで何がちょっと迷惑だったのか?

まずは、マニュアルをご覧ください。

もうお分かりですよね!

例の関数、戻り値としてレコード型を返してくれるのはよいのですが、該当データが存在しなかった場合にNULLを返す作りになっています。そのレコード型はどのように比較すれば該当データが存在しないのか外部の仕様からは全く見えません。(当然、レコードがNULLであるか判定する関数も用意されていません。)

レコード型がNULLかテストできないので以下のようなコードは書けないのですよ!

SCOTT> set serveroutput on size 100000
SCOTT> l
1 DECLARE
2 members inconvenient_package.membersType;
3 BEGIN
4 members := inconvenient_package.inconvenient_function(99);
5 IF members IS NULL THEN
6 RAISE NO_DATA_FOUND;
7 ELSE
8 DBMS_OUTPUT.PUT_LINE('Dept name/id:'||members.deptname||'/'||TO_CHAR(members.deptno));
9 FOR i IN members.employees.FIRST..members.employees.LAST LOOP
10 DBMS_OUTPUT.PUT_LINE(' -- '||members.employees(i).ename);
11 END LOOP;
12 END IF;
13* END;
SCOTT> /
IF members IS NULL THEN
*
行5でエラーが発生しました。:
ORA-06550: 行5、列6:
PLS-00306: 'IS NULL'の呼出しで、引数の数または型が正しくありません。
ORA-06550: 行5、列3:PL/SQL: Statement ignored

利用する側からすると、該当データが存在しない場合のコードをどうかいて良いのかAPIからは判断がつかない(該当関数のソースコードを読まない限り判断できない)。
この点がちょいと迷惑だったわけ。

例の関数をそのまま利用した場合以下のようなコードを書かないと該当データが存在しないことを判定できない。
(赤太字部分は利用する関数のコード及びこの関数がアクセスしている表定義を見て判断する必要がある。面倒くさい! なんでAPI見ただけでコード書く事できないような共通関数があるの〜〜〜。)

SCOTT> l
1 DECLARE
2 members inconvenient_package.membersType;
3 BEGIN
4 members := inconvenient_package.inconvenient_function(99);
5 IF members.employees.COUNT = 0
6 OR members.deptno IS NULL
7 THEN
8 DBMS_OUTPUT.PUT_LINE('該当データが存在しません.');
9 ELSE
10 DBMS_OUTPUT.PUT_LINE(
11 'Dept name/id:'
12 ||members.deptname
13 ||'/'||TO_CHAR(members.deptno)
14 );
15 FOR i IN members.employees.FIRST..members.employees.LAST LOOP
16 DBMS_OUTPUT.PUT_LINE(' -- '||members.employees(i).ename);
17 END LOOP;
18 END IF;
19* END;
SCOTT> /
該当データが存在しません.

PL/SQLプロシージャが正常に完了しました。


SCOTT> l
1 DECLARE
2 members inconvenient_package.membersType;
3 BEGIN
4 members := inconvenient_package.inconvenient_function(10);
5 IF members.employees.COUNT = 0
6 OR members.deptno IS NULL
7 THEN
8 DBMS_OUTPUT.PUT_LINE('該当データが存在しません.');
9 ELSE
10 DBMS_OUTPUT.PUT_LINE(
11 'Dept name/id:'
12 ||members.deptname
13 ||'/'||TO_CHAR(members.deptno)
14 );
15 FOR i IN members.employees.FIRST..members.employees.LAST LOOP
16 DBMS_OUTPUT.PUT_LINE(' -- '||members.employees(i).ename);
17 END LOOP;
18 END IF;
19* END;
SCOTT> /
Dept name/id:ACCOUNTING/10
-- CLARK
-- KING
-- MILLER

PL/SQLプロシージャが正常に完了しました。

SCOTT>

例の関数を修正するとしたら、私個人の趣味だとNO_DATA_FOUND例外はそのままスローするように例外ハンドラー部分を削除しますね。
(ファンクションである必要も無さそうなのでプロシージャにして該当データ無しなどを表すステータスを返す方法を好む方も多いのですが。)

例の関数を修正しNO_DATA_FOUND例外をそのままスローする仕様に変更すれば共通関数のソースコードや関連する表定義まで追っかける必要は無くなりますよ!。w
以下、NO_DATA_FOUNDの例外ハンドラーを削除した例

CREATE OR REPLACE PACKAGE BODY inconvenient_package
AS
FUNCTION inconvenient_function(iDeptNo IN dept.deptNo%TYPE)
RETURN membersType
AS
members membersType;
BEGIN
SELECT
deptNo
,dName
INTO
members.deptNo
,members.deptName
FROM
dept
WHERE
deptNo = iDeptNo
;
--
SELECT *
BULK COLLECT INTO
members.employees
FROM
emp
WHERE
deptno = iDeptNo
;
RETURN members;
-- NO_DATA_FOUND例外ハンドラーを削除して利用する側でハンドリングさせる
END inconvenient_function;
END inconvenient_package;
/

前述のように修正すれば以下のようなコードを書くだけでよく余計なことに気を使う事もなくなる。(関数のコードやアクセスしている表定義なんて覗かなくても該当データが存在しない場合、NO_DATA_FOUND例外をキャッチするだけで済む)

SCOTT> l
1 DECLARE
2 members inconvenient_package.membersType;
3 BEGIN
4 BEGIN
5 members := inconvenient_package.inconvenient_function(10);
6 DBMS_OUTPUT.PUT_LINE(
7 'Dept name/id:'
8 ||members.deptname
9 ||'/'||TO_CHAR(members.deptno)
10 );
11 FOR i IN members.employees.FIRST..members.employees.LAST LOOP
12 DBMS_OUTPUT.PUT_LINE(' -- '||members.employees(i).ename);
13 END LOOP;
14 --
15 EXCEPTION
16 WHEN NO_DATA_FOUND THEN
17 DBMS_OUTPUT.PUT_LINE('該当データが存在しません。');
18 END;
19* END;
SCOTT> /
Dept name/id:ACCOUNTING/10
-- CLARK
-- KING
-- MILLER

PL/SQLプロシージャが正常に完了しました。

SCOTT> l
1 DECLARE
2 members inconvenient_package.membersType;
3 BEGIN
4 BEGIN
5 members := inconvenient_package.inconvenient_function(99);
6 DBMS_OUTPUT.PUT_LINE(
7 'Dept name/id:'
8 ||members.deptname
9 ||'/'||TO_CHAR(members.deptno)
10 );
11 FOR i IN members.employees.FIRST..members.employees.LAST LOOP
12 DBMS_OUTPUT.PUT_LINE(' -- '||members.employees(i).ename);
13 END LOOP;
14 --
15 EXCEPTION
16 WHEN NO_DATA_FOUND THEN
17 DBMS_OUTPUT.PUT_LINE('該当データが存在しません。');
18 END;
19* END;
SCOTT> /
該当データが存在しません。

PL/SQLプロシージャが正常に完了しました。

SCOTT>

2009/3/25追記:
この例では該当関数の例外ハンドラーを削除しただけですが、もし該当関数やプロシージャの例外ハンドラーでエラーログ(例えばLog4PLSQLとか)などでエラーなどを追跡しやすくするようなロジックを実装していたら・・・・例外ハンドラーを削除することは避けてくださいね。そのような場合は例外ハンドラー内で同じ例外をスローするように変更すべきです(プロジェクトの規約でユーザー定義例外をスローするようにしているところもあります。)
そのようにすることで該当関数から呼び出しもとへ同じ例外がスローされるようになります。具体的な例は別エントリで書く予定。

ついでなのでOTNオンラインマニュアルのURLを変更。(OTNのオンラインマニュアルは訂正が入るとリビジョンが上がりURLまで変っちゃうのが面倒なのよ。ほんと。。URL変らないように工夫してくれないかな。。

|

トラックバック

この記事のトラックバックURL:
http://app.cocolog-nifty.com/t/trackback/106341/42460482

この記事へのトラックバック一覧です: ちょいと迷惑だったストアドファンクション(つづき):

コメント

コメントを書く