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

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

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

目次

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

前提

Oracle XE11をインストールし、SQLコマンドラインでPL/SQLを作成します。
OracleXE11のインストールと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行目は、STR1という変数に文字列をセットしています。型と桁数の指定が必要です。
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
  BEGIN
    select name into name1 from SYAIN
    where ID = id1;
    dbms_output.put_line('名前は' || name1);
  END;
/
プロシージャが作成されました。

2行目は、INです。入力で使用します。
3行目は、OUTです。出力で使用します。
7行目はselect文で取得したnameをintoの右の変数name1にセットしています。
※データは1件のみ取得する想定です。

プロシージャを実行する

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

set serveroutput on
variable name1 varchar2(20)
execute test2(1,:name1)
名前は鈴木
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;

3行目は、OUTがあるので出力の指定です。型はSYS_REFCURSORです。
6行目は、変数name1の宣言です。%TYPEはDBの項目の型になります。
8行目は、OPENでカーソルを開き実行しています。
10行目からはループです。12行目のNOT FOUNDになるまで繰り返します。
11行目は、このカーソルには複数行のデータがありますが、その1行を対象にし項目nameを変数name1にセットしています。
13行目は、コンソールに変数の値を表示します。

プロシージャを実行する

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

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

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

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

作成時にコンパイルエラーが発生したときは、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;
プロシージャが削除されました。

関連の記事

OracleXE11のインストールとHRテーブルを確認
Oracle SQLコマンドラインを使用するサンプル

△上に戻る