SelectInsertのデバッグがメンドイとき

SelectInsertするときにInsert先のテーブルにPK、UK、FK、NOTNULL制約などが張ってあって
Insertに失敗すると結構ストレスがたまります。
そもそもどのレコードで失敗したのかわからなかったりすると
NotNullだったらSelectのWhereにIS NOT NULLかいて探したり
UKだったら GROUP HAVING 書いて探したり
かなりめんどくさいので10gからはdbms_errlogパッケージ使ってレコード特定してます。
http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/appdev.102/B19245-02/d_errlog.htm

つかいかた例：

• Insert先にUKが張ってあり、Select元で重複が発生している状態

select元テーブル

CREATE TABLE SELECT_TABLE
(
    NAME                           VARCHAR2(10)
);

select_tableデータ

NAME
a
b
c
d
e
f
a
g
h

insert先テーブル

CREATE TABLE INSERT_TABLE
(
    NAME                           VARCHAR2(10),
    CONSTRAINT UK_INSERT_TABLE UNIQUE (NAME) USING INDEX
)

上記の状態でSelectInsert

INSERT INTO INSERT_TABLE(NAME) SELECT NAME FROM SELECT_TABLE;

すると、「ORA-00001: unique constraint violated.」と一意制約違反で失敗しますが
どのレコードで失敗したかは自分で下記のようなSQLでSelectしてこないとわかりません。

SELECT
	NAME
	,COUNT(*)
FROM
	SELECT_TABLE
GROUP BY
	NAME
HAVING
	COUNT(*) > 1;

がんばってデバッグするのも大変なので
まずdbms_errlogパッケージのcreate_error_logプロシージャを呼び出します。

exec dbms_errlog.create_error_log('INSERT_TABLE');

このプロシージャを呼ぶとエラー行保持テーブルが作成されます。

CREATE TABLE NIWANO.ERR$_INSERT_TABLE
(
    ORA_ERR_NUMBER$                NUMBER,
    ORA_ERR_MESG$                  VARCHAR2(2000),
    ORA_ERR_ROWID$                 UROWID,
    ORA_ERR_OPTYP$                 VARCHAR2(2),
    ORA_ERR_TAG$                   VARCHAR2(2000),
    NAME                           VARCHAR2(4000)
)
/

次に先ほどのSelectInsertの最後にLOG ERRORS INTOをつけます。

INSERT INTO INSERT_TABLE(NAME) SELECT NAME FROM SELECT_TABLE
LOG ERRORS INTO ERR$_INSERT_TABLE('SelectInsertで失敗した') REJECT LIMIT 10;

すると、SelectInsertは失敗しませんが、ERR$_INSERT_TABLEに下記のようにエラーレコードが入ります。

ORA_ERR_NUMBER$	ORA_ERR_MESG$	ORA_ERR_ROWID$	ORA_ERR_OPTYP$	ORA_ERR_TAG$	NAME
1	"ORA-00001: 一意制約(UK_INSERT_TABLE)に反しています"		I	SelectInsertで失敗した	a

これをみれば NAME=a のレコードでInsertが失敗したことがわかり、さらにInsert可能行はすべてInsertされます。

ちなみにLOG ERRORSの内容は
LOG ERRORS INTO エラーログ格納テーブル名('エラータグ名') REJECT LIMIT エラー許容件数;
って感じです。

今回のようにSQLのデバッグにも活用できますし、SQLでエラー行はスキップしてInsertしたい場合などにも
使えると思いますのでOracle10gの環境が近くにある方は使ってみるとストレスから解放されるかもしれません。

BYPASS_UJVC

仕事でselectupdateをすることがあって、
副問い合わせに対してUPDATE出来たら楽だなと思ってたら
ORA-01779が出てしまったので調べてたら

http://emboss.blog28.fc2.com/blog-entry-13.html

みたいにヒント句で回避できるらしい
ってことで出来ることはわかったのだけど

そもそも　更新対象テーブル：更新データテーブル　が
１：N　の場合にどういう動きをするかよく分からなかったので試して見た

・更新するテーブル

CREATE TABLE UP_TABLE
(
    ID                             NUMBER NOT NULL,
    NAME                           VARCHAR2(10),
    CONSTRAINT UP_TABLE_PK PRIMARY KEY (ID) USING INDEX
)

・更新データが入ってるテーブル

CREATE TABLE DATA_TABLE
(
    UP_ID                          NUMBER NOT NULL,
    NAME                           VARCHAR2(10)
)

