ストアドプロシージャメモ
勉強したしとりあえずメモとして残す
ストアドプロシージャとは
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:
ストアドプロシージャ - オラクル・Oracle PL/SQL 入門