Oracle PL/SQLのプロシージャのサンプル

OracleのPL/SQLのプロシージャのサンプルです。

確認環境
・Oracle Express Edition 11g Release 2
・Windows 10

目次

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

前提

Oracle XE11をインストールし、SQLコマンドラインでPL/SQLを実行します。
Oracle11gのインストールとHRテーブルを確認

文字列を出力する

文字列を出力するプロシージャのサンプルです。

CREATE OR REPLACE PROCEDURE TEST1
IS
/*
  サンプルテスト
*/
  STR1 VARCHAR2(15) := 'テストです';
BEGIN
  DBMS_OUTPUT.PUT_LINE(STR1); --テストですと出力される
END;
/
プロシージャが作成されました。

1行目は、TEST1というプロシージャを作成しています。
CREATE OR REPLACEなので修正も可能です。
2行目は、ISまたはASを入力します。意味はどちらも同じです。
3から5行目は、複数行のコメントです。/*で始まり*/で終了します。
6行目は、変数です。VARCHAR2は型で(15)は桁数です。文字列をセットしています。
8行目は、コンソールに文字列を出力します。--は単一行のコメントです。
10行目は、最後にスラッシュ(/)で作成が実行されます。(※sqlplus使用時)

プロシージャを実行する

上記で作成したプロシージャをSQLコマンドラインで実行するサンプルです。

SQL> set serveroutput on
SQL> execute test1()
テストです

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

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

select intoで項目を取得する

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

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

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

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

  dbms_output.put_line('名字は' || wk_name || 'です');
END;

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

プロシージャを実行する

上記で作成したプロシージャをSQLコマンドラインで実行するサンプルです。

SQL> set serveroutput on
SQL> execute test1(1)
名字は鈴木です

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

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

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

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

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

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

select文でカーソル(cursor)とfetchとloopを使用するサンプルです。
このサンプルではテストデータとして複数件のデータがあります。

create or replace procedure TEST1
IS
  	CURSOR cur1 IS
		SELECT name,romaji FROM SYAIN;

	syain_rec cur1%ROWTYPE;
BEGIN
  OPEN cur1;
	  LOOP
	    FETCH cur1 INTO syain_rec;
	        EXIT WHEN cur1%NOTFOUND;

	    DBMS_OUTPUT.PUT_LINE(syain_rec.name);
	    DBMS_OUTPUT.PUT_LINE(syain_rec.romaji);
	  END LOOP;
  CLOSE cur1;
END;

3行目は、カーソルcur1を宣言しています。内容は4行目のselect文です。
6行目は、カーソルで取得したレコードの変数です。%ROWTYPEでDBの型と同じになります。
8行目は、カーソルをopenしています。
9行目は、LOOPです。15行目のEND LOOPまでの間を繰り返します。
10行目は、FETCHです。カーソルから取得した値をレコードの変数にセットしています。
11行目は、カーソルのデータがなくなったらこのループを抜けます(EXIT)。
13,14行目は、取得した値をコンソールに表示します。

プロシージャを実行する

上記で作成したプロシージャをSQLコマンドラインで実行するサンプルです。

SQL> set serveroutput on
SQL> execute test1()
鈴木
suzuki
安田
yasuda
佐藤
sato

2行目は、プロシージャを実行しています。
取得した値の分表示されます。

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

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

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

	syain_rec cur1%ROWTYPE;

BEGIN
  OPEN cur1;
    FETCH cur1 INTO syain_rec;
  CLOSE cur1;

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

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

プロシージャを実行する

上記で作成したプロシージャをSQLコマンドラインで実行するサンプルです。

SQL> set serveroutput on
SQL> variable name1 varchar2(20)
SQL> variable romaji1 varchar2(20)
SQL> execute test1(1,:name1,:romaji1)

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

SQL> print name1

NAME1
--------------------------------------------------------------------------------
鈴木

SQL> print romaji1

ROMAJI1
--------------------------------------------------------------------------------
suzuki

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

returnで処理を終了する

returnで処理を終了するサンプルです。

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

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

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

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

NULLで処理を何もしない

NULLは、処理を何もしません。

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

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

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

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

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

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

SQL> execute test1();
id1 = 2
romaji1 = tanaka

SQL> execute test1(10);
id1 = 10
romaji1 = tanaka

SQL> execute test1(romaji1=>'suzuki');
id1 = 2
romaji1 = suzuki

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

SQLコマンドラインで作成時にコンパイルエラーが発生したときは、show errorと入力するとエラー内容を表示してくれます。

警告: ファンクションが作成されましたが、コンパイル・エラーがあります。

show error
FUNCTION TEST2のエラーです。

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0      PL/SQL: Compilation unit analysis terminated
3/8      PLS-00201: 識別子Aを宣言してください。
SQL>

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

ALL_OBJECTSテーブルで確認できます。

SELECT * FROM ALL_OBJECTS 
WHERE OBJECT_TYPE = 'PROCEDURE' AND OWNER = 'HR';

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

プロシージャを削除する

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

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

関連の記事

Oracle PL/SQLのファンクションのサンプル
Oracle PL/SQLのcommitとrollbackと例外処理

△上に戻る