寝て起きて寝て

プログラミングが出来ない情報系のブログ

ストアドプロシージャメモ

勉強したしとりあえずメモとして残す

ストアドプロシージャとは

Oracleデータベースに格納されているプロシージャやファンクションのこと。 DB内で動くプログラムなので、クライアントとサーバーを何度も行きかうような処理に対してはとても有効。

サーバーのシステム日付を取得するような手続きや繰り返しのない処理には高速化は期待はできない。

因みにコンパイルエラーが出ると 「警告: ファンクションが作成されましたが、コンパイル・エラーがあります。」 とかいう糞みたいなエラーが出るので、SQLPlusで

SQL>SHOW ERR

と叩いてあげるとエラー内容を教えてくれる。

ストアドプロシージャーの種類

主に二種類ある

1.FUNCTION 2.PROCEDURE

二つの違いは「値を返す」か「値を返さない」かが大きな違いとなる。

1.FUNCTION

まず値を返すFUNCTION FUNCTIONの場合RETURNを必ずつけないといけない 因みにRETRUNで返せる値は一つのみ

例:

CREATE OR REPLACE FUNCTION function_test
(
  i_num IN NUMBER
)
RETURN NUMBER
IS
  p NUMBER := 3;
  o_sum NUMBER := 0;
BEGIN
  o_sum := i_num + p;
  RETURN o_sum;
END;
/

コンパイルからの実行(SQLPLUSからの実行)

SQL> CREATE OR REPLACE FUNCTION function_test
  2  (
  3    i_num IN NUMBER
  4  )
  5  RETURN NUMBER
  6  IS
  7    p NUMBER := 3;
  8    o_sum NUMBER := 0;
  9  BEGIN
 10    o_sum := i_num + p;
 11    RETURN o_sum;
 12  END;
 13  /
 
 ファンクションが作成されました。
 
 
 SQL> SELECT function_test(3) FROM DUAL;

FUNCTION_TEST(3)
----------------
               6

慣れ親しんだいつもの関数

2.PROCEDURE

次に値を返さないPROCEDURE ただし、パラメーターは返せるので注意

例:

 CREATE OR REPLACE PROCEDURE procedure_test
(
  i_num IN NUMBER,
  o_sum OUT NUMBER
)
IS
  p NUMBER := 3;
BEGIN
  o_sum := i_num + p;
END;
/

コンパイルからの実行(SQLPLUSからの実行)

 SQL> CREATE OR REPLACE PROCEDURE procedure_test
  2  (
  3    i_num IN NUMBER,
  4    o_sum OUT NUMBER
  5  )
  6  IS
  7    p NUMBER := 3;
  8  BEGIN
  9    o_sum := i_num + p;
 10  END;
 11  /

プロシージャが作成されました。
 
 
 SQL> VARIABLE o_sum NUMBER;
 SQL> EXECUTE procedure_test(3,:o_sum);
 PL/SQLプロシージャが正常に完了しました。

 SQL> SELECT :o_sum FROM DUAL;

    :O_SUM
----------
         6
 

PROCEDUREだと SQLPLUSでの実行が面倒くさい

実行してから返ってくるパラメータ用のバインド変数を宣言しなくてはならない

二つの使い分けとしては

ファンクションは新しい式、演算子としての目的で使用

プロシージャは新しい文としての目的で使用

するべきなんじゃないかなぁと。(RETURNで返せる値一つだと使いにくいしね)

配列で返せたりしませんかね・・・

参考URL:

procedure と function

ストアドプロシージャ - オラクル・Oracle PL/SQL 入門

ORACLE/オラクルPL/SQLリファレンス(ストアドプロシージャ)

ORACLE/オラクルSQLリファレンス(SQLPLUS)