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

ときおり散歩する公園で、かもがたくさん群れをなして歩いているのを見ました。珍しいと思って急いで撮ってみた写真です。こんなにたくさん歩いているとはびっくりですね。何かあったのかな～！

＜本日の題材＞
ORA-28001:パスワードが期限切れです。

今回は、昨日までOracleを使って問題なかったのに、ログインしようとしたら急に「ORA-28001:パスワードが期限切れです。」というエラーが出てログインできなくなるという事態に遭遇したことがあったので、それについて情報としてアップします。

これは、今までOracle10gを使っていたが、Oracle11gにバージョンアップした場合などによく起きることがあります。理由としては、Oracle10gまではユーザのパスワードの有効期限はデフォルトで無期限であったものが、Oracle11gからはデフォルトで180日に設定されているため、180日たったときに起きてしまうというものです。

さすがにOracle11gがリリースされてかなりの期間が立ちますので、既に知っておられる方が多いかとは思いますが、意外とこのエラーに出くわした方が多いのではないでしょうか。

セキュリティ上はたしかにパスワードを定期的に変更したほうがよいのでしょうが、開発時やテストなどでは無期限にしてアクセスしたい場合も多いと思います。

Oracle11gの場合、「プロファイル」という、ユーザーに対するシステムリソースやパスワードの制限の設定をまとめたものをユーザに割り当てることで、一括してパスワードの制御などを行うことができるようになっており、パスワードの有効期間もそこで設定されます。
ですので、デフォルトのプロファイルが設定されているユーザであれば、このときの対処法の例としては、

１）デフォルトのプロファイルのパスワード有効期限を無期限にする
２）エラーでログインできないユーザのパスワードを再設定する
３）ロックされているアカウントのロックを解除する

というかたちで対応はできることになります。

例として、SQL Plusで行う場合を以下に記載します。
DOSプロンプトからSQL Plusを起動。

sqlplus sys/[パスワード]@接続文字列 as sysdba

・デフォルトのプロファイルのパスワード有効期限を無期限にします。

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

・エラーでログインできないユーザのパスワードを再設定します。

ALTER USER ユーザ名 IDENTIFIED BY 新パスワード;

 ・ロックされているアカウントのロックを解除します。

 以上で、再度 scott でログインできるようになります。

今日は以上まで

 

にほんブログ村

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

きれいだった桜も散ってしまいました。なかなか忙しくて、ゆっくり花見をすることができませんでしたが、ほんの1週間ほどではありますが、力いっぱい咲いて私たちを感動させ、喜ばせてくれる自然とは、本当にすごいと、改めて感じるこの季節ですね。
ちなみに、桜の中で日本で圧倒的に多いのがソメイヨシノ（染井吉野）ですが、もとは江戸の染井村の造園師や植木職人によって育成されたものが、各地に接木されて増やされたもので、すべてのソメイヨシノは元をたどればかなり限られた数の原木につながり、それらのクローンだということです。（会社の同僚から教えてもらいました）

＜本日の題材＞
自律型トランザクション

今回は、Oracleにおける自律型トランザクション処理について、見てみます。

これは、ある一連のトランザクション処理を行う中で、それとは独立したトランザクションを宣言することができる機能で、メインのトランザクション処理はロールバックされても、そこだけCOMMITさせることが可能になります。
私も、あるデータ変換を行うメインのトランザクション処理を実行する中で、何らかのエラーが起きた場合にそのエラーがどういうものなのかをエラー用のログテーブルに書き出すという機能で使用したことがあります。当然、メインのトランザクションはエラーが発生したのでロールバックして何も更新されない状態にしますが、エラーの内容はログに書き出したいので、メインのトランザクションとは独立したトランザクションとして、例外処理の中でログに登録するというものです。
（ログの登録をメインのトランザクションの中に入れてしまうと、エラー時にトランザクションをロールバックすれば、ログへの登録もロールバックされてしまうため）
ほとんどオラクル社の提示していたサンプルをそのまま利用したかたちではありましたが。。。

例）
①まず、エラーログ用のテーブルを作成

CREATE TABLE ERROR_LOG (
   ERROR_CODE NUMBER
  , ERROR_MESSAGE VARCHAR2(4000)
  , BACKTRACE CLOB
  , CALLSTACK CLOB
  , CREATED_ON DATE
  , CREATED_BY VARCHAR2(30)
);

②エラー記録用のプロシージャの作成

ここで、自律型トランザクションの宣言を、PRAGMA AUTONOMOUS_TRANSACTION;で行います。

CREATE OR REPLACE PROCEDURE record_error
IS
   PRAGMA AUTONOMOUS_TRANSACTION;     -- 自律型トランザクションの宣言
   l_code  PLS_INTEGER := SQLCODE;
   l_mesg  VARCHAR2(32767) := SQLERRM;
