ORACLE/PROCEDURE編

オラクルちょこっとリファレンス

HOME > CREATE PROCEDURE編

PROCEDURE(プロシージャ)

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

プロシージャでは1つの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)

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

ToTop