はじめに
次の現場でPL/SQLを使用するので、実務で頻出する基本パターンを5つに絞って学習しました。
1. 変数代入の「型」をテーブルに合わせる (%TYPE)
実務では、テーブルのカラムの桁数が途中で変わることがよくあります。
変数の型を直接書くと修正が大変なので、テーブルの定義を「参照」するのが鉄則です。
DECLARE
--「usersテーブルのname列と同じ型にして!」という書き方
v_user_name users.name%TYPE;
BEGIN
SELECT name INTO v_user_name FROM users WHERE id = 100;
DBMS_OUTPUT.PUT_LINE('取得した名前: ' || v_user_name);
END;
2. 検索結果を変数に放り込む (SELECT INTO)
SQLの結果をそのまま画面に出すのではなく、一旦「変数(箱)」に入れて、その後の計算や条件分岐に使い回します。
DECLARE
v_count NUMBER;
BEGIN
-- データの件数を数えて、v_countに入れる
SELECT COUNT(*) INTO v_count FROM orders WHERE status = 'PENDING';
-- 変数の中身を見て、処理を分ける
IF v_count > 0 THEN
DBMS_OUTPUT.PUT_LINE(v_count || '件の未処理データがあります。');
END IF;
END;
3.大量データを1行ずつ捌く (FOR LOOP + CURSOR)
現場で最も多い作業が「対象データを抽出して、1件ずつ特定の処理をする」というループです。
FORループを使うと、開始から終了までをスマートに書けます。
BEGIN
-- SELECTの結果(1行ずつ)を変数 r に入れて回す
FOR r IN (SELECT id, price FROM products WHERE stock = 0) LOOP
-- r.id や r.price で各列の値にアクセスできる
UPDATE products SET status = 'SOLD_OUT' WHERE id = r.id;
END LOOP;
COMMIT; -- ループが終わったら一括で確定
END;
4.異常事態に備える (EXCEPTION)
「データが見つからなかった」「計算でエラーが出た」ときに、システムを急停止させないための対策です。
DECLARE
v_name users.name%TYPE;
BEGIN
SELECT name INTO v_name FROM users WHERE id = 999;
DBMS_OUTPUT.PUT_LINE('名前は' || v_name || 'です');
EXCEPTION
-- 「データが見つからない」というエラーが起きたらここへ飛ぶ
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('ユーザーが見つかりませんでしたが、処理を続行します');
v_name := 'ゲスト'; -- 代わりの値を入れておく
END;
5.処理をまとめて保存する (PROCEDURE)
一連の処理に名前をつけて保存したものです。
-- 「ポイント追加」という名前で処理を保存
CREATE OR REPLACE PROCEDURE add_point (p_id IN NUMBER) IS
BEGIN
UPDATE users SET point = point + 10 WHERE id = p_id;
COMMIT; -- 確定
END;
まとめ
今回は、実務ですぐに役立つPL/SQLの基本を学習しました。
%TYPE で箱を用意し、
SELECT INTO でデータを入れて、
FOR LOOP で1件ずつ回し、
EXCEPTION でエラーを守り、
PROCEDURE として保存して使い回す。
この5つの流れを意識するだけで、現場のコードがぐっと読みやすくなりそうだなと思いました。これからもさらに学習を深めていきたいです。
参考