Hatena::ブログ(Diary)

niwaのoracle日記 このページをアンテナに追加 RSSフィード Twitter

2015-10-15

with句の再帰問合せを利用した抜け番検索

22:53 | with句の再帰問合せを利用した抜け番検索を含むブックマーク with句の再帰問合せを利用した抜け番検索のブックマークコメント

Oracle WITH句で再帰問合せができると聞いたので

抜け番検索を作ってみました。

http://www.oracle.com/technetwork/jp/articles/otnj-sql-image7-1525406-ja.html#c

まず抜け番のあるテーブルレコードを作成。

create table test(num number);
insert into test values(1);
insert into test values(3);
insert into test values(5);
insert into test values(7);
insert into test values(9);
insert into test values(10);
commit;

抜け番問合せ

with num_list(cnt) as (
	select 1 from dual
	union all
	select 1 + cnt from num_list where cnt < 10
)
select
	n.cnt
from
	n_list n
where
	not exitst(
		select
			1
		from
			test t
		where
			t.num = n.cnt)
)
order by n.cnt;

with句のエイリアスnum_listをwith句内で呼び出しています。

再帰問合せはUNION ALLのみ対応してるみたいですね。

union allの下のselectでwhere句にて最大件数を指定しないとサイクルしていると怒られます。

これに 12c で追加された fetch句で

fetch first 1 rows only

とかを記載すれば、空き番の一番若い番号を取得できそうですね。

蛇足ですが

抜け番を使用したinsertを行う場合は整合性を保つためにはテーブル毎ロックが必要です。


数年ぶりのブログなのでちょっと緊張

トラックバック - http://d.hatena.ne.jp/niwanos/20151015

2011-01-18

SQLLoaderのCTLファイル作成Function

17:00 | SQLLoaderのCTLファイル作成Functionを含むブックマーク SQLLoaderのCTLファイル作成Functionのブックマークコメント

tsv用

CREATE OR REPLACE FUNCTION MAKE_CTL
(
	P_TABLE_NAME IN VARCHAR2
)
--RETURN CLOB
RETURN VARCHAR2
IS
	CURSOR CUR_TABLE_COLUMN(V_TABLE_NAME VARCHAR2) IS SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE TABLE_NAME = V_TABLE_NAME;
--	CTL_STR CLOB;
	CTL_STR VARCHAR2(4000);
	CR CONSTANT CHAR(1) := CHR(13);
	LF CONSTANT CHAR(1) := CHR(10);
	NL CONSTANT CHAR(2) := CR || LF;
BEGIN
	FOR COL IN CUR_TABLE_COLUMN(P_TABLE_NAME) LOOP
		IF CUR_TABLE_COLUMN%ROWCOUNT > 1 THEN
			CTL_STR := CTL_STR || ',';
		ELSE
			CTL_STR := CTL_STR || ' ';
		END IF;
		CTL_STR := CTL_STR || COL.COLUMN_NAME || NL;
	END LOOP;

	CTL_STR :=
		         'OPTIONS(SKIP=1,ERRORS=-1)'
		|| NL || 'LOAD DATA'
		|| NL || 'REPLACE'
		|| NL || 'INTO TABLE ' || P_TABLE_NAME
		|| NL || 'FIELD TERMINATED BY X''09'''
		|| NL || 'TRAILING NULLCOLS'
		|| NL || '('
		|| NL || CTL_STR
		|| NL || ')';

	RETURN CTL_STR;
END;
/

TYPEのMEMBER FUNCTION PROCEDURE を使ってVARCHAR2をラッピングしてみた

16:57 | TYPEのMEMBER FUNCTION PROCEDURE を使ってVARCHAR2をラッピングしてみたを含むブックマーク TYPEのMEMBER FUNCTION PROCEDURE を使ってVARCHAR2をラッピングしてみたのブックマークコメント

sqlloader用のctlファイル作成function

tsv用

CREATE OR REPLACE FUNCTION MAKE_CTL
(
	P_TABLE_NAME IN VARCHAR2
)
--RETURN CLOB
RETURN VARCHAR2
IS
	CURSOR CUR_TABLE_COLUMN(V_TABLE_NAME VARCHAR2) IS SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE TABLE_NAME = V_TABLE_NAME;
--	CTL_STR CLOB;
	CTL_STR VARCHAR2(4000);
	CR CONSTANT CHAR(1) := CHR(13);
	LF CONSTANT CHAR(1) := CHR(10);
	NL CONSTANT CHAR(2) := CR || LF;
BEGIN
	FOR COL IN CUR_TABLE_COLUMN(P_TABLE_NAME) LOOP
		IF CUR_TABLE_COLUMN%ROWCOUNT > 1 THEN
			CTL_STR := CTL_STR || ',';
		ELSE
			CTL_STR := CTL_STR || ' ';
		END IF;
		CTL_STR := CTL_STR || COL.COLUMN_NAME || NL;
	END LOOP;

	CTL_STR :=
		         'OPTIONS(SKIP=1,ERRORS=-1)'
		|| NL || 'LOAD DATA'
		|| NL || 'REPLACE'
		|| NL || 'INTO TABLE ' || P_TABLE_NAME
		|| NL || 'FIELD TERMINATED BY X''09'''
		|| NL || 'TRAILING NULLCOLS'
		|| NL || '('
		|| NL || CTL_STR
		|| NL || ')';

	RETURN CTL_STR;
END;
/
トラックバック - http://d.hatena.ne.jp/niwanos/20110118

2010-11-30

TYPEのMEMBER FUNCTION PROCEDURE を使ってVARCHAR2をラッピングしてみた

19:19 | TYPEのMEMBER FUNCTION PROCEDURE を使ってVARCHAR2をラッピングしてみたを含むブックマーク TYPEのMEMBER FUNCTION PROCEDURE を使ってVARCHAR2をラッピングしてみたのブックマークコメント

CREATE OR REPLACE TYPE STRING_TYPE
AS OBJECT (
	 STR VARCHAR2(4000)
	,MEMBER FUNCTION LEN RETURN NUMBER
	,MEMBER FUNCTION SUBSTRING(S IN NUMBER,L IN NUMBER) RETURN VARCHAR2
	,MEMBER PROCEDURE PRINT
)
/
CREATE OR REPLACE TYPE BODY STRING_TYPE AS
	MEMBER FUNCTION LEN RETURN NUMBER
	IS
	BEGIN
		RETURN LENGTH(STR);
	END;
	MEMBER FUNCTION SUBSTRING(S IN NUMBER,L IN NUMBER) RETURN VARCHAR2
	IS
	BEGIN
		RETURN SUBSTR(STR,S,L);
	END;
	MEMBER PROCEDURE PRINT
	IS
	BEGIN
		DBMS_OUTPUT.PUT_LINE(STR);
	END;
END;
/

STRING_TYPEってtypeを作ってlengthとsubstrを実装した。

実装

CREATE OR REPLACE PROCEDURE TEST
IS
	MOJI STRING_TYPE;
BEGIN
	MOJI := STRING_TYPE('AIUEO');
	MOJI.PRINT;
	DBMS_OUTPUT.PUT_LINE('LENGTH=' || MOJI.LEN);
	DBMS_OUTPUT.PUT_LINE('SUBSTR(2,2)=' || MOJI.SUBSTRING(1,1));
END;
/

実行結果

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2    MOJI STRING_TYPE;
  3  BEGIN
  4    MOJI := STRING_TYPE('AIUEO');
  5    MOJI.PRINT;  
  6    DBMS_OUTPUT.PUT_LINE('LENGTH=' || MOJI.LEN);
  7    DBMS_OUTPUT.PUT_LINE('SUBSTR(2,2)=' || MOJI.SUBSTRING(1,1));
  8  END;
  9  /
AIUEO
LENGTH=5
SUBSTR(2,2)=A

PL/SQL procedure successfully completed.
トラックバック - http://d.hatena.ne.jp/niwanos/20101130

2010-09-30

遅延制約

14:45 | 遅延制約を含むブックマーク 遅延制約のブックマークコメント

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

夢が広がりますね。

2010-09-29

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

19:31 | CSVを作成するためのSQLを生成するFunctionを含むブックマーク 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

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

トラックバック - http://d.hatena.ne.jp/niwanos/20100929