DB/Oracle/PLSQL/プロシージャとファンクション

Top/DB/Oracle/PLSQL/プロシージャとファンクション

目次†

使い捨ての無名なのじゃなくて、名前を付けて保存しておくプロシージャもしくはファンクションのお話。サブプログラム。

↑

プロシージャとファンクション†

名前を付けて保存しておくサブプログラムのこと。保存場所はローカルだったりサーバだったりする。名前を付けて保存してあるので、名前を指定してほかのアプリケーションからコール（実行）できる。

値を返さないのがプロシージャで、値を返すのがファンクション。プロシージャの中からファンクションを呼び出す、という形が多い。のかなあ。

↑

プロシージャ†

↑

基本の構文†

だいたい無名ブロックと一緒。

  • ストアドプロシージャの構文 
    -- CREATE でデータベースにオブジェクトとして格納する
-- OR REPLACE は既存の場合に上書きする指定
create [or replace]
-- DECLARE の代わりに PROCEDURE を使う
-- 引数として受け取るパラメータも指定可。任意なのでなくてもいい
   -- パラメータは [ パラメータ名 モード データ型 ] の順。モードについては後述
   -- データ型に桁数は不要
procedure hoge (v_arg1 out number, v_arg2 in out varchar2)
is
    -- 宣言部
    ...
begin
    -- 実行部
    ...
exception
    -- 例外処理部
    ...
end;
  • パラメータのモード
    モード説明
    in呼び出し元から受け取るパラメータ。モードを省略するとこれになる
    out呼び出し元に返すパラメータ
    in out呼び出し元から受け取って返すパラメータ
↑

実行方法†

他の無名ブロックやプロシージャから実行する場合は、プロシージャ名を単に記述するだけ。

  • HOGE プロシージャを他のサブプログラムや無名ブロックから呼び出す 
    declare
    v_hoge number;
begin
    -- 引数がいらなければプロシージャ名だけ
    hoge;

    -- 引数（IN パラメータ）が必要なら渡す
    hoge(10);

    -- OUT パラメータの場合はプロシージャからの格納先となる変数を与える
    hoge(v_hoge);
end;
/

SQL*Plus から直接動かす場合は EXECUTE 文を用いる。引数の指定方法は上と一緒。

  • HOGE プロシージャを SQL*Plus から呼び出す 
    -- 単なる呼び出し
execute hoge

-- 引数の指定（たとえばバインド変数）
execute hoge(:v_hoge)
↑

ファンクション†

いわゆる関数。考え方は他言語と同じ。値を代入するためだったり、条件判定に使用したり、フィルタに使用したり、いろいろな目的に使える。

↑

基本の構文†

プロシージャとほとんど一緒。PROCEDURE が FUNCTION になったのと、返り値のための RETURN が増えた点だけが違う。

RETURN で返せるデータは一つだけ。

  • ストアドファンクションの構文 
    create [or replace]
-- FUNCTION を宣言
-- パラメータは任意。IN パラメータのみを推奨
function hoge (v_arg1 number, v_arg2 varchar2)

-- 返り値の型を指定
return number
is
    -- 宣言部
    ...
begin
    -- 実行部
    -- 必ずどこかで RETURN が必要
    return v_hoge;
    ...
exception
    -- 例外処理部
    ...
end;
↑

実行方法†

  • HOGE ファンクションを他のサブプログラムや無名ブロックから呼び出す 
    declare
    v_hoge number;
    v_fuga varchar2(10);
begin
    -- 引数がいらなければプロシージャ名だけ
    v_hoge := hoge;

    -- 引数（IN パラメータ）が必要なら渡す
    v_hoge := hoge(10);

    -- 条件判定に使ったり、SELECT 文に使ったり
    if hoge(v_hoge) > 100 then
        select hoge(120) into v_fuga from fuga;
    end if; 
end;
/

SQL*Plus から直接動かす場合ってあんまりないと思うけど、DUAL 表からひいて使うとかかなあ。

  • HOGE ファンクションを SQL*Plus から呼び出す 
    select hoge from dual;
select hoge(10) from dual;
↑

コンパイルとエラーの詳細†

CREATE PROCEDURE したら『警告: プロシージャが作成されましたが、コンパイル・エラーがあります。』って怒られた。でもどこがエラーなんだか何も教えてくれない…… という腹立たしい事態に遭遇したときはおまじないを唱えると幸せになれる。

日本語はおかしいんだけどまあ…… ないよりまし……？

  • SHOW ERROR 
    -- サーバに格納しようとしたらエラーが出るけど詳細不明
SQL> @hoge.sql

警告: プロシージャが作成されましたが、コンパイル・エラーがあります。

-- いらいらしながらおまじないを唱える
SQL> show error
PROCEDURE HOGEのエラーです。

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/5      PLS-00103: 記号"V_HOGE"が見つかりました。
         次のうちの1つが入るとき:
         ( ;
         is with authid as cluster compress order using compiled
         wrapped external deterministic parallel_enable pipelined
         result_cache
         記号"is" は続行のために"V_HOGE"に代わりました。

5/1      PLS-00103: 記号"END"が見つかりました。 次のうちの1つが入るとき:
         := . (
         % ;

LINE/COL ERROR
-------- -----------------------------------------------------------------
         記号";" は続行のために"END"に代わりました。
Last-modified: 2011-11-09 (水) 14:21:42