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

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

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

目次

プロシージャ 文字列を出力する
  select文を実行する
  select文でcursorとfetchを使用する
  returnで処理を終了する
エラー 作成時にコンパイルエラーが発生したとき
確認 プロシージャの存在確認を行う
削除 プロシージャを削除する

前提

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というプロシージャを作成しています。
2行目は、ISまたはASを入力します。意味はどちらも同じです。
3から5行目は、複数行のコメントです。/*で始まり*/で終了します。
6行目は、変数です。VARCHAR2は型で(15)は桁数です。文字列をセットしています。
8行目は、文字列を出力する命令です。--は単一行のコメントです。
10行目は、最後にスラッシュ(/)で作成が実行されます。(※sqlplus使用時)

DBMS_OUTPUTパッケージは値を出力します。

プロシージャを実行する

上記で作成したプロシージャを実行するサンプルです。

set serveroutput on
execute test1
test1
PL/SQLプロシージャが正常に完了しました。

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

select文を実行する

select文を実行するプロシージャのサンプルです。
入力として数値を渡してSQLを実行し、出力として文字列を受け取ります。

CREATE OR REPLACE procedure TEST2(
  id1    IN   number,
  name1  OUT  varchar2
)
IS
 wk_name   syain.name%TYPE;
 wk_romaji syain.romaji%TYPE;
BEGIN
  select name,romaji into wk_name,wk_romaji from SYAIN
  where ID = id1;
  name1 := '名前は' || wk_name || ' ローマ字は' || wk_romaji;
  dbms_output.put_line(name1);
END;
/

2行目は、INです。外部から値を渡します。
3行目は、OUTです。外部に値を渡します。
4,5行目は、変数です。%TYPEはDBの項目の型になります。
→「syainテーブルのname,romajiという項目と同じ型です」という宣言です。
9行目はselect文で取得したname,romajiを右の変数のwk_nameとwk_romajiにセットしています。
※データは1件のみ取得する想定です。
10行目は、外部から渡したIDで条件を指定します。
11行目は、変数と文字列を結合しています。結合は縦線(||)を2本続けます。
12行目は、変数を出力します。

プロシージャを実行する

上記で作成したプロシージャを実行するサンプルです。

set serveroutput on
variable name1 varchar2(20)
execute test2(1,:name1)
名前は鈴木 ローマ字はsuzuki
PL/SQLプロシージャが正常に完了しました。

1行目の、set serveroutput onは、文字列を出力するのに必要です。
2行目は、バインド変数です。varchar2を指定しています。
https://docs.oracle.com/cd/E57425_01/121/SQPUG/GUID-B52D6F2E-A28A-48B6-B73F-6A9D018BD107.htm
3行目は、executeでプロシージャを実行します。
4行目は、プロシージャから文字列が出力されています。

select文でcursorとfetchを使用する

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

create or replace procedure TEST1(
  CURSOR1 OUT SYS_REFCURSOR
)
IS
  name1     syain.name%TYPE;
BEGIN
  OPEN CURSOR1 FOR
    select name from SYAIN;
  LOOP
    FETCH CURSOR1 INTO name1;
        EXIT WHEN CURSOR1%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(name1 );
  END LOOP;
END;

2行目は、OUTがあるので出力の指定です。型はSYS_REFCURSORです。
5行目は、変数です。%TYPEはDBの項目の型になります。
→「syainテーブルのnameという項目と同じ型です」という宣言です。
7行目は、OPENでカーソルを開き実行しています。
9行目からはループです。11行目のNOT FOUNDになるまで繰り返します。
10行目は、このカーソルには複数行のデータがありますが、その1行を対象にしてDBの項目nameから変数のname1にセットしています。
12行目は、コンソールに変数の値を表示します。

プロシージャを実行する

上記で作成したプロシージャを実行するサンプルです。

SQL> set serveroutput on
SQL> variable CURSOR1 REFCURSOR
SQL> execute test1(:CURSOR1)
鈴木
田中
佐藤
PL/SQLプロシージャが正常に完了しました。

2行目は、バインド変数です。REFCURSORを指定しています。
3行目は、プロシージャを実行しています。

returnで処理を終了する

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

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

BEGIN
  IF (num1 = 1) THEN
     dbms_output.put_line('処理しません');
     RETURN;
  END IF;
  dbms_output.put_line('処理を行います');
END;
/

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

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

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

作成時にコンパイルエラーが発生したときは、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のファンクションのサンプル

△上に戻る