BEGIN
   INSERT INTO error_log (error_code
                       ,  error_message
                       ,  backtrace
                       ,  callstack
                       ,  created_on
                       ,  created_by)
        VALUES (l_code
              , l_mesg
              , sys.DBMS_UTILITY.format_error_backtrace
              , sys.DBMS_UTILITY.format_call_stack
              , SYSDATE
              , USER);
 
   COMMIT;
END;
/

※DBMS_UTILITY.format_error_backtrace ファンクションはエラーが発生した行を取得し、DBMS_UTILITY.format_call_stack ファンクションは、エラー発生箇所までの実行コールスタック、どのように処理が呼び出されてきたかを示します。

③上記のエラー記録用のプロシージャを、メインのプロシージャの例外処理で呼び出します。今回は簡単な例として、部門表(DEPT)にデータを登録するという処理を作成し、エラーが発生したら処理は全てロールバックしますが、エラーログにエラーの記録を残すようにします。

BEGIN
    INSERT INTO DEPT VALUES (50, 'PLANNING', 'LOS ANGELES');
    INSERT INTO DEPT VALUES (60, 'DEVELOPMENT', 'SEATTLE');
    INSERT INTO DEPT VALUES (50, 'GENERAL AFFAIRS', 'SAN FRANCISCO');
 
COMMIT;

--例外処理
EXCEPTION
   WHEN others THEN
     record_error();
     RAISE;
END;
/

上記は、DEPT表へのデータの登録を行って、最後にCOMMIT;を実行する処理ですが、途中でDEPTNOのキー重複によりエラーが発生します。そのエラーの内容を「error_log」テーブルには記録を残すというものになります。

処理を実行すると、

DEPT表の中身は、
SELECT * FROM DEPT;

処理はロールバックされています。

ERROR_LOG テーブルの中身は、
SELECT * FROM error_log;

 エラーの内容がログとして記録されています。

また、SQL Serverにおいては、自律型トランザクションの機能はサポートしていないようです。同様な処理を行いたい場合には、TRY ～CATCHのCATCHブロックの中で、新たにトランザクションを開始してログの登録を行うなどの処理が必要です。

上記と同様の処理を行うとすると、

BEGIN TRY
  BEGIN TRAN
      INSERT INTO DEPT VALUES (50, 'PLANNING', 'LOS ANGELES');
      INSERT INTO DEPT VALUES (60, 'DEVELOPMENT', 'SEATTLE');
      INSERT INTO DEPT VALUES (50, 'GENERAL AFFAIRS', 'SAN FRANCISCO');
      COMMIT
END TRY

BEGIN CATCH
  IF @@TRANCOUNT > 0
      ROLLBACK 

  BEGIN TRAN
         SELECT ERROR_NUMBER()
                          , ERROR_SEVERITY() AS エラー重大度
                          , ERROR_MESSAGE() AS エラーメッセージ
                          , ERROR_LINE()

         INSERT INTO error_log(
                ERROR_NUMBER
              , ERROR_SEVERITY
              , ERROR_MESSAGE
              , ERROR_LINE
              )
         SELECT
                ERROR_NUMBER()
              , ERROR_SEVERITY() AS エラー重大度
              , ERROR_MESSAGE() AS エラーメッセージ
              , ERROR_LINE()
   COMMIT;
END CATCH

DEPT表の中身は、
SELECT * FROM dbo.DEPT;

ROLLBACKされて、レコードは増えていません。

ERROR_LOGテーブルの中身は、

SELECT * FROM dbo.ERROR_LOG;

エラー情報が記録されているのが確認できます。

今日は以上まで

にほんブログ村

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

 小学校の息子の卒業式に行ってきたときの写真です。
今まで、子供の卒業式には行ったことが無く、母親が行くものという認識でいたのですが、「今は父親も卒業式にはたくさん来てるのよ！」という妻の言葉もあり、参加してみたところ、確かに夫婦で卒業式に来てる方のほうが多いんですね。父親も子育てを楽しんでいる姿を感じました。もっと子育てに関心を持たなければと、いたく反省した次第です。

＜本日の題材＞
トランザクション処理

今回は、トランザクション処理について、見てみます。

トランザクション処理とは、データの一貫性を保つために、1つ以上のDML文(UPDATE、INSERT、DELETEなど)を1つの単位として管理し、実行結果が完全に成功か、完全に失敗かのどちらかになることを保証する機能です。
よく例として挙げられるのが、銀行のシステムです。例えば、AさんがB社より商品を購入して、代金として10万円をB社の口座に振り込むとした場合、処理としては、
①    Aさんの口座から10万円を減額
②    B社の口座に10万円を増額
という2つの処理を実施しなければなりません。ところが、①の処理を終えた段階で何らかのトラブルが発生して、②の処理を実行できなくなってしまった場合には、データに不整合が発生してしまいます。そのため、この2つの処理を1つのトランザクション処理としてまとめて、両方の処理が成功した場合のみこの変更処理を確定し、処理が一部でも失敗した場合には、全ての処理を取り消すということが必要になります。

