例外処理(エラー処理)(ORACLE編2)

ITコーディネータのシュウです。

1423578385215

娘がディズニーランドに行ってきて、撮ってきた写真です。
昨年、社会現象にもなったアナと雪の女王をテーマにした、スペシャルイベント「アナとエルサのフローズンファンタジー」。入口のところで大きく宣伝してたのを帰る時に撮ったと言ってました。シンデレラ城を舞台にしたプロジェクションマッピング「ワンス・アポン・ア・タイム~スペシャルウィンターエディション」は座席指定券をもらう抽選に当たらなかったので、いい席では見れなかったと残念がってました。

4月からチケットが少し値上がりしてしまうのは残念だけど、それでも何度でも行ってみたくなる魅力がありますよね!

さて、話は変わりますが、dbSheetClientのユーザー事例に紀文食品様が載りました。おでん用の商品などに「紀文」の焼印が押されていることでも有名な、練り製品ではトップシェアを持つ会社ですね。
dbSheetClient
で念願の「品質検査管理システム」を構築したとのこと。詳しくは、こちらを参照してみてください。
http://www.newcom07.jp/dbsheetclient/usrvoice/usrcase8.html

<本日の題材>
例外処理(エラー処理) (Oracle編2)

前回は、ORACLEの例外処理についての一般的記述方法や制御について取り上げてみました。今回は、ORACLEの例外の種類とそれに応じた記述方法について、見てみます。
例外は内部例外とユーザー定義例外にわけられ、内部例外はさらに事前定義の内部例外と無名の内部例外に分けられます。

事前定義の内部例外には、前回の例で示した
・数値データをゼロで割ろうとした「ZERO_DIVIDE」例外(ORA-01476)
・SELECT INTO文で複数の行を戻したときの「TOO_MANY_ROWS」例外(ORA-01422)
・検索の結果1行も戻されない「NO_DATA_FOUND」例外(ORA-01403)
などいろいろあります。

こういったものは、それぞれに名前が定義されているため、例外処理部で個別に処理を指定することができますが、名前が付けられていない無名の内部例外については、宣言部で明示的に名前を付けてあげ、さらに PRAGMA EXCEPTION_INITでORACLEのエラーと例外名を関連付ける必要があります。

例)
DECLARE
  KETA_ERR EXCEPTION;
  PRAGMA EXCEPTION_INIT(KETA_ERR, -01438);
BEGIN
  INSERT INTO DEPT(DEPTNO, DNAME) VALUES(501, '企画部');
  COMMIT;
EXCEPTION
  WHEN KETA_ERR THEN
     DBMS_OUTPUT.PUT_LINE('桁数が大きすぎます');
  WHEN OTHERS THEN
     DBMS_OUTPUT.PUT_LINE(sqlcode);
     DBMS_OUTPUT.PUT_LINE(sqlerrm);
END;
/
plsql_ユーザ定義例外0

これは、DEPT表にデータを登録する際、DEPTNOは2桁の数値でなければならないところ、3桁を登録しようとしたときに出る無名の内部例外、「ORA-01438:この列に許容される指定精度より大きな値です」に名前を付けて、それを例外処理部で使っている例です。

なお、OTHERSハンドラを使用すると、例外処理部で例外名が指定されていないすべての例外を処理することができるため、上記の例では「KETA_ERR」例外以外の例外が発生した場合には、OTHERSハンドラの処理が行われるようになります。
そのときにどのような例外が発生したのかを確認するのに、SQLCODE関数(エラー番号を戻す関数)、SQLERRM関数(エラーメッセージを戻す関数)のようなエラー報告関数をすると便利です。

さて、ユーザー定義例外というのは、ORACLEのエラーではなく、ユーザーが作成する例外になります。

下の例は、FOR LOOP文の繰り返しの中で、ある数量をマイナスしていき、その数値が10未満になった場合には、ユーザー定義例外を呼出して、エラーとして処理するというものです。

例)
DECLARE
  Amount  NUMBER;
  Amount_ERR EXCEPTION;                    -- ユーザー定義例外の宣言
