Oracle SQL その他・Tips
- INSERTでのRETURNING...INTO句
- DATE型の内部構造についての考察
- 「LEFT JOIN」を使ったSQL文
- 「NULL」の取扱ではまったこと
- SUBSTR関数でVBのRight、Left関数の様な使い方
- ログインエラーでのアカウントロック解除の対処方法
おすすめ書籍
- 図解入門よくわかる最新Oracleデータベースの基本と仕組み[第4版] (How‐nual Visual Guide Book)
- Oracleの基本 ~データベース入門から設計/運用の初歩まで
- [改訂第4版]SQLポケットリファレンス
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で行う例を示します。
'Oracleへのコネクションの確立（xxxの部分はそれぞれのシステムにあわせる）
Dim cnn As New OracleConnection
cnn.ConnectionString = "User Id=xxx;Password=xxx;Data Source=xxx"
Try
cnn.Open()
Try
Dim cmd = New OracleCommand
'SQL文
cmd.CommandText = "INSERT INTO TT_売上(売上番号, 売上日, 得意先コード, 担当者コード)" & _
" VALUES(TS_伝票番号.NEXTVAL, TO_CHAR(SYSDATE, 'YYYY/MM/DD'), :CUSTOM, :PERSON)" & _
" RETURNING 売上番号, 売上日, 得意先コード, 担当者コード" & _
" INTO :URINO, :URIDATE, :CUSTNO, :PERNO"
'引数のバインドは名称
cmd.BindByName = True
'VALUES句の引数の定義
Dim pCustom As OracleParameter = cmd.Parameters.Add("CUSTOM", 1)
Dim pPerson As OracleParameter = cmd.Parameters.Add("PERSON", 1)
'INTO句の引数の定義（データ型はDBのテーブルのカラム定義にあわせる）
Dim pUrino As OracleParameter = cmd.Parameters.Add("URINO", Oracle.DataAccess.Client.OracleDbType.Decimal)
pUrino.Direction = Data.ParameterDirection.ReturnValue
Dim pUridate As OracleParameter = cmd.Parameters.Add("URIDATE", Oracle.DataAccess.Client.OracleDbType.Date)
pUridate.Direction = Data.ParameterDirection.ReturnValue
Dim pCustno As OracleParameter = cmd.Parameters.Add("CUSTNO", Oracle.DataAccess.Client.OracleDbType.Decimal)
pCustno.Direction = Data.ParameterDirection.ReturnValue
Dim pPerno As OracleParameter = cmd.Parameters.Add("PERNO", Oracle.DataAccess.Client.OracleDbType.Decimal)
pPerno.Direction = Data.ParameterDirection.ReturnValue
'コネクション
cmd.Connection = cnn
'SQLの実行
cmd.ExecuteNonQuery()
MsgBox("売上番号 : " & pUrino.Value.ToString & " " & pUridate.Value.ToString & " " & pCustno.Value.ToString & " " & pPerno.Value.ToString)
Catch ex As Exception
MsgBox("insert error:" & ex.Message)
Finally
'コネクションを閉じる
cnn.Close()
End Try
Catch ex As Exception
MsgBox("open error:" & ex.Message)
End 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行が選択されました。
■関連記事
⇒表の結合・クロス結合⇒表の結合・等結合
⇒表の結合・外部結合
⇒表の結合・自己結合
⇒表の結合２(JOIN)・等結合「INNER JOIN」での結合
⇒表の結合２(JOIN)・外部結合「INNER JOIN」「LEFT JOIN」での結合
⇒表の結合２(JOIN)・JOINのAND条件
転職を本気で考えている方向けのプログラミングスクール！【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」などもあります。