データは下記の通り
UP_TABLE

ID	NAME
1	A
2	B

DATA_TABLE

UP_ID	NAME
1	a
1	b
1	c
2	d

で、DATA_TABLE.UP_ID と　UP_TABLE.ID を結合して、UP_TABLE.NAME　を　DATA_TABLE.NAME　を更新するUPDATE文をBYPASSつかって書いてみる

UPDATE
	(
	SELECT /*+ BYPASS_UJVC*/
		 UP.NAME
		,DT.NAME NAME_UP
	FROM
		UP_TABLE UP
	INNER JOIN
		DATA_TABLE DT
	ON
		DT.UP_ID = UP.ID
	) UP
SET
	UP.NAME = UP.NAME_UP

すると　結果は

ID	NAME
1	c
2	d

UP_TABLE.IDの1でみるとDATA_TABLE.NAMEが「c」で更新される
そもそもUPDATE対象のレコードは2件なのに4件更新されたとメッセージが帰ってきたので
DATA_TABLEと結合した分更新されて、最後に更新されたデータが残っている模様

つまり、１：Ｎで結合して１に対してＮのレコードで更新した時にはＮ回更新されていて、最後の更新結果がテーブルに残る

ためしに

UPDATE
	(
	SELECT /*+ BYPASS_UJVC*/
		 UP.NAME
		,DT.NAME NAME_UP
	FROM
		UP_TABLE UP
	INNER JOIN
		DATA_TABLE DT
	ON
		DT.UP_ID = UP.ID
	ORDER BY
		DT.NAME DESC
	) UP
SET
	UP.NAME = UP.NAME_UP

のようにソート順を変更すると結果は

ID	NAME
1	a
2	d

になった

結局は副問い合わせ内でのソート順で更新されていくので、副問い合わせを使ったUPDATEをする際には
注意が必要みたいですね

まー、そもそも一意にならないselectupdateなんてすることはそうそう無いと思うのですが
ちょっと気になったので調べた結果です

今回のテーブル定義でのSELECTUPDATEだったら

UPDATE UP_TABLE UP
SET
	UP.NAME = (SELECT MAX(DT.NAME) FROM DATA_TABLE DT WHERE DT.UP_ID = UP.ID GROUP BY DT.UP_ID)
WHERE
	UP.ID IN
		(SELECT DT.UP_ID FROM DATA_TABLE DT)

みたいに、UPDATEしたい情報を一意にしてから
更新すれば事足りますのでこっちを推奨したいところです

自律型トランザクションのサンプル（AUTONOMOUS_TRANSACTION)

PL/SQLでログを取りたい場合とかに実プログラムのトランザクションとは別で
ログ用のトランザクションを作成したいときとか結構あると思います。

そんなときはAUTONOMOUS_TRANSACTIONを使うと自律型トランザクションを実装出来ます。

サンプルを書きました。

やってることはログインユーザが持ってるオブジェクト名とタイプをOBJECT_LISTテーブルに1件ずつINSERT
して1件ずつログを吐く。
自律型を確認するためOBJCT_LISTへのINSERTはROLLBACKしてみるという簡単なプロシージャ。

ログテーブル

CREATE TABLE PLSQL_LOG
(
    LOG_LEVEL                      VARCHAR2(10) NOT NULL,
    LOG_MESSAGE                    VARCHAR2(100) NOT NULL,
    REGISTER_DATE                  TIMESTAMP(6) DEFAULT systimestamp NOT NULL
)

ロガープロシージャ

CREATE OR REPLACE PROCEDURE LOGGER
(
	 V_LOG_LEVEL IN VARCHAR2
	,V_LOG_MESSAGE IN VARCHAR2
)
IS
	--自律型トランザクションであることを明記
	PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
	--ログを登録
	INSERT INTO
		PLSQL_LOG
			(LOG_LEVEL,LOG_MESSAGE,REGISTER_DATE)
		VALUES
			(V_LOG_LEVEL,V_LOG_MESSAGE,SYSTIMESTAMP);
	COMMIT;
END LOGGER;
/

オブジェクト一覧格納テーブル

CREATE TABLE OBJECT_LIST
(
    OBJECT_NAME                    VARCHAR2(100) NOT NULL,
    OBJECT_TYPE                    VARCHAR2(100) NOT NULL
)

OBJECT_LISTインサートプロシージャ

CREATE OR REPLACE PROCEDURE CREATE_OBJECT_LIST
IS
	CURSOR OBJ_LIST IS SELECT * FROM USER_OBJECTS;
