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;
ファンクションが削除されました。関連の記事