ORACLE／PROCEDURE編

HOME > CREATE PROCEDURE編

PROCEDURE（プロシージャ）

プロシージャとはORACLEではPL/SQLと呼ばれているもので、事前にSQL文をデータベースにコンパイルして置いておき、使用するときはそれを呼び出すだけという使い方をします。

プロシージャでは１つのSQL文では記述することができない複雑な処理を書くことができます。また、ネットワークの負荷を少なくする、処理が早いといったメリットがあります。

プロシージャの作成には、CREATE PROCEDURE句を使用します。

CREATE PROCEDURE構文

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

パラメータ	説明
OR REPLACE	既存のプロシージャを置き換える（更新）時に指定する
引数名	引数名を指定する
IN	値を受け取るだけの変数プロシージャに値を渡す時に使います。
OUT	値を返すだけの変数呼び出し元で変数をセットすると、プロシージャ実行後にセットした変数に値が入ります。プロシージャから値を受け取りたい時に使います。
INOUT	受け取った値を書き換えて返す変数プロシージャに値を渡すとともに、値を受け取りたい時に使います。
データ型	引数のデータ型を指定する　データ型についてはPL/SQL(宣言部)編をご覧ください。
IS 　宣言部	プロシージャ内部で使用する変数を宣言する　→詳細はPL/SQL(宣言部)編をご覧ください。
BEGIN 　処理部	プロシージャに行わせるメイン処理を記述する　→詳細はPL/SQL(実行部)編をご覧ください。
EXCEPTION 　例外処理部	メイン処理で起きた例外の処理をする　→詳細はPL/SQL(例外部)編をご覧ください。

CREATE PROCEDURE例文: CREATE PROCEDURE test_proc(aa IN NUMBER, bb OUT VARCHAR2(2)) IS CURSOR c1 IS SELECT * FROM pref_master WHERE pref_code = aa; BEGIN DBMS_OUTPUT.PUT_LINE('start...'); FOR rec IN c1 LOOP DBMS_OUTPUT.PUT_LINE(rec.pref_code || '-' || rec.pref_name); END LOOP; DBMS_OUTPUT.PUT_LINE('...end'); bb := 'OK'; EXCEPTION WHEN others THEN DBMS_OUTPUT.PUT_LINE('..error..'); bb := 'NG'; END; /

この例ではカーソルを使って、指定された条件で検索を行い、取得できた件数分ループさせて値を表示するというプロシージャです。
「DBMS_OUTPUT」はOracleに標準装備されているユーティリティパッケージです。

プロシージャの実行には「EXEC　プロシージャ名（引数）」で実行します。

構文（プロシージャの実行）

EXEC <プロシージャ名>([引数]);

PROCEDURE実行の例文: --実行例（上記「プロシージャの作成」の例のプロシージャの実行） set serveroutput on; exec test_proc(22);

「DBMS_OUTPUT」パッケージを使ってプロンプトに値を表示させたい時は事前に、
「SET SERVEROUTPUT ON」を実行しておく必要があります。
（SERVEROUTPUT はシステム変数です。詳しくは→システム変数編をご覧ください。）

プロシージャのソースを表示するには USER_SOURCEデータディクショナリを検索します。
（ファンクションのソース表示も同様の方法で表示できます。）
　データディクショナリとは？→データディクショナリ編

ソース表示構文

SELECT TEXT FROM USER_SOURCE WHERE NAME = 'プロシージャ名';

PROCEDUREソース確認の例文: --（例）プロシージャ（test_proc）のソースを確認する。 SELECT TEXT FROM USER_SOURCE WHERE NAME = 'TEST_PROC';

プロシージャ名は必ず、大文字で指定してください。

プロシージャの削除には DROP PROCEDURE句を使用します。

DROP PROCEDURE構文

DROP PROCEDURE <プロシージャ名>;

PROCEDURE削除の例文: --（例）プロシージャ（プロシージャ名：test_proc）を削除する。 DROP PROCEDURE test_proc;

プロシージャの情報を表示するには ALL_OBJECTSデータディクショナリビューを検索します。

プロシージャの情報を表示する

SELECT * FROM ALL_OBJECTS WHERE OBJECT_TYPE = 'PROCEDURE';

データディクショナリビューには、DBA_やUSER_で始まるものもあります。
　違いは参照できる範囲が異なります。

【ALL_OBJECTSの主要カラム】
カラム	内容
OWNER	オブジェクトの所有者
OBJECT_NAME	オブジェクト名
SUBOBJECT_NAME	サブオブジェクト名　（存在する場合のみ）
OBJECT_ID	オブジェクトID
OBJECT_TYPE	オブジェクトの種類　（TABLE/VIEW/FUNCTION/PROCEDUREなど）
CREATED	作成日時
LAST_DDL_TIME	変更日時
TIMESTAMP	作成日時（文字列）
STATUS	オブジェクトのステータス（VALID/INVALID）
TEMPORARY	一時オブジェクトかどうか　（Y/N）
GENERATED	システムによって作成されたオブジェクトかどうか　（Y/N）

ここで取り上げているカラムは、主要なカラムのみです。