ここではOracleのストアドプロシージャについて紹介します。

ストアドプロシージャとは

Oracleのストアドプロシージャは PL/SQL というオラクル独自の手続き型言語で記述されたプログラムです。
ストアドプロシージャとストアドファンクションの違いは、戻り値がないのがストアドプロシージャ、戻り値があるのがストアドファンクションです。

ストアドプロシージャの特徴

  • RETURNで戻り値を設定することができない。
  • OUTパラメータを設定することで戻り値を設定することが可能。
  • SQL内で呼び出すことができない。
  • 呼び出すにはCALL文を使う必要がある。


ストアドプロシージャの作成

CREATE PROCEDURE文

ストアドプロシージャの作成は、CREATE PROCEDURE文を使用します。

構文

CREATE [OR REPLACE] PROCEDURE {ストアドプロシージャ名}
       [({引数名} [IN | OUT | INOUT] {データ型}, ...)]
    宣言部
BEGIN
    処理部
EXCEPTION
    例外処理部
END;


パラメータ・モード説明
IN呼び出し元から値を受け取るだけの引数。
OUTストアドプロシージャ側から呼び出し元へ値を返すだけの変数。
INOUT呼び出し元から受け取った値をストアドプロシージャ側で書き換えて返す変数。


以下はストアドプロシージャを作成するサンプルです。

CREATE OR REPLACE PROCEDURE PROC_GET_INFO(
    IN_NO IN VARCHAR,
    OUT_INFO OUT VARCHAR
)
IS
    CURSOR c1 IS SELECT * FROM emp;
 
BEGIN
    FOR rec IN c1 LOOP
        IF rec.emp_no= IN_NO THEN
            OUT_INFO:= TO_CHAR(rec.emp_birthday, 'YYYY');
        END IF;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE(OUT_INFO || '年生まれ');
 
EXCEPTION
    WHEN others THEN
        DBMS_OUTPUT.PUT_LINE('Error');
END;
/


ストアドプロシージャの実行

以下は SQL*Plus でストアドプロシージャをEXECUTE文で実行するサンプルです。

SQL> SET SERVEROUTPUT ON;
SQL> VARIABLE OUT_INFO VARCHAR2(20);
SQL> EXECUTE PROC_GET_INFO('001', :OUT_INFO);
1965年生まれ

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


ストアドプロシージャの削除

DROP PROCEDURE文

ストアドプロシージャの削除は、DROP PROCEDURE文を使用します。

構文

DROP PROCEDURE {ストアドプロシージャ名};


以上、Oracleのストアドプロシージャについて解説しました。