【Oracle】PL/SQL入門

# はじめに

OracleのPL/SQLを使った業務を長らく担当していなかったため、完全に忘れてしまっていたPL/SQLを復習するついでに、記事としてまとめてみました。

# 使用した環境

* Oracle社が提供している[Oracle Live SQL](https://livesql.oracle.com/)でOracle19cを利用しました。

# 作成したPL/SQL

## 最も基本的な構文

* PL/SQLはDECLAREの「宣言部」と、BEGIN～ENDの「処理部」に大別されます。
* コンソールへの出力は`DBMS_OUTPUT.PUT_LINE()`を使っています。
 
```sql:最も基本的な構文
-- 宣言部
DECLARE
  message VARCHAR2(50);
-- 処理部
BEGIN
  message := 'Hello, world!';
  DBMS_OUTPUT.PUT_LINE(message);
END;
```

```text:実行結果
Hello, world!
```

## SELECT文を書く時の注意点

* SELECT文を書くときは、`SELECT～INTO`と書いてSELECT文の結果を変数に代入しないとエラーが起きてしまいます。

```sql:誤ったSQL
-- 宣言部
DECLARE
  date_time VARCHAR2(50);
-- 処理部
BEGIN
  SELECT SYSDATE
  FROM DUAL;
  
  DBMS_OUTPUT.PUT_LINE(date_time);
END;
```

```text:「誤ったSQL」の実行結果
ORA-06550: line 5, column 3:
PLS-00428: an INTO clause is expected in this SELECT statement 
```

```sql:正しく動作するSQL
-- 宣言部
DECLARE
  date_time VARCHAR2(50);
-- 処理部
BEGIN
  SELECT SYSDATE
  INTO date_time
  FROM DUAL;
  
  DBMS_OUTPUT.PUT_LINE(date_time);
END;
```

```text:「正しく動作するSQL」の実行結果
28-MAR-20
```

## IF文

* if文は一般的な言語とほぼ同じ構文ですが、`ELSE IF`ではなく`ELSIF`と書く点に注意が必要です。

```sql: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;
```

```text:実行結果
名前はmkojimaおよびokojimaではありません。
```

## FOR文

* ループ内で使われるループ変数（カウンタ変数、ループカウンタ）は、暗黙的に宣言されるので、DECLARE内で宣言する必要はありません。
 * 以下のコードでは`i`がループ変数となっています。
* 「小の月」の判定には、`IN`を使うことで簡潔に表現しています。
 * `IF (i=2 OR i=4 OR i=6 OR i=9 OR i=11) THEN`と書くことも可能ですが、条件式が長くなるため可読性が悪くなってしまいます。
* 文字列の結合には`||`を使っています。
 * [CONCAT関数](https://www.shift-the-oracle.com/sql/functions/concat.html)を使って文字列を結合することも出来ます。

```sql: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;
```

```text:実行結果
1月は「大の月」です
2月は「小の月」です
3月は「大の月」です
4月は「小の月」です
5月は「大の月」です
6月は「小の月」です
7月は「大の月」です
8月は「大の月」です
9月は「小の月」です
10月は「大の月」です
11月は「小の月」です
12月は「大の月」です
```

## 例外処理

* 例外処理を行う場合、EXCEPTIONで区切られた「例外処理部」を設けます。
* 例外処理部では例外の処理に応じて処理を分けるため、必ず`WHEN 例外 THEN 例外処理;`の形で例外処理を記述します。

```sql:例外処理
-- 宣言部
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;
```

```text:実行結果
名前がありません。
```


## %TYPE型

* 変数のデータ型として`%TYPE`を用いると、テーブル側の設計変更でデータ型が変更されてもPL/SQL側の変数のデータ型を変更する必要がなくなるため、コードの保守性が向上します。

```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;
```

```text:実行結果
部署コード：10
部署名：ACCOUNTING
```

## %ROWTYPE型

* テーブルやビュー、カーソルの構造をまとめたデータ型として`%ROWTYPE`を用いると、テーブル側の設計変更でデータ型が変更されてもPL/SQL側の変数のデータ型を変更する必要がなくなるため、コードの保守性が向上します。
* `%TYPE`の場合は1つずつの変数に対して宣言が必要でしたが、`%ROWTYPE`の場合は行データをまとめて1つの変数として宣言できます。
* `SELECT DEPTNO, DNAME, LOC...`の部分は、`SELECT *...`と書き換えることも可能です。

```sql:%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;
```

```text:実行結果
部署コード：10
部署名：ACCOUNTING
地域：NEW YORK
```

# 参考URL

* [SHIFT the Oracle - PL/SQL プログラミング入門](https://www.shift-the-oracle.com/plsql/)
* [ここからはじめよう Oracle PL/SQL入門](https://www.oracle.com/technetwork/jp/ondemand/database/db-basic/b-11-plsql-1448399-ja.pdf)