PL/SQLをかじってみた(2)

業務で必要になるかもしれないのでPL/SQLについて学んでみました。
今回はPL/SQLでSQL文を記述するにはどうすればいいのかについて学習していきます。

###使用した環境
Oracle Live SQL

###使用したテーブル 
テーブル名：TABLE_SAMPLE

| ID (number型) | NAME (varchar2(20))|
|:-------------|--------------------:|
| 1            | AYAKA               | 
| 2            | ISHIDA              | 
| 3            | UNO                 |        

###SELECT文
SELECT文とはデータを取得するSQL文ですよね。
Javaの場合はコネクションクラスを作って、そのあとステートメントクラスを作って、んで、えっと……あぁ、面倒くさい!!
となりますが、PL/SQLでは上記の処理は一切不要。
ただ結果を受け取るための変数を作るだけでいいんです。
そう、PL/SQLは変数でしかSELECT文を実行した結果を取得できません。
しかもinto句で変数を使わないとダメです。

※失敗例

```sql
DECLARE
BEGIN
        SELECT COUNT(*) FROM TABLE_SAMPLE;
END;
```

```
ORA-06550: line 3, column 9:
PLS-00428: an INTO clause is expected in this SELECT statement 
```

※成功例

```sql
DECLARE
    --件数を取得するための変数(countはsqlの予約語なので使えない)
    datacount NUMBER ; 
BEGIN
        --into句を利用して件数を取得
        SELECT COUNT(*) into datacount FROM TABLE_SAMPLE; 
        --件数を表示する
        DBMS_OUTPUT.PUT_LINE(datacount || '件');    
END;
```

```
Statement processed.
3件
```

####カーソル

複数行取得したい場合は**カーソル**を利用します。
カーソルとは複数行ある結果から1行ずつ取り出すことのできるものです。
JavaやC言語のカーソルと同じですね。
具体的な流れは以下の通りです。

1. カーソルを定義し、実行するＳＱＬ文と紐づける
1. カーソルを開く（開いたカーソルの中にはＳＱＬの結果（結果セット）が入っている）
1. カーソルから取得したデータを変数に入れる
1. データがあることを確認する
1. 変数で何かしら処理を行う
1. 処理が終了したら、カーソルを閉じる

これをソースにすると以下のようになります。
複数行あるのでLOOP文でグルングルン回します。

```sql
DECLARE
    --1.カーソルを定義し、実行するＳＱＬ文と紐づける
    --文法：CURSOR カーソル名 IS SQL文
    CURSOR cur IS SELECT * FROM TABLE_SAMPLE;
    
    --取得する行のレコードを定義する
    TYPE rec IS RECORD(
        id NUMBER,
        name varchar2(20)
    );
    
    --カーソルからデータを受け取る変数を定義する
    data rec;
BEGIN
    --2.カーソルを開く
    --開いた瞬間1行目のデータが登録される
    OPEN cur;
    
    LOOP
        --3.カーソルから取得したデータを変数に入れる
        FETCH cur INTO data;

        --5.データがなくなるまで繰り返す
        EXIT WHEN cur%NOTFOUND;
        
        --4.変数で何かしら処理を行う
        DBMS_OUTPUT.PUT_LINE('ID:'||data.id||' '||'NAME:'||data.name);

    END LOOP;

        --処理が終了したら、カーソルを閉じる
        CLOSE cur;
END;
```

```
Statement processed.
ID:1 NAME:AYAKA
ID:2 NAME:ISHIDA
ID:3 NAME:UNO
```

####%TYPEと%ROWTYPE
上記SQLで使用しているTABLE_SAMPLEのレコードのタイプ定義。
これ2つならまだしも大量にカラムがあっても、
以下のように一つ一つ入力しないといけないみたいです。

```sql
    TYPE rec IS RECORD(
        id NUMBER,
        name varchar2(20),
        A1 varchar2(20),
        A2 varchar2(20),
        A3 NUMBER,
        A4 varchar2(20),
        A5 varchar2(20),
        A6 varchar2(20),
        A7 varchar2(20),
        A8 varchar2(20),
    );
```

