Oracle PL/SQLのストアドファンクションのサンプル

ファンクション	文字列のhello worldを出力する
	select文で1つの値を取得する
	select文で複数列の値を取得する
エラー	作成時にコンパイルエラーが発生したとき
確認	ファンクションの存在確認を行う
削除	ファンクションを削除する

環境

実行は、SQL PlusとSQL Developerで確認しています。

文字列のhello worldを出力する

文字列のhello worldを出力するファンクションのサンプルです。
ファンクションは、Returnで値を返します。

CREATE OR REPLACE FUNCTION TEST1
RETURN VARCHAR2
IS
/*
  comment
*/
	STR1 VARCHAR2(20) := 'hello world';
	
BEGIN
	RETURN STR1; --hello worldと出力される
END;

1行目は、TEST1というファンクションを作成します。
CREATE OR REPLACEなので既にある場合は上書きされます。

2行目は、戻り値の型を指定します。VARCHAR2型にしました。
3行目は、ISまたはASを入力します。意味はどちらも同じです。
4から6行目は、複数行のコメントです。/*で始まり*/で終了します。
7行目は、変数です。
10行目は、RETURNで文字列を戻します。--は単一行のコメントです。

SQL Plusで実行する場合、最終行にスラッシュ(/)を入力します。

ファンクションを実行する

上記で作成したファンクションをSQL Plusで実行します。

SQL> select TEST1() from dual;

TEST1()
--------------------------------------------------------------------------------
hello world

select文で実行できます。

ファンクション実行時にエラーが発生した場合

ファンクション実行時にエラーが発生した場合は、ORA-XXXXXの例外を返します。
初期値を返すようにしていても値は戻しません。

select文で1つの値を取得する

select文で1つの値を取得するサンプルです。
入力として数値を渡してSQLを実行し、出力として文字列を受け取ります。

CREATE OR REPLACE FUNCTION TEST1(id1 IN NUMBER) RETURN VARCHAR2
IS
  wk_name VARCHAR2(20);
BEGIN
  SELECT name INTO wk_name FROM employee
  WHERE ID = id1;
  RETURN wk_name;
END;

1行目のidがNUMBER型の引数です。Return VARCHAR2は戻り値の型です。
3行目のwk_nameは、戻り値の名前とデータ型です。
5行目は、selectで取得した値をwk_nameにセットします。
6行目は、id1に引数の値がセットされます。※id1をidに変えるとエラーになります。
7行目は、wk_nameの値を戻します。

ファンクションを実行する

上記で作成したファンクションをSQL Plusで実行します。

SQL> select test1('1') from dual;

TEST1('1')
--------------------------------------------------------------------------------
鈴木

select文で実行できます。

select文で複数列の値を取得する

select文で複数列の値を取得するサンプルです。

CREATE OR REPLACE FUNCTION TEST1(id1 IN NUMBER) RETURN VARCHAR2
IS
	name1    employee.name%TYPE;
	romaji1  employee.romaji%TYPE;
	r1       VARCHAR2(40);

	CURSOR cur1 IS
		SELECT name,romaji FROM employee
		WHERE ID = id1;

	employee_rec cur1%ROWTYPE;
BEGIN
    open cur1;
	fetch cur1 into employee_rec;
    close cur1;
    name1    := employee_rec.name;
	romaji1  := employee_rec.romaji;

	r1 := name1 ||','|| romaji1;

RETURN r1;
END;

8行目は、2つ項目を取得しています。
17,18行目は、取得した値を変数に入れています。
19行目は、取得した値をカンマ区切りで連結しています。
21行目は、リターンで値を戻します。

ファンクションとTYPEで複数行返す場合は以下を参照願います。
Oracle PL/SQL ファンクションとTYPEで複数行返す

ファンクションを実行する

上記で作成したファンクションをSQL Plusで実行します。

SQL>  select test1(1) from dual;

TEST3(1)
--------------------------------------------------------------------------------
鈴木,suzuki

作成時にコンパイルエラーが発生したとき

ファンクション作成時にコンパイルエラーが発生したときは、show errorと入力するとエラー内容が表示されます。

SQL> CREATE OR REPLACE FUNCTION TEST1
  2  RETURN VARCHAR2
  3  IS
  4  /*
  5    comment
  6  */
  7
  8  BEGIN
  9  RETURN STR1; --hello worldと出力される
 10  END;
 11  /

警告: ファンクションが作成されましたが、コンパイル・エラーがあります。

SQL> show error
FUNCTION TEST1のエラーです。

LINE/COL ERROR
-------- -----------------------------------------------------------------
9/1      PL/SQL: Statement ignored
9/8      PLS-00201: 識別子STR1を宣言してください。
SQL>

ファンクションの存在確認を行う

現ユーザがアクセスできるファンクションを確認できます

SELECT * FROM ALL_PROCEDURES
where OBJECT_TYPE = 'FUNCTION' and OWNER = 'SYSTEM'

上記は、systemスキーマのファンクションを確認しています。

現ユーザが所有するファンクションを確認できます

SELECT * FROM USER_PROCEDURES

ファンクションを削除する

ファンクションの削除は、以下のように「DROP FUNCTION ファンクション名」と入力します。

DROP FUNCTION test1;
ファンクションが削除されました。