Oracle SQL (その他・Tips:INSERT文でのRETURNING句、DATE型の内部構造･･･)

Oracle SQL その他・Tips

RETURNING・・・INTO
INSERTでのRETURNING...INTO句
DATE型の考察
DATE型の内部構造についての考察
外部結合演算子(+)とANSI規格
「LEFT JOIN」を使ったSQL文
NULLについて再考
「NULL」の取扱ではまったこと
SUBSTR関数について
SUBSTR関数でVBのRight、Left関数の様な使い方
アカウントロック解除
ログインエラーでのアカウントロック解除の対処方法

RETURNING・・・INTO

INSERT文にRETURNING句をつけることでPL/SQLの変数に挿入した値が取得できます。ただし、INSERT ... SELECT の構文では使用できません。（INSERT ... SELECT では複数の行が挿入されるため当たり前といえばそうですが）このRETURNING句を利用できるのは、バージョンがOracle10g以降の様です。以下に例を示します。

SQL> VAR v1 NUMBER;
SQL> VAR v2 CHAR(40);
SQL> INSERT INTO TM_得意先(得意先コード, 得意先名)
  2  VALUES(1000, 'テスト得意先')
  3  RETURNING 得意先コード, 得意先名 INTO :v1, :v2;
 
1行が作成されました。
 
SQL> PRINT
 
        V1
----------
      1000
 
 
V2
--------------------------------------------------------------------------------
テスト得意先
 
SQL>

PL/SQLでの実行ですので、RETURNING句で戻される変数を「var」コマンドで宣言します。 RETURNING句で変数を指定し、実行した結果を「print」コマンドで表示しています。 VALUES句で指定された値が、RETURNING句により戻されていることが分かります。

このRETURNING句を利用することで、INSERT文の中で使用していた順序の値をINSERT直後に知ることができます。伝票データの伝票番号に順序を使用する場合、伝票データのINSERTを行う前に、SELECT文により順序から値を取得し、その値をINSERTのVALUES句に設定して値を渡してやります。

このやり方では、最初のSELECT文の前にトランザクションを開始し、次の伝票データのINSERT文の後でトランザクションのコミットをする必要があります。（このあたりの一連の処理を、PL/SQLで組んだりVBのロジックで組んだりしますが） RETURNING句で順序により設定された値を返す例を、VB.NETで行う例を示します。

01'Oracleへのコネクションの確立（xxxの部分はそれぞれのシステムにあわせる）
02Dim cnn As New OracleConnection
03cnn.ConnectionString = "User Id=xxx;Password=xxx;Data Source=xxx"  
04Try
05    cnn.Open()
06    Try
07        Dim cmd = New OracleCommand
08        'SQL文
09        cmd.CommandText = "INSERT INTO TT_売上(売上番号, 売上日, 得意先コード, 担当者コード)" & _
10                          " VALUES(TS_伝票番号.NEXTVAL, TO_CHAR(SYSDATE, 'YYYY/MM/DD'), :CUSTOM, :PERSON)" & _
11                          " RETURNING 売上番号, 売上日, 得意先コード, 担当者コード" & _
12                          " INTO :URINO, :URIDATE, :CUSTNO, :PERNO"
13        '引数のバインドは名称
14        cmd.BindByName = True
15        'VALUES句の引数の定義
16        Dim pCustom As OracleParameter = cmd.Parameters.Add("CUSTOM", 1)
17        Dim pPerson As OracleParameter = cmd.Parameters.Add("PERSON", 1)
18        'INTO句の引数の定義（データ型はDBのテーブルのカラム定義にあわせる）
19        Dim pUrino As OracleParameter = cmd.Parameters.Add("URINO", Oracle.DataAccess.Client.OracleDbType.Decimal)
20        pUrino.Direction = Data.ParameterDirection.ReturnValue
21        Dim pUridate As OracleParameter = cmd.Parameters.Add("URIDATE", Oracle.DataAccess.Client.OracleDbType.Date)
22        pUridate.Direction = Data.ParameterDirection.ReturnValue
23        Dim pCustno As OracleParameter = cmd.Parameters.Add("CUSTNO", Oracle.DataAccess.Client.OracleDbType.Decimal)
24        pCustno.Direction = Data.ParameterDirection.ReturnValue
25        Dim pPerno As OracleParameter = cmd.Parameters.Add("PERNO", Oracle.DataAccess.Client.OracleDbType.Decimal)
26        pPerno.Direction = Data.ParameterDirection.ReturnValue
27        'コネクション
28        cmd.Connection = cnn
29        'SQLの実行
30        cmd.ExecuteNonQuery()
31        MsgBox("売上番号 : " & pUrino.Value.ToString & " " & pUridate.Value.ToString & " " & pCustno.Value.ToString & " " & pPerno.Value.ToString)
32    Catch ex As Exception
33        MsgBox("insert error:" & ex.Message)
34    Finally
35        'コネクションを閉じる
36        cnn.Close()
37    End Try
38Catch ex As Exception
39    MsgBox("open error:" & ex.Message)
40End Try