なお、DBMS(データベース管理システム)には、どこからどこまでの処理が1つのトランザクションとしての区切りなのかについては、自動的にはわかりません。あくまでユーザが明確に指定して上げる必要があります。

ここで、SQL Serverの場合には、前回説明したエラー処理も含めると以下のようにすることができます。

BEGIN TRY
    BEGIN TRAN
        (DML文の処理)
    COMMIT
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK
END CATCH

@@TRANCOUNT関数は、現在の接続で実行された BEGIN TRANSACTION ステートメントの数を返す関数で、トランザクションが1つ以上開始されている場合、1以上の数字になります。トランザクションが開始されているかどうか確認したいときに使用します。上記では、トランザクションを設定していることは分かっているので、基本的に必要ありませんが、念のため記述しています。

下記の例は、Aさんの口座から100000をマイナスし、B社の口座に100000をプラスする処理をトランザクションで実施する内容ですが、Aさんの口座から減額した後で、RAISERROR関数にてエラーを起こします。そのためトランザクション開始後、エラーが起きるまでに行った更新処理がロールバックされて、元に戻ることを確認します。

BEGIN TRY
    BEGIN TRAN
        UPDATE accounts SET
            balance = balance - 100000
       WHERE account_number = 1234567;

      RAISERROR('RAISERRORにより強制終了',11,1);

      UPDATE accounts SET
          balance = balance + 100000
       WHERE account_number = 9876543; 
     COMMIT
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
         ROLLBACK

     SELECT ERROR_NUMBER() AS エラー番号
   , ERROR_SEVERITY() AS エラー重大度
   , ERROR_MESSAGE() AS エラーメッセージ
   , ERROR_LINE() AS エラー行
END CATCH

処理前後の口座の金額を確認すると、上記の処理前も、処理後も同じであることが確認できました。ですので、このトランザクション処理は確実にロールバックされたということです。

なお、OracleやDB2については、データベースへ接続して最初のSQLの実行から自動的にトランザクションが開始されるため、SQL Serverのような「BEGIN TRANSACTION」のようなコマンドは不要です。その後、COMMITか、ROLLBACKによりトランザクションが終了するかたちになります。

以下は、上記の例をOracleで同様に実施した場合の例。

DECLARE
  Trans_ERR EXCEPTION;             -- ユーザー定義例外の宣言
BEGIN
      UPDATE accounts SET
          balance = balance - 100000
       WHERE account_number = 1234567;

       RAISE Trans_ERR;                     -- ユーザー定義例外の呼出し

      UPDATE accounts SET
         balance = balance + 100000
       WHERE account_number = 9876543;

     COMMIT;
EXCEPTION
    WHEN Trans_ERR THEN
       ROLLBACK;
       DBMS_OUTPUT.PUT_LINE('強制的にエラーを発生、ロールバックしました');

END;
/

処理前後の口座の金額は、変わっていないことを確認しました。

ちなみに、MySQL、PostgreSQLでのトランザクションの開始コマンドは、それぞれ以下になるようです。
MySQL： START TRANSACTION
PostgreSQL： BEGIN TRANSACTION

今日は以上まで

にほんブログ村

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

先日、東京駅のすぐそばのJPタワー・KITTE 4FのHall&Conferenceで行われたSecurity Days 2015のセミナーに、今年も参加してきました。目的はITコーディネータのポイント取得を兼ねてセキュリティに関する最新の情報収集。相変わらず大きな問題である標的型攻撃対策、内部犯行を含めての情報漏洩対策など、様々な内容が盛りだくさん。その中で、2015年10月から始まるマイナンバー制度や、個人情報保護法改正に向けた内容など、いろいろと気になる点もありました。「個人情報の保護レベルを世界水準に合わせよう」ということを指摘されている、新潟大学の鈴木正朝先生の講演が、私としては結構インパクトがありましたね。日本よりも規制の厳しい欧米の水準に合わせないと、ゲノムなど含めて世界の情報を日本に集めることができなり、産業的に大きな問題になるということを言っておられ、説得力がありました。

写真は、そのビルの屋上庭園「KITTEガーデン」から撮った東京駅の写真です。まだ風がちょっと寒かった！

＜本日の題材＞
例外処理(エラー処理) (SQL Server編)

前回、前々回は、ORACLEの例外処理について取り上げました。
今回は、SQL Serverの例外処理について、見てみます。

