【PL/SQL】プロシージャの書き方（作成/実行/呼び出し/削除）

oreno-admin

4年前

ここではORACLE社が提供するPL/SQL言語でプロシージャの書き方（作成/実行/呼び出し/削除）を紹介しています。

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

プロシージャとは

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

ストアドプロシージャはいわゆるバッチ処理に該当し、データの登録や更新、削除などの処理をまとめて登録することが出来ます。

ストアドプロシージャはSQL内で呼び出しすることは出来ません。

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

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

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

CREATE文：プロシージャの作成・登録方法

プロシージャはCREATE PROCEDURE文で作成・登録することが出きます。

CREATE PROCEDURE～以降に宣言部、処理部、例外処理部と順にPL/SQLの処理を書いていきます。

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

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

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

・OR REPLACE
　同じ名前のプロシージャが存在する場合、上書き更新します。

・IN
　プロシージャの引数として値を受け取るだけの変数の宣言します。

・OUT
　プロシージャの結果として値を格納する変数の宣言します。

・IN OUT
　IN,OUT、両方の特性を持ちます。
　プロシージャの引数としても使え、プロシージャの結果で上書きし返すことが出来ます。

・宣言部
　プロシージャ内で使用する変数を宣言します。

・処理部
　プロシージャ内の処理をコーディングします。

・EXCEPTION
　プロシージャ内で発生したエラーに対する処理を記述します。

プロシージャのサンプルプログラム

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

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

「proc1(a IN NUMBER,b OUT NUMBER)」として引数を宣言しています。

OUT引数のbには「b := a * c;」でaとcを掛けた値を代入しています。

CREATE OR REPLACE PROCEDURE proc1(a IN NUMBER,b OUT NUMBER)
IS
    c NUMBER := 5;
BEGIN
    b := a * c;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('ERROR');
END;
/

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

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

「proc2(a IN NUMBER,b IN OUT NUMBER)」として引数を宣言しています。

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

CREATE OR REPLACE PROCEDURE proc2(a IN NUMBER,b IN OUT NUMBER)
IS
BEGIN
    b := a * b;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('ERROR');
END;
/

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

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

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

CREATE OR REPLACE PROCEDURE proc3
IS
BEGIN
    DBMS_OUTPUT.PUT_LINE('TEST');
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('ERROR');
END;
/

EXECUTE文：プロシージャの実行・呼び出し

プロシージャはEXECUTE文で実行・呼び出すことが出来ます。

「EXECUTE プロシージャ名;」でSQLPLUSから実行できます。

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

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

「EXECUTE proc1(:input1,:output1);」で2つの引数を渡してプロシージャを実行しています。

「print :output1;」で変数の値を出力して結果を確認しています。

SQL> variable input1 number;
SQL> variable output1 number;
SQL> EXECUTE :input1 := 1;

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

SQL> EXECUTE proc1(:input1,:output1);

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

SQL> print :output1;

   OUTPUT1
----------
         5

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

IN,OUT引数と同様に、「variable 変数名データ型;」で「EXECUTE :変数名 := 値;」で変数に値を代入することが出きます。

「 EXECUTE proc1(:input1,:inout1);」で2つの引数を渡してプロシージャを実行しています。

「print :inout1;」で変数の値を出力して結果を確認しています。

SQL> variable input1 number;
SQL> variable inout1 number;

SQL> EXECUTE :input1 := 5;

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

SQL> EXECUTE :inout1 := 5;

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

SQL> EXECUTE proc1(:input1,:inout1);

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

SQL> print :inout1;

    INOUT1
----------
        25

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

引数なしのプロシージャの場合は「EXECUTE プロシージャ名」のみで実行出来ます。

ここではプロシージャを実行する前に「set serveroutput on;」を実行し、DBMSパッケージからの標準出力をONにしています。

SQL> set serveroutput on;
SQL> EXECUTE proc3;
TEST

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

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

EXECUTE文は、EXECと省略して記述することもできます。

SQL> EXEC proc3;
TEST

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

プロシージャの削除

プロシージャを削除するときはDROP PROCEDURE文を使います。

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

DROP PROCEDURE プロシージャ名;

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

SQL> DROP PROCEDURE proc3;

プロシージャが削除されました。

プロシージャの作成に必要な権限

プロシージャの作成や実行にはCREATE権限やEXECUTE権限が必要です。

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