ORACLE 無名ブロック(DECLARE)を理解する
ORACLE PL/SQL無名ブロックを理解しましょう。
ORACLEの無名ブロックとは、SQL*PlusやSQL*Developerなどのツールで実行する、PL/SQLブロックのことです。データベース内のデータ操作に用いられる、PL/SQLのサブプログラムという言い方もできますね。
PL/SQLブロックは、DECLARE、BEGIN、EXCEPTION、ENDにて定義します。つまり、宣言セクション、実行可能セクション、例外処理セクションの3つのセクションから構成され、実行可能セクションのみ必須となっています。
ここでは ORACLE 無名ブロックを理解する ためサンプルを用意して説明します。
Sponsored Links
目次
無名ブロックの構成を理解する
無名ブロックの構成(PL/SQLの構文)は下記の通りです。
DECLARE /* 宣言セクション */ BEGIN /* 実行可能セクション */ EXCEPTION /* 例外セクション */ END;
宣言セクション(DECLARE)
宣言セクションは、変数や定数、カーソルや関数、例外などを宣言を記述するセクションです。宣言する必要がなければDECLAREは省略できます。
実行可能セクション(BEGIN)
実行可能セクションは、SQL文や制御ロジックなどの実行文を記述するセクションです。ここは必須となりますので、必ず何か記述する必要があります。
例外セクション(EXCEPTION)
例外セクションは、実行可能セクションで発生した例外をキャッチし、対応処置を記述するセクションです。例外を捕捉する必要がなければEXCEPTIONは省略できます。
終わり(END)
PL/SQLブロックの最後に必ず記述します。
ふむふむ、なるほど。つまり最小の文としては、
BEGIN
DBMS_OUTPUT.put_line ('Hello World!');
END;
こんな感じになるのですね。
Sponsored Links
無名ブロックを全セクションで構成
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を使う以上どうしてもストアドプロシージャに頼らないとならない場面が出てきますからね。
この辺りの基本はキッチリ押さえておきましょう。
おつかれさまでした。
Sponsored Links