BEGIN
  Amount := 20;
  FOR lc IN 1..30 LOOP
    Amount := Amount – 1;
    IF Amount < 10 THEN
       RAISE Amount_ERR;                     -- ユーザー定義例外の呼出し
    END IF;
  END LOOP;
EXCEPTION
  WHEN Amount_ERR THEN
     DBMS_OUTPUT.PUT_LINE(‘ユーザー定義例外発生: 数量 = ‘||Amount);
END;
/
plsql_ユーザ定義例外1

また、ユーザーが独自に「ORA-xxxx」形式のエラーコードとエラーメッセージを定義して、エラーを表示させるような場合には、RAISE_APPLICATION_ERROR を使用します。
このときにユーザー定義のエラーとして使用が許可されている番号は ORA-20000~ORA-20999 までです。

例)
  RAISE_APPLICATION_ERROR(-20001, 'エラーが発生しました');

また、例外が発生して処理が例外処理部に移ると、制御は実行部には戻らないため、処理の途中でブロックは終了してしまいます。しかし、その後も処理を継続したい場合には、ブロックのネストを使用することで可能になります。

例)
DECLARE
  Amount  NUMBER;
  Amount_ERR EXCEPTION;
BEGIN
  Amount := 20;
  FOR lc IN 1..10 LOOP
    BEGIN                                       -- ブロックのネスト
      Amount := Amount - 2;
      IF Amount = 10 THEN
        RAISE Amount_ERR;
      ELSE
        DBMS_OUTPUT.PUT_LINE('Amount = '||Amount);
      END IF;
    EXCEPTION                                  -- ネストされたブロック内での例外処理
      WHEN Amount_ERR THEN
      DBMS_OUTPUT.PUT_LINE('ユーザー定義例外発生:数量 = '||Amount);
    END;
  END LOOP;
END;
/
plsql_ユーザ定義例外ブロックネスト

上記は、Amountの値が10になった時点で例外が発生していますが、ネストされたブロックの中での例外のため、その後のLOOPの処理が継続されていることが確認できます。

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

例外処理(エラー処理) (Oracle編)

ITコーディネータのシュウです。

DSC_2080_2

前回に引き続き、加須花崎水上公園で撮った写真です。用水路に鳥が泳いでいました。おそらくカルガモだと思います。スイスイーと気持ちよさそうに泳いでました。たまに行列を作って行進したりする姿も見かけますよね。

そういえば、「鴨の水搔き」という言葉がありますが、《気楽そうに浮かんでいる鴨も、水面下では水かきを絶えず動かしているところから》人知れない苦労があることのたとえで使われる言葉なんですよね。
見習うところも多いかもしれませんね。

<本日の題材>
例外処理(エラー処理) (Oracle編)

今回は、処理の途中で例外(エラー)が発生した場合の処理を記述する例外処理(エラー処理)について取り上げてみたいと思います。ORACLEのPL/SQL とSQL ServerのTransact SQLでは記述の仕方が異なるため、今回はまずORACLEについて見てみたいと思います。

PL/SQLでは、実行時のエラーや警告のことを例外と言いますが、もし、処理の途中で例外が発生したときに、PL/SQLブロックに実行部しかなかった場合、PL/SQLは異常終了(OS側に制御が戻る)します。

例えば、ある商品について、前年の出荷実績に対する今年の予算の比率を計算で出す処理をPL/SQLで記述した場合、新商品のためなどの理由で前年の出荷実績が 0 であった場合には、下記のようになります。

DECLARE
  Yosan  NUMBER := 12000;
  Zennen_Jisseki  NUMBER := 0;
  Tai_Zennenhi NUMBER;
BEGIN
  Tai_Zennenhi := 100.0 * Yosan / Zennen_Jisseki;
  DBMS_OUTPUT.PUT_LINE('予算/前年実績 = ' || Tai_Zennenhi||'%');
END;
/

上記を実行すると(SQL Plus)、下記のような ORA-01476 のエラーが出て異常終了します。これは数値を0で割ろうとしたときに出るエラーです。
plsql_err

