Oracle FUNCTIONの作成
- ファンクションの作成構文
- 簡単なファンクションの作成と実行１(NUMBER型)
- 簡単なファンクションの作成と実行２(DATE型)
- 簡単なファンクションの作成と実行３(VARCHAR2型)
- ファンクションのソースコードの表示
- テーブルデータを取得するファンクションの作成と実行
- ファンクションの内部ではDMLが実行できない例
おすすめ書籍
- 図解入門よくわかる最新Oracleデータベースの基本と仕組み[第4版] (How‐nual Visual Guide Book)
- Oracleの基本 ~データベース入門から設計/運用の初歩まで
- [改訂第4版]SQLポケットリファレンス
ファンクションの作成構文
CREATE [OR REPLACE] [<スキーマ名>.]<ファンクション名> FUNCTION
(
<引数1> < IN | OUT | IN OUT > <データ型> ,
<引数2> < IN | OUT | IN OUT > <データ型> ,
...
<引数N> < IN | OUT | IN OUT > <データ型>
)
RETURN <データ型>
IS
{変数宣言セクション}
BEGIN
{実行部セクション}
[
EXCEPTION
{例外処理部セクション}
]
END;
スキーマ名 はいわゆるユーザ名のことで省略すれば現在ログインしているスキーマに対して表は作成されます。
但し、前提条件としてログインスキーマに対してファンクションの作成権限が与えられていないとこのCREATE文は失敗します。
尚、[OR REPLACE] はファンクションを上書き登録する場合に指定します。
引数 は複数持つことが出来ますが省略することも可能です。
< IN | OUT | INT OUT > は以下の様になります。
- IN ：読取り専用パラメータです。パラメータの値を変更してコール側に戻せません。
- OUT：書込み専用パラメータです。パラメータの値を設定してコール側に戻します。
- IN OUT：読取り/書込みパラメータです。パラメータの値を変更してコール側に戻すことができます。
（尚、書込みパラメータの指定は、通常でしたら使用することは無いと思います。）
引数 の次の RETURN はこのファンクションが返す値のデータ型を宣言します。このデータ型はデータ長を宣言することができないので注意が必要です。
{変数宣言セクション} は、このファンクション内で使用する変数のデータ型、カーソル等を宣言します。
{実行部セクション} は、このファンクションが起動された時に実行されるコードを記述します。 このセクションの最後でファンクションの戻り値を返すRETURN文を記述します。
{例外処理部セクション} は、このファンクション内で発生した例外に対応する処理コードを記述します。 このセクションの記述は無くても構文としては成り立ちますが、やはり例外の処理は行うべきだと思います。
例外処理の結果としてどんな値を返すのかは、最初に決めておく必要があります。
簡単なファンクションの作成と実行１(NUMBER型)
SQL> CREATE OR REPLACE FUNCTION TEST_FNC1 (
2 InNumber IN NUMBER
3 )
4 RETURN NUMBER
5 IS
6 BEGIN
7 RETURN InNumber * 2;
8 END;
9 /
ファンクションが作成されました。
SQL> SELECT TEST_FNC1(100) FROM DUAL;
TEST_FNC1(100)
--------------
200
この TEST_FNC1 は NUMBER 型の引数を2倍にして返す簡単なファンクションです。
このファンクションを実行する場合は、システム関数の使い方と同様に行えますので、
簡単な方法としてはシステムのダミーテーブルの DUAL を利用し SELECT 文を実行します。
結果として引数の2倍の値が返ることが確認できます。
他の方法としては、PL/SQL のコマンドを以下の様に入力し、システムに定義済みの表示用プロシージャをコールすることで
テストファンクションの実行結果を表示できます。
PL/SQL の DBMS_OUTPUT パッケージは標準出力にメッセージを表示するためにシステムに用意されているパッケージです。
そのパッケージの中の PUT_LINE プロシージャで結果をCHAR型に変換し出力しています。
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 N NUMBER;
3 BEGIN
4 N := TEST_FNC1(100);
5 DBMS_OUTPUT.PUT_LINE('RESULT=' || TO_CHAR(N));
6 END;
7 /
RESULT=200
PL/SQLプロシージャが正常に完了しました。
簡単なファンクションの作成と実行２(DATE型)
SQL> CREATE OR REPLACE FUNCTION TEST_FNC2 ( 2 InDate IN DATE 3 ) 4 RETURN DATE 5 IS 6 BEGIN 7 RETURN InDate + 1; 8 END; 9 / ファンクションが作成されました。 SQL> SELECT TEST_FNC2(SYSDATE) , SYSDATE FROM DUAL; TEST_FNC SYSDATE -------- -------- 18-12-11 18-12-10
この TEST_FNC2 は DATE 型の引数に1日加算して返す簡単なファンクションです。
DATE 型は整数部で日数を持っているため、加算したい日数をそのまま整数加算してやれば結果が求められます。
簡単なファンクションの作成と実行３(VARCHAR2型)
SQL> CREATE OR REPLACE FUNCTION TEST_FNC3 (
2 InVarchar IN VARCHAR2
3 )
4 RETURN VARCHAR2
5 IS
6 BEGIN
7 RETURN '/' || InVarchar || '/';
8 END;
9 /
ファンクションが作成されました。
SQL> SELECT TEST_FNC3('TEST') FROM DUAL;
TEST_FNC3('TEST')
--------------------------------
/TEST/
この TEST_FNC3 は VARCHAR2 型の引数に前後に文字列を付加して返す簡単なファンクションです。
ファンクションのソースコードの表示
作成されたファンクションのソースコードを表示するには、 USER_SOURCE データディクショナリビューを SELECT することで表示できます。 USER_SOURCE のビュー属性は以下の DESC 実行で分かる様に、ファンクション名として NAME があり、 ソースコードの1行ずつを行番号の LINE と共に TEXT に格納されています。
SQL> DESC USER_SOURCE
名前 NULL? 型
----------------------------------------- -------- ----------------------------
NAME VARCHAR2(30)
TYPE VARCHAR2(12)
LINE NUMBER
TEXT VARCHAR2(4000)
SQL> SELECT TEXT FROM USER_SOURCE
2 WHERE NAME = 'TEST_FNC1'
3 ORDER BY TYPE, LINE;
TEXT
--------------------------------------------------------------------------------
FUNCTION TEST_FNC1 (
InNumber in NUMBER
)
RETURN number
IS
BEGIN
RETURN InNumber * 2;
END;
8行が選択されました。
ここまで簡単な例を記してきましたが、今後はより複雑なファンクションを載せていきたいと思います。
テーブルデータを取得するファンクションの作成と実行
テーブルからのデータを、与えられた引数を元に取得するファンクションの作成例を示したいと思います。
テスト用テーブルとして以下のもの作成します。
CREATE TABLE TEST (ID NUMBER, NAME VARCHAR2(32)); INSERT INTO TEST VALUES (1, 'aaa*****'); INSERT INTO TEST VALUES (2, 'bbb@@@@@'); INSERT INTO TEST VALUES (3, 'ccc+++++'); INSERT INTO TEST VALUES (4, 'ddd-----'); COMMIT;
このテーブルの ID を引数として与え、 NAME の値を返すファンクションを作成します。
以下にそのコードを示します。変数宣言部にテーブルからの取得値を設定する VARCHAR2 型の変数を宣言します。
BEGIN から始まる実行部に値を取得する SELECT...INTO 文を宣言し、 RETURN でワーク変数値を返します。
この SELECT ではエラーが発生する場合がありますので、EXCEPTION の例外処理部でデータが無かった場合と
、その他のエラーの場合の処理を記述します。
SQL> CREATE OR REPLACE FUNCTION TEST_FNC4 ( 2 INID IN NUMBER 3 ) 4 RETURN VARCHAR2 5 IS 6 /* ワーク変数の宣言 */ 7 WK VARCHAR2(32); 8 BEGIN 9 /* TESTテーブルからの取得 */ 10 SELECT NAME INTO WK FROM TEST WHERE ID = INID; 11 RETURN WK; 12 EXCEPTION 13 /* エラーの場合 */ 14 WHEN NO_DATA_FOUND THEN 15 RETURN 'NOT FOUND'; 16 WHEN OTHERS THEN 17 RETURN 'OTHER ERR'; 18 END; 19 / ファンクションが作成されました。
このファンクションを実行すると以下の様になります。
引数の ID にテーブルに無いものを渡すと、NOT FOUND が返り、例外処理が働いていることが分かります。
その他のエラーの場合は現象を起こせなかったのですが、取り敢えず保険として宣言してあります。
SQL> SELECT TEST_FNC4(1) FROM DUAL; TEST_FNC4(1) -------------------------------------------------------------------------------- aaa***** SQL> SELECT TEST_FNC4(4) FROM DUAL; TEST_FNC4(4) -------------------------------------------------------------------------------- ddd----- SQL> SELECT TEST_FNC4(5) FROM DUAL; TEST_FNC4(5) -------------------------------------------------------------------------------- NOT FOUND
ファンクションの内部ではDMLが実行できない例
ファンクションの内部では DML いわゆるテーブルに対して INSERT UPDATE DELETE の処理ができません。
以下にそのコードの例を示します。
BEGIN から始まる実行部に INSERT 処理を記述してあります。また、
この INSERT ではエラーが発生しますので、EXCEPTION の例外処理部でその他のエラーの場合の処理を記述します。
ここでPL/SQLのエラーコードを返す SQLCODE と、エラーメッセージを返す SQLERRM の内容を出力します。
以下にファンクションの生成の実行と、呼び出し時のエラー表示を以下に示します。
SQL> CREATE OR REPLACE FUNCTION TEST_FNC5 (
2 INID IN NUMBER
3 ,INNAME IN VARCHAR2
4 )
5 RETURN NUMBER
6 IS
7 BEGIN
8 /* TESTテーブルからの取得 */
9 INSERT INTO TEST VALUES(INID, INNAME);
10 COMMIT;
11 RETURN 0;
12 EXCEPTION
13 /* エラーの場合 */
14 WHEN NO_DATA_FOUND THEN
15 RETURN -2;
16 WHEN OTHERS THEN
17 /* エラー番号とメッセージを出力 */
18 DBMS_OUTPUT.PUT_LINE('SQLCODE: ' || SQLCODE) ;
19 DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM) ;
20 RETURN -1;
21 END;
22 /
ファンクションが作成されました。
SQL> SET SERVEROUTPUT ON
SQL> SELECT TEST_FNC5(6, '1111') FROM DUAL;
TEST_FNC5(6,'1111')
-------------------
-1
SQLCODE: -14551
SQLERRM: ORA-14551: 問合せの中でDML操作を実行することはできません。
SQL>
