ORACLE 無名ブロック(DECLARE)を理解する

2019.02.25 2020.05.25

ORACLE PL/SQL無名ブロックを理解しましょう。

ORACLE 無名ブロック(DECLARE)を理解する

ORACLEの無名ブロックとは、SQL*PlusやSQL*Developerなどのツールで実行する、PL/SQLブロックのことです。データベース内のデータ操作に用いられる、PL/SQLのサブプログラムという言い方もできますね。

PL/SQLブロックは、DECLARE、BEGIN、EXCEPTION、ENDにて定義します。つまり、宣言セクション、実行可能セクション、例外処理セクションの3つのセクションから構成され、実行可能セクションのみ必須となっています。

ここでは ORACLE 無名ブロックを理解する ためサンプルを用意して説明します。

1 無名ブロックの構成を理解する
2 無名ブロックを全セクションで構成
3 まとめ

無名ブロックの構成を理解する

無名ブロックの構成(PL/SQLの構文)は下記の通りです。

DECLARE
  /*　宣言セクション */
BEGIN
  /*　実行可能セクション */
EXCEPTION
  /*　例外セクション */
END;

宣言セクション(DECLARE)

宣言セクションは、変数や定数、カーソルや関数、例外などを宣言を記述するセクションです。宣言する必要がなければDECLAREは省略できます。

実行可能セクション(BEGIN)

実行可能セクションは、SQL文や制御ロジックなどの実行文を記述するセクションです。ここは必須となりますので、必ず何か記述する必要があります。

例外セクション(EXCEPTION)

例外セクションは、実行可能セクションで発生した例外をキャッチし、対応処置を記述するセクションです。例外を捕捉する必要がなければEXCEPTIONは省略できます。

終わり(END)

PL/SQLブロックの最後に必ず記述します。

ふむふむ、なるほど。つまり最小の文としては、

BEGIN
  DBMS_OUTPUT.put_line ('Hello World!');
END;

こんな感じになるのですね。

無名ブロックを全セクションで構成

PL/SQLの使用例です。せっかくなので全セクションで構成してみましょう。

仕様は「カーソルを使って社員テーブル(EMPLOYEES)からID順に全件取得し、標準出力(DBMS_OUTPUT)にEMAILを表示する」としてみましょうかね。

必要なテーブルとデータは下記の通りです。

CREATE TABLE EMPLOYEES (
  ID  NVARCHAR2(255) NOT NULL,
  NAME  NVARCHAR2(255) NOT NULL,
  EMAIL  NVARCHAR2(255),
  SEX  NVARCHAR2(1),--1:男,2:女,null:不明
  SALARY  NUMBER,--null:不明
  CREATED_AT  DATE NOT NULL,
  UPDATED_AT  DATE,
  CONSTRAINT PK_EMPLOYEES PRIMARY KEY (ID) USING INDEX
);
INSERT INTO EMPLOYEES VALUES('001','hoge','hoge@example.com','1','30',sysdate,sysdate);
INSERT INTO EMPLOYEES VALUES('002','piyo','piyo@example.com','2','50',sysdate,sysdate);
INSERT INTO EMPLOYEES VALUES('003','fuga','fuga@example.com',null,null,sysdate,sysdate);
INSERT INTO EMPLOYEES VALUES('004','foo','foo@example.com','2','65',sysdate,sysdate);
INSERT INTO EMPLOYEES VALUES('005','bar','bar@example.com','1','25',sysdate,sysdate);
INSERT INTO EMPLOYEES VALUES('006','baz','baz@example.com','2','70',sysdate,sysdate);
INSERT INTO EMPLOYEES VALUES('007','saka','saka@example.com',null,'100',sysdate,sysdate);

全セクションで構成して記述するとこんな感じになるでしょうか。

DECLARE
--カーソル
CURSOR CUR_EMP IS
SELECT * FROM EMPLOYEES;
--変数
WK_EMP  EMPLOYEES%ROWTYPE;
BEGIN
  DBMS_OUTPUT.PUT_LINE('### START ###');
  WK_EMP := NULL;
  IF CUR_EMP%ISOPEN THEN
    CLOSE CUR_EMP;
  END IF;
  OPEN CUR_EMP;
  LOOP
    FETCH CUR_EMP INTO WK_EMP;
    EXIT WHEN CUR_EMP%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(WK_EMP.EMAIL);
  END LOOP;
  IF CUR_EMP%ISOPEN THEN
    CLOSE CUR_EMP;
  END IF;
  DBMS_OUTPUT.PUT_LINE('### END ###');
  RETURN;
EXCEPTION
WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

テストしてみると・・・、

### START ###
hoge@example.com
piyo@example.com
fuga@example.com
foo@example.com
bar@example.com
baz@example.com
saka@example.com
### END ###

おおおー、表示された―＾＾

まとめ

PL/SQLを使うメリットって、パフォーマンスに優れている点が一番じゃないですかね。プログラムをデータベース内に格納できるとか、SQLとの親和性が高いとか色々言われますが、行き着くところは応答速度の良さが最大の魅力なんじゃないかなーっと。

今どきDBMSに大きく依存してしまう開発もどうかと思うけど、ORACLEを使う以上どうしてもストアドプロシージャに頼らないとならない場面が出てきますからね。

この辺りの基本はキッチリ押さえておきましょう。

おつかれさまでした。