これについて、下記のように例外処理部を追加してエラーのときの対応を明記することで、異常終了させずに正常終了させることができます。例外処理部は、BEGIN ~ END; の間に、EXCEPTION句を入れて、EXCEPTION ~ END; の間に例外処理を記述します。

DECLARE
  Yosan  NUMBER := 12000;
  Zennen_Jisseki  NUMBER := 0;
  Tai_Zennenhi NUMBER;
BEGIN
  Tai_Zennenhi := 100.0 * Yosan / Zennen_Jisseki;
  DBMS_OUTPUT.PUT_LINE('予算/前年実績 = ' || Tai_Zennenhi||'%');
EXCEPTION
  WHEN ZERO_DIVIDE THEN
      DBMS_OUTPUT.PUT_LINE('0で数値を割ろうとしています');
      Tai_Zennenhi := NULL; 
  WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('他のエラーが発生しました');
      Tai_Zennenhi := NULL;
END;
/
plsql_例外処理JPG

上記は、予算を前年実績の0値で割ろうとしたときに、「ZERO_DIVIDE」という例外が発生し、EXCEPTION以下の例外処理部に処理が移り、「0で数値を割ろうとしています」というメッセージを出して処理を終了しています。

例外が発生したときの制御は以下のようになります。

plsql_例外処理制御

 ただ、上記のように 0 で割る可能性があらかじめわかっている場合は、処理1のところにエラーチェックを行うことで例外を回避できます。

DECLARE
  Yosan  NUMBER := 12000;
  Zennen_Jisseki  NUMBER := 0;
  Tai_Zennenhi NUMBER;
BEGIN
  Tai_Zennenhi := CASE Zennen_Jisseki WHEN 0 THEN NULL
                                             ELSE 100.0 * Yosan / Zennen_Jisseki END;

  IF Tai_Zennenhi IS NOT NULL THEN
    DBMS_OUTPUT.PUT_LINE('予算/前年実績 = ' || Tai_Zennenhi||'%');
  ELSE
    DBMS_OUTPUT.PUT_LINE('前年実績はありませんでした');
  END IF;
 
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('他のエラーが発生しました');
      Tai_Zennenhi := NULL;
END;
/

plsql_例外処理回避

 この場合、予算の対前年実績比率を出す際に、CASE文で 0 で割るのではなく NULLに設定するかたちにしており、例外処理は発生していません。

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

複数行のデータを集計して横展開

ITコーディネータのシュウです。

DSC_2091

 埼玉県加須市にある、加須花崎水上公園の散歩道です。プールと自然観察園の間のほうの道で撮った写真になります。水上公園ということで、夏のプールが注目されますが、お花見やバーベキュー、また自然観察園ではいろいろな木々やウッドデッキ、野鳥の観察小屋などもあり、季節によっていろいろな表情が見れるので、私はとても気に入っています。

<本日の題材>
複数行のデータを集計して横展開

今回は、テーブルレイアウト上、キー毎に複数行あるデータを、横に並べて集計しながら1行で表示させたい場合のSQLについて取り上げてみたいと思います。(以前、カンマ区切りで横並びにするケースについて、SQL Serverの場合にFOR XML句を取り上げたことがありますが、今回はよく使う例としてCASE文を使用します)

例えば、部品別の日別の仕入数量のデータが、部品コード、年月日、数量というようなレイアウトでDBに登録されている場合に、部品別に、月を横に並べて仕入数量の月別合計値を表示させるようなケース。(SQL Serverで検証)

DB上は以下のようにデータが登録されています。

SELECT
    部品コード
  , SUBSTRING(年月日,1,6) AS 年月
  , SUM(数量)  AS 仕入数量
  FROM 部品発注表
 GROUP BY 部品コード, SUBSTRING(年月日,1,6)
 ORDER BY 部品コード, SUBSTRING(年月日,1,6);

部品発注データ

これを、部品コード毎で、月を横に並べて仕入数量を表示させたい場合、以下のようなSQLを実行します。

SELECT
  A.部品コード