BEGIN
	--自分の持っているオブジェクトをSELECTしてループ
	FOR REC IN OBJ_LIST LOOP
		--OBJECT_LISTにオブジェクト名とタイプを登録
		INSERT INTO
			OBJECT_LIST
				(OBJECT_NAME,OBJECT_TYPE)
			VALUES
				(REC.OBJECT_NAME,REC.OBJECT_TYPE);
		--ログを出力
		LOGGER('DEBUG','OBJECT_NAME='||REC.OBJECT_NAME||' OBJECT_TYPE='||REC.OBJECT_TYPE);
	END LOOP;
	--自律の確認のためROLLBACK
	ROLLBACK;
END CREATE_OBJECT_LIST;
/

上記のCREATE_OBJECT_LISTプロシージャを実行するとPLSQL_LOGテーブルにはCOMMITされてOBJECT_LISTテーブルはROLLBACKされます。

応用次第で色々使い道があるので色々試して見ると楽しいです。

スキーマ（ユーザ）同士の定義比較

開発と本番でスキーマが分かれていてテーブル定義に差異が出てしまうことって時たまあるとおもいます。
で、その差異を洗うにはこんな感じの一発で差異がとれたりします。

たとえば
current_schema
history_schema
っていう2つのスキーマがあって

current_schemaには
TABLE_A(ID,NAME)
history_schemaには
TABLE_A(ID,NAME,DIV),TABLE_B(ID,NAME)
があったとき

まずテーブル名の差異は

	SELECT
		TABLE_NAME
		,'CURRENT'
	FROM
		ALL_TABLES
	WHERE
		OWNER = 'CURRENT_SCHEMA'
MINUS
	SELECT
		TABLE_NAME
		,'CURRENT'
	FROM
		ALL_TABLES
	WHERE
		OWNER = 'HISTORY_SCHEMA'
UNION ALL
	SELECT
		TABLE_NAME
		,'HISTORY'
	FROM
		ALL_TABLES
	WHERE
		OWNER = 'HISTORY_SCHEMA'
MINUS
	SELECT
		TABLE_NAME
		,'HISTORY'
	FROM
		ALL_TABLES
	WHERE
		OWNER = 'CURRENT_SCHEMA'

でとれます。
上記のsqlでは、current_schemaにしかないテーブルは2カラム目にCURRENT,history_schemaにしかないテーブルは2カラム目にHISTORYで結果が帰ってきます。

さらにカラムまでチェックしたい場合は

	SELECT
		TABLE_NAME
		,COLUMN_NAME
		,'CURRENT'
	FROM
		ALL_TAB_COLUMNS
	WHERE
		OWNER = 'CURRENT_SCHEMA'
MINUS
	SELECT
		TABLE_NAME
		,COLUMN_NAME
		,'CURRENT'
	FROM
		ALL_TAB_COLUMNS
	WHERE
		OWNER = 'HISTORY_SCHEMA'
UNION ALL
	SELECT
		TABLE_NAME
		,COLUMN_NAME
		,'HISTORY'
	FROM
		ALL_TAB_COLUMNS
	WHERE
		OWNER = 'HISTORY_SCHEMA'
MINUS
	SELECT
		TABLE_NAME
		,COLUMN_NAME
		,'HISTORY'
	FROM
		ALL_TAB_COLUMNS
	WHERE
		OWNER = 'CURRENT_SCHEMA'

な感じでcurrentとhistoryのカラム名の比較ができます。
さらに、型や桁数などまで見たければ、ALL_TAB_COLUMNS.DATA_TYPE,DATA_LENGTHまで比較対象に加えればokです。

ここまでで ALL_TABLES とか ALL_TAB_COLUMNSとかあまり使わないテーブル名が出てきたかと思いますが
これはORACLEのデータディクショナリビューといいます。
Oracleのオンラインドキュメントだといろんなとこに書かれていて見にくいので
下記のサイトなんかおすすめです。
http://luna.gonna.jp/oracle/ora_ddv.html

※ただし、SYSTEM権限のあるユーザでログインする必要がありますが、、、、

最大値のレコードを取得する方法 その２

以前
http://d.hatena.ne.jp/niwanos/20090501/1241195931
みたいに最大値のレコード取得の方法を書いてましたが
Oracleのマニュアルにこれよりもスマートな方法が書いてあったのでちょっと紹介します。

前回は

SELECT
	LOG.*