また、「大改造ということでこのカラムを全部違う種類に変更する！」なんて言われたら面倒くさすぎて卒倒してしまいますよね？
少なくとも、開発時間がこれのために削られてしまいます。
もっとなんか簡単に書けないのか？
そんなあなたにはこちら
**%ROWTYPE**　
これでテーブルの全てのカラム名やデータ型を書かずに、まとめて以下のように定義するだけで済みます。

```
　　変数名 カーソル名%ROWTYPE;
```
あんなにあった行が1行だけで済むなんて。
えっ？私は一部の項目だけあればいいんだ。
でも、データ型は変えるためだけにプログラムをいじりたくない？
そんなあなたにはこちら
**%TYPE**　
これはテーブルの１つの項目を指定できます。

```
　　変数名 テーブル名.項目%TYPE;
```

便利ですよね。
####INSERT/DELETE/UPDATE文
SQL文そのまま使える。
サンプル

```sql

DECLARE
    CURSOR cur IS SELECT * FROM TABLE_SAMPLE;
    TYPE rec IS RECORD(
        id NUMBER,
        name varchar2(20)
    );
    data rec;
BEGIN
    
    DBMS_OUTPUT.PUT_LINE('元のデータ');
    OPEN cur;
    
    LOOP
        FETCH cur INTO data;
        EXIT WHEN cur%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('ID:'||data.id||' '||'NAME:'||data.name);
    END LOOP;

    DBMS_OUTPUT.PUT_LINE('件数:' || cur%ROWCOUNT || '件');

    CLOSE cur;

    DBMS_OUTPUT.PUT_LINE('追加処理(INSERT文)');
    INSERT INTO TABLE_SAMPLE (ID,NAME)
    VALUES(1234,'INSERT');

    OPEN cur;
    LOOP
        FETCH cur INTO data;
        EXIT WHEN cur%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('ID:'||data.id||' '||'NAME:'||data.name);
    END LOOP;
    
    DBMS_OUTPUT.PUT_LINE('件数:' || cur%ROWCOUNT || '件');

    CLOSE cur;
    
    DBMS_OUTPUT.PUT_LINE('更新処理(UPDATE文)');
    UPDATE TABLE_SAMPLE 
       SET NAME = 'KOUSIN'
     WHERE ID = 1234;

    OPEN cur;
    LOOP
        FETCH cur INTO data;
        EXIT WHEN cur%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('ID:'||data.id||' '||'NAME:'||data.name);
    END LOOP;

    DBMS_OUTPUT.PUT_LINE('件数:' || cur%ROWCOUNT || '件');

    CLOSE cur;

    
    DBMS_OUTPUT.PUT_LINE('削除(DELETE文)');
    DELETE 
      FROM TABLE_SAMPLE 
     WHERE ID = 1234;

    OPEN cur;
    LOOP
        FETCH cur INTO data;
        EXIT WHEN cur%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('ID:'||data.id||' '||'NAME:'||data.name);
    END LOOP;

    DBMS_OUTPUT.PUT_LINE('件数:' || cur%ROWCOUNT || '件');

    CLOSE cur;

END;

```

結果

```
Statement processed.
元のデータ
ID:1 NAME:AYAKA
ID:2 NAME:ISHIDA
ID:3 NAME:UNO
件数:3件
追加処理(INSERT文)
ID:1234 NAME:INSERT
ID:1 NAME:AYAKA
ID:2 NAME:ISHIDA
ID:3 NAME:UNO
件数:4件
更新処理(UPDATE文)
ID:1234 NAME:KOUSIN
ID:1 NAME:AYAKA
ID:2 NAME:ISHIDA
ID:3 NAME:UNO
件数:4件
削除(DELETE文)
ID:1 NAME:AYAKA
ID:2 NAME:ISHIDA
ID:3 NAME:UNO
件数:3

```

以上

####参考にした記事
[超入門PL/SQL]
(https://atmarkit.itmedia.co.jp/ait/series/5485/ "超入門「PL/SQL」")
[ここからはじめようOracle PL/SQL入門](https://www.oracle.com/technetwork/jp/ondemand/database/db-basic/b-11-plsql-1448399-ja.pdf "Oracle ここからはじめようOracle PL/SQL入門")