Oracle PL/SQLのストアドプロシージャのサンプル

サンプル	文字列のhello worldを出力する
	INとOUTを使用する
	select intoで項目を取得する
	select文でカーソル(cursor)とfetchを使用
	returnで処理を終了する
	NULLで処理を何もしない
	入力項目にデフォルト値をセットする(default)
エラー	作成時にコンパイルエラーが発生したとき
確認	プロシージャの存在確認を行う
削除	プロシージャを削除する

環境

実行は、SQL PlusとSQL Developerで確認しています。

文字列のhello worldを出力する

文字列のhello worldを出力するプロシージャです。

CREATE OR REPLACE PROCEDURE TEST1
IS
/*
  comment
*/
  STR1 VARCHAR2(15) := 'hello world';
BEGIN
  DBMS_OUTPUT.PUT_LINE(STR1); --hello worldと出力される
END;

1行目は、TEST1というプロシージャを作成しています。
CREATE OR REPLACEなので既にある場合は上書きされます。

2行目は、ISまたはASを入力します。意味はどちらも同じです。
3から5行目は、複数行のコメントです。/*で始まり*/で終了します。
6行目は、変数に文字列をセットしています。VARCHAR2は型で(15)は桁数です。
8行目は、コンソールに文字列を出力します。--は単一行のコメントです。

SQL Plusで実行する場合、最終行にスラッシュ(/)を入力します。

プロシージャを実行する

上記で作成したプロシージャをSQL Plusで実行します。

SQL> set serveroutput on
SQL> execute test1()
hello world

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

1行目のset serveroutput onは、serverのoutputをonにして文字列を出力します。
2行目は、executeでプロシージャを実行します。
3行目は、プロシージャから文字列が出力されています。

INとOUTを使用する

create or replace procedure TEST1
(
  STR1  IN  varchar2,
  STR2  OUT varchar2
)
IS

BEGIN
  STR2 := STR1 || ' and blue';
END;

3行目のINは入力で4行目のOUTは出力です。

プロシージャを実行する

上記で作成したプロシージャをSQL Plusで実行します。

SQL> set serveroutput on
SQL> variable out1 varchar2(20)
SQL> execute test1('red',:out1)

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

SQL> print out1

OUT1
--------------------------------------------------------------------------------
red and blue

2行目は、出力の変数を定義しています。バインド変数です。
SQL*Plusからアクセスできるようになります。
https://docs.oracle.com/cd/E57425_01/121/SQPUG/GUID-B52D6F2E-A28A-48B6-B73F-6A9D018BD107.htm
3行目の1番目の引数は入力で、2番目の引数は出力です。
7行目は、print＋変数で取得した値を表示しています。

select intoで項目を取得する

select テーブルの項目 into 変数 from テーブル名

select文を実行するプロシージャのサンプルです。
入力として数値を渡してSQLを実行します。

create or replace procedure TEST1(
  id1    IN   number
)
IS
 wk_name   employee.name%TYPE;

BEGIN
  select name into wk_name from employee
  where ID = id1;

  dbms_output.put_line('my name is ' || wk_name || '.');
END;

2行目は、INの項目です。外部から値を受け取ります。
5行目は、変数です。%TYPEはDBの項目の型になります。
→「employeeテーブルのnameという項目と同じ型です」という宣言です。
8行目はselect intoで取得したnameを右の変数のwk_nameにセットしています。
※PKの項目を指定するので1件のみ取得します。
9行目は、外部から受け取ったIDで条件を指定します。

select intoで複数項目を取得する

BEGIN
  select name,romaji into wk_name,wk_romaji from employee
  where ID = id1;

selectで取得する項目を複数にし、intoの後の変数も複数にすれば取得できます。

select文でカーソル(cursor)とfetchを使用

select文でカーソル(cursor)とfetchを使用するサンプルです。

create or replace procedure TEST1
(
  id1      IN   number,
  name1    OUT  varchar2,
  romaji1  OUT  varchar2
)
IS
  	CURSOR cur1 IS
		SELECT name,romaji FROM employee
		WHERE id = id1;

	employee_rec cur1%ROWTYPE;

BEGIN
  OPEN cur1;
    FETCH cur1 INTO employee_rec;
  CLOSE cur1;

  name1   := employee_rec.name;
  romaji1 := employee_rec.romaji;
END;

3行目は、INで入力の項目です。
4,5行目は、OUTで出力の項目です。
10行目は、INの項目のidを条件にしています。
19,20行目は、取得したレコードからOUTの項目に値をセットしています。

結果確認用のコマンド

set serveroutput on
variable out1 varchar2(20)
variable out2 varchar2(20)
execute test1(1,:out1,:out2)
print out1
print out2

returnで処理を終了する

returnは、処理を終了します。

CREATE OR REPLACE PROCEDURE TEST1
IS
  num1 NUMBER := 1;

BEGIN
  IF (num1 = 1) THEN
     dbms_output.put_line('process1'); --出力される
     RETURN;
  END IF;
  dbms_output.put_line('process2');
END;

8行目は、returnです。ここでこのプロシージャの処理が終了します。

実行した場合、7行目の「process1」が出力されます。
10行目の「process2」は出力されません。

NULLで処理を何もしない

NULLは、処理を何もしない時に使用します。

CREATE OR REPLACE PROCEDURE TEST1
IS
  num1 NUMBER := 1;

BEGIN
  IF (num1 = 1) THEN
     NULL;
  END IF;
  dbms_output.put_line('process2'); --出力される
END;

7行目のNULLは、何もしない場合に記述します。
何も記述しない場合(NULLがない場合)は、エラーになります。

入力項目にデフォルト値をセットする(default)

create or replace procedure TEST1(
  id    IN   number   default 2,
  name  IN   varchar2 default 'tanaka'
)IS
BEGIN
	dbms_output.put_line('id = ' || id);
    dbms_output.put_line('name = '|| name);
END;

デフォルト値は、defaultの後に値を指定します。
値がない場合はデフォルト値になります。以下は実行結果です。

SQL> execute test1();
id = 2
name = tanaka

SQL> execute test1(10);
id = 10
name = tanaka

SQL> execute test1(name=>'suzuki');
id = 2
name= suzuki

作成時にコンパイルエラーが発生したとき

プロシージャ作成時にコンパイルエラーが発生したときは、show errorと入力するとエラー内容が表示されます。

SQL> CREATE OR REPLACE PROCEDURE TEST1
  2  IS
  3  /*
  4    comment
  5  */
  6  BEGIN
  7    DBMS_OUTPUT.PUT_LINE(STR1); --hello worldと出力される
  8  END;
  9  /

警告: プロシージャが作成されましたが、コンパイル・エラーがあります。

SQL> show error
PROCEDURE TEST1のエラーです。

LINE/COL ERROR
-------- -----------------------------------------------------------------
7/3      PL/SQL: Statement ignored
7/24     PLS-00201: 識別子STR1を宣言してください。
SQL>

プロシージャの存在確認を行う

現ユーザがアクセスできるプロシージャを確認できます

SELECT * FROM ALL_PROCEDURES
where OBJECT_TYPE = 'PROCEDURE' and OWNER = 'SYSTEM'

上記は、systemスキーマのプロシージャを確認しています。

現ユーザが所有するプロシージャを確認できます

SELECT * FROM USER_PROCEDURES

プロシージャを削除する

プロシージャの削除は、以下のように「DROP PROCEDURE プロシージャ名」と入力します。

DROP PROCEDURE test1;
プロシージャが削除されました。