サイトアイコン PL／SQL日記

【PL/SQL】ファンクションの書き方（作成/実行/呼び出し/削除）

oreno-admin


ここではORACLE社が提供するPL/SQL言語でファンクションの書き方（作成/実行/呼び出し/削除）を紹介しています。

PL/SQLで実行できる処理は↓でまとめて紹介していますので参考にしてください。
＞＞PL/SQLの処理

ファンクションとは

PL/SQLの処理は、スアドプロシージャ、ストアドファンクションとしてデータベース上に作成することが出きます。

ストアドファンクションはいわゆる部品に該当し、ストアドプロシージャやSQLから呼び出されます。

プロシージャとファンクションの違い

ストアドファンクションが部品の位置付けである一方、ストアドプロシージャはバッチ処理に位置付けられます。

バッチ処理とはデータの登録や更新をまとめて行うプログラムのことです。

また、ストアドファンクションはストアドプロシージャと違い大きく以下の2点が違います。
　・必ず返却値が必要なこと
　・SQLでも利用可能なこと

CREATE文：ファンクションの作成・登録方法

ファンクションはCREATE FUNCTION文で作成・登録することが出きます。

CREATE FUNCTIONで名前、引数、戻り値を記述します。その後、宣言部、処理部、例外処理部と順にPL/SQLの処理を書いていきます。

宣言部、例外処理部については省略可能です。処理部は必須です。

RETURN文で戻り値を指定します。

PL/SQLの基本的な書き方については↓で紹介していますので参考にしてください。
＞＞PL/SQLの書き方

CREATE [OR REPLACE] FUNCTION 名前 [(引数名 {IN | OUT | IN OUT } データ型,..)]
RETURN 戻り値のデータ型
IS
  /* 宣言部 */
BEGIN
  /* 処理部 */
  RETURN 戻り値;
EXCEPTION
  /* 例外処理部 */
END;
/

・OR REPLACE
　同じ名前のファンクションが存在する場合、上書き更新します。

・IN
　ファンクションの引数として値を受け取るだけの変数の宣言します。

・OUT
　ファンクションの結果として値を格納する変数の宣言します。

・IN OUT
　IN,OUT、両方の特性を持ちます。
　ファンクションの引数としても使え、ファンクションの結果で上書きし返すことが出来ます。

・宣言部
　ファンクション内で使用する変数を宣言します。

・処理部
　ファンクション内の処理をコーディングします。

・EXCEPTION
　ファンクション内で発生したエラーに対する処理を記述します。

ファンクションのサンプルプログラム

INを指定したサンプルプログラム

IN引数を指定したプログラムの例です。

「FUNCTION func1(p1 IN NUMBER)」として引数を宣言しています。

「RETURN NUMBER」で戻り値のデータ型をNUMBER型に指定しています。

結果は「p1 * 5;」でp1に5を掛けた値を返しています。

CREATE OR REPLACE FUNCTION func1(p1 IN NUMBER)
RETURN NUMBER IS
BEGIN
    RETURN p1 * 5;
END;
/

OUT引数を指定したサンプルプログラム

OUT引数を指定したプログラムの例です。

「FUNCTION func1(p1 IN NUMBER,p2 OUT NUMBER)」として、IN引数にp1を、OUT引数にp2を宣言しています。

「RETURN NUMBER」で戻り値のデータ型をNUMBER型に指定しています。

「p2 := p1 * p1;」として、OUT引数p2に値を代入しています。

「RETURN p2;」で戻り値にp2の値を返しています。

CREATE OR REPLACE FUNCTION func1(p1 IN NUMBER,p2 OUT NUMBER)
RETURN NUMBER IS
BEGIN
    p2 := p1 * p1;
    RETURN p2;
END;
/

IN OUT引数を指定したサンプルプログラム

IN OUT引数を指定したプログラムの例です。

「func3(p1 IN NUMBER,p2 IN OUT NUMBER)」としてIN引数とIN OUT引数を宣言しています。

「p2 := p1 * p2;」でIN OUT引数のp2にp1とp2を掛けた値を代入しています。

「RETURN p2;」で戻り値にp2の値を返しています。

CREATE OR REPLACE FUNCTION func3(p1 IN NUMBER,p2 IN OUT NUMBER)
RETURN NUMBER IS
BEGIN
    p2 := p1 * p2;
    RETURN p2;