SQLServerのTransact-SQLでは、SQLServer2005以降において例外処理がサポートされています。記述の仕方は MicrosoftのVisual C#、Visual C++ 言語での例外処理に似ていて、Transact-SQL ステートメントのグループを TRY ブロックで囲み、TRY ブロック内でエラーが発生すると、CATCH ブロックで囲まれた別のステートメントのグループに制御が渡されるというものです。

構文は以下：

BEGIN TRY
  (処理ロジック)
END TRY
BEGIN CATCH
  (ERROR 処理ロジック)
END CATCH

下記の例は、「STEP 1」表示の後、INSERT文でデータを登録するときに、桁あふれのエラーにより処理が中断され、「STEP 2」を表示することなく、エラーがCATCHされてエラー処理（エラーメッセージの表示）が実行されることを示します。

BEGIN TRY
  SELECT 'STEP 1'
  INSERT INTO dbo.社員マスタ(社員番号,社員名,拠点)
  VALUES(123456, 'TEST_MENBER', 'シカゴ');

  SELECT 'STEP 2'
END TRY
BEGIN CATCH
  SELECT
             ERROR_NUMBER() AS エラー番号
           , ERROR_SEVERITY() AS エラー重大度
           , ERROR_MESSAGE() AS エラーメッセージ
           , ERROR_LINE() AS エラー行
END CATCH
GO

ここで、エラーに関する情報は、”ERROR_” で始まるシステム関数で取得することができます。
ERROR_NUMBER()         --            エラー番号
ERROR_MESSAGE()       --            エラーメッセージ
ERROR_SEVERITY()       --            エラーの重大度レベル
ERROR_STATE()               --            エラーの状態番号
ERROR_LINE()                   --            エラーが発生した行番号
ERROR_PROCEDURE()  --            エラーが発生したストアドプロシージャ                                                                         またはトリガー名

※エラーの重大度レベルについては、以下のMSのHPを参照
  https://msdn.microsoft.com/ja-jp/library/ms164086.aspx

ただし、この TRY　～ CATCH ステートメントは、以下の種類のエラーはCATCHできないということです。
・構文エラーなどのコンパイルエラー
・再コンパイルで発生するエラー（コンパイル後の名前の遅延解決により発生するオブジェクト名の解決エラーなど）

以下の例は、実際には存在しないテーブルを抽出するSQLを実行してコンパイルエラーが発生するケースで、このときにはTRY　～ CATCHではキャッチされず、実行したアプリ（例えばSQLServer Management Studio）にエラーが返されます。

BEGIN TRY
    PRINT N'処理の実行開始';
    SELECT * FROM 存在しないテーブル;
END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO

この種のエラーは、エラーの発生場所と同一の実行レベルの TRY...CATCH 構造では処理されません。上記のような場合でもエラー処理をするためには、エラーが発生するコードを TRY ブロック内部の独立したバッチで実行、すなわち、ストアドプロシージャに記述するか、sp_executesql を使用して動的な Transact-SQL ステートメントを実行するかたちにすることで、低いレベルで発生したエラーをTRY...CATCH 構造でCATCHできるということです。以下が動的SQLを使ったその例になります。

BEGIN TRY
    PRINT N'処理の実行開始';
        DECLARE @sql VARCHAR(4000)
        SET @sql = 'SELECT * FROM 存在しないテーブル'
        EXEC(@sql)
END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO

実行結果は、上記のようにエラーをCATCHして、BEGIN CATCHのエラー処理が実行されています。

今日は以上まで

にほんブログ村

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

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

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

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

＜本日の題材＞
例外処理(エラー処理) (Oracle編２)

前回は、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;
/

これは、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;
/

また、ユーザーが独自に「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;
/

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

今日は以上まで

にほんブログ村

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

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

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

＜本日の題材＞
例外処理(エラー処理) (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で割ろうとしたときに出るエラーです。

これについて、下記のように例外処理部を追加してエラーのときの対応を明記することで、異常終了させずに正常終了させることができます。例外処理部は、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;
/

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

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

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

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;
/

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

今日は以上まで

にほんブログ村

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

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

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

今回は、テーブルレイアウト上、キー毎に複数行あるデータを、横に並べて集計しながら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コーディネータのシュウです。

近くの神社に息子と寄ってみました。三が日は毎年人が多いので、ずらして行ったところ、あまり人も多くなくて、すぐにお詣りできました。
ラッキー！

新年あけましておめでとうございます。
旧年中は、大変お世話になりました。
つたない文章ながら、少しずつアクセス数も増えてきて、大変うれしく思っております。
今年も、へたりながらも頑張りますので、よろしくお願いします。

今日は以上まで

にほんブログ村