Procedure

概要

プロシージャとは、サブプログラムの一種であり、パラメータを受け取り、コール側環境からコールすることが可能な名前付きのPL/SQLブロックである。プロシージャは、特定の処理を実行することを目的に使用する。

プロシージャの作成

1. プロシージャ作成の構文：

CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter1 parameter_mode1 data_type1,
  parameter2 parameter_mode2 data_type2,
   ......)]
IS | AS
  PL/SQLブロック;

• パラメータに指定するデータ型は、サイズの指定ができない
• パラメータに指定できるデータ型は、サイズの指定のないスカラーデータ型、%TYPE, %ROWTYPEである。
• プロシージャの作成途中でコンパイラエラーが発生した場合でも、ソースコードはデータディクショナリに格納される。
• コンパイルエラーが発生した場合には、SHOW ERRORSコマンドを発行し、エラーの確認ができる。

2. パラメータモード

• INパラメータ（デフォルト）：プロシージャからコール側環境に値を渡す。

INパラメータの例：

SQL> CREATE OR REPLACE PROCEDURE del_emp
  2  (p_emp_no          in number,
  3   p_emp_name        in varchar2)
  4  IS
  5  BEGIN
  6      DELETE FROM emp
  7      WHERE emp_no = p_emp_no AND emp_name = p_emp_name;
  8      COMMIT;
  9      DBMS_OUTPUT.PUT_LINE('社員' || p_emp_name || 'を削除しました');
 10  END del_emp;
 11  /
プロシージャが作成しました。
 
SQL> EXCUTE del_emp(101, 'JONES')
社員JONESを削除しました
 
PL/SQLプロシージャが正常に完了しました。

• OUTパラメータ：プロシージャからコール側環境に値を戻す。

OUTパラメータの例：

SQL> CREATE OR REPLACE PROCEDURE sel_emp
  2  (p_id          IN emp.emp_no%type,
  3   p_name        OUT emp.emp_name%type)
  4  IS
  5  BEGIN
  6      SELECT emp_name INTO p_name FROM emp
  7      WHERE emp_no = p_id;
  8  END del_emp;     
  9  /
 
プロシージャが作成しました。
 
SQL> VARIABLE v_name VARCHAR2(15)
SQL> EXCUTE sel_emp(101, :v_name)
 
PL/SQLプロシージャが正常に完了しました。
 
SQL> PRINT v_name
 
V_NAME
---------------------------------------
JOHNNY

• IN OUTパラメータ：1つのパラメータを使用して、コール側環境から初期値を渡し、プロシージャ側からは、プロシージャ内で処理した値を戻す。

IN OUTパラメータの例：

次のプロシージャ「date_proc」では、1つの仮パラメータ「p_date」を使用し、初期値として"20080306"を渡して、プロシージャ内で書式の変更を行った結果"2008年03月06日"を戻す。

SQL> CREATE OR REPLACE PROCEDURE date_proc
  2  (p_date      IN OUT varchar2)
  3  IS
  4  BEGIN
  5      p_date := SUBSTR(p_date, 1, 4) || '年' || SUBSTR(p_date, 5, 2) || '月' || SUBSTR(p_date, 7, 2) || '日';
  6  END date_proc;
  7  /
 
プロシージャが作成しました。
 
SQL> VARIABLE v_date VARCHAR2(15)
SQL> BEGIN
  2    :v_date := '20080306';
  3  END;
  4  /
 
PL/SQLプロシージャが正常に完了しました。
 
SQL> PRINT v_name
 
V_DATE
---------------------------------------
20080306
 
SQL> EXCUTE date_proc(:v_date)
 
PL/SQLプロシージャが正常に完了しました。
 
SQL> PRINT v_date
----------------------------------------
2008年03月06日

3. パラメータの受け渡し

プロシージャのコール

1. サブプログラムの宣言

繰り返し実行するコードを別のプロシージャとして作成するのではなく、サブプログラムとして定義することができる。サブプログラムは、任意のPL/SQLブロックの宣言部で定義することが可能だが、これは、ローカルなサブプログラムとなるため、定義されたブロックの外部からはコールすることができない。外部からもコールする必要が発生するのであれば、ローカルサブプログラムではなく、スタンドアロンプロシージャとして作成する。

サブプログラムの宣言および使用例：

SQL> CREATE OR REPLACE PROCEDURE main_proc
  2  (p_value_1    IN NUMBER)
  3  IS
  4    v_id    NUMBER := 10;
  5    PROCEDURE sub_proc    -- サブプログラム「sub_proc」の宣言
  6    IS
  7      BEGIN
  8        ......
  9      END sub_proc;
 10  BEGIN
 11    ......
 12    sub_proc;    -- サブプログラム「sub_proc」のコール
 13    ......
 14  END main_proc;
 15  /
 
プロシージャが作成しました

ローカルサブプログラムの宣言は、PL/SQLブロック宣言部で定義するが、必ず一番最後に定義する。ローカルサブプログラムよりも後に変数などの宣言が含まれる場合には、コンパイルエラーが発生する。

2. 無名PL/SQLブロックからのコール

無名PL/SQLブロックから、スタンドアロンプロシージャをコールする方法を次に示す。

SQL> DECLARE
  2    v_no        NUMBER := 103;
  3    v_name    VARCHAR2(15);
  4  BEGIN
  5      ......
  6      sel_emp(v_no, v_name);
  7      ......
  8  END;
  9  /
 
PL/SQLプロシージャが正常に完了しました。

前の例は、無名PL/SQLブロックから、スタンドアロンプロシージャ「sel_emp」をコールする。
SQL*Plusからプロシージャをコールするには、EXCUTEコマンドを使用したが、無名PL/SQLブロックからプロシージャをコールする場合には、コールするプログラム名のみを指定する。

3. 別のプロシージャからのコール

次は、別のプロシージャからのコールする方法を示す。

SQL> CREATE OR REPLACE PROCEDURE sel_dept
  2  IS 
  3    p_id      NUMBER := 102;
  4    v_name    VARCHAR2(10);
  5  BEGIN
  6    ......
  7    sel_emp(v_id, v_name);
  8    ......
  9  END sel_dept;
 10  /
 
プロシージャが作成しました

前の例は、プロシージャ「sel_dept」からプロシージャ「sel_emp」をコールしている。無名PL/SQLブロックからプロシージャをコールする場合と同様に、コールするプログラム名のみを指定する。

プロシージャでの例外処理

プロシージャの削除