Oracleのプロシージャ・ファンクション(PROCEDURE・FUNCTION)を作成する【PL/SQL】

2018.03.09

Oracleのストアドには２種類あります。

  1. プロシージャ(PROCEDURE)・・・実行のみ
  2. ファンクション(FUNCTION)・・・戻り値あり

戻り値があるのがファンクションで、戻り値がないのがプロシージャです。

プロシージャを作成するCREATE PROCEDURE（PL/SQL）

プロシージャを作成するには「CREATE PROCEDURE~」を使用します。

-- プロシージャを作成する 
CREATE OR REPLACE PROCEDURE {プロシージャ名}(パラメータ IN/OUT 型)
IS
  ｛変数宣言｝
BEGIN
  ｛処理｝
END;

「OR REPLACE」はUPDATEのことで、作成済みのプロシージャがあった場合に使用します。最初からつけておいた方が何かと便利です。

パラメータ

  • IN・・・値渡し
  • OUT・・・参照渡し

型

  • VARCHAR2・・・文字列型
  • NUMBER・・・数値型
  • DATE・・・日付型
  • テーブル%ROWTYPE・・・指定テーブルと同じ型
  • テーブル.列名%TYPE・・・指定テーブルの列と同じ型

 

例１. プロシージャを作成する

-- プロシージャを作成する 
CREATE OR REPLACE PROCEDURE proA
IS
  nCnt NUMBER;
  rEmp emp%ROWTYPE;
  tEmpNo emp.empno%TYPE;
BEGIN
  nCnt := 1;
  rEmp.empno := 'A0101'; 
  tEmpNo := 'A0101';
END;

ここではシンプルなプロシージャを作成しました。

変数「rEmp」は「emp」テーブルと同じ型となります。「emp」テーブルの列がすべて使えます。ここでは「rEmp.empno」とし、「emp」テーブルの「empno」を使っています。

変数「tEmpNo」は「emp」テーブルの「empno」と同じ型となります。

 

例２. プロシージャを作成する（変数を渡してSQLを実行する）

-- プロシージャを作成する 
CREATE OR REPLACE PROCEDURE proA(nEno IN NUMBER)
IS
  nCnt NUMBER; 
BEGIN
  UPDATE emp
  SET salary = salary * 2
  WHERE empno = nEno;
END;

ここではパラメータ（変数）「nEno」に値を渡して、それをPL/SQLプロシージャが受け取って、SQLのUPDATEを実行するプロシージャを作成しました。

 

ファンクションを作成するCREATE FUNCTION（PL/SQL）

ファンクションを作成するには「CREATE FUNCTION~」を使用します。

-- ファンクションを作成する 
CREATE OR REPLACE FUNCTION {ファンクション名}(パラメータ IN/OUT 型) RETURN 型
IS
  ｛変数宣言｝
BEGIN
  ｛処理｝
END;

ファンクションの特徴は戻り値があることです。戻り値として「RETURN　型」を記述します。

例１. ファンクションを作成する

-- ファンクションを作成する 
CREATE OR REPLACE FUNCTION funA RETURN NUMBER 
IS
  nCnt NUMBER; 
BEGIN
  nCnt := 1;
  RETURN nCnt;
END;

ここではNUMBER型を返す「funA」ファンクションを作成しました。「1」を返すシンプルなファンクションです。

例２. ファンクションを作成する（変数を渡してSQLを実行する）

-- ファンクションを作成する 
CREATE OR REPLACE FUNCTION funA(nEno IN NUMBER) RETURN NUMBER 
IS
  nSal NUMBER; 
BEGIN
  SELECT salary INTO nSal FROM emp
  WHERE empno = nEno;
  RETURN nSal;
END;

ここではPL/SQLファンクション「funA」がパラメータ（変数）「nEno」に値をうけとります。「funA」でSELECTを実施し「salary」を取得し、その「salary」を返すファンクションを作成しました。

以上、Oracleのプロシージャ、ファンクション(PROCEDURE・FUNCTION)を作成するSQLでした。

コメント