, SUM(CASE SUBSTRING(A.年月日,5,2) WHEN '04' THEN A.数量 ELSE 0 END) AS "4月"
, SUM(CASE SUBSTRING(A.年月日,5,2) WHEN '05' THEN A.数量 ELSE 0 END) AS "5月"
, SUM(CASE SUBSTRING(A.年月日,5,2) WHEN '06' THEN A.数量 ELSE 0 END) AS "6月"
, SUM(CASE SUBSTRING(A.年月日,5,2) WHEN '07' THEN A.数量 ELSE 0 END) AS "7月"
, SUM(CASE SUBSTRING(A.年月日,5,2) WHEN '08' THEN A.数量 ELSE 0 END) AS "8月"
, SUM(CASE SUBSTRING(A.年月日,5,2) WHEN '09' THEN A.数量 ELSE 0 END) AS "9月"
, SUM(CASE SUBSTRING(A.年月日,5,2) WHEN '10' THEN A.数量 ELSE 0 END) AS "10月"
, SUM(CASE SUBSTRING(A.年月日,5,2) WHEN '11' THEN A.数量 ELSE 0 END) AS "11月"
, SUM(CASE SUBSTRING(A.年月日,5,2) WHEN '12' THEN A.数量 ELSE 0 END) AS "12月"
, SUM(CASE SUBSTRING(A.年月日,5,2) WHEN '01' THEN A.数量 ELSE 0 END) AS "1月"
, SUM(CASE SUBSTRING(A.年月日,5,2) WHEN '02' THEN A.数量 ELSE 0 END) AS "2月"
, SUM(CASE SUBSTRING(A.年月日,5,2) WHEN '03' THEN A.数量 ELSE 0 END) AS "3月"
  FROM [dbo].[部品発注表] A
  JOIN [dbo].[年度マスタ] B ON SUBSTRING(A.年月日,1,6) = B.年月
 WHERE B.年度 = '2014'
 GROUP BY A.部品コード
 ORDER BY A.部品コード;

部品発注横並び

このように、部品コード毎に、4月~3月まで月毎の仕入数量を横並びで表示させることができました。

上記では年度マスタを以下のように設定しています。
SELECT * FROM [dbo].[年度マスタ]
 ORDER BY 年度,表示順;

年度マスタ

 ORACLEでも同様に行うことができますが、上記のSUBSTRING関数は、ORACLEではSUBSTR関数となります。そこだけ注意すれば同様に実行できます。

また、上記の例は、数量の場合でしたが、文字項目を横並びにしたい場合もあると思います。

例えば、下記のように、日本の様々なランキングをデータとして持つテーブルがあるとします。

SELECT
  A.区分NO
, B.区分名
, A.名称
, A.数値
, B.単位
  FROM dbo.ランキング A
  JOIN dbo.ランキング区分 B ON A.区分NO = B.区分NO
  ORDER BY A.区分NO, A.NO;

日本ランキング
※データはもっとたくさんありますが、表示は上記まで

これらの都市人口や川の長さ、湖の面積、山の高さなどの区分ごとに、ランキングの1位から10位までを横並びで表示させたいとします。

まず、各区分毎にランキング情報を抽出するのは、以下:

SELECT
  A.区分NO
, B.区分名
, A.名称
, RANK() OVER (PARTITION BY A.区分NO ORDER BY A.数値 DESC) RANK順位
, A.数値
, B.単位
  FROM dbo.ランキング A
  JOIN dbo.ランキング区分 B ON A.区分NO = B.区分NO
  ORDER BY A.区分NO, RANK順位;

日本ランキング_ランク順
※データはもっとたくさんありますが、表示は上記まで

これを各区分毎に横並びで表示するのは、例えば以下のSQLのように、MAX関数などを使用して集約したかたちにします。

 SELECT
  C.区分NO
, C.区分名
, MAX(CASE C.RANK順位 WHEN 1 THEN C.名称 ELSE '' END)
  AS "1位"
, MAX(CASE C.RANK順位 WHEN 2 THEN C.名称 ELSE '' END)
  AS "2位"