FROM
	LOG_TABLE LOG
WHERE
	LOG.ID IN (
		SELECT
			MAX(L.ID) ID
		FROM
			LOG_TABLE L
		GROUP BY
			L.LOG_ID)

な感じで最大レコードを取得していたのですが
OVERを使う方法もあるそうです

SELECT
	 MAX_LOG.ID
	,MAX_LOG.LOG_ID
	,MAX_LOG.NAME
	,MAX_LOG.REGISTER_DATE
FROM
	(SELECT
		ID
		,LOG_ID
		,NAME
		,REGISTER_DATE
		,MAX(ID) OVER(PARTITION BY LOG_ID) MAX_ID
FROM
	LOG_TABLE LOG) MAX_LOG
WHERE
	MAX_LOG.MAX_ID = MAX_LOG.ID

これだと同じテーブルを2回SELECTしなくて済むので多少はやいかもしれません

うちの環境だとコストが　前のだと5、今回ので4になったので1だけ早い結果でした

LAG関数、LEAD関数

OracleにはLAG、LEAD関数というものがあります。
何ができるかというと、、、
ソートしたレコードの前or後のデータを参照できます。

なにに使うんだろうとちょっと考えてみたんですが、
前月差などを取るときに使えそうです。

こんな感じ

テーブル定義

CREATE TABLE SALES
(
    YEAR_MONTH                     NUMBER(6,0),
    VALUE                          NUMBER(10,0)
)

でデータとして

YEAR_MONTH	VALUE
200904	100
200905	200
200906	100

があったとして、前月差を取得するために

SELECT
	YEAR_MONTH
	,VALUE
	,LAG(VALUE) OVER(ORDER BY YEAR_MONTH) PAST
	,VALUE - LAG(VALUE) OVER(ORDER BY YEAR_MONTH) DIFF
FROM
	SALES

こんなSQLを書きます。

するとこんな結果が帰ってきます

YEAR_MONTH	VALUE	PAST	DIFF
200904	100
200905	200	100	100
200906	100	200	-100

PASTに前月のVALUEが、
DIFFに前月差が出ます。

これくらいしかLAG、LEADの使い道が思いつかない。。。。
ただ、なんか可能性を感じる関数ですのでもう少し考えてみます。

最大値のレコードを取得する方法

自分はこの方法しか知らないので知ってる人は是非教えてほしいところですが。

1テーブル内で、履歴的な管理をしているときに
たとえば↓みたいなテーブルがあって

CREATE TABLE LOG_TABLE 
(
	 ID NUMBER
	,LOG_ID NUMBER
	,NAME VARCHAR2(20)
	,REGISTER_DATE TIMESTAMP(6)
	,CONSTRAINT PK_LOG_TABLE PRIMARY KEY (ID) USING INDEX
)

最新のレコードを取得するためにはID単位でLOG_IDのMAXレコードが必要になったときには

SELECT
	LOG.*
FROM
	LOG_TABLE LOG
WHERE
	LOG.ID IN (
		SELECT
			MAX(L.ID) ID
		FROM
			LOG_TABLE L
		GROUP BY
			L.LOG_ID)

というように自己結合してMAX対象レコードを取るのが一般的かなーと思います。

たかが最新を取りたいだけなのにこんなめんどいSQLをかかにゃいけないのは
問い合わせ言語のSQLと言うものの限界なのかもしれない

話は変わるのですがOracleOpenWorld行ってきました。
http://blogs.oracle.com/Oraclejapanpr/2009/04/oracle_openworld_tokyo_2009_3.html

基調講演聞いてきましたが
オラクルのイノベーションを実現するために今後も企業買収は進めていくそうです。
話によると、

• Application
• Database
• Middleware
• Infrastructure

のすべてを掌握したいそうで、ただし、oracleすべてで完結出来る状態にはするが
ほかのソリューションとの親和性を高くしたいみたいです。
ただし、Oracleで揃えたときに、ユーザに利便性の高いものを提供出来るようにしたいとのこと。

初めて行ったけど面白かったー。
基調講演の最中みんな結構写真とか取ってて、自分もデジカメ持参したから取ろうと思ったら電池切れてた。
んでよくよく周りを見回したら携帯で取ってる人も結構いたのですが
「カシャ」とか「ピロリーン」とか鳴らして取ってる人たちをみてちょっと凹んだのであきらめました。

とったのは↓の一枚のみ、　うつってないけどウェンディも来てた。可愛かったー