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

nas2019/10/16

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

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

目次

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

前提

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行目は、コンソールに文字列を出力します。--は単一行のコメントです。

プロシージャを実行する

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

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

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 || 'と青です';
END;

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

プロシージャを実行する

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

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

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

SQL> print out1

OUT1
--------------------------------------------------------------------------------
赤と青です

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   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で条件を指定します。

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

BEGIN
  select name,romaji into wk_name,wk_romaji from SYAIN
  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 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で出力の項目です。
10行目は、INの項目のidを条件にしています。
19,20行目は、取得したレコードからOUTの項目に値をセットしています。

 

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がない場合)は、エラーになります。

 

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

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と例外処理

△上に戻る