はじめに

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

【Oracle】PL/SQL入門

はじめに

使用した環境

作成したPL/SQL

最も基本的な構文

SELECT文を書く時の注意点

IF文

FOR文

例外処理

%TYPE型

%ROWTYPE型

参考URL