はじめに
【Oracle】PL/SQL入門、【Oracle】ストアドプロシージャ入門の記事に続いて、ストアドファンクションについて復習した結果を記事としてまとめてみました。
ちなみにこちらの資料によると、ストアドファンクションとは「PL/SQLで記述した、なんらかの値を返却する処理手続きに名前をつけ、Oracle Databaseに格納したもの」と定義されています。
使用した環境
- Oracle社が提供しているOracle Live SQLでOracle19cを利用しました。
プロシージャとファンクションの使い分け
プロシージャ
- バッチ処理などの一連の処理手続きを実装する
- プロシージャ内でINSERT/UPDATE/DELETEを行う
- エラーを例外にて捕捉できるだけでなく、必要に応じてROLLBACKすることが出来る。
ファンクション
- パラメータで渡された値の判定、変換処理、値の抽出を行う。
- ファンクション内ではINSERT/UPDATE/DELETEを行わないのが一般的。
- エラーを例外にて捕捉できるが、例外処理は行わずにエラーが発生したことを示す値を返すことが多い。
- パラメータ・モードが
OUTの引数を設定した場合、そのストアドファンクションをSQL中で呼び出すとエラー（ORA-06572）が発生する。 - こちらのページを見ると、ORA-06572の対応策として「引数リストでOUTパラメータを使用せずに、PL/SQLファンクションを再作成してください。」と書かれています。
-
別のページには、「ファンクションでは、OUTおよびIN OUTを使用しないでください。」と書かれているので、
OUTだけでなくIN OUTも使えないようです。 - SQL文中で使える。（※一部例外あり）
作成したストアドファンクション
最も基本的な構文
- 以下のコードは、引数のないストアドファンクションとなります。
- 基本的な構文はストアドプロシージャと同じです。
- ファンクション名の後ろに、戻り値のデータ型を指定します。
- 一般的な言語と同様に、戻り値は
RETURN 戻り値の変数名;で指定します。
最も基本的な構文
-- 日付を返すストアドファンクション
CREATE OR REPLACE FUNCTION get_date RETURN VARCHAR2
-- 宣言部
IS
date_time VARCHAR2(50);
-- 処理部
BEGIN
SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD')
INTO date_time
FROM DUAL;
RETURN date_time;
END;
実行用SQL
SELECT
get_date()
FROM
dual;
実行結果
2020/04/13
引数のあるストアドファンクション
- 以下は引数が2つ（※パラメータ・モードはいずれもIN）のストアドファンクションです。
- 三角形の面積を求めた結果を、戻り値として返却しています。
引数のあるストアドファンクション2
-- 三角形の面積を求めるストアドファンクション。
-- base: 底辺
-- height: 高さ
-- RETURN: 三角形の面積
CREATE OR REPLACE FUNCTION calc_area(base IN NUMBER, height IN NUMBER) RETURN NUMBER
-- 宣言部
IS
area NUMBER;
-- 処理部
BEGIN
SELECT (base * height / 2)
INTO area
FROM DUAL;
RETURN area;
END;
実行用SQL
-- 宣言部
DECLARE
base NUMBER;
height NUMBER;
area NUMBER;
BEGIN
base := 7;
height := 5;
SELECT
calc_area(base, height)
INTO
area
FROM
dual;
DBMS_OUTPUT.PUT_LINE('底辺が' || base || '、高さが' || height || 'の三角形の面積は' || area || 'です。');
END;
実行結果
底辺が7、高さが5の三角形の面積は17.5です。
例外処理を含んだストアドファンクション
- 以下は、社員名をキーにして社員番号を取得するストアドファンクションです。
- SCOTT.EMPテーブルに社員名が存在しない時は、エラーを示す値（-1）を返します。
- レコードが1件も無い時はNO_DATA_FOUNDという例外が発生するため、それを拾ってエラー処理をしています。
例外処理を含んだストアドファンクション
-- 社員番号を求めるストアドファンクション。
-- name: 社員名
-- RETURN: 社員番号。該当する社員名が無い時は-1を返す。
CREATE OR REPLACE FUNCTION get_emp_no(name IN VARCHAR2) RETURN NUMBER
-- 宣言部
IS
emp_no NUMBER;
-- 処理部
BEGIN
SELECT
EMPNO
INTO
emp_no
FROM
SCOTT.EMP
WHERE
ENAME = name;
RETURN emp_no;
-- 例外処理部
EXCEPTION
WHEN NO_DATA_FOUND THEN
emp_no:= -1;
RETURN emp_no;
END;
実行用SQL
DECLARE
emp_no NUMBER;
BEGIN
SELECT
get_emp_no('CLARK2')
INTO
emp_no
FROM
dual;
IF (emp_no > 0) THEN
DBMS_OUTPUT.PUT_LINE('社員番号は' || emp_no || 'です。');
ELSE
DBMS_OUTPUT.PUT_LINE('指定した社員名のレコードが存在しません。');
END IF;
END;
実行結果
指定した社員名のレコードが存在しません。