はじめに
OracleのPL/SQLを使った業務を長らく担当していなかったため、完全に忘れてしまっていたPL/SQLを復習するついでに、記事としてまとめてみました。
使用した環境
- Oracle社が提供しているOracle Live SQLでOracle19cを利用しました。
作成したPL/SQL
最も基本的な構文
- PL/SQLはDECLAREの「宣言部」と、BEGIN～ENDの「処理部」に大別されます。
- コンソールへの出力は
DBMS_OUTPUT.PUT_LINE()を使っています。
最も基本的な構文
-- 宣言部
DECLARE
message VARCHAR2(50);
-- 処理部
BEGIN
message := 'Hello, world!';
DBMS_OUTPUT.PUT_LINE(message);
END;
実行結果
Hello, world!
SELECT文を書く時の注意点
- SELECT文を書くときは、
SELECT～INTOと書いてSELECT文の結果を変数に代入しないとエラーが起きてしまいます。
誤ったSQL
-- 宣言部
DECLARE
date_time VARCHAR2(50);
-- 処理部
BEGIN
SELECT SYSDATE
FROM DUAL;
DBMS_OUTPUT.PUT_LINE(date_time);
END;
「誤ったSQL」の実行結果
ORA-06550: line 5, column 3:
PLS-00428: an INTO clause is expected in this SELECT statement
正しく動作するSQL
-- 宣言部
DECLARE
date_time VARCHAR2(50);
-- 処理部
BEGIN
SELECT SYSDATE
INTO date_time
FROM DUAL;
DBMS_OUTPUT.PUT_LINE(date_time);
END;
「正しく動作するSQL」の実行結果
28-MAR-20
IF文
- if文は一般的な言語とほぼ同じ構文ですが、
ELSE IFではなくELSIFと書く点に注意が必要です。
if文
-- 宣言部
DECLARE
name VARCHAR2(10);
message VARCHAR2(100);
-- 処理部
BEGIN
name := 'nkojima';
IF (name = 'mkojima') THEN
message := '名前はmkojimaです。';
ELSIF (name = 'okojima') THEN
message := '名前はokojimaです。';
ELSE
message := '名前はmkojimaおよびokojimaではありません。';
END IF;
DBMS_OUTPUT.PUT_LINE(message);
END;
実行結果
名前はmkojimaおよびokojimaではありません。
FOR文
- ループ内で使われるループ変数（カウンタ変数、ループカウンタ）は、暗黙的に宣言されるので、DECLARE内で宣言する必要はありません。
- 以下のコードでは
iがループ変数となっています。 - 「小の月」の判定には、
INを使うことで簡潔に表現しています。 -
IF (i=2 OR i=4 OR i=6 OR i=9 OR i=11) THENと書くことも可能ですが、条件式が長くなるため可読性が悪くなってしまいます。 - 文字列の結合には
||を使っています。 - CONCAT関数を使って文字列を結合することも出来ます。
for文
-- 宣言部
DECLARE
-- 処理部
BEGIN
FOR i IN 1..12 LOOP
IF (i IN (2, 4, 6, 9, 11)) THEN
DBMS_OUTPUT.PUT_LINE(i || '月は「小の月」です');
ELSE
DBMS_OUTPUT.PUT_LINE(i || '月は「大の月」です');
END IF;
END LOOP;
END;
実行結果
1月は「大の月」です
2月は「小の月」です
3月は「大の月」です
4月は「小の月」です
5月は「大の月」です
6月は「小の月」です
7月は「大の月」です
8月は「大の月」です
9月は「小の月」です
10月は「大の月」です
11月は「小の月」です
12月は「大の月」です
例外処理
- 例外処理を行う場合、EXCEPTIONで区切られた「例外処理部」を設けます。
- 例外処理部では例外の処理に応じて処理を分けるため、必ず
WHEN 例外 THEN 例外処理;の形で例外処理を記述します。
例外処理
-- 宣言部
DECLARE
name VARCHAR2(10);
noNameException EXCEPTION;
-- 処理部
BEGIN
name := '';
IF (name is not null) THEN
DBMS_OUTPUT.PUT_LINE('名前は' || name || 'です。');
ELSE
RAISE noNameException;
END IF;
-- 例外処理部
EXCEPTION
WHEN noNameException THEN
DBMS_OUTPUT.PUT_LINE('名前がありません。');
END;
実行結果
名前がありません。
%TYPE型
- 変数のデータ型として
%TYPEを用いると、テーブル側の設計変更でデータ型が変更されてもPL/SQL側の変数のデータ型を変更する必要がなくなるため、コードの保守性が向上します。
%TYPE型
-- 宣言部
DECLARE
depNo SCOTT.DEPT.DEPTNO%TYPE;
depName SCOTT.DEPT.DNAME%TYPE;
-- 処理部
BEGIN
SELECT
DEPTNO,
DNAME
INTO
depNo,
depName
FROM
SCOTT.DEPT
WHERE
DEPTNO = 10;
DBMS_OUTPUT.PUT_LINE('部署コード：' || depNo);
DBMS_OUTPUT.PUT_LINE('部署名：' || depName);
END;
実行結果
部署コード：10
部署名：ACCOUNTING
%ROWTYPE型
- テーブルやビュー、カーソルの構造をまとめたデータ型として
%ROWTYPEを用いると、テーブル側の設計変更でデータ型が変更されてもPL/SQL側の変数のデータ型を変更する必要がなくなるため、コードの保守性が向上します。 -
%TYPEの場合は1つずつの変数に対して宣言が必要でしたが、%ROWTYPEの場合は行データをまとめて1つの変数として宣言できます。 -
SELECT DEPTNO, DNAME, LOC...の部分は、SELECT *...と書き換えることも可能です。
%ROWTYPE型
-- 宣言部
DECLARE
dep SCOTT.DEPT%ROWTYPE;
-- 処理部
BEGIN
SELECT
DEPTNO,
DNAME,
LOC
INTO
dep
FROM
SCOTT.DEPT
WHERE
DEPTNO = 10;
DBMS_OUTPUT.PUT_LINE('部署コード：' || dep.DEPTNO);
DBMS_OUTPUT.PUT_LINE('部署名：' || dep.DNAME);
DBMS_OUTPUT.PUT_LINE('地域：' || dep.LOC);
END;
実行結果
部署コード：10
部署名：ACCOUNTING
地域：NEW YORK