このRETURNING句はINSERTのみならず、UPDATE、DELETE文でも使用できます。
但し、この方法では戻されるデータ件数が１件の場合のみ可能です。（UPDATE、DELETE対象の行が１行の場合）

SQL> SELECT * FROM TT_売上 WHERE 得意先コード=3;
 
  売上番号 売上日   得意先コード 担当者コード
---------- -------- ------------ ------------
         3 02-11-04            3            5
         4 02-11-13            3            2
 
SQL> UPDATE TT_売上 set 得意先コード=3
  2  WHERE 得意先コード=3
  3  RETURNING 売上番号 INTO :v1;
RETURNING 売上番号 INTO :v1
                        *
行3でエラーが発生しました。:
ORA-24369:
1つ以上のバインド・ハンドルに、必要なコールバックが登録されていません。

尚、以下の様にコレクション型を宣言し、「INTO」の前に「BULK COLLECT」を記し、コレクションに値を返すことができます。
UPDATE処理後は、必要に応じて返されたコレクションのデータ処理を行います。

SQL> DECLARE
  2      TYPE URINO_ARRAY_TYP IS TABLE OF TT_売上.売上番号%TYPE INDEX BY BINARY_INTEGER;
  3      URINO_ARRAY URINO_ARRAY_TYP;
  4  BEGIN
  5      UPDATE TT_売上 set 得意先コード=3
  6      WHERE 得意先コード=3
  7      RETURNING 売上番号 BULK COLLECT INTO URINO_ARRAY;
  8
  9      FOR i IN 1..URINO_ARRAY.COUNT LOOP
 10          DBMS_OUTPUT.PUT_LINE('売上番号(' || TO_CHAR(i) || ')=' || URINO_ARRAY(i));
 11      END LOOP;
 12  END;
 13  /
売上番号(1)=3
売上番号(2)=4
 
PL/SQLプロシージャが正常に完了しました。

ページのトップへ戻る

DATE型の考察

DATE型の内部は、DATE型の整数部に日付データを、小数部に時刻データを持っています。
小数部は、１日の秒数分の１を最小の単位としています。つまり「1/(24*60*60)」を(24*60*60)回足すと１日になるということです。以下にPL/SQLでDATE型への整数部への加算、減算、及び小数部への加算、減算を行った例を示します。

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2   WDATE DATE;
  3  BEGIN
  4   WDATE := TO_DATE('2011/07/31 13:10:56', 'YYYY/MM/DD HH24:MI:SS');
  5   /* 数値の1を加算すると日付は次の日付となる */
  6   WDATE := WDATE + 1;
  7   DBMS_OUTPUT.PUT_LINE('WDATE=' || TO_CHAR(WDATE, 'YYYY/MM/DD HH24:MI:SS'));
  8   /* 数値の1を減算すると日付は前の日付となる */
  9   WDATE := WDATE - 1;
 10   DBMS_OUTPUT.PUT_LINE('WDATE=' || TO_CHAR(WDATE, 'YYYY/MM/DD HH24:MI:SS'));
 11
 12   /* 1日は(24*60*60)秒で,(1/24*60*60)を1日分合計すると日付は次の日付となる */
 13   FOR I IN 1 .. (24*60*60) LOOP
 14    WDATE := WDATE + (1 / (24*60*60));
 15   END LOOP;
 16   DBMS_OUTPUT.PUT_LINE('WDATE=' || TO_CHAR(WDATE, 'YYYY/MM/DD HH24:MI:SS'));
 17
 18   /* 1日は(24*60*60)秒で,(1/24*60*60)を1日分減算すると日付は前の日付となる */
 19   FOR I IN 1 .. (24*60*60) LOOP
 20    WDATE := WDATE - (1 / (24*60*60));
 21   END LOOP;
 22   DBMS_OUTPUT.PUT_LINE('WDATE=' || TO_CHAR(WDATE, 'YYYY/MM/DD HH24:MI:SS'));
 23  END;
 24  /