, MAX(CASE C.RANK順位 WHEN 3 THEN C.名称 ELSE '' END)
  AS "3位"
, MAX(CASE C.RANK順位 WHEN 4 THEN C.名称 ELSE '' END)
  AS "4位"
, MAX(CASE C.RANK順位 WHEN 5 THEN C.名称 ELSE '' END)
 AS "5位"
, MAX(CASE C.RANK順位 WHEN 6 THEN C.名称 ELSE '' END)
  AS "6位"
, MAX(CASE C.RANK順位 WHEN 7 THEN C.名称 ELSE '' END)
  AS "7位"
, MAX(CASE C.RANK順位 WHEN 8 THEN C.名称 ELSE '' END)
  AS "8位"
, MAX(CASE C.RANK順位 WHEN 9 THEN C.名称 ELSE '' END)
  AS "9位"
, MAX(CASE C.RANK順位 WHEN 10 THEN C.名称 ELSE '' END)
  AS "10位"
 FROM
 (SELECT
   A.区分NO
 , B.区分名
 , A.名称
 , RANK() OVER (PARTITION BY A.区分NO ORDER BY A.数値 DESC) RANK順位
 , A.数値
    FROM dbo.ランキング A
    JOIN dbo.ランキング区分 B ON A.区分NO = B.区分NO
 ) C
 GROUP BY C.区分NO, C.区分名
 ORDER BY C.区分NO;

日本ランキング_ランク順横並び

 ※最近の統計では、最上川と木曽川はどちらも229kmで同じ7位となっているようですが、以前は最上川が7位、木曽川が8位とされていたようです。今回は、取りあえずそちらを使っています。

上記は、ORACLEでもまったく同様に実行することができます。

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

相関副問合せを利用したデータの更新

ITコーディネータのシュウです。

DSC_2141_2

知人の方から頂いた、千葉港から富士山の方角を見た夕日の写真です。右下のほうに富士山が写ってます。晴れた日には、埼玉から富士山が見えるのですが、千葉からも見えるんですね。はじめて知りました。埼玉からのほうが少し大きく見えるような気がしますが、さすが日本一の高さを誇る富士山です。

久しぶりの投稿になります。また、開発の仕事が忙しくなってきました。へたり気味ですが、頑張って行くぞ!

そういえば、dbSheetClientのユーザー事例にウシオ電機様が載りました。様々なランプをはじめとする光製品の開発・販売などで世界的なトップシェア商品を多数持っている企業ですね。
詳しくは、こちらを参照してみてください。
http://www.newcom07.jp/dbsheetclient/usrvoice/usrcase7.html

<本日の題材>
相関副問合せを利用したデータの更新

前回、EXISTS句について取り上げましたが、そのときに、相関副問合せでの例を示しました。今回は、更新の際に、相関副問合せを利用する場合を取り上げてみたいと思います。
(SQL Serverで検証)

通販システムなどで顧客管理をする際に、顧客の受注から顧客のランクを判別して、その顧客のランクによってサービスなどを区別する場合があると思います。
そのための項目「顧客ランク」を顧客マスタに追加したとします。

そして、例えば以下のように顧客のランクを定めて、あるタイミング毎に夜間バッチ処理にて更新を行うとします。
直近3ヶ月以内に
・1万円以上購入した方を「A」ランク
・5千円以上1万円未満の方を「B」ランク
・2千円以上5千円未満の方を「C」ランク
・それ以外(受注はあるが2千円未満)の方を「D」ランク
直近3ヶ月以内に受注がない方は NULL

直近3ヶ月以内の顧客の購入金額を検索してランクを区別する場合のSELECT文は、例えば以下のようにできます。

--①SQL文
SELECT顧客番号
      , SUM(決済金額) AS 購入金額
      , CASE WHEN SUM(決済金額) >= 10000 THEN 'A'
             WHEN SUM(決済金額) >= 5000 THEN 'B'
             WHEN SUM(決済金額) >= 2000 THEN 'C'
             ELSE 'D' END AS ランク
  FROM dbo.受注テーブル
 WHERE 受注日 >= DATEADD(MONTH, -3, GETDATE())
 GROUP BY 顧客番号;
