15.プロシージャ(PROCEDURE)と関数(FUNCTION)

■プロシージャ(Procedure)とは？
　→特定の作業を実行する名前のあるPL/SQL BLOCKだ。
　→パラメーターを受けられ、繰り返し使用できるBLOCKだ。
　→通常、連続実行または複雑なトランザクションを実行するPL/SQL BLOCKをデータベースに
　　保存するために作る。

■プロシージャの文法

CREATE OR REPLACE procedure name
    IN argument
    OUT argument
    IN OUT argument

IS

    [変数の宣言]

BEGIN  --->必須

    [PL/SQL Block]
    --SQL文、PL/SQL制御文

    [EXCEPTION] --->選択
    --errorが発生する時実行する文章

END;  --->必須

　・CREATE OR REPLACEを使用して作る
　・ISでPL/SQLのブロックを始める
　・LOCAL変数はISとBEGINの間に宣言する

■プロシージャ作成例

--プロシージャの名前はupdate sal
--update salプロシージャは社員番号を受けて給与をアップさせる
--プロシージャの終わりは「/」を表記する
SQL> CREATE OR REPLACE PROCEDURE update_sal
  2  /* IN Parameter */
  3  (v_empno IN NUMBER)
  4
  5  IS
  6
  7  BEGIN
  8
  9   UPDATE emp
 10  set sal = sal * 1.1
 11  WHERE empno = v_empno;
 12
 13  COMMIT;
 14
 15  END update_sal;
 16  /

プロシージャが作成されました。

SQL>

■プロシージャ実行例 　EXECUTEを使用してプロシージャを実行する

SQL> EXECUTE update_sal(7369);

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

SQL>
--社員番号7369の社員の給与が10%アップされたか確認してみよう

■関数（Function）とは？
　→通常、計算後の値を返す為に関数を使用するケースが多い
　→構成はほとんどプロシージャと似ているが、INパラメータのみ使用できる
　→返すデータタイプをRETURN文に宣言しなければいけない
　→PL/SQLブロック内でRETURN文を通じて値を返す

■関数文法

--PL/SQLブロックには少なくとも１つのRETURN文が必要だ
--PL/SQL Blockは関数が実行する内容を定義した本体部分た

CREATE OR REPLACE FUNCTION function name
 [(argument...)]

 RETURN datatype
  --datatypeは返す値のdatatypeだ

IS

 [PL/SQL Block]

 --RETURN文必須
 RETURN 変数;

END;

■関数作成例

SQL> CREATE OR REPLACE FUNCTION FC_update_sal
  2  (v_empno IN NUMBER)
  3
  4  --RETURNされる変数のデータタイプ定義は必須
  5  RETURN NUMBER
  6
  7  IS
  8
  9  --%type変数を使用
 10  v_sal emp.sal%type;
 11
 12  BEGIN
 13
 14  UPDATE emp
 15  SET sal = sal * 1.1
 16  WHERE empno = v_empno;
 17
 18  COMMIT;
 19
 20  SELECT sal
 21  INTO v_sal
 22  FROM emp
 23  WHERE empno = v_empno;
 24
 25  --RETURNは必須
 26  RETURN v_sal;
 27
 28  END;
 29  /

ファンクションが作成されました。

SQL>

■関数実行例

--まず関数が返す値を保存する変数を宣言
SQL> VAR salary NUMBER;

--EXECUTEを使用して関数を実行する
SQL> EXECUTE :salary := FC_update_sal(7900);

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

--PRINT文で出力
SQL> PRINT salary;

    SALARY
----------
    1149.5

SQL>