Hatena::ブログ(Diary)

on the center line.

2008-03-11

PLS-00231: 関数XXXXXXはSQLでは使用できません、の原因

| 21:42

ファンクションXXXXXXを宣言しているにもかかわらず、

PLS-00231: 関数XXXXXXはSQLでは使用できません。

このエラーが発生。どうやらfunctionというのはprocedureと違って、パッケージ内プライベートとして作成することはできないみたい。なので、function XXXXXX の宣言をパッケージの方(パッケージボディではない方)に加えてやると万事解決。

create or replace package foo is
   function XXXXXX return date;

   -- and any definition....
end;

create or replace package foo is
   function XXXXXX return date is
   begin
      return add_months(trunc(sysdate,'MONTH'), 2);
   end;

   -- and any definition....
end;

PL/SQLでジェネリックなリストを使う

| 11:55

PL/SQLにはTable型というものがあり、これは簡単にいうと「ジェネリック型のリスト」ののような感じです。

declare
  type listClass is table of 社員%rowtype;
  list listClass;
begin
  select * bulk collect into list from 社員;
end;

このように使えるみたいです。次に、このリストを、戻り値として返す例です。

create package foo is
  -- リスト型の定義
  type listClass is table of 社員%rowtype;
  -- リスト型を返す関数の定義
  function bar return listClass;
end;

create or replace package body foo is

  function bar return listClass is
    -- リスト型オブジェクトを定義
    list listClass;
  begin
    -- bulk collect into を使って代入
    select * bulk collect into list from 社員;

    return list;
  end;
  
end;

見慣れない「bulk collect into 〜」を使う以外は、かなり直感的なプログラムだと思います。また、リストの要素型には以下のように、ユーザ定義型を指定することもできるようです。

create package foo is
  -- リストの要素型の定義
  type rowClass is record (id number, name varchar2(10));  
  -- リスト型の定義
  type listClass is table of rowClass;

ここでは「record」型として、ユーザ定義の型を定義しています。

ちなみに、functionの呼び出し元では、以下のようになります。

declare
    list foo.listClass;
begin
    list := foo.bar();

    -- 取得した内容を、デバッグ出力で確認
    for i in 1 .. list.count loop
      dbms_output.put_line(list(i).name);
    end loop;
end;

smithsmith 2009/06/04 14:14 >>Ctrl+M
Category->Window の Maximize Active View or Editor に何も割り当てないようにするんですね。
参考になりました。ありがとうございます〜

こころこころ 2011/10/24 15:47 参考になりましたです。

shuntakeuch1shuntakeuch1 2018/01/22 19:58 ありがとうございます。

2008-02-26

SELECT INTO で0件だとエラーとなってしまうので

| 19:16

PL/SQLでSELECT INTO 文を使った場合、検索結果が0件だとエラーとなってしまうので、別な書き方を模索してみました。

SELECT INTO の場合

declare
  num  number;
begin
  select id into num
  from .....;
end;

以下の書き方が一番スマートではないかと。

declare 
  num number;
begin
  for a in (select id from .....) loop
    num := a.id;
  end loop;
end;

2008-02-21

ストアドプロシージャでREF CURSORを渡すには

| 13:40

マニュアルを読まずにネット見ながらコーディングしてたら意外に苦戦。忘れないようにメモ。

type curtype is ref cursor;
procedure foo(cu in curtype) is
  row  table_name%rowtype;
begin
  loop
      fetch cu into row;
      exit when cu%notfound;
      -- any process --
  end loop;
end;

declare
   cu curtype;
begin
   open cu for select ....;
   foo(cu);
   close cu;
end;

オープンしてから渡さないとだめみたいでした。

2008-02-20

OracleのカーソルはSELECT文の理想的な形

| 12:31

久しぶりにPL/SQLでカーソルを使ってプログラミングすることがあって、思い出したり調べたりしながらやりました。で、思ったのだけど、PL/SQLのカーソルってこんなに便利だったっけ?

DECLARE
 CURSOR cu IS SELECT...;
BEGIN
 FOR row IN cu LOOP
  row.column_name
 END LOOP
END;

前は、OPENとかクローズとかブレイクとかいろいろ面倒なことやってたような気がするんだけど。んで、調べてみたところ、、、

どうやら、カーソルには明示的カーソル/暗黙的カーソルの2種類があって、面倒だったのは「明示的カーソル」のようです。「暗黙的カーソル」って前からあったのかなあ?

思うに、Webアプリケーション(Java、.NET、PHP)とかでもこれぐらい手軽に検索できてもいいんじゃないかな。