--(10 行処理されました)

顧客ランク抽出

この抽出結果を元に顧客マスタの顧客ランクを更新するSQLとしては、例えば以下のようにできます。

--②SQL文
UPDATE顧客マスタ SET
   顧客ランク =
          (SELECT A.ランク
              FROM (SELECT 顧客番号
                                , SUM(決済金額) AS 購入金額
                                , CASE WHEN SUM(決済金額) >= 10000 THEN 'A'
                                                WHEN SUM(決済金額) >= 5000 THEN 'B'
                                                WHEN SUM(決済金額) >= 2000 THEN 'C'
                                                ELSE 'D' END AS ランク
                                  FROM dbo.受注テーブル
                                WHERE 受注日 >= DATEADD(month, -3, GETDATE())
                                GROUP BY 顧客番号) A
            WHERE A.顧客番号 =顧客マスタ.顧客番号
            );

相関副問合せを使用し、副問合せで抽出した顧客番号と顧客マスタの顧客番号が一致する行については、副問合せで抽出したランクの値で顧客ランクのデータを更新するようにしています。
また、一致する顧客番号が副問合せの抽出にないレコードについては、顧客ランクはNULLに更新されます。

例えば、現在の顧客ランクが以下のような場合:
--③SQL文(顧客ランクがNULL以外のデータを抽出)

SELECT顧客番号,顧客ランク
  FROM dbo.顧客マスタ
 WHERE 顧客ランク IS NOT NULL
 ORDER BY 顧客番号;
--(8行処理されました)

顧客ランク_現状

上記の②SQLを実行すると

UPDATE顧客マスタ SET
   顧客ランク =
          (SELECT A.ランク
              FROM (SELECT 顧客番号
                                , SUM(決済金額) AS 購入金額
                                , CASE WHEN SUM(決済金額) >= 10000 THEN 'A'
                                                WHEN SUM(決済金額) >= 5000 THEN 'B'
                                                WHEN SUM(決済金額) >= 2000 THEN 'C'
                                                ELSE 'D' END AS ランク
                                  FROM dbo.受注テーブル
                                WHERE 受注日 >= DATEADD(month, -3, GETDATE())
                                GROUP BY 顧客番号) A
            WHERE A.顧客番号 =顧客マスタ.顧客番号
            );

現在の顧客ランクを確認する(NULL以外のレコード抽出)
--上記③SQL文を実行した結果

顧客ランク_更新後
--(10 行処理されました)

更新前は8件ありましたが、6行目~8行目のレコードは、顧客ランク「A」~「D」の抽出条件からはずれたためにNULLに更新されています。結果として、①SQL文で実行した10件が顧客ランクが設定されたかたちになります。

今回のケースは上記の方法でよいですが、②SQLで、副問合せで抽出される顧客番号のレコードのみを更新したいような場合には、WHEREの条件として、下記のようにEXISTS句を使用して条件を絞る必要があります。

WHERE EXISTS
   (SELECT 'x'
       FROM (SELECT 顧客番号
                         , SUM(決済金額) AS 購入金額
                         , CASE WHEN SUM(決済金額) >= 10000 THEN 'A'
                                          WHEN SUM(決済金額) >= 5000 THEN 'B'
                                          WHEN SUM(決済金額) >= 2000 THEN 'C'
                                          ELSE 'D' END AS ランク
                          FROM dbo.受注テーブル
                        WHERE 受注日 >= DATEADD(month, -3, GETDATE())
                         GROUP BY 顧客番号) A
    WHERE A.顧客番号 =顧客マスタ.顧客番号
 );

上記の条件を設定したかたちでUPDATEを行うと、10件のみが更新されることになります。

ですので、今回の処理についても、一旦全レコードの顧客ランク項目を全て「NULL」に更新してから、上記のWHERE EXISTS句を付けたUPDATE文で顧客ランクを設定するというかたちでもいいかと思います。

上記の内容は、ORACLEでも同様になります。

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村