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