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

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

確認環境
・Oracle Express Edition 11g Release 2
・Windows 10

目次

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

前提

Oracle XE11をインストールし、SQLコマンドラインでPL/SQLを実行します。
Oracle11gのインストールとHRテーブルを確認

文字列を出力する

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

CREATE OR REPLACE FUNCTION TEST1
RETURN VARCHAR2
IS
/*
  サンプルテスト
*/
	STR1 VARCHAR2(20) := 'テストです';
	
BEGIN
	RETURN STR1; --テストですと出力される
END;

1行目は、TEST1というファンクションを作成します。CREATE OR REPLACEなので修正も可能です。
2行目は、戻り値の型を指定します。VARCHAR2型にしました。
3行目は、ISまたはASを入力します。意味はどちらも同じです。
4から6行目は、複数行のコメントです。/*で始まり*/で終了します。
7行目は、変数です。
10行目は、RETURNで文字列を戻します。--は単一行のコメントです。

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

上記で作成したファンクションをSQLコマンドラインで実行するサンプルです。

SQL> select TEST1() from dual;

TEST1()
--------------------------------------------------------------------------------
テストです

select文で実行できます。

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

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

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

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

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

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

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

上記で作成したファンクションをSQLコマンドラインで実行するサンプルです。

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

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

select文で実行できます。

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

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

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

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

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

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

RETURN r1;
END;

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

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

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

上記で作成したファンクションをSQLコマンドラインで実行するサンプルです。

SQL>  select test3(1) from dual;

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

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

SQLコマンドラインで作成時にコンパイルエラーが発生したときは、show errorと入力するとエラー内容を表示します。

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

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

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0      PL/SQL: Compilation unit analysis terminated
3/8      PLS-00201: 識別子Aを宣言してください。
SQL>

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

ALL_OBJECTSテーブルで確認できます。

SELECT * FROM ALL_OBJECTS 
WHERE OBJECT_TYPE = 'FUNCTION' AND OWNER = 'HR';

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

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

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

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

関連の記事

Oracle11gのインストールとHRテーブルを確認
Oracle PL/SQLのストアドプロシージャのサンプル

△上に戻る