END;
/

引数なしのサンプルプログラム

引数なしのプログラム例です。

「[(引数名 {IN | OUT | INOUT } データ型,..)]」の部分を書かなければ、引数なしのファンクションになります。

CREATE OR REPLACE FUNCTION func4
RETURN NUMBER IS
BEGIN
    RETURN 'test';
END;
/

ファンクションの実行・呼び出し

ファンクションはEXECUTE文で実行・呼び出すことが出来ます。

「EXECUTE ファンクション名;」でSQLPLUSから実行できます。

INを指定した実行・呼び出し例

「variable 変数名 データ型;」で変数を宣言して、IN引数にパラメータを渡しています。「EXECUTE :変数名 := 値;」で変数に値を代入することが出きます。

OUT引数のないファンクションはSQLでも実行可能です。

ここでは「SELECT func1(:input1) FROM dual;」として、SELECT句で実行しています。

SQL> variable input1 NUMBER;
SQL> execute :input1 := 5;

PL/SQLプロシージャが正常に完了しました。

SQL> SELECT func1(:input1) FROM dual;

FUNC1(:INPUT1)
--------------
            25

SQL> SELECT func1(10) FROM dual;

 FUNC1(10)
----------
        50

OUT引数を指定した実行・呼び出し例

IN引数(input1)、OUT引数(output1)、そして戻り値を受け取る変数(output2)を宣言しています。

「EXECUTE :output2 := func2(:input1,:output1);」でファンクションの実行と共に、戻り値をoutput2に代入しています。

OUT引数のないファンクションはSQLでも実行可能です。

「print :変数名;」で変数の内容を確認しています。

SQL> variable input1 NUMBER;
SQL> variable output1 NUMBER;
SQL> variable output2 NUMBER;
SQL> execute :input1 := 5;

PL/SQLプロシージャが正常に完了しました。

SQL> EXECUTE :output2 := func2(:input1,:output1);

PL/SQLプロシージャが正常に完了しました。

SQL> print :output1;

   OUTPUT1
----------
        25

SQL> print :output2;

   OUTPUT2
----------
        25

IN OUT引数を指定した実行・呼び出し例

IN引数(input1)、IN OUT引数(inout1)、そして戻り値を受け取る変数(output1)を宣言しています。

「execute :output1 := func3(:input1,:inout1);」でファンクションの実行と共に、戻り値をoutput1に代入しています。

SQL> variable input1 NUMBER;
SQL> variable inout1 NUMBER;
SQL> variable output1 NUMBER;

SQL> execute :input1 := 5;

PL/SQLプロシージャが正常に完了しました。

SQL> execute :inout1 := 10;

PL/SQLプロシージャが正常に完了しました。

SQL> execute :output1 := func3(:input1,:inout1);

PL/SQLプロシージャが正常に完了しました。

SQL> print :inout1;

    INOUT1
----------
        50

SQL> print :output1;

   OUTPUT1
----------
        50

引数なしの実行・呼び出し例

戻り値を受け取る変数(output1)を宣言しています。

「execute :output1 := func4;」でファンクションを実行しています。

SQL> variable output1 VARCHAR2(10);
SQL> execute :output1 := func4;

PL/SQLプロシージャが正常に完了しました。

SQL> print :output1;

OUTPUT1
----------------------------------------------------------------
test

引数の無いファンクションもSQL内で実行することが出来ます。

SQL> SELECT func4 FROM dual;

FUNC4
--------------------------------------------------------------------------------
test

EXEC文での実行・呼び出し例

SQL> variable output1 VARCHAR2(10);
SQL> exec :output1 := func4;

PL/SQLプロシージャが正常に完了しました。

SQL> print :output1;

OUTPUT1
----------------------------------------------------------------
test

ファンクションの削除

ファンクションを削除するときはDROP FUNCTION文を使います。

DROP FUNCTION文の構文は次の通りです。

DROP FUNCTION ファンクション名;

実行すると次のような結果が返ってきます。

SQL> DROP FUNCTION func1;

ファンクションが削除されました。

ファンクションの作成に必要な権限

ファンクションの作成や実行にはCREATE権限やEXECUTE権限が必要です。

必要な権限については↓でまとめていますので参考にしてください。
＞＞作成や実行に必要な権限と確認・付与する方法

モバイルバージョンを終了