業務で必要になるかもしれないので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句で変数を使わないとダメです。
※失敗例
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
※成功例
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言語のカーソルと同じですね。
具体的な流れは以下の通りです。
- カーソルを定義し、実行するＳＱＬ文と紐づける
- カーソルを開く（開いたカーソルの中にはＳＱＬの結果（結果セット）が入っている）
- カーソルから取得したデータを変数に入れる
- データがあることを確認する
- 変数で何かしら処理を行う
- 処理が終了したら、カーソルを閉じる
これをソースにすると以下のようになります。
複数行あるのでLOOP文でグルングルン回します。
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つならまだしも大量にカラムがあっても、
以下のように一つ一つ入力しないといけないみたいです。
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文そのまま使える。
サンプル
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
以上