遅延制約

OracleMasterSilverの勉強していて始めて知った遅延制約を試してみた。

CREATE TABLE deferred_test
(
	 id		NUMBER NOT NULL
	,name	VARCHAR2(100) NOT NULL
);

ALTER TABLE deferred_test ADD CONSTRAINT pk_deferred_test PRIMARY KEY (id);

INSERT INTO deferred_test(id,name) VALUES(1,'aaa');
INSERT INTO deferred_test(id,name) VALUES(1,'bbb');

INSERT INTO deferred_test(id,name) VALUES(1,'bbb')
*
行1でエラーが発生しました。:
ORA-00001: 一意制約(PK_DEFERRED_TEST)に反しています


ROLLBACK;

ALTER TABLE deferred_test drop CONSTRAINT pk_deferred_test;

ALTER TABLE deferred_test ADD CONSTRAINT pk_deferred_test PRIMARY KEY (id) INITIALLY DEFERRED DEFERRABLE;

INSERT INTO deferred_test(id,name) VALUES(1,'aaa');
INSERT INTO deferred_test(id,name) VALUES(1,'bbb');

COMMIT;

COMMIT
*
行1でエラーが発生しました。:
ORA-02091: トランザクションがロールバックされました。
ORA-00001: 一意制約(PK_DEFERRED_TEST)に反しています

トランザクションが終わるまで制約のチェックがかからない、、、すばらしい。

「INITIALLY DEFERRED DEFERRABLE」で遅延制約をできるように設定してます。

制約は、遅延可能または遅延不可、および初期遅延または初期即時のどちらかに定義できます。これらの属性は、制約ごとに異なるものを指定できます。それらの定義は、CONSTRAINT句の中で次のキーワードを使用して指定します。

* DEFERRABLEまたはNOT DEFERRABLE

* INITIALLY DEFERREDまたはINITIALLY IMMEDIATE

http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/server.102/B19215-02/data_int.htm

夢が広がりますね。

CSVを作成するためのSQLを生成するFunction

CREATE OR REPLACE FUNCTION MAKE_CSV_SQL
	(
		 p_table_name IN VARCHAR2
	)
RETURN VARCHAR2
IS
	CURSOR cur_columns(v_table_name VARCHAR2) IS
		SELECT
			*
		FROM
			user_tab_columns
		WHERE
			table_name = v_table_name
		ORDER BY
			column_id;
	v_sql_text	VARCHAR2(4000);
	first_flag	BOOLEAN:=TRUE;
BEGIN

	v_sql_text := 'SELECT ';
	FOR rec_columns IN cur_columns(UPPER(p_table_name)) LOOP
		IF first_flag THEN
			first_flag := FALSE;
		ELSE
			v_sql_text := v_sql_text || ' ||'',''|| ';
		END IF;

		v_sql_text := v_sql_text || ' ''"''|| ';

		CASE rec_columns.data_type
				WHEN 'DATE' THEN
					v_sql_text := v_sql_text || 'TO_CHAR(' || rec_columns.column_name || '''YYYY/MM/DD HH24:MI:SS'')';
				WHEN 'TIMESTAMP' THEN
					v_sql_text := v_sql_text || 'TO_CHAR(' || rec_columns.column_name || '''YYYY/MM/DD HH24:MI:SS.FF9'')';
				ELSE
					v_sql_text := v_sql_text || 'TO_CHAR(' || rec_columns.column_name || ')';
		END CASE;

		v_sql_text := v_sql_text || '||''"''';

	END LOOP;

	v_sql_text := RTRIM(v_sql_text,'aaa') || ' FROM ' || p_table_name;
	RETURN v_sql_text;
END;
/

テーブル名を入れればsqlが生成されます。

SELECT
	make_csv_sql(table_name)
FROM
	user_tables

こんな感じで自分が持ってるテーブルのCSV用SQLを全部つくれたりします。

sqlplus WHENEVER SQLERROR を検証

id:yohei-a に WHENEVER SQLERROR について聞いたので試してみました
sqlplus内で起きたsqlエラーをどのように処理するか（exit or continue）とか
エラー時にトランザクションをどうするか（commit or rollback ）
を指定できます。
まともにsqlplus使うつもりならとても有用ですね。

↓オラクルのマニュアル
http://otndnld.oracle.co.jp/document/products/oracle11g/111/doc_dvd/server.111/E05784-01/ch12052.htm

今回試したのは WEHNEVER SQLERROR EXIT です
まずこんなshellをつくって

#!/bin/sh

sqlplus -s user/pass @./test.sql > /dev/null

