Why not login to Qiita and try out its useful features?

We'll deliver articles that match you.

You can read useful information later.

2
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

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

Last updated at Posted at 2021-09-16

業務で必要になるかもしれないので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言語のカーソルと同じですね。
具体的な流れは以下の通りです。

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

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

DECLARE
    --1.カーソルを定義し、実行するSQL文と紐づける
    --文法: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 
これはテーブルの1つの項目を指定できます。

  変数名 テーブル名.項目%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

以上

####参考にした記事
[超入門PL/SQL]
(https://atmarkit.itmedia.co.jp/ait/series/5485/ "超入門「PL/SQL」")
ここからはじめようOracle PL/SQL入門

2
2
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up

Qiita Advent Calendar is held!

Qiita Advent Calendar is an article posting event where you post articles by filling a calendar 🎅

Some calendars come with gifts and some gifts are drawn from all calendars 👀

Please tie the article to your calendar and let's enjoy Christmas together!

2
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?