WDATE=2011/08/01 13:10:56
WDATE=2011/07/31 13:10:56
WDATE=2011/08/01 13:10:56
WDATE=2011/07/31 13:10:56
 
PL/SQLプロシージャが正常に完了しました。
 
SQL>

このDATE型の性質は日付を使ったループ処理等で有効に使えるような気がしますが．．．。（裏技的なのかもしれませんが）

上記のPL/SQLのことをSELECT文で行うことも可能です。

SQL> SELECT
  2   TO_CHAR(TO_DATE('2011/07/31', 'YYYY/MM/DD') + 2, 'YYYY/MM/DD') ,
  3   TO_CHAR(TO_DATE('2011/07/31 13:10:56', 'YYYY/MM/DD HH24:MI:SS') + (1 / (24*60*60)),
  4           'YYYY/MM/DD HH24:MI:SS')
  5  FROM DUAL;
 
TO_CHAR(TO TO_CHAR(TO_DATE('20
---------- -------------------
2011/08/02 2011/07/31 13:10:57
 
SQL>

ページのトップへ戻る

転職を本気で考えている方向けのプログラミングスクール！【WebCampPRO】

外部結合演算子 (+) とANSI規格について

SELECT文などでテーブルの外部結合を行う場合、WHERE句でカラム名の直後に「(+)」の演算子を付加して行います。
ただこの演算子「(+)」はOracleだけの表現方法で、他のSQL-SERVERやMySQLなどのデータベースではANSIの規格に準拠しています。
Oracleも9iのバージョンからこのANSIの規格もサポートしています。

さて、今までこのサイトで載せてきたSQL文は全て演算子「(+)」を使ってきましたが、ANSIの規格でどうなるのかを以下に示します。

「売上明細」テーブルと連結される「商品」マスタで、商品コードが「商品」マスタ側に無い場合でも「売上明細」を抽出したい場合には以下の様なSQLとなります。

SQL> SELECT TU.売上番号, TU.明細番号, TU.商品コード, TS.商品名
  2  FROM TT_売上明細 TU , TM_商品 TS
  3  WHERE TU.商品コード = TS.商品コード(+)
  4  ORDER BY TU.売上番号, TU.明細番号;
 
  売上番号   明細番号 商品コード 商品名
---------- ---------- ---------- ----------------------------------------
         1          1          1 PC-9001
         1          2          4 NOTE-1010
         2          1          2 PC-9002
         2          2          5 NOTE-1020
         2          3         11 HUB-A001
         3          1          3 PC-9003
         4          1          6 NOTE-1030
         4          2          7 PRT-3001
         5          1          8
         5          2          9 CRT-1001
         6          1          8
         6          2          9 CRT-1001
         6          3         10 CRT-2001
         8          1          8
         8          2          9 CRT-1001
         9          1          7 PRT-3001
         9          2          9 CRT-1001
 
17行が選択されました。

上は、売上明細のデータの中にある商品コードの「8」の商品マスタのデータを仮に「81」とした結果を示しています。商品コードが存在しない部分の商品名はNULLが表示されています。
これをANSIの規格で書き直したのが以下のSQLです。

SQL> SELECT TU.売上番号, TU.明細番号, TU.商品コード, NVL(TS.商品名, '商品が未登録') AS 商品名
  2  FROM TT_売上明細 TU LEFT JOIN TM_商品 TS
  3  ON TU.商品コード = TS.商品コード
  4  ORDER BY TU.売上番号, TU.明細番号;
 
  売上番号   明細番号 商品コード 商品名
---------- ---------- ---------- ----------------------------------------
         1          1          1 PC-9001
         1          2          4 NOTE-1010
         2          1          2 PC-9002
         2          2          5 NOTE-1020
         2          3         11 HUB-A001
         3          1          3 PC-9003
         4          1          6 NOTE-1030
         4          2          7 PRT-3001
         5          1          8 商品が未登録
         5          2          9 CRT-1001
         6          1          8 商品が未登録
         6          2          9 CRT-1001
         6          3         10 CRT-2001
         8          1          8 商品が未登録
         8          2          9 CRT-1001
         9          1          7 PRT-3001
         9          2          9 CRT-1001
 
17行が選択されました。

少し、SELECT句の中を変えましたが、結果は同じものが表示されます。 LEFT JOIN のやり方をまとめますと以下の様な感じです。尚、結合するキーが2個以上ある場合は、「AND」で条件を追加します。

FROM <表名A> LEFT JOIN <表名B>
ON  <表名A>.<キーA1> = <表名B>.<キーB1>
AND <表名A>.<キーA2> = <表名B>.<キーB2>
...

演算子「(+)」が付かない場合、いわゆる等結合では「LEFT」の部分を「INNER」に変えます。

SQL> SELECT TU.売上番号, TU.明細番号, TU.商品コード, TS.商品名
  2  FROM TT_売上明細 TU INNER JOIN TM_商品 TS
  3  ON TU.商品コード = TS.商品コード
  4  ORDER BY TU.売上番号, TU.明細番号;
 
  売上番号   明細番号 商品コード 商品名
---------- ---------- ---------- ----------------------------------------
         1          1          1 PC-9001
         1          2          4 NOTE-1010
         2          1          2 PC-9002
         2          2          5 NOTE-1020
         2          3         11 HUB-A001
         3          1          3 PC-9003
         4          1          6 NOTE-1030
         4          2          7 PRT-3001
         5          2          9 CRT-1001
         6          2          9 CRT-1001
         6          3         10 CRT-2001
         8          2          9 CRT-1001
         9          1          7 PRT-3001
         9          2          9 CRT-1001
 
14行が選択されました。

ページのトップへ戻る

転職を本気で考えている方向けのプログラミングスクール！【WebCampPRO】

NULLについて再考

最近の仕事で少し簡単なところでつまずいたのですが、それがNULLの取扱でした。
例として「TM_商品」のテーブルに「削除フラグ」のカラムを追加します。「削除フラグ」はNUMBER型として、「１」が立っていたら削除済みと考えます。
取り合えず、テストとして削除済みのデータを追加してみます。

SQL> ALTER TABLE TM_商品
  2  ADD 削除フラグ NUMBER(1);
 
表が変更されました。
 
SQL> INSERT INTO TM_商品 VALUES(999, 'TEST999', '', 9000, 1500, 1);
 
1行が作成されました。
 
SQL> SELECT * FROM TM_商品;
 
商品コード 商品名                                   商品区分               仕入単価   売上単価 削除フラグ
---------- ---------------------------------------- -------------------- ---------- ---------- ----------
         1 PC-9001                                  デスクトップパソコン      65000      98000
         2 PC-9002                                  デスクトップパソコン      95000     120000
         3 PC-9003                                  デスクトップパソコン     150000     190000
         4 NOTE-1010                                ノートパソコン           125000     188000
         5 NOTE-1020                                ノートパソコン           145000     200000
         6 NOTE-1030                                ノートパソコン           155000     220000
         7 PRT-3001                                 プリンタ                  45000      88000
        81 PRT-4001                                 プリンタ                 115000     180000
         9 CRT-1001                                 ディスプレイ              45000      78000
        10 CRT-2001                                 ディスプレイ              55000      98000
        11 HUB-A001                                 ネットワーク              20000      40000
        12 HUB-B001                                 ネットワーク              40000      60000
        13 HUB-C001                                                                     100000
        14 HUB-D001                                                                      20000
       999 TEST999                                                             9000       1500       1
 
15行が選択されました。

そこで削除されていないデータの一覧を抽出しようとして以下のSQLを実行すると、結果は何もありませんでした。

SQL> SELECT * FROM TM_商品 WHERE 削除フラグ != 1;
 
レコードが選択されませんでした。

当然といえばそうなのですが、「削除フラグ」を追加した時点では、既に存在する「TM_商品」のデータの「削除フラグ」はNULLになります。 WHERE句の「!=」判定ではNULLのデータは除外されるのを忘れていました。それで、NVL関数を用いた以下のSQLで正常な結果が得られる様になりました。

SQL> SELECT * FROM TM_商品 WHERE NVL(削除フラグ, 0) != 1;
 
商品コード 商品名                                   商品区分               仕入単価   売上単価 削除フラグ
---------- ---------------------------------------- -------------------- ---------- ---------- ----------
         1 PC-9001                                  デスクトップパソコン      65000      98000
         2 PC-9002                                  デスクトップパソコン      95000     120000
         3 PC-9003                                  デスクトップパソコン     150000     190000
         4 NOTE-1010                                ノートパソコン           125000     188000
         5 NOTE-1020                                ノートパソコン           145000     200000
         6 NOTE-1030                                ノートパソコン           155000     220000
         7 PRT-3001                                 プリンタ                  45000      88000
        81 PRT-4001                                 プリンタ                 115000     180000
         9 CRT-1001                                 ディスプレイ              45000      78000
        10 CRT-2001                                 ディスプレイ              55000      98000
        11 HUB-A001                                 ネットワーク              20000      40000
        12 HUB-B001                                 ネットワーク              40000      60000
        13 HUB-C001                                                                     100000
        14 HUB-D001                                                                      20000
 
14行が選択されました。

最初のところで「削除フラグ」のカラムを追加したときに、DEFAULTで既定値を０とし、「NOT NULL」を指定しておけばこんなことにはならなかったのですが。既に存在するカラムに対して、以下の変更はできますが、DEFAULT指定をするとエラーになります。カラムデータが既にNULLだった場合には、変更はできないようです。

SQL> ALTER TABLE TM_商品 MODIFY 削除フラグ DEFAULT 0;
 
表が変更されました。
 
SQL> ALTER TABLE TM_商品 MODIFY 削除フラグ DEFAULT 0 NOT NULL;
ALTER TABLE TM_商品 MODIFY 削除フラグ DEFAULT 0 NOT NULL
*
行1でエラーが発生しました。:
ORA-02296: (xxxxx.)を有効化できません - NULL値が見つかりました。

ページのトップへ戻る

SUBSTR関数でVBのRight、Left関数の様な使い方

Oracleの関数には、VB.NETで用意されているRight関数やLeft関数が存在しません。文字列の抽出で、左から何桁とか、右から何桁とかを切り出す処理はよくあるものです。 OracleではSUBSTR関数しかないので、これを使うしかありません。

SUBSTR関数の引数は以下の様になっています。

SUBSTR ( string , position [, length] )
SUBSTRB ( string , position [, length] )

・string：抽出元の文字列
・position：抽出開始位置(１～)　
　（負の場合は右端から逆順に処理）
・length：抽出文字列長
・関数の戻り値：抽出文字列

SUBSTRは文字列をキャラクタ単位での処理を行い、 SUBSTRBは文字列をバイト単位での処理を行います。

文字列に漢字等のマルチバイト文字が含まれている場合にはSUBSTRBは問題があります。文字コードがシフトJISの場合で例を示しますと以下の様になります。

SQL> SELECT
  2   '/' || SUBSTR('123457890', 2, 2) || '/' AS 半角SUBSTR
  3  ,'/' || SUBSTR('１２３４５６７８９０', 2, 2) || '/' AS 全角SUBSTR
  4  ,'/' || SUBSTRB('123457890', 3, 2) || '/' AS 半角SUBSTRB
  5  ,'/' || SUBSTRB('１２３４５６７８９０', 3, 3) || '/' AS 全角SUBSTRB
  6  FROM DUAL;
 
半角SUBS 全角SUBSTR   半角SUBS 全角SUBSTR
-------- ------------ -------- ----------
/23/     /２３/       /34/     /２ /

SUBSTRは全角文字があっても、1文字は1文字として処理されます。ただし、SUBSTRBは４つ目の例でも分かるように、漢字の泣き別れが発生し、 3桁目には半角の空白が返ってきます。
漢字の泣き別れの件はどうするのかは、テーブル設計や、出力する時に対応する様にするしかないと思います。
上の例の position のところをマイナスにすると以下の様になります。
文字列の最後からの処理になっているのが分かります。

SQL> SELECT
  2   '/' || SUBSTR('123457890', -2, 2) || '/' AS 半角SUBSTR
  3  ,'/' || SUBSTR('１２３４５６７８９０', -2, 2) || '/' AS 全角SUBSTR
  4  ,'/' || SUBSTRB('123457890', -3, 2) || '/' AS 半角SUBSTRB
  5  ,'/' || SUBSTRB('１２３４５６７８９０', -3, 3) || '/' AS 全角SUBSTRB
  6  FROM DUAL;
 
半角SUBS 全角SUBSTR   半角SUBS 全角SUBSTR
-------- ------------ -------- ----------
/90/     /９０/       /89/     / ０/

さて、Right関数 Left関数 的な利用ですが、 Right関数 では position を「1」として length に文字数を設定します。 Left関数 では position を文字数のマイナス値として length に文字数を設定します。

SQL> SELECT
  2   '/' || SUBSTR('123457890', 1, 2) || '/' AS 半角Left関数的
  3  ,'/' || SUBSTR('１２３４５６７８９０', 1, 2) || '/' AS 全角Left関数的
  4  ,'/' || SUBSTRB('123457890', -2, 2) || '/' AS 半角Right関数的
  5  ,'/' || SUBSTRB('１２３４５６７８９０', -4, 4) || '/' AS 全角Right関数的
  6  FROM DUAL;
 
半角LEFT 全角LEFT関数 半角RIGH 全角RIGHT関
-------- ------------ -------- ------------
/12/     /１２/       /90/     /９０/

尚、番号などを先頭「0」付きで編集する場合、以下の様な使い方もあります。

SQL> SELECT
  2   '/' || SUBSTR(TO_CHAR(123, '000000000000'), -8, 8) || '/' AS 数値先頭０付加
  3  ,'/' || TO_CHAR(123, 'FM00000000') || '/' AS 先頭０付加書式
  4  FROM DUAL;
 
数値先頭０付加                       先頭０付加書式
------------------------------------ ----------------------
/00000123/                           /00000123/

TO_CHAR関数 でフォーマットを使えば２行目の様にできますが、変換する数値が8桁より大きく、表示が下8桁のみの場合は１行目の方法も有用かと思います。

■関連記事

⇒Oracle SQL 関数その１・SUBSTR

ログインエラーでアカウントがロックされた場合の、強制ログインを使った対処方法

オラクルでログイン処理で何回もエラーを起こした場合には、既定回数を超えるとアカウントがロックされてしまいます。
以下のSQLの実行を見てください。（SQLコマンドラインから実行しています）
なお、既定回数はデフォルトでは10回の様です。 10回目の「CONNECT」でアカウントがロックされました。

SQL> CONNECT TEST/TEST
ERROR:
ORA-28000: the account is locked

このTESTユーザのアカウントのロックを解除する為に、強制的にログインします。 「/ AS SYSDBA」でのログインはOS認証で「SYSDBA」の権限を持つログインになります。

SQL> CONNECT / AS SYSDBA
接続されました。
SQL> ALTER USER TEST ACCOUNT UNLOCK;
 
ユーザーが変更されました。
 
SQL>

「ALTER USER」命令によりアカウントをアンロックしています。
尚、強制ログインの方法は「CONNECT SYSTEM/MANAGER AS SYSDBA」などもあります。

OracleSQL入門
↑

VBTips
↑

GoogleMaps API v3
↓

リンク