ret=$?

echo $ret

で、test.sqlを書き換えながら下記のパターンを試してみました

set serverout on size 10000
set linesize 1000

WHENEVER SQLERROR EXIT SUCCESS
select count(*) from d;
EXIT 123

結果：0

set serverout on size 10000
set linesize 1000
WHENEVER SQLERROR EXIT FAILURE
select count(*) from d;
EXIT 123

結果：1

set serverout on size 10000
set linesize 1000
WHENEVER SQLERROR EXIT WARNING
select count(*) from d;
EXIT 123

結果：2

set serverout on size 10000
set linesize 1000
WHENEVER SQLERROR EXIT 9999
select count(*) from d;
EXIT 123

結果：15
??
戻り値は8bitまでなので255までとなる、それ以上いくとまた0から開始。。。
select mod(9999,256) from dual;
=15

set serverout on size 10000
set linesize 1000
VARIABLE test_error_code NUMBER
BEGIN
:test_error_code := 111;
END;
/
WHENEVER SQLERROR EXIT :test_error_code
select count(*) from d;
EXIT 123

結果：111

set serverout on size 10000
set linesize 1000
WHENEVER SQLERROR SQL.SQLCODE
select count(*) from d;
EXIT 123

結果：174

ORA-00942: table or view does not exist のエラーが出るはずなので
select mod(942,256) from dual;
=174

テーブル定義書をpl/sqlでつくってみた

SET SERVEROUT ON SIZE 1000000
SET LINESIZE 1000

DECLARE

CURSOR table_cur IS
	SELECT
		 t.table_name AS table_name
		,tc.comments AS comments
	FROM
		user_tables t
	LEFT JOIN
		user_tab_comments tc
	ON
		t.table_name = tc.table_name
	ORDER BY
		t.table_name;

CURSOR column_cur(b_table_name varchar2) IS

SELECT
	 c.column_name AS column_name_p
	,SUBSTR(cc.comments,1,INSTR(cc.comments,'|')-1) AS column_name_l
	,SUBSTR(cc.comments,INSTR(cc.comments,'|')+1,LENGTH(cc.comments)-INSTR(cc.comments,'|')) AS column_note
	,(SELECT
		'PK'
	 FROM
		user_constraints con
	 INNER JOIN
		user_cons_columns conc
	 ON
		con.table_name = conc.table_name
	 AND
		con.constraint_name = conc.constraint_name
	 WHERE
		con.constraint_type = 'P'
	 AND
		conc.table_name = c.table_name
	 AND
		conc.column_name = c.column_name) as primary_key
	,c.nullable
	,c.data_type
	,c.data_length
	,c.data_scale
	,c.data_default
FROM
	user_tab_columns c
LEFT JOIN
	user_col_comments cc
ON
	cc.table_name = c.table_name
AND
	cc.column_name = c.column_name
WHERE
	c.table_name = b_table_name
ORDER BY
	c.column_id;

BEGIN

DBMS_OUTPUT.PUT_LINE(
	    'PHYSICAL_TABLE_NAME'
	|| ',LOGICAL_TABLE_NAME'
	|| ',PHYSICAL_COLUMN_NAME'
	|| ',LOGICAL_COLUMN_NAME'
	|| ',PRIMARY_KEY'
	|| ',NULLABLE'
	|| ',DATA_TYPE'
	|| ',DATA_LENGTH'
	|| ',DATA_SCALE'
	|| ',DATA_DEFAULT'
	|| ',COLUMN_NOTE'
);

FOR rec_tab IN table_cur LOOP
	DBMS_OUTPUT.PUT_LINE(rec_tab.table_name || ',' || rec_tab.comments);

	FOR rec_col IN column_cur(rec_tab.table_name) LOOP
		DBMS_OUTPUT.PUT_LINE(
			   ',,' || rec_col.column_name_p
			|| ',' || rec_col.column_name_l
			|| ',' || rec_col.primary_key
			|| ',' || rec_col.nullable
			|| ',' || rec_col.data_type
			|| ',' || rec_col.data_length
			|| ',' || rec_col.data_scale
			|| ',' || rec_col.data_default
			|| ',' || rec_col.column_note
		);
	END LOOP;
END LOOP;

END;
/
QUIT;

テーブルコメントを テーブル論理名に
カラムコメントを カラム論理名＋備考につかってみました。
カラムコメントは セパレータを「|」にして論理名と備考をわけてます。

ストアドプロシージャのパラメータnocopyについて

http://www.shift-the-oracle.com/plsql/subprogram-parameter-mode.html
上記のサイトをみててORACLEでは参照渡ししているわけではないことを初めて知った。
参照渡しするにはnocopyヒントを付ける必要があるとのこと。

参照渡しならば、メモリ食わなくてすむのでコレクションなどの引き渡し時に
使いたいところですが、例外の時の動きが異なるらしい。
下記のプロシージャを実行すると

CREATE OR REPLACE PROCEDURE NCP_TEST
IS

	NCPNUM NUMBER;
	CPNUM NUMBER;

	PROCEDURE NCP(NUM IN OUT NOCOPY NUMBER)
	IS
	BEGIN
		NUM := 100;
		RAISE VALUE_ERROR;
	END;
	PROCEDURE CP(NUM IN OUT NUMBER)
	IS
	BEGIN
		NUM := 100;
		RAISE VALUE_ERROR;
	END;
BEGIN

	NCPNUM := 1;
	CPNUM := 1;

	BEGIN
		NCP(NCPNUM);
	EXCEPTION
		WHEN OTHERS THEN
			DBMS_OUTPUT.PUT_LINE(NCPNUM);
	END;


	BEGIN
		CP(CPNUM);
	EXCEPTION
		WHEN OTHERS THEN
			DBMS_OUTPUT.PUT_LINE(CPNUM);
	END;
END;
/

例外発生時にCPの方はCPNUMが１だが、NCPの方はNCPNUMは100になる。
NOCOPYヒントを付けてると、代入した時点で参照ポインタの値を直接書き換えるため
例外が発生しても値は書き換わっている。

逆にNOCOPYヒントを付けない場合は、呼び出したプロシージャ内での処理が完了したあとに
パラメータを書き換えるので処理中に例外が発生した場合には呼び出し元の
変数内容は書き換わらない。

バグの原因になりそうな気がするのでNOCOPYヒント使うときは例外処理に
注意してコーディングする必要がありますね。

動的ＳＱＬ実行プロシージャ

INDEX の REBUILD などをPLSQLで実行するときは
execute immediateだとddlの実行ができないので
通常はDBMS_SQLパッケージを使用するとおもいますが
なんかSQL実行したいだけなのにいろいろと記述しなきゃいけなくて
めんどくさいのでSQL投げたら実行してくれるプロシージャを作ってみた。

	PROCEDURE EXEC_SQL(SQL_TEXT IN VARCHAR2)
	IS
		V_CURSOR			INTEGER;
		V_POSITION			INTEGER;
	BEGIN
		V_CURSOR := DBMS_SQL.OPEN_CURSOR;
		DBMS_SQL.PARSE(V_CURSOR,SQL_TEXT, DBMS_SQL.NATIVE);
		V_POSITION := DBMS_SQL.EXECUTE(V_CURSOR);
		DBMS_SQL.CLOSE_CURSOR(V_CURSOR);
	END;

ただし、SELECTは不可、INSERT,UPDATE,DELETEではバインド不可
使い道が限られそう

ホットペッパーのWebAPIを使用してHTTPからXML取得、パースするプロシージャ

DBMS_XMLDOMでxmlのパースしてみたかったので
UTL_HTTP使ってWebAPI叩いてXML取得、パースするプロシージャを作ってみました。
ホットペッパーに怒られないか心配ですが（汗

CREATE OR REPLACE PROCEDURE XML_PARSE_TEST
IS

	FUNCTION GET_XML(URL IN VARCHAR2) RETURN DBMS_XMLDOM.DOMDOCUMENT
	IS
		REQ			UTL_HTTP.REQ;
		RESP		UTL_HTTP.RESP;
		XML_TEXT	CLOB;
		XML_LINE	VARCHAR2(1024);
	BEGIN
		REQ := UTL_HTTP.BEGIN_REQUEST(
			 URL=>URL
			,METHOD=>'GET'
		);
		UTL_HTTP.SET_BODY_CHARSET(
			 R=>REQ
			,CHARSET=>'UTF-8'
		);
		RESP := UTL_HTTP.GET_RESPONSE(R=>REQ);
		LOOP
			UTL_HTTP.READ_LINE(RESP, XML_LINE, TRUE);
			XML_TEXT := XML_TEXT || XML_LINE;
		END LOOP;
		UTL_HTTP.END_RESPONSE(RESP);
	EXCEPTION
		WHEN UTL_HTTP.END_OF_BODY THEN
			UTL_HTTP.END_RESPONSE(RESP);
			RETURN DBMS_XMLDOM.NEWDOMDOCUMENT(XML_TEXT);
		WHEN OTHERS THEN
			DBMS_OUTPUT.PUT_LINE(UTL_HTTP.GET_DETAILED_SQLERRM);
			RETURN NULL;
	END;

	FUNCTION GET_NODE_LIST(LIST_NAME IN VARCHAR2,XMLDOC IN DBMS_XMLDOM.DOMDOCUMENT) RETURN DBMS_XMLDOM.DOMNODELIST
	IS
		NODELIST			DBMS_XMLDOM.DOMNODELIST;
	BEGIN
		NODELIST := DBMS_XMLDOM.GETELEMENTSBYTAGNAME(
			XMLDOC, LIST_NAME
		);
		RETURN NODELIST;
	END;

	FUNCTION GET_NODE(NODE_NAME IN VARCHAR2,NODE_LIST IN DBMS_XMLDOM.DOMNODELIST) RETURN DBMS_XMLDOM.DOMNODE
	IS
		V_NODE	DBMS_XMLDOM.DOMNODE;
	BEGIN
		FOR I IN 1..DBMS_XMLDOM.GETLENGTH(NODE_LIST) LOOP
			V_NODE := DBMS_XMLDOM.ITEM(NODE_LIST, I-1);

			IF DBMS_XMLDOM.GETNODENAME(V_NODE) = NODE_NAME THEN
				RETURN DBMS_XMLDOM.GETFIRSTCHILD(V_NODE);
			END IF;

		END LOOP;
	END;

	FUNCTION GET_NODE_VALUE(NODE_NAME IN VARCHAR2,XMLDOC IN DBMS_XMLDOM.DOMDOCUMENT) RETURN VARCHAR2
	IS
	BEGIN
		RETURN DBMS_XMLDOM.GETNODEVALUE(
			GET_NODE(
				 NODE_NAME
				,DBMS_XMLDOM.GETCHILDNODES(
					DBMS_XMLDOM.ITEM(
						 GET_NODE_LIST('Results',XMLDOC)
						,0
					)
				)
			)
		);
	END;

	PROCEDURE SHOW_SHOPINFO
	IS
		XMLDOC				DBMS_XMLDOM.DOMDOCUMENT;
		NODELIST			DBMS_XMLDOM.DOMNODELIST;
		NODE				DBMS_XMLDOM.DOMNODE;
		SHOP_LIST			DBMS_XMLDOM.DOMNODELIST;
		START_COUNT			NUMBER;
		RESULT_COUNT		NUMBER;
		PAGE_PER_COUNT		CONSTANT NUMBER := 10;
	BEGIN

		XMLDOC := GET_XML('http://api.hotpepper.jp/GourmetSearch/V110/?key=guest&ServiceAreaCD=SA11&Count='||PAGE_PER_COUNT);

		RESULT_COUNT := GET_NODE_VALUE('NumberOfResults',XMLDOC);

		DBMS_OUTPUT.PUT_LINE('RESULT_COUNT='||RESULT_COUNT);
		DBMS_OUTPUT.PUT_LINE('--------------------------------------------------');

--		FOR I IN 0..TRUNC(RESULT_COUNT / PAGE_PER_COUNT) LOOP
		FOR I IN 0..9 LOOP
			START_COUNT := (I * PAGE_PER_COUNT) + 1;

			XMLDOC := GET_XML('http://api.hotpepper.jp/GourmetSearch/V110/?key=guest&ServiceAreaCD=SA11&Count='||PAGE_PER_COUNT||'&Start='||START_COUNT);

			NODELIST := GET_NODE_LIST('Shop',XMLDOC);

			FOR I IN 1..DBMS_XMLDOM.GETLENGTH(NODELIST) LOOP
				NODE := DBMS_XMLDOM.ITEM(NODELIST,I-1);
				SHOP_LIST := DBMS_XMLDOM.GETCHILDNODES(NODE);

				DBMS_OUTPUT.PUT_LINE(DBMS_XMLDOM.GETNODEVALUE(GET_NODE('ShopName',SHOP_LIST)));
				DBMS_OUTPUT.PUT_LINE(DBMS_XMLDOM.GETNODEVALUE(GET_NODE('ShopAddress',SHOP_LIST)));
				DBMS_OUTPUT.PUT_LINE('--------------------------------------------------');
			END LOOP;

			DBMS_XMLDOM.FREEDOCUMENT(XMLDOC);
		END LOOP;

	END;

BEGIN
	SHOW_SHOPINFO;
END;
/

ACL設定必須です。
あと、DOMDOCUMENTは使用後かならずFREEDOCUMENTで解放してください。
解放しないとメモリをモコモコつかいます。