Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
Help us understand the problem. What is going on with this article?

PL/SQLの基礎を4日でマスターする【2日目】後編

More than 1 year has passed since last update.

さて、二日目の後編です。後編では、カーソル処理で取り出した現在の行に対して、更新処理を行う方法と、例外処理を記載する方法を学習したいと思います。
また、デバックを行う際に役立つストアド・プログラムをいくつか記載しておきます。

0. Agenda

  1. Where Current of 句
  2. カーソルの種類
  3. カーソルの属性
  4. 例外処理
  5. デバック時に役立つストアド・プログラム

1. Where Current of 句

カーソル処理を行っている場合に、取り出した現在の行に対して更新処理/削除処理を行いたい場合があるかと思います。
その場合には、Where Current of句を用います。

WhereCurrenOf文
DECLARE 
  CURSOR emp_cur IS SELECT sal, empno FROM emp WHERE deptno = 10
  FOR UPDATE;

BEGIN
  FOR emp_rec IN emp_cur LOOP
    IF emp_rec.sal < 2500 THEN 
      UPDATE emp SET sal = sal+100 WHERE CURRENT OF emp_cur;
    END IF;
  END LOOP;
END;
/

Where Current Of文のメリットはアクセスが高速であることと、コーディングが簡素化されることです。FOR-カーソルを扱う場合は、ぜひ活用するべきだと思います。

2. カーソルの種類

種類 概要
明示カーソル 明示的にオープンからクローズまでを制御するカーソル
暗黙カーソル 明示カーソル以外のすべてのカーソルで使用されるカーソル

3. カーソル属性

明示カーソル属性のなかでも、主に利用するものを記載しておきます。

種類 概要
%NOTFOUND 直前のFETCHが行を取り出せた場合はFALSE,取り出せない場合はTRUEを戻す。
%FOUND %NOTFOUNDの逆
%ROWCOUNT これまでに取り出された行数
%ISOPEN カーソルがOPNEしている場合はtrue,していない場合はFALSEを戻す。

また、暗黙カーソル属性を使用する場合、暗黙カーソル属性の前に、SQL<暗黙カーソル属性>を記述します。
暗黙カーソルは、直前に実行されたSQLにしか適用できない。

4. 例外処理

例外処理は、EXCEPTION 句で囲み、どの例外が起こった場合に、どのような処理を行うかを記載します。
詳しい例外の種類を書いていくときりがないので、今回は、ユーザー定義例外について取り上げます。
ユーザー定義例外は、特定例外をユーザーが独自に定義することができるというものです。
例えば、給料を100以下に更新しようとした場合、例外を発生させて更新処理を未然に防ぐ、
といった使い方が可能です。
それでは、ユーザー定義例外を記述する方法を見ていきましょう。

ユーザー定義例外
/*
  ユーザー定義例外 -宣言部
  <例外名> EXCEPTION;

  ユーザー定義例外の呼び出し - 実行部
  RAISE <例外名>;
*/

DECLARE
  no NUMBER ;
  err EXCEPTION; 
BEGIN
  no := 90;
  IF no <= 90 THEN
    RAISE err;
  END IF;
EXCEPTION
  WHEN err THEN
  DBMS_OUTPUT.PUT_LINE('値を90未満にはできません。');
END;
/

5. デバック時に役立つストアド・プログラム

USER_SOURCEビュー
USER_SOURCEビューでは、ユーザーが所有するストアド・オブジェクトのソースコードを表示することができます。

列名 概要
NAME オブジェクト名
TYPE オブジェクト型(FUNCTION, PROCEDUREなど)
LINE このソースの行番号
TEXT 格納されたオブジェクトのソースコード

エラーの詳細を確認する
| コマンド | 概要 |
|:--------------------|:--------------------------------------------------|
|SHOW ERRORS | 最後に作成または変更された、プログラムのコンパイルエラーを表示する。|
|USER ERRORS ビュー | SHOW ERRORS と同じ情報が表示される。|
|SHOW ERRORS<オブジェクトの種類><名前>| 指定したプログラムのコンパイルエラーを表示する。|

NVL関数による置換
NVL関数は、指定した列の値がnullだった場合に、指定した代わりの値を代入してくれる関数です。

NVL関数
/*
  NVL(列名, 指定した列の値がnullの場合に置き換える値)
*/
CREATE OR REPLACE PROCEDURE debug_test IS
  sal_col emp.sal%TYPE;
  comm_col emp.comm%TYPE;
  total_sal emp.sal%TYPE;
BEGIN
  SELECT sal, NVL(comm,0) INTO sal_col, comm_col FROM emp WHERE empno = 7369;
  total_sal := sal_col + comm_col;
  DBMS_OUTPUT.PUT('total_sal');
END;
/

【参考文献】
『プロとしてのOracle PL/SQL入門』 アシスト教育部 2017年 第3版

samurai_se
アニオタ声豚ラーメンレンジャー(本職)兼SE(副業)の勉強のアウトプット用アカウントです。 座右の銘は「人事を尽くして天命を待つ」「降りかかる火の粉は完全消火、ついでに損害賠償請求。保険金がめておかわり3杯」
https://www.untitledreport.tokyo/
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away