【Oracle】ストアドファンクション入門

# はじめに

[【Oracle】PL/SQL入門](https://qiita.com/nkojima/items/93a9c01741965f11bb8c)、[【Oracle】ストアドプロシージャ入門](https://qiita.com/nkojima/items/2f793251e4465a3a8da2)の記事に続いて、ストアドファンクションについて復習した結果を記事としてまとめてみました。

ちなみに[こちらの資料](https://www.oracle.com/technetwork/jp/ondemand/database/db-basic/b-11-plsql-1448399-ja.pdf)によると、ストアドファンクションとは「PL/SQLで記述した、なんらかの値を返却する処理手続きに名前をつけ、Oracle Databaseに格納したもの」と定義されています。

# 使用した環境

* Oracle社が提供している[Oracle Live SQL](https://livesql.oracle.com/)でOracle19cを利用しました。

# プロシージャとファンクションの使い分け

## プロシージャ

* バッチ処理などの一連の処理手続きを実装する
* プロシージャ内でINSERT/UPDATE/DELETEを行う
* エラーを例外にて捕捉できるだけでなく、必要に応じてROLLBACKすることが出来る。

## ファンクション

* パラメータで渡された値の判定、変換処理、値の抽出を行う。
    * ファンクション内ではINSERT/UPDATE/DELETEを行わないのが一般的。
* エラーを例外にて捕捉できるが、例外処理は行わずにエラーが発生したことを示す値を返すことが多い。
* パラメータ・モードが`OUT`の引数を設定した場合、そのストアドファンクションをSQL中で呼び出すとエラー（ORA-06572）が発生する。
 * [こちらのページ](https://www.oraexcel.com/database-oracle-10gR1-ORA-06572/lang-jp)を見ると、ORA-06572の対応策として「引数リストでOUTパラメータを使用せずに、PL/SQLファンクションを再作成してください。」と書かれています。
 * [別のページ](https://docs.oracle.com/cd/E15817_01/appdev.111/e05670/parameter_declaration.htm)には、「ファンクションでは、OUTおよびIN OUTを使用しないでください。」と書かれているので、`OUT`だけでなく`IN OUT`も使えないようです。
* SQL文中で使える。（※一部例外あり）


# 作成したストアドファンクション

## 最も基本的な構文

* 以下のコードは、引数のないストアドファンクションとなります。
* 基本的な構文はストアドプロシージャと同じです。
* ファンクション名の後ろに、戻り値のデータ型を指定します。
 * 一般的な言語と同様に、戻り値は`RETURN 戻り値の変数名;`で指定します。


```sql:最も基本的な構文
-- 日付を返すストアドファンクション
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:実行用SQL
SELECT
  get_date()
FROM
  dual;
```

```text:実行結果
2020/04/13
```

## 引数のあるストアドファンクション

* 以下は引数が2つ（※パラメータ・モードはいずれもIN）のストアドファンクションです。
* 三角形の面積を求めた結果を、戻り値として返却しています。

```sql:引数のあるストアドファンクション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:実行用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;
```

```text:実行結果
底辺が7、高さが5の三角形の面積は17.5です。
```

## 例外処理を含んだストアドファンクション

* 以下は、社員名をキーにして社員番号を取得するストアドファンクションです。
* SCOTT.EMPテーブルに社員名が存在しない時は、エラーを示す値（-1）を返します。
 * レコードが1件も無い時はNO_DATA_FOUNDという例外が発生するため、それを拾ってエラー処理をしています。

```sql:例外処理を含んだストアドファンクション
-- 社員番号を求めるストアドファンクション。
-- 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:実行用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;
```

```text:実行結果
指定した社員名のレコードが存在しません。
```


# 参考URL

* [SHIFT the Oracle - PL/SQL プログラミング入門](https://www.shift-the-oracle.com/plsql/)
* [ここからはじめよう Oracle PL/SQL入門](https://www.oracle.com/technetwork/jp/ondemand/database/db-basic/b-11-plsql-1448399-ja.pdf)