appendヒントとかbulk collectとかcollectionとかconnect byとかの話(nologgingパターンは忘れた)

はじめに

plsqlにdml文書いてあそんでいただけの話です。ほんとうにとりとめのない話です。

小ネタ

connect by level <= 10000000はoraerror。1000万件エラー。100万件はOK。connect byの限界(デフォルト)。ただメモリいじればいけると思う。ただマウントからやり直してインスタンス再起動はめんどい。結合で回避。

コード表示

create table tbl___src___ as select level as rn, chr(level+64) as str from dual connect by level <= 10000000
エラー・レポート -
ORA-30009: CONNECT BY操作のためのメモリーが足りません
30009. 0000 -  "Not enough memory for %s operation"
*Cause:    The memory size was not sufficient to process all the levels of the
           hierarchy specified by the query.
*Action:   In WORKAREA_SIZE_POLICY=AUTO mode, set PGA_AGGREGATE_TARGET to
           a reasonably larger value.
           Or, in WORKAREA_SIZE_POLICY=MANUAL mode, set SORT_AREA_SIZE to a
           reasonably larger value.

select * from V$PARAMETER where NAME in ('workarea_size_policy','pga_aggregate_target');

drop table tbl___src___ purge;
--limit
create table tbl___src___ as select level as rn, chr(level+64) as str from dual connect by level <= 1000000;
--ORA-30009
create table tbl___src___ as select level as rn, chr(level+64) as str from dual connect by level <= 10000000;
--join
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;

オブジェクト型とコレクション型作る

クエリ

コード表示

create or replace type item is object (rn number,str varchar2(10));
/
create or replace type liz is table of item;
/

小ネタ

正規表現つかうとオシャンにいろいろできたりする。スクリプトをコールする前で前処理を組み込みたいとき、どうしたらいいかと思う。正規表現使ってハンディにできないかと考えたら、できるという話。

まず、コンパイルする。コンソールにはかれたプロシージャ名をコピってexecなんとかに書き換える。

コード表示

exec PROC___SELECT_INSERT___
exec PROC___DIRECT_PATH_INSERT___
exec PROC___NO_APPEND_HINT_DONQQQT_BULK_COLLECT_INSERT_UNTIL_ALL_PUSH___
exec PROC___APPEND_HINT_DONQQQT_BULK_COLLECT_INSERT_UNTIL_ALL_PUSH___
exec PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_NO_LIMIT___
exec PROC___APPEND_HINT_BULK_COLLECT_INSERT_NO_LIMIT___
exec PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_1000___
exec PROC___APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_1000___
exec PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_10000___
exec PROC___APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_10000___
exec PROC___NO_APPEND_HINT_NO_BULK_COLLECT_INSERT___
exec PROC___APPEND_HINT_NO_BULK_COLLECT_INSERT___

--置換前:$
--置換後:;\r\n/

exec PROC___SELECT_INSERT___;
/
exec PROC___DIRECT_PATH_INSERT___;
/
exec PROC___NO_APPEND_HINT_DONQQQT_BULK_COLLECT_INSERT_UNTIL_ALL_PUSH___;
/
exec PROC___APPEND_HINT_DONQQQT_BULK_COLLECT_INSERT_UNTIL_ALL_PUSH___;
/
exec PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_NO_LIMIT___;
/
exec PROC___APPEND_HINT_BULK_COLLECT_INSERT_NO_LIMIT___;
/
exec PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_1000___;
/
exec PROC___APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_1000___;
/
exec PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_10000___;
/
exec PROC___APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_10000___;
/
exec PROC___NO_APPEND_HINT_NO_BULK_COLLECT_INSERT___;
/
exec PROC___APPEND_HINT_NO_BULK_COLLECT_INSERT___;
/

必要な前処理が以下であるとする。

コード表示

drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;

前処理を組み込むまえにonelinerにしておく。

コード表示

--置換前:$
--置換後:\\r\\n

drop table tbl___src___ purge;\r\n
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level &lt;= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;\r\n
drop table tbl___tar___ purge;\r\n
create table tbl___tar___ as select * from tbl___src___ where 1 &lt;&gt; 1;\r\n
alter system checkpoint;\r\n
--alter system switch logfile;\r\n
alter system flush shared_pool;\r\n
alter system flush buffer_cache;\r\n

--置換前:\r\n
--置換後:

drop table tbl___src___ purge;\r\ncreate table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level &lt;= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;\r\ndrop table tbl___tar___ purge;\r\ncreate table tbl___tar___ as select * from tbl___src___ where 1 &lt;&gt; 1;\r\nalter system checkpoint;\r\n--alter system switch logfile;\r\nalter system flush shared_pool;\r\nalter system flush buffer_cache;\r\n

onelinerにした後のスニペットをexecの前にぴろっと入れる。

コード表示

--置換前:^(?=exec)
--置換後:drop table tbl___src___ purge;\r\ncreate table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;\r\ndrop table tbl___tar___ purge;\r\ncreate table tbl___tar___ as select * from tbl___src___ where 1 <> 1;\r\nalter system checkpoint;\r\n--alter system switch logfile;\r\nalter system flush shared_pool;\r\nalter system flush buffer_cache;\r\n

drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___SELECT_INSERT___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___DIRECT_PATH_INSERT___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___NO_APPEND_HINT_DONQQQT_BULK_COLLECT_INSERT_UNTIL_ALL_PUSH___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___APPEND_HINT_DONQQQT_BULK_COLLECT_INSERT_UNTIL_ALL_PUSH___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_NO_LIMIT___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___APPEND_HINT_BULK_COLLECT_INSERT_NO_LIMIT___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_1000___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_1000___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_10000___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_10000___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___NO_APPEND_HINT_NO_BULK_COLLECT_INSERT___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___APPEND_HINT_NO_BULK_COLLECT_INSERT___;
/


コンパイルスクリプト

まぁあそびです。

コード表示

create or replace procedure proc___select_insert___ as
	srt_tp timestamp;
	end_tp timestamp;
begin
	select systimestamp into srt_tp from dual;
	insert into tbl___tar___
		select
			*
		from
			tbl___src___;
	commit;
	select systimestamp into end_tp from dual;
	dbms_output.put_line('proc___select_insert___');
	dbms_output.put_line(end_tp - srt_tp);
end;
/
create or replace procedure proc___direct_path_insert___ as
	srt_tp timestamp;
	end_tp timestamp;
begin
	select systimestamp into srt_tp from dual;
	insert /*+ append */ into tbl___tar___
		select
			*
		from
			tbl___src___;
	commit;
	select systimestamp into end_tp from dual;
	dbms_output.put_line('proc___direct_path_insert___');
	dbms_output.put_line(end_tp - srt_tp);
end;
/
create or replace procedure proc___no_append_hint_donqqqt_bulk_collect_insert_until_all_push___ as
	lz   liz;
	srt_tp timestamp;
	end_tp timestamp;
	cursor csr is select
		item(rn, str)
	              from
		tbl___src___;
begin
	select systimestamp into srt_tp from dual;
	open csr;
	loop
		begin
			fetch csr bulk collect into lz;
			exit when csr%notfound;
		end;
	end loop;
	close csr;
	select systimestamp into end_tp from dual;
	dbms_output.put_line('proc___no_append_hint_donqqqt_bulk_collect_insert_until_all_push___:bulk_collect_fetch');
	dbms_output.put_line(end_tp - srt_tp);
	select systimestamp into srt_tp from dual;
	insert into tbl___tar___
		select
			*
		from
			table ( lz );
	commit;
	select systimestamp into end_tp from dual;
	dbms_output.put_line('proc___no_append_hint_donqqqt_bulk_collect_insert_until_all_push___');
	dbms_output.put_line(end_tp - srt_tp);
end;
/
create or replace procedure proc___append_hint_donqqqt_bulk_collect_insert_until_all_push___ as
	lz   liz;
	srt_tp timestamp;
	end_tp timestamp;
	cursor csr is select
		item(rn, str)
	              from
		tbl___src___;
begin
	select systimestamp into srt_tp from dual;
	open csr;
	loop
		begin
			fetch csr bulk collect into lz;
			exit when csr%notfound;
		end;
	end loop;
	close csr;
	select systimestamp into end_tp from dual;
	dbms_output.put_line('proc___append_hint_donqqqt_bulk_collect_insert_until_all_push___:bulk_collect_fetch');
	dbms_output.put_line(end_tp - srt_tp);
	select systimestamp into srt_tp from dual;
	insert /*+ append */ into tbl___tar___
		select
			*
		from
			table ( lz );
	commit;
	select systimestamp into end_tp from dual;
	dbms_output.put_line('proc___append_hint_donqqqt_bulk_collect_insert_until_all_push___');
	dbms_output.put_line(end_tp - srt_tp);
end;
/
create or replace procedure proc___no_append_hint_bulk_collect_insert_no_limit___ as
	lz   liz;
	srt_tp timestamp;
	end_tp timestamp;
	cursor csr is select
		item(rn, str)
	              from
		tbl___src___;
begin
	select systimestamp into srt_tp from dual;
	open csr;
	loop
		begin
			fetch csr bulk collect into lz;
			exit when csr%notfound;
			insert into tbl___tar___
				select
					*
				from
					table ( lz );
			commit;
		end;
	end loop;
	close csr;
	select systimestamp into end_tp from dual;
	dbms_output.put_line('proc___no_append_hint_bulk_collect_insert_no_limit___');
	dbms_output.put_line(end_tp - srt_tp);
end;
/
create or replace procedure proc___append_hint_bulk_collect_insert_no_limit___ as
	lz   liz;
	srt_tp timestamp;
	end_tp timestamp;
	cursor csr is select
		item(rn, str)
	              from
		tbl___src___;
begin
	select systimestamp into srt_tp from dual;
	open csr;
	loop
		begin
			fetch csr bulk collect into lz;
			exit when csr%notfound;
			insert /*+ append */ into tbl___tar___
				select
					*
				from
					table ( lz );
			commit;
		end;
	end loop;
	close csr;
	select systimestamp into end_tp from dual;
	dbms_output.put_line('proc___append_hint_bulk_collect_insert_no_limit___');
	dbms_output.put_line(end_tp - srt_tp);
end;
/
create or replace procedure proc___no_append_hint_bulk_collect_insert_limit_1000___ as
	lz   liz;
	srt_tp timestamp;
	end_tp timestamp;
	cursor csr is select
		item(rn, str)
	              from
		tbl___src___;
begin
	select systimestamp into srt_tp from dual;
	open csr;
	loop
		begin
			fetch csr bulk collect into lz limit 1000;
			exit when csr%notfound;
			insert into tbl___tar___
				select
					*
				from
					table ( lz );
			commit;
		end;
	end loop;
	close csr;
	select systimestamp into end_tp from dual;
	dbms_output.put_line('proc___no_append_hint_bulk_collect_insert_limit_1000___');
	dbms_output.put_line(end_tp - srt_tp);
end;
/
create or replace procedure proc___append_hint_bulk_collect_insert_limit_1000___ as
	lz   liz;
	srt_tp timestamp;
	end_tp timestamp;
	cursor csr is select
		item(rn, str)
	              from
		tbl___src___;
begin
	select systimestamp into srt_tp from dual;
	open csr;
	loop
		begin
			fetch csr bulk collect into lz limit 1000;
			exit when csr%notfound;
			insert /*+ append */ into tbl___tar___
				select
					*
				from
					table ( lz );
			commit;
		end;
	end loop;
	close csr;
	select systimestamp into end_tp from dual;
	dbms_output.put_line('proc___append_hint_bulk_collect_insert_limit_1000___');
	dbms_output.put_line(end_tp - srt_tp);
end;
/
create or replace procedure proc___no_append_hint_bulk_collect_insert_limit_10000___ as
	lz   liz;
	srt_tp timestamp;
	end_tp timestamp;
	cursor csr is select
		item(rn, str)
	              from
		tbl___src___;
begin
	select systimestamp into srt_tp from dual;
	open csr;
	loop
		begin
			fetch csr bulk collect into lz limit 10000;
			exit when csr%notfound;
			insert into tbl___tar___
				select
					*
				from
					table ( lz );
			commit;
		end;
	end loop;
	close csr;
	select systimestamp into end_tp from dual;
	dbms_output.put_line('proc___no_append_hint_bulk_collect_insert_limit_10000___');
	dbms_output.put_line(end_tp - srt_tp);
end;
/
create or replace procedure proc___append_hint_bulk_collect_insert_limit_10000___ as
	lz   liz;
	srt_tp timestamp;
	end_tp timestamp;
	cursor csr is select
		item(rn, str)
	              from
		tbl___src___;
begin
	select systimestamp into srt_tp from dual;
	open csr;
	loop
		begin
			fetch csr bulk collect into lz limit 10000;
			exit when csr%notfound;
			insert /*+ append */ into tbl___tar___
				select
					*
				from
					table ( lz );
			commit;
		end;
	end loop;
	close csr;
	select systimestamp into end_tp from dual;
	dbms_output.put_line('proc___append_hint_bulk_collect_insert_limit_10000___');
	dbms_output.put_line(end_tp - srt_tp);
end;
/
create or replace procedure proc___no_append_hint_no_bulk_collect_insert___ as
	lz   liz;
	srt_tp timestamp;
	end_tp timestamp;
	cursor csr is select
		cast(collect(item(rn, str) ) as liz)
	              from
		tbl___src___;
begin
	select systimestamp into srt_tp from dual;
	open csr;
	loop
		begin
			fetch csr into lz;
			exit when csr%notfound;
			insert into tbl___tar___
				select
					*
				from
					table ( lz );
			commit;
		end;
	end loop;
	close csr;
	select systimestamp into end_tp from dual;
	dbms_output.put_line('proc___no_append_hint_no_bulk_collect_insert___');
	dbms_output.put_line(end_tp - srt_tp);
end;
/
create or replace procedure proc___append_hint_no_bulk_collect_insert___ as
	lz   liz;
	srt_tp timestamp;
	end_tp timestamp;
	cursor csr is select
		cast(collect(item(rn, str) ) as liz)
	              from
		tbl___src___;
begin
	select systimestamp into srt_tp from dual;
	open csr;
	loop
		begin
			fetch csr into lz;
			exit when csr%notfound;
			insert /*+ append */ into tbl___tar___
				select
					*
				from
					table ( lz );
			commit;
		end;
	end loop;
	close csr;
	select systimestamp into end_tp from dual;
	dbms_output.put_line('proc___append_hint_no_bulk_collect_insert___');
	dbms_output.put_line(end_tp - srt_tp);
end;
/

小ネタからできた実行スクリプト

コード表示

drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___SELECT_INSERT___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___DIRECT_PATH_INSERT___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___NO_APPEND_HINT_DONQQQT_BULK_COLLECT_INSERT_UNTIL_ALL_PUSH___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___APPEND_HINT_DONQQQT_BULK_COLLECT_INSERT_UNTIL_ALL_PUSH___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_NO_LIMIT___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___APPEND_HINT_BULK_COLLECT_INSERT_NO_LIMIT___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_1000___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_1000___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_10000___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_10000___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___NO_APPEND_HINT_NO_BULK_COLLECT_INSERT___;
/
drop table tbl___src___ purge;
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;
alter system checkpoint;
--alter system switch logfile;
alter system flush shared_pool;
alter system flush buffer_cache;
exec PROC___APPEND_HINT_NO_BULK_COLLECT_INSERT___;
/

実行結果コンソールログ

ここから必要な情報を抜き取る。そのままべと。

コード表示


Table TBL___SRC___が削除されました。


Table TBL___SRC___は作成されました。


Table TBL___TAR___が削除されました。


Table TBL___TAR___は作成されました。


System CHECKPOINTが変更されました。


System FLUSHが変更されました。


System FLUSHが変更されました。

proc___select_insert___
+000000000 00:00:01.451000000


PL/SQLプロシージャが正常に完了しました。


Table TBL___SRC___が削除されました。


Table TBL___SRC___は作成されました。


Table TBL___TAR___が削除されました。


Table TBL___TAR___は作成されました。


System CHECKPOINTが変更されました。


System FLUSHが変更されました。


System FLUSHが変更されました。

proc___direct_path_insert___
+000000000 00:00:01.549663000


PL/SQLプロシージャが正常に完了しました。


Table TBL___SRC___が削除されました。


Table TBL___SRC___は作成されました。


Table TBL___TAR___が削除されました。


Table TBL___TAR___は作成されました。


System CHECKPOINTが変更されました。


System FLUSHが変更されました。


System FLUSHが変更されました。

proc___no_append_hint_donqqqt_bulk_collect_insert_until_all_push___:bulk_collect_fetch
+000000000 00:00:07.844085000
proc___no_append_hint_donqqqt_bulk_collect_insert_until_all_push___
+000000000 00:00:08.425256000


PL/SQLプロシージャが正常に完了しました。


Table TBL___SRC___が削除されました。


Table TBL___SRC___は作成されました。


Table TBL___TAR___が削除されました。


Table TBL___TAR___は作成されました。


System CHECKPOINTが変更されました。


System FLUSHが変更されました。


System FLUSHが変更されました。

proc___append_hint_donqqqt_bulk_collect_insert_until_all_push___:bulk_collect_fetch
+000000000 00:00:07.904504000
proc___append_hint_donqqqt_bulk_collect_insert_until_all_push___
+000000000 00:00:10.287960000


PL/SQLプロシージャが正常に完了しました。


Table TBL___SRC___が削除されました。


Table TBL___SRC___は作成されました。


Table TBL___TAR___が削除されました。


Table TBL___TAR___は作成されました。


System CHECKPOINTが変更されました。


System FLUSHが変更されました。


System FLUSHが変更されました。

proc___no_append_hint_bulk_collect_insert_no_limit___
+000000000 00:00:07.862762000


PL/SQLプロシージャが正常に完了しました。


Table TBL___SRC___が削除されました。


Table TBL___SRC___は作成されました。


Table TBL___TAR___が削除されました。


Table TBL___TAR___は作成されました。


System CHECKPOINTが変更されました。


System FLUSHが変更されました。


System FLUSHが変更されました。

proc___append_hint_bulk_collect_insert_no_limit___
+000000000 00:00:07.909035000


PL/SQLプロシージャが正常に完了しました。


Table TBL___SRC___が削除されました。


Table TBL___SRC___は作成されました。


Table TBL___TAR___が削除されました。


Table TBL___TAR___は作成されました。


System CHECKPOINTが変更されました。


System FLUSHが変更されました。


System FLUSHが変更されました。

proc___no_append_hint_bulk_collect_insert_limit_1000___
+000000000 00:00:14.981688000


PL/SQLプロシージャが正常に完了しました。


Table TBL___SRC___が削除されました。


Table TBL___SRC___は作成されました。


Table TBL___TAR___が削除されました。


Table TBL___TAR___は作成されました。


System CHECKPOINTが変更されました。


System FLUSHが変更されました。


System FLUSHが変更されました。

proc___append_hint_bulk_collect_insert_limit_1000___
+000000000 00:01:45.558595000


PL/SQLプロシージャが正常に完了しました。


Table TBL___SRC___が削除されました。


Table TBL___SRC___は作成されました。


Table TBL___TAR___が削除されました。


Table TBL___TAR___は作成されました。


System CHECKPOINTが変更されました。


System FLUSHが変更されました。


System FLUSHが変更されました。

proc___no_append_hint_bulk_collect_insert_limit_10000___
+000000000 00:00:14.202969000


PL/SQLプロシージャが正常に完了しました。


Table TBL___SRC___が削除されました。


Table TBL___SRC___は作成されました。


Table TBL___TAR___が削除されました。


Table TBL___TAR___は作成されました。


System CHECKPOINTが変更されました。


System FLUSHが変更されました。


System FLUSHが変更されました。

proc___append_hint_bulk_collect_insert_limit_10000___
+000000000 00:00:25.469245000


PL/SQLプロシージャが正常に完了しました。


Table TBL___SRC___が削除されました。


Table TBL___SRC___は作成されました。


Table TBL___TAR___が削除されました。


Table TBL___TAR___は作成されました。


System CHECKPOINTが変更されました。


System FLUSHが変更されました。


System FLUSHが変更されました。

proc___no_append_hint_no_bulk_collect_insert___
+000000000 00:00:24.833968000


PL/SQLプロシージャが正常に完了しました。


Table TBL___SRC___が削除されました。


Table TBL___SRC___は作成されました。


Table TBL___TAR___が削除されました。


Table TBL___TAR___は作成されました。


System CHECKPOINTが変更されました。


System FLUSHが変更されました。


System FLUSHが変更されました。

proc___append_hint_no_bulk_collect_insert___
+000000000 00:00:18.574835000


PL/SQLプロシージャが正常に完了しました。

--置換前:^(?!.*(proc|\+)).*$
--置換後:

--置換前:^\r\n
--置換後:

proc___select_insert___
+000000000 00:00:01.451000000
proc___direct_path_insert___
+000000000 00:00:01.549663000
proc___no_append_hint_donqqqt_bulk_collect_insert_until_all_push___:bulk_collect_fetch
+000000000 00:00:07.844085000
proc___no_append_hint_donqqqt_bulk_collect_insert_until_all_push___
+000000000 00:00:08.425256000
proc___append_hint_donqqqt_bulk_collect_insert_until_all_push___:bulk_collect_fetch
+000000000 00:00:07.904504000
proc___append_hint_donqqqt_bulk_collect_insert_until_all_push___
+000000000 00:00:10.287960000
proc___no_append_hint_bulk_collect_insert_no_limit___
+000000000 00:00:07.862762000
proc___append_hint_bulk_collect_insert_no_limit___
+000000000 00:00:07.909035000
proc___no_append_hint_bulk_collect_insert_limit_1000___
+000000000 00:00:14.981688000
proc___append_hint_bulk_collect_insert_limit_1000___
+000000000 00:01:45.558595000
proc___no_append_hint_bulk_collect_insert_limit_10000___
+000000000 00:00:14.202969000
proc___append_hint_bulk_collect_insert_limit_10000___
+000000000 00:00:25.469245000
proc___no_append_hint_no_bulk_collect_insert___
+000000000 00:00:24.833968000
proc___append_hint_no_bulk_collect_insert___
+000000000 00:00:18.574835000


--置換前:\r\n
--置換後:\t

proc___select_insert___	+000000000 00:00:01.451000000	proc___direct_path_insert___	+000000000 00:00:01.549663000	proc___no_append_hint_donqqqt_bulk_collect_insert_until_all_push___:bulk_collect_fetch	+000000000 00:00:07.844085000	proc___no_append_hint_donqqqt_bulk_collect_insert_until_all_push___	+000000000 00:00:08.425256000	proc___append_hint_donqqqt_bulk_collect_insert_until_all_push___:bulk_collect_fetch	+000000000 00:00:07.904504000	proc___append_hint_donqqqt_bulk_collect_insert_until_all_push___	+000000000 00:00:10.287960000	proc___no_append_hint_bulk_collect_insert_no_limit___	+000000000 00:00:07.862762000	proc___append_hint_bulk_collect_insert_no_limit___	+000000000 00:00:07.909035000	proc___no_append_hint_bulk_collect_insert_limit_1000___	+000000000 00:00:14.981688000	proc___append_hint_bulk_collect_insert_limit_1000___	+000000000 00:01:45.558595000	proc___no_append_hint_bulk_collect_insert_limit_10000___	+000000000 00:00:14.202969000	proc___append_hint_bulk_collect_insert_limit_10000___	+000000000 00:00:25.469245000	proc___no_append_hint_no_bulk_collect_insert___	+000000000 00:00:24.833968000	proc___append_hint_no_bulk_collect_insert___	+000000000 00:00:18.574835000	


--置換前:proc
--置換後:\r\nproc


proc___select_insert___	+000000000 00:00:01.451000000	
proc___direct_path_insert___	+000000000 00:00:01.549663000	
proc___no_append_hint_donqqqt_bulk_collect_insert_until_all_push___:bulk_collect_fetch	+000000000 00:00:07.844085000	
proc___no_append_hint_donqqqt_bulk_collect_insert_until_all_push___	+000000000 00:00:08.425256000	
proc___append_hint_donqqqt_bulk_collect_insert_until_all_push___:bulk_collect_fetch	+000000000 00:00:07.904504000	
proc___append_hint_donqqqt_bulk_collect_insert_until_all_push___	+000000000 00:00:10.287960000	
proc___no_append_hint_bulk_collect_insert_no_limit___	+000000000 00:00:07.862762000	
proc___append_hint_bulk_collect_insert_no_limit___	+000000000 00:00:07.909035000	
proc___no_append_hint_bulk_collect_insert_limit_1000___	+000000000 00:00:14.981688000	
proc___append_hint_bulk_collect_insert_limit_1000___	+000000000 00:01:45.558595000	
proc___no_append_hint_bulk_collect_insert_limit_10000___	+000000000 00:00:14.202969000	
proc___append_hint_bulk_collect_insert_limit_10000___	+000000000 00:00:25.469245000	
proc___no_append_hint_no_bulk_collect_insert___	+000000000 00:00:24.833968000	
proc___append_hint_no_bulk_collect_insert___	+000000000 00:00:18.574835000	

測定結果

普通にcollectionとかでこねくりまわさんほうがよさげ。limit句で処理量を多くすると早くなるのはほぉーとなった。

おまけ

こういうのって追加パターンあるとメンテめんどってなるから、なるたけハンディにやりたい。そういった話。最初に思いついたやつ。

コード表示

declare
	cursor csr is
	with sub as(
	select 'PROC___SELECT_INSERT___'as proc from dual union all
	select 'PROC___DIRECT_PATH_INSERT___'as proc from dual union all
	select 'PROC___NO_APPEND_HINT_DONQQQT_BULK_COLLECT_INSERT_UNTIL_ALL_PUSH___'as proc from dual union all
	select 'PROC___APPEND_HINT_DONQQQT_BULK_COLLECT_INSERT_UNTIL_ALL_PUSH___'as proc from dual union all
	select 'PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_NO_LIMIT___'as proc from dual union all
	select 'PROC___APPEND_HINT_BULK_COLLECT_INSERT_NO_LIMIT___'as proc from dual union all
	select 'PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_1000___'as proc from dual union all
	select 'PROC___APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_1000___'as proc from dual union all
	select 'PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_10000___'as proc from dual union all
	select 'PROC___APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_10000___'as proc from dual union all
	select 'PROC___NO_APPEND_HINT_NO_BULK_COLLECT_INSERT___'as proc from dual union all
	select 'PROC___APPEND_HINT_NO_BULK_COLLECT_INSERT___'as proc from dual
	)select * from sub;

proc clob;

begin
	open csr;
	loop
		begin
		fetch csr into proc;
		exit when csr%notfound;
		execute immediate 'begin '|| proc || '; end;';
		exception when others then dbms_output.put_line('[ '|| sqlcode||']'||sqlerrm);
		end;
	end loop;
	close csr;
end;
/

うえのだとあるスクリプトだけ実行したいってなったときコメントアウトするけど、アウトするところによってはコンパイルエラーとなってめんど。なので、実行スクリプト名を格納するテーブル適当につくる。こんな感じ。

コード表示

drop table exec_script purge;
create table exec_script as
	with sub as(
	select 'PROC___SELECT_INSERT___'as proc from dual union all
	select 'PROC___DIRECT_PATH_INSERT___'as proc from dual union all
	select 'PROC___NO_APPEND_HINT_DONQQQT_BULK_COLLECT_INSERT_UNTIL_ALL_PUSH___'as proc from dual union all
	select 'PROC___APPEND_HINT_DONQQQT_BULK_COLLECT_INSERT_UNTIL_ALL_PUSH___'as proc from dual union all
	select 'PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_NO_LIMIT___'as proc from dual union all
	select 'PROC___APPEND_HINT_BULK_COLLECT_INSERT_NO_LIMIT___'as proc from dual union all
	select 'PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_1000___'as proc from dual union all
	select 'PROC___APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_1000___'as proc from dual union all
	select 'PROC___NO_APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_10000___'as proc from dual union all
	select 'PROC___APPEND_HINT_BULK_COLLECT_INSERT_LIMIT_10000___'as proc from dual union all
	select 'PROC___NO_APPEND_HINT_NO_BULK_COLLECT_INSERT___'as proc from dual union all
	select 'PROC___APPEND_HINT_NO_BULK_COLLECT_INSERT___'as proc from dual
	)select row_number() over (order by rownum) as seq,proc from sub;

select * from exec_script;

んで、cursorのところちょちょっとなおす。こんな感じ。where句に実行したいseqを指定できるようになるから、何番目以降とか指定できたり、特定の処理だけとかもできる。

コード表示

declare
	cursor csr is select proc from exec_script where seq = 1;
--	cursor csr is select proc from exec_script where seq >= 1;
--	cursor csr is select proc from exec_script where seq between 3 and 5;
	proc clob;
begin
	open csr;
	loop
		begin
		fetch csr into proc;
		exit when csr%notfound;
		execute immediate 'begin '|| proc || '; end;';
		exception when others then dbms_output.put_line('[ '|| sqlcode||']'||sqlerrm);
		end;
	end loop;
	close csr;
end;
/

前処理とかもテーブル入れておけばいいですかね。後処理とかも同様なノリでできるでしょう。

コード表示

drop table pre_script purge;
create table pre_script as
with sub as(
select 'drop table tbl___src___ purge' as proc from dual union all
select 'create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7' as proc from dual union all
select 'drop table tbl___tar___ purge' as proc from dual union all
select 'create table tbl___tar___ as select * from tbl___src___ where 1 <> 1' as proc from dual union all
select 'alter system checkpoint' as proc from dual union all
select '--alter system switch logfile' as proc from dual union all
select 'alter system flush shared_pool' as proc from dual union all
select 'alter system flush buffer_cache' as proc from dual
)select row_number() over (order by rownum) as seq,proc from sub;
select * from pre_script;

まぁ、なんでこの話したかっていうと、merge文のパターン忘れたなとおもったからで、実行するプロシージャと処理するプロシージャは切り離しておけばいいかなーとめんどくならないかなーとおもっただけのことです。実行するプロシージャもオブジェクトにしておけば実行もそんなにかかなくていいですね。こんな感じ。

コード表示

create or replace procedure script_run(p_pre_from_seq number:=1,p_pre_to_seq number:=1,p_exec_from_seq number:=1,p_exec_to_seq number:=1)
authid current_user
as
	cursor pre_csr is select proc from pre_script where seq between p_pre_from_seq and p_pre_to_seq;
	cursor exec_csr is select proc from exec_script where seq between p_exec_from_seq and p_exec_to_seq;
	pre_proc clob;
	exec_proc clob;
begin
	open exec_csr;
	loop
		begin
		fetch exec_csr into exec_proc;
		exit when exec_csr%notfound;
			open pre_csr;
			loop
				begin
				fetch pre_csr into pre_proc;
				exit when pre_csr%notfound;
				dbms_output.put_line(pre_proc);
				execute immediate pre_proc;
				exception when others then dbms_output.put_line('[ '|| sqlcode||']'||sqlerrm);
				end;
			end loop;
			close pre_csr;
		dbms_output.put_line('begin '|| exec_proc || '; end;');
		execute immediate 'begin '|| exec_proc || '; end;';
		dbms_output.put_line(lpad('_',60,'_'));
		exception when others then dbms_output.put_line('[ '|| sqlcode||']'||sqlerrm);
		end;
	end loop;
	close exec_csr;
end;
/

ついでに忘れた、merge文のパターンも

コード表示

create or replace procedure proc___merge_insert___ as
	srt_tp timestamp;
	end_tp timestamp;
begin
	select systimestamp into srt_tp from dual;
	merge into tbl___tar___ tar
	using(select * from tbl___src___) src
	on(tar.rn = src.rn and tar.str = src.str)
	when not matched then insert values(src.rn,src.str);
    commit;
	select systimestamp into end_tp from dual;
	dbms_output.put_line('proc___merge_insert___');
	dbms_output.put_line(end_tp - srt_tp);
end;
/

うえでつくったプロシージャ名をテーブルに投入。

コード表示

select * from exec_script;

merge into exec_script tar
using (
	with sub as(
	select 'PROC___MERGE_INSERT___' as proc from dual
	)select max(s2.seq) + 1 as seq,max(s1.proc) as proc from sub s1,exec_script s2
)src
on(tar.proc = src.proc)
when not matched then insert values(src.seq,src.proc);
commit;

select * from exec_script;

実行例。コメントとしてテーブル格納しておけば、無視してくれてるポイから、いけてる雰囲気。

コード表示

set serveroutput on;
exec script_run(1,8,1,2);
/
drop table tbl___src___ purge
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7
drop table tbl___tar___ purge
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1
alter system checkpoint
--alter system switch logfile
[ -900]ORA-00900: SQL文が無効です。
alter system flush shared_pool
alter system flush buffer_cache
begin PROC___SELECT_INSERT___; end;
proc___select_insert___
+000000000 00:00:01.351316000
____________________________________________________________
drop table tbl___src___ purge
create table tbl___src___ as with fct as(select level as rn, chr(level+64) as str from dual connect by level <= 10)select s1.* from fct s1,fct s2,fct s3,fct s4,fct s5,fct s6,fct s7
drop table tbl___tar___ purge
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1
alter system checkpoint
--alter system switch logfile
[ -900]ORA-00900: SQL文が無効です。
alter system flush shared_pool
alter system flush buffer_cache
begin PROC___DIRECT_PATH_INSERT___; end;
proc___direct_path_insert___
+000000000 00:00:01.449993000
____________________________________________________________


PL/SQLプロシージャが正常に完了しました。


おわりに

plsqlたのしいな!もっと楽しい方法あったら、教えてください。以上、ありがとうございました。

sourceテーブルからオブジェクト型でデータ取得し、bulk collectでcollection変換したあとtable()かましてtargetテーブルに登録した話

移行元と移行先テーブル作る

クエリ

コード表示

drop table tbl___src___ purge;
create table tbl___src___ as select level as rn, chr(level+64) as str from dual connect by level <= 3;
drop table tbl___tar___ purge;
create table tbl___tar___ as select * from tbl___src___ where 1 <> 1;

オブジェクト型とコレクション型作る

クエリ

コード表示

create or replace type item is object (rn number,str varchar2(10));
/
create or replace type liz is table of item;
/

select item(rn,str) from tbl___src___;
select cast(collect(item(rn,str)) as liz) from tbl___src___;

表題のプロシージャ

クエリ

コード表示

set serveroutput on;

create or replace procedure proc___liz___ as
	cnt number;
	lz liz;
	cursor csr is select item(rn,str) from tbl___src___;
begin
	open csr;
	loop
		begin
			fetch csr bulk collect into lz;
			exit when csr%notfound;
		end;
	end loop;
	dbms_output.put_line('ele_cnt:' || lz.count);
	dbms_output.put_line('ele_fst:' || lz.first);
	dbms_output.put_line('ele_lst:' || lz.last);
	dbms_output.put_line('________________________________________');
	for i in lz.first..lz.last loop
		dbms_output.put_line('rn:' || lz(i).rn || ',str:' || lz(i).str);
	end loop;
	close csr;
	insert into tbl___tar___ select * from table(lz);
	commit;
end;
/

移行されたことを確認

クエリ

コード表示

select * from tbl___tar___;
exec proc___liz___;

動的にファンクションの呼び出しを制御する話

環境

18cで

コード表示

[oracle@centos7 ~]$ docker exec -it orcl_18cr3_1 bash
[oracle@f285aba0589a ~]$ sqlplus aine/ORACLE_PWD@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Tue Mar 26 20:34:01 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Last Successful login time: Tue Mar 26 2019 20:28:33 +09:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

ファンクション2つ適当に作る

クエリ

コード表示

create or replace function func___01___(p_num number)
return number
as rt number := p_num;
begin
	return rt * 10;
end;
/

create or replace function func___02___(p_num number)
return number
as rt number := p_num;
begin
	return rt * 100;
end;
/

select FUNC___01___(10) as rt from dual;
select FUNC___02___(10) as rt from dual;

実行例

コード表示

AINE@pdb1> select FUNC___01___(10) as rt from dual;

           RT
-------------
          100

1 row selected.

Elapsed: 00:00:00.00

AINE@pdb1> select FUNC___02___(10) as rt from dual;

           RT
-------------
         1000

1 row selected.

Elapsed: 00:00:00.00

ファンクション呼び出しファンクション

クエリ

コード表示


create or replace function func___invocker___(p_func_type number,p_num number)
return number
as rt number;
build_sql clob := to_clob('');
begin
	build_sql := 'select '||'func'||'___' || lpad(p_func_type,2,0) || '___' || '(' ||':p_num'|| ')' || ' from dual';
	execute IMMEDIATE build_sql into rt using p_num;
	return rt;
end;
/

実行例

コード表示

AINE@pdb1> select func___invocker___(1,10) as rt from dual;

           RT
-------------
          100

1 row selected.

Elapsed: 00:00:00.00
AINE@pdb1> select func___invocker___(2,10) as rt from dual;

           RT
-------------
         1000

1 row selected.

Elapsed: 00:00:00.01

パッケージにくるんでみると、パッケージ内での処理を気にせず、呼び出し側はcallできる。

クエリ

コード表示

create or replace package func___pkg___ is
	function func___01___(p_num number) return number;
	function func___02___(p_num number) return number;
end;
/
create or replace package body func___pkg___ is
	procedure func___fmt___(mtd varchar2) as
	begin
		DBMS_OUTPUT.PUT_LINE(mtd||'is called');
		return;
	end;
	function func___01___(p_num number)
	return number
	as rt number := p_num;
	begin
		func___fmt___('func___01___');
		return rt * 10;
	end;
	function func___02___(p_num number)
	return number
	as rt number := p_num;
	begin
		func___fmt___('func___02___');
		return rt * 100;
	end;
end;
/
select FUNC___PKG___.FUNC___01___(10) as rt from dual;
select FUNC___PKG___.FUNC___02___(10) as rt from dual;

実行例

コード表示

AINE@pdb1> select FUNC___PKG___.FUNC___01___(10) as rt from dual;

           RT
-------------
          100

1 row selected.

func___01___is called
Elapsed: 00:00:00.00

AINE@pdb1> select FUNC___PKG___.FUNC___02___(10) as rt from dual;

           RT
-------------
         1000

1 row selected.

func___02___is called
Elapsed: 00:00:00.00

パッケージの文言を追加しただけ。

クエリ

コード表示

create or replace function func___invocker___(p_func_type number,p_num number)
return number
as rt number;
build_sql clob := to_clob('');
begin
	build_sql := 'select '|| 'func___pkg___.' ||'func'||'___' || lpad(p_func_type,2,0) || '___' || '(' ||':p_num'|| ')' || ' from dual';
	execute IMMEDIATE build_sql into rt using p_num;
	return rt;
end;
/
select func___invocker___(1,10) as rt from dual;
select func___invocker___(2,10) as rt from dual;

実行例

コード表示

AINE@pdb1> select func___invocker___(1,10) as rt from dual;

           RT
-------------
          100

1 row selected.

func___01___is called
Elapsed: 00:00:00.00
AINE@pdb1> select func___invocker___(2,10) as rt from dual;

           RT
-------------
         1000

1 row selected.

func___02___is called
Elapsed: 00:00:00.00

collect関数で入れ子できた話

環境

18cで

コード表示

[oracle@centos7 ~]$ docker exec -it orcl_18cr3_1 bash
[oracle@f285aba0589a ~]$ sqlplus aine/ORACLE_PWD@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Tue Mar 26 20:34:01 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Last Successful login time: Tue Mar 26 2019 20:28:33 +09:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

タイプ作る

3つ今回は。

コード表示

drop type val force;
/
drop type item force;
/
drop type liz force;
/

create or replace type val is object(val clob);
/
create or replace type item is object(rn number,vl val);
/
create or replace type liz is table of item;
/

はじめのデータ

クエリ

コード表示

with sub as(
select mod(level,2) as rn,lpad(row_number()over(partition by mod(level,2) order by level),3,0) as str from dual connect by level <= 7
)select * from sub;

実行例

コード表示

AINE@pdb1> with sub as( select mod(level,2) as rn,lpad(row_number()over(partition by mod(level,2) order by level),3,0) as str from dual connect by level <= 7 )select * from sub;

           RN STR
------------- ------------
            0 001
            0 002
            0 003
            1 001
            1 002
            1 003
            1 004

7 rows selected.

Elapsed: 00:00:00.00

strをオブジェクト型に変換

クエリ

コード表示

with sub as(
select mod(level,2) as rn,lpad(row_number()over(partition by mod(level,2) order by level),3,0) as str from dual connect by level <= 7
)select rn,val(str) as val from sub;

実行例

コード表示

AINE@pdb1> col val for a50
AINE@pdb1> with sub as( select mod(level,2) as rn,lpad(row_number()over(partition by mod(level,2) order by level),3,0) as str from dual connect by level <= 7 )select rn,val(str) as val from sub;

           RN VAL(VAL)
------------- --------------------------------------------------
            0 VAL('001')
            0 VAL('002')
            0 VAL('003')
            1 VAL('001')
            1 VAL('002')
            1 VAL('003')
            1 VAL('004')

7 rows selected.

Elapsed: 00:00:00.01

mapみたいにitemでラップ

クエリ

コード表示

with sub as(
select mod(level,2) as rn,lpad(row_number()over(partition by mod(level,2) order by level),3,0) as str from dual connect by level <= 7
)select item(rn,val(str)) as item from sub;

実行例

コード表示

AINE@pdb1> col item for a50
AINE@pdb1> with sub as( select mod(level,2) as rn,lpad(row_number()over(partition by mod(level,2) order by level),3,0) as str from dual connect by level <= 7 )select item(rn,val(str)) as item from sub;

ITEM(RN, VL(VAL))
--------------------------------------------------
ITEM(0, VAL('001'))
ITEM(0, VAL('002'))
ITEM(0, VAL('003'))
ITEM(1, VAL('001'))
ITEM(1, VAL('002'))
ITEM(1, VAL('003'))
ITEM(1, VAL('004'))

7 rows selected.

Elapsed: 00:00:00.01

最後はさまっておしまい

クエリ

コード表示

with sub as(
select mod(level,2) as rn,lpad(row_number()over(partition by mod(level,2) order by level),3,0) as str from dual connect by level <= 7
)select rn,cast(collect(item(rn,val(str))) as liz) as liz from sub group by rn;

実行例

コード表示

AINE@pdb1> col liz for a100
AINE@pdb1> with sub as( select mod(level,2) as rn,lpad(row_number()over(partition by mod(level,2) order by level),3,0) as str from dual connect by level <= 7 )select rn,cast(collect(item(rn,val(str))) as liz) as liz from sub group by rn;

           RN LIZ(RN, VL(VAL))
------------- ----------------------------------------------------------------------------------------------------
            0 LIZ(ITEM(0, VAL('001')), ITEM(0, VAL('003')), ITEM(0, VAL('002')))
            1 LIZ(ITEM(1, VAL('001')), ITEM(1, VAL('004')), ITEM(1, VAL('003')), ITEM(1, VAL('002')))

2 rows selected.

Elapsed: 00:00:00.01

サマル単位を指定しないと

クエリ

コード表示

with sub as(
select mod(level,2) as rn,lpad(row_number()over(partition by mod(level,2) order by level),3,0) as str from dual connect by level <= 7
)select cast(collect(item(rn,val(str))) as liz)  as liz from sub;

実行例

コード表示

AINE@pdb1> col liz for a170
AINE@pdb1> with sub as( select mod(level,2) as rn,lpad(row_number()over(partition by mod(level,2) order by level),3,0) as str from dual connect by level <= 7 )select cast(collect(item(rn,val(str))) as liz) as liz from sub;

LIZ(RN, VL(VAL))
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
LIZ(ITEM(0, VAL('001')), ITEM(0, VAL('002')), ITEM(0, VAL('003')), ITEM(1, VAL('001')), ITEM(1, VAL('002')), ITEM(1, VAL('003')), ITEM(1, VAL('004')))

1 row selected.

Elapsed: 00:00:00.01

ロール関連のテーブルいじった

まえがき

私はDBAではありませんが、権限関連に興味を持ったので、いろいろいじってみました。

データディクシヨナリビューの分類

こういうところからあまり知らないw。基本が大事ってやつだな。

prefix desc
user_ ログインユーザが所有しているオブジェクトのみが確認できるビュー
all_ ログインユーザが参照できるオブジェクトのみが確認できるビュー
dba_ データベースに存在するすべてのオブジェクトが確認できるビュー。デフォルトで
はSELECT ANY DICTIONARYシステム権限を持つユーザーのみがアクセス可能。

item_lizパッケージでコレクション操作系のファンクションそろえた。

随時増やしていきたいな。層を厚くしたい。

コード表示

CREATE OR REPLACE TYPE liz AS TABLE OF VARCHAR2(4000);
/

CREATE OR REPLACE PACKAGE item_liz
IS
    FUNCTION segregate(p_liz IN VARCHAR2) RETURN liz;
    FUNCTION ecl_dupli_liz(p_liz IN liz) RETURN liz;
    FUNCTION cnt_liz(p_liz IN liz) RETURN NUMBER;
END;
/
CREATE OR REPLACE PACKAGE BODY item_liz
IS
    FUNCTION segregate(p_liz IN VARCHAR2) RETURN liz
    IS
        rt liz;
    BEGIN
        rt := NULL;
		WITH sub AS(
			SELECT
				LEVEL AS rn
				,p_liz AS liz
			FROM
				dual
			CONNECT BY
				LEVEL <= LENGTH(p_liz) - LENGTH(REPLACE(p_liz,',','')) + 1
		)
		SELECT
			CAST(COLLECT(
				decode(rn,1,substr(liz,1,instr(liz,',',1,rn) - 1),substr(liz,instr(liz,',',1,rn -1 ) + 1,nvl(nullif(instr(liz,',',1,rn),0),4000)- instr(liz,',',1,rn-1)-1))
			)AS liz) AS ele
		INTO rt
		FROM
			sub;
        RETURN rt;
    END;

    FUNCTION ecl_dupli_liz(p_liz IN liz) RETURN liz
    IS
        rt liz;
    BEGIN
        rt := NULL;
		SELECT
			CAST(COLLECT(ele) AS liz)
		INTO rt
		FROM
			(
				SELECT DISTINCT
					COLUMN_VALUE AS ele
				FROM
					TABLE ( p_liz )
			);
        RETURN rt;
    END;

    FUNCTION cnt_liz(p_liz IN liz) RETURN NUMBER
	IS
        rt NUMBER;
	BEGIN
        rt := NULL;
		SELECT
			COUNT(COLUMN_VALUE)
		INTO rt
		FROM TABLE ( p_liz );
		RETURN rt;
	END;
END;
/

DBAロールって結局どんな権限持っているの。オブジェクト権限やシステム権限のうち。

dba_role_privsテーブルに管理されているぽい。見たところ、ロールのロールみたいにヒエラルキーがある。

コード表示

SET PAGESIZE 50000
SET LINESIZE 1000
SET TAB OFF
SET TRIMSPOOL ON
COL root_role FOR a10
COL related_role FOR a50

WITH liz___source___ AS(
SELECT
	SUM(CASE WHEN LEVEL <> 1 THEN 0 ELSE 1 END) OVER (ORDER BY ROWNUM) AS grp
	,LEVEL AS lv
	,CONNECT_BY_ROOT grantee AS root_role
	,grantee
	,granted_role
	,CONNECT_BY_ISLEAF AS isleaf
	,sys_connect_by_path(granted_role,',') AS liz
FROM
	dba_role_privs
START WITH
	grantee = 'DBA'
CONNECT BY
	PRIOR granted_role = grantee
)
,liz___create___ AS(
SELECT
	root_role
	,item_liz.ecl_dupli_liz(item_liz.segregate(substr(LISTAGG(liz)WITHIN GROUP (ORDER BY grp+grpseq),2))) AS liz
FROM
	(
	SELECT
		s1.grp
		,row_number()OVER(PARTITION BY s1.grp ORDER BY lv) AS grpseq
		,s1.lv
		,s1.root_role
		,s1.liz
	FROM
		liz___source___ s1
	WHERE
		s1.isleaf = 1
	)
GROUP BY
	root_role
)SELECT root_role AS root_role,COLUMN_VALUE AS related_role FROM liz___create___,TABLE(liz)
;

実行例

コード表示

ROOT_ROLE  RELATED_ROLE                                      
---------- --------------------------------------------------
DBA        HS_ADMIN_EXECUTE_ROLE                             
DBA        DATAPUMP_IMP_FULL_DATABASE                        
DBA        DATAPUMP_EXP_FULL_DATABASE                        
DBA        GATHER_SYSTEM_STATISTICS                          
DBA        OLAP_XS_ADMIN                                     
DBA        WM_ADMIN_ROLE                                     
DBA        EXP_FULL_DATABASE                                 
DBA        EXECUTE_CATALOG_ROLE                              
DBA        SELECT_CATALOG_ROLE                               
DBA        EM_EXPRESS_BASIC                                  
DBA        OLAP_DBA                                          
DBA        JAVA_ADMIN                                        
DBA        CAPTURE_ADMIN                                     
DBA        IMP_FULL_DATABASE                                 
DBA        HS_ADMIN_SELECT_ROLE                              
DBA        EM_EXPRESS_ALL                                    
DBA        XDBADMIN                                          
DBA        OPTIMIZER_PROCESSING_RATE                         
DBA        SCHEDULER_ADMIN                                   
DBA        XDB_SET_INVOKER                                   

20行が選択されました。 

重複排除して件数見ると、20件ぐらいDBAロールに関連しているロールがありそう。。おおいな

関連したロールに紐付く、システム権限を調べる。下のクエリとの取得結果と当てれば、OKそう。基本はleft outer joinでおそるおそる調べる。見失いたくないので。

コード表示

SELECT
	grantee
	,RTRIM(XMLAGG(XMLELEMENT(E,PRIVILEGE,',').EXTRACT('//text()')).getclobval(),',') AS liz
FROM
	dba_sys_privs
GROUP BY
	grantee
;

見失うところでした。nullはどのテーブルで管理しているんだろ。

コード表示

SET PAGESIZE 50000
SET LINESIZE 1000
SET TAB OFF
SET TRIMSPOOL ON
COL root_role FOR a10
COL related_role FOR a50

WITH liz___source___ AS(
SELECT
	SUM(CASE WHEN LEVEL <> 1 THEN 0 ELSE 1 END) OVER (ORDER BY ROWNUM) AS grp
	,LEVEL AS lv
	,CONNECT_BY_ROOT grantee AS root_role
	,grantee
	,granted_role
	,CONNECT_BY_ISLEAF AS isleaf
	,sys_connect_by_path(granted_role,',') AS liz
FROM
	dba_role_privs
START WITH
	grantee = 'DBA'
CONNECT BY
	PRIOR granted_role = grantee
)
,liz___create___ AS(
SELECT
	root_role
	,item_liz.ecl_dupli_liz(item_liz.segregate(substr(LISTAGG(liz)WITHIN GROUP (ORDER BY grp+grpseq),2))) AS liz
FROM
	(
	SELECT
		s1.grp
		,row_number()OVER(PARTITION BY s1.grp ORDER BY lv) AS grpseq
		,s1.lv
		,s1.root_role
		,s1.liz
	FROM
		liz___source___ s1
	WHERE
		s1.isleaf = 1
	)
GROUP BY
	root_role
)
,role___relation___ AS(
SELECT root_role AS root_role,COLUMN_VALUE AS related_role FROM liz___create___,TABLE(liz)
)
,role___sys_prv_mst___ AS(
SELECT
	grantee
	,RTRIM(XMLAGG(XMLELEMENT(E,PRIVILEGE,',').EXTRACT('//text()')).getclobval(),',') AS liz
FROM
	dba_sys_privs
GROUP BY
	grantee
)SELECT
	s1.root_role
	,s1.related_role
	,s2.liz
FROM
	role___relation___ s1
		LEFT OUTER JOIN role___sys_prv_mst___ s2
			ON
				s1.related_role = s2.grantee
;

全文検索とかで調べんのかな。。。やってみるか。。なんか調べてみると以下のような文章が出てきた。PUBLICロールってすべてのユーザーが前提として持っているようなロールぽい。このロールに権限強いやつallocateするととんでもないことになるね。

SYSスキーマ内のオブジェクトへのアクセスの許可

SELECT_CATALOG_ROLE,EXECUTE_CATALOG_ROLE,DELETE_CATALOG_ROLEこの3つのロールはSYSユーザーのオブジェクトもはや何でもどうにでもできるようなやばいやつなのか。DBAロールにはそんなロールまでついているのか。。。すごい。

もうチョイ調べたら、いい感じのリストでてきた!!!

ユーザー権限とロールに関する情報の検索  

さっきわりとがんばって書いたやつROLE_ROLE_PRIVSテーブルで管理されていそうで、萎え。nullのやつたぶんシステム全体として定義しているもので、割り当てる権限とかもう定数で固定されているから、nullでいいんじゃないか??個別でグぐることにした。

HS_ADMIN_EXECUTE_ROLE

Provides the EXECUTE privilege for users who want to use the Heterogeneous Services (HS) PL/SQL packages.

GATHER_SYSTEM_STATISTICS

Provides privileges to update system statistics, which are collected using the DBMS_STATS.GATHER_SYSTEM_STATISTICS procedure.これはシステム統計情報取得パッケージを実行するのに必要なロールってことだな!!さっきのは???だったけど。

GATHER_SYSTEM_STATISTICS

Provides privileges to administer security for Oracle OLAP.分析関数いじるのに必要なロールなんだろうキット。

WM_ADMIN_ROLE

DBMS_WMプロシージャいじるのに必要なロールなんだろな。用途が解せぬ。

JAVA_ADMIN

Provides administrative permissions to update policy tables for Oracle Database Java applications.用途が解せぬ。

CAPTURE_ADMIN

Provides the privileges necessary to create and manage privilege analysis policies.用途が解せぬ。

HS_ADMIN_SELECT_ROLE

Provides privileges to query the Heterogeneous Services data dictionary views.用途が解せぬ。

XDBADMIN

Allows the grantee to register an XML schema globally, as opposed to registering it for use or access only by its owner. It also lets the grantee bypass access control list (ACL) checks when accessing Oracle XML DB Repository.用途が解せぬ。

OPTIMIZER_PROCESSING_RATE

Provides privileges to execute the GATHER_PROCESSING_RATE, SET_PROCESSING_RATE, and DELETE_PROCESSING_RATE procedures in the DBMS_STATS package. These procedures manage the processing rate of a system for automatic degree of parallelism (Auto DOP). Auto DOP uses these processing rates to determine the optimal degree of parallelism for a SQL statement.チューニング関連ってことはなんとなく分かる。

コード表示

ROOT_ROLE  RELATED_ROLE                                       LIZ                                                                             
---------- -------------------------------------------------- --------------------------------------------------------------------------------
DBA        HS_ADMIN_EXECUTE_ROLE                              NULL                                                                            
DBA        DATAPUMP_IMP_FULL_DATABASE                         GRANT ANY ROLE,ALTER RESOURCE COST,EXECUTE ANY OPERATOR,GRANT ANY PRIVILEGE,CREA
DBA        DATAPUMP_EXP_FULL_DATABASE                         CREATE SESSION,CREATE TABLE                                                     
DBA        GATHER_SYSTEM_STATISTICS                           NULL                                                                            
DBA        OLAP_XS_ADMIN                                      NULL                                                                            
DBA        WM_ADMIN_ROLE                                      NULL                                                                            
DBA        EXP_FULL_DATABASE                                  READ ANY FILE GROUP,FLASHBACK ANY TABLE,BACKUP ANY TABLE,EXEMPT REDACTION POLICY
DBA        EXECUTE_CATALOG_ROLE                               NULL                                                                            
DBA        SELECT_CATALOG_ROLE                                NULL                                                                            
DBA        EM_EXPRESS_BASIC                                   CREATE SESSION,EM EXPRESS CONNECT                                               
DBA        OLAP_DBA                                           SELECT ANY TABLE,CREATE ANY VIEW,CREATE ANY CUBE,INSERT ANY TABLE,CREATE ANY TAB
DBA        JAVA_ADMIN                                         NULL                                                                            
DBA        CAPTURE_ADMIN                                      NULL                                                                            
DBA        IMP_FULL_DATABASE                                  CREATE ANY TABLE,DROP ANY DIMENSION,DROP ANY ROLE,DROP PUBLIC DATABASE LINK,DROP
DBA        HS_ADMIN_SELECT_ROLE                               NULL                                                                            
DBA        EM_EXPRESS_ALL                                     CREATE JOB,ALTER TABLESPACE,ADMINISTER ANY SQL TUNING SET,ADMINISTER SQL TUNING 
DBA        XDBADMIN                                           NULL                                                                            
DBA        OPTIMIZER_PROCESSING_RATE                          NULL                                                                            
DBA        SCHEDULER_ADMIN                                    MANAGE SCHEDULER,CREATE ANY CREDENTIAL,CREATE EXTERNAL JOB,EXECUTE ANY CLASS,CRE
DBA        XDB_SET_INVOKER                                    NULL                                                                            

20行が選択されました。 

とりあえず、個々のロールに割りついている権限の数、調べる

IMP_FULL_DATABASEおおいな。

コード表示

SET PAGESIZE 50000
SET LINESIZE 1000
SET TAB OFF
SET TRIMSPOOL ON
COL root_role FOR a10
COL related_role FOR a50

WITH liz___source___ AS(
SELECT
	SUM(CASE WHEN LEVEL <> 1 THEN 0 ELSE 1 END) OVER (ORDER BY ROWNUM) AS grp
	,LEVEL AS lv
	,CONNECT_BY_ROOT grantee AS root_role
	,grantee
	,granted_role
	,CONNECT_BY_ISLEAF AS isleaf
	,sys_connect_by_path(granted_role,',') AS liz
FROM
	dba_role_privs
START WITH
	grantee = 'DBA'
CONNECT BY
	PRIOR granted_role = grantee
)
,liz___create___ AS(
SELECT
	root_role
	,item_liz.ecl_dupli_liz(item_liz.segregate(substr(LISTAGG(liz)WITHIN GROUP (ORDER BY grp+grpseq),2))) AS liz
FROM
	(
	SELECT
		s1.grp
		,row_number()OVER(PARTITION BY s1.grp ORDER BY lv) AS grpseq
		,s1.lv
		,s1.root_role
		,s1.liz
	FROM
		liz___source___ s1
	WHERE
		s1.isleaf = 1
	)
GROUP BY
	root_role
)
,role___relation___ AS(
SELECT root_role AS root_role,COLUMN_VALUE AS related_role FROM liz___create___,TABLE(liz)
)
,role___sys_prv_mst___ AS(
SELECT
	grantee
	,RTRIM(XMLAGG(XMLELEMENT(E,PRIVILEGE,',').EXTRACT('//text()')).getclobval(),',') AS liz
FROM
	dba_sys_privs
GROUP BY
	grantee
)
,list___role_prv___ AS (
SELECT
	s1.root_role
	,s1.related_role
	,s2.liz
FROM
	role___relation___ s1
		LEFT OUTER JOIN role___sys_prv_mst___ s2
			ON
				s1.related_role = s2.grantee
)
SELECT
	root_role
	,related_role
	,item_liz.cnt_liz(item_liz.segregate(liz)) AS cnt
--	,column_value as related_prv
FROM
	list___role_prv___
--	,table(item_liz.segregate(liz))
ORDER BY
	item_liz.cnt_liz(item_liz.segregate(liz)) DESC
;

ROOT_ROLE  RELATED_ROLE                                              CNT
---------- -------------------------------------------------- ----------
DBA        IMP_FULL_DATABASE                                          81
DBA        OLAP_DBA                                                   27
DBA        EM_EXPRESS_ALL                                             23
DBA        DATAPUMP_IMP_FULL_DATABASE                                 14
DBA        EXP_FULL_DATABASE                                          14
DBA        SCHEDULER_ADMIN                                             8
DBA        EM_EXPRESS_BASIC                                            2
DBA        DATAPUMP_EXP_FULL_DATABASE                                  2
DBA        SELECT_CATALOG_ROLE                                         0
DBA        GATHER_SYSTEM_STATISTICS                                    0
DBA        JAVA_ADMIN                                                  0
DBA        WM_ADMIN_ROLE                                               0
DBA        HS_ADMIN_SELECT_ROLE                                        0
DBA        HS_ADMIN_EXECUTE_ROLE                                       0
DBA        XDBADMIN                                                    0
DBA        OPTIMIZER_PROCESSING_RATE                                   0
DBA        XDB_SET_INVOKER                                             0
DBA        EXECUTE_CATALOG_ROLE                                        0
DBA        OLAP_XS_ADMIN                                               0
DBA        CAPTURE_ADMIN                                               0

20行が選択されました。 


明細展開してみる

コード表示

SET PAGESIZE 50000
SET LINESIZE 1000
SET TAB OFF
SET TRIMSPOOL ON
COL root_role FOR a10
COL related_role FOR a50
COL related_prv FOR a50

WITH liz___source___ AS(
SELECT
	SUM(CASE WHEN LEVEL <> 1 THEN 0 ELSE 1 END) OVER (ORDER BY ROWNUM) AS grp
	,LEVEL AS lv
	,CONNECT_BY_ROOT grantee AS root_role
	,grantee
	,granted_role
	,CONNECT_BY_ISLEAF AS isleaf
	,sys_connect_by_path(granted_role,',') AS liz
FROM
	dba_role_privs
START WITH
	grantee = 'DBA'
CONNECT BY
	PRIOR granted_role = grantee
)
,liz___create___ AS(
SELECT
	root_role
	,item_liz.ecl_dupli_liz(item_liz.segregate(substr(LISTAGG(liz)WITHIN GROUP (ORDER BY grp+grpseq),2))) AS liz
FROM
	(
	SELECT
		s1.grp
		,row_number()OVER(PARTITION BY s1.grp ORDER BY lv) AS grpseq
		,s1.lv
		,s1.root_role
		,s1.liz
	FROM
		liz___source___ s1
	WHERE
		s1.isleaf = 1
	)
GROUP BY
	root_role
)
,role___relation___ AS(
SELECT root_role AS root_role,COLUMN_VALUE AS related_role FROM liz___create___,TABLE(liz)
)
,role___sys_prv_mst___ AS(
SELECT
	grantee
	,RTRIM(XMLAGG(XMLELEMENT(E,PRIVILEGE,',').EXTRACT('//text()')).getclobval(),',') AS liz
FROM
	dba_sys_privs
GROUP BY
	grantee
)
,list___role_prv___ AS (
SELECT
	s1.root_role
	,s1.related_role
	,s2.liz
FROM
	role___relation___ s1
		LEFT OUTER JOIN role___sys_prv_mst___ s2
			ON
				s1.related_role = s2.grantee
)
SELECT
	root_role
	,related_role
	,item_liz.cnt_liz(item_liz.segregate(liz)) AS cnt
	,column_value as related_prv
FROM
	list___role_prv___
	,table(item_liz.segregate(liz))
ORDER BY
	item_liz.cnt_liz(item_liz.segregate(liz)) DESC
	,column_value
;

ROOT_ROLE  RELATED_ROLE                                              CNT RELATED_PRV                                       
---------- -------------------------------------------------- ---------- --------------------------------------------------
DBA        IMP_FULL_DATABASE                                          81 ADMINISTER DATABASE TRIGGER                       
DBA        IMP_FULL_DATABASE                                          81 ADMINISTER RESOURCE MANAGER                       
DBA        IMP_FULL_DATABASE                                          81 ADMINISTER SQL MANAGEMENT OBJECT                  
DBA        IMP_FULL_DATABASE                                          81 ALTER ANY PROCEDURE                               
DBA        IMP_FULL_DATABASE                                          81 ALTER ANY TABLE                                   
DBA        IMP_FULL_DATABASE                                          81 ALTER ANY TRIGGER                                 
DBA        IMP_FULL_DATABASE                                          81 ALTER ANY TYPE                                    
DBA        IMP_FULL_DATABASE                                          81 ALTER DATABASE                                    
DBA        IMP_FULL_DATABASE                                          81 ALTER PROFILE                                     
DBA        IMP_FULL_DATABASE                                          81 ALTER RESOURCE COST                               
DBA        IMP_FULL_DATABASE                                          81 ALTER TABLESPACE                                  
DBA        IMP_FULL_DATABASE                                          81 ALTER USER                                        
DBA        IMP_FULL_DATABASE                                          81 ANALYZE ANY                                       
DBA        IMP_FULL_DATABASE                                          81 AUDIT ANY                                         
DBA        IMP_FULL_DATABASE                                          81 AUDIT SYSTEM                                      
DBA        IMP_FULL_DATABASE                                          81 BECOME USER                                       
DBA        IMP_FULL_DATABASE                                          81 COMMENT ANY TABLE                                 
DBA        IMP_FULL_DATABASE                                          81 CREATE ANY CLUSTER                                
DBA        IMP_FULL_DATABASE                                          81 CREATE ANY CONTEXT                                
DBA        IMP_FULL_DATABASE                                          81 CREATE ANY DIMENSION                              
DBA        IMP_FULL_DATABASE                                          81 CREATE ANY DIRECTORY                              
DBA        IMP_FULL_DATABASE                                          81 CREATE ANY INDEX                                  
DBA        IMP_FULL_DATABASE                                          81 CREATE ANY INDEXTYPE                              
DBA        IMP_FULL_DATABASE                                          81 CREATE ANY LIBRARY                                
DBA        IMP_FULL_DATABASE                                          81 CREATE ANY MATERIALIZED VIEW                      
DBA        IMP_FULL_DATABASE                                          81 CREATE ANY OPERATOR                               
DBA        IMP_FULL_DATABASE                                          81 CREATE ANY PROCEDURE                              
DBA        IMP_FULL_DATABASE                                          81 CREATE ANY SEQUENCE                               
DBA        IMP_FULL_DATABASE                                          81 CREATE ANY SQL PROFILE                            
DBA        IMP_FULL_DATABASE                                          81 CREATE ANY SYNONYM                                
DBA        IMP_FULL_DATABASE                                          81 CREATE ANY TABLE                                  
DBA        IMP_FULL_DATABASE                                          81 CREATE ANY TRIGGER                                
DBA        IMP_FULL_DATABASE                                          81 CREATE ANY TYPE                                   
DBA        IMP_FULL_DATABASE                                          81 CREATE ANY VIEW                                   
DBA        IMP_FULL_DATABASE                                          81 CREATE DATABASE LINK                              
DBA        IMP_FULL_DATABASE                                          81 CREATE PROFILE                                    
DBA        IMP_FULL_DATABASE                                          81 CREATE PUBLIC DATABASE LINK                       
DBA        IMP_FULL_DATABASE                                          81 CREATE PUBLIC SYNONYM                             
DBA        IMP_FULL_DATABASE                                          81 CREATE ROLE                                       
DBA        IMP_FULL_DATABASE                                          81 CREATE ROLLBACK SEGMENT                           
DBA        IMP_FULL_DATABASE                                          81 CREATE SESSION                                    
DBA        IMP_FULL_DATABASE                                          81 CREATE TABLESPACE                                 
DBA        IMP_FULL_DATABASE                                          81 CREATE USER                                       
DBA        IMP_FULL_DATABASE                                          81 DELETE ANY TABLE                                  
DBA        IMP_FULL_DATABASE                                          81 DROP ANY CLUSTER                                  
DBA        IMP_FULL_DATABASE                                          81 DROP ANY CONTEXT                                  
DBA        IMP_FULL_DATABASE                                          81 DROP ANY DIMENSION                                
DBA        IMP_FULL_DATABASE                                          81 DROP ANY DIRECTORY                                
DBA        IMP_FULL_DATABASE                                          81 DROP ANY INDEX                                    
DBA        IMP_FULL_DATABASE                                          81 DROP ANY INDEXTYPE                                
DBA        IMP_FULL_DATABASE                                          81 DROP ANY LIBRARY                                  
DBA        IMP_FULL_DATABASE                                          81 DROP ANY MATERIALIZED VIEW                        
DBA        IMP_FULL_DATABASE                                          81 DROP ANY OPERATOR                                 
DBA        IMP_FULL_DATABASE                                          81 DROP ANY OUTLINE                                  
DBA        IMP_FULL_DATABASE                                          81 DROP ANY PROCEDURE                                
DBA        IMP_FULL_DATABASE                                          81 DROP ANY ROLE                                     
DBA        IMP_FULL_DATABASE                                          81 DROP ANY SEQUENCE                                 
DBA        IMP_FULL_DATABASE                                          81 DROP ANY SQL PROFILE                              
DBA        IMP_FULL_DATABASE                                          81 DROP ANY SYNONYM                                  
DBA        IMP_FULL_DATABASE                                          81 DROP ANY TABLE                                    
DBA        IMP_FULL_DATABASE                                          81 DROP ANY TRIGGER                                  
DBA        IMP_FULL_DATABASE                                          81 DROP ANY TYPE                                     
DBA        IMP_FULL_DATABASE                                          81 DROP ANY VIEW                                     
DBA        IMP_FULL_DATABASE                                          81 DROP PROFILE                                      
DBA        IMP_FULL_DATABASE                                          81 DROP PUBLIC DATABASE LINK                         
DBA        IMP_FULL_DATABASE                                          81 DROP PUBLIC SYNONYM                               
DBA        IMP_FULL_DATABASE                                          81 DROP ROLLBACK SEGMENT                             
DBA        IMP_FULL_DATABASE                                          81 DROP TABLESPACE                                   
DBA        IMP_FULL_DATABASE                                          81 DROP USER                                         
DBA        IMP_FULL_DATABASE                                          81 EXECUTE ANY OPERATOR                              
DBA        IMP_FULL_DATABASE                                          81 EXECUTE ANY PROCEDURE                             
DBA        IMP_FULL_DATABASE                                          81 EXECUTE ANY TYPE                                  
DBA        IMP_FULL_DATABASE                                          81 GLOBAL QUERY REWRITE                              
DBA        IMP_FULL_DATABASE                                          81 GRANT ANY OBJECT PRIVILEGE                        
DBA        IMP_FULL_DATABASE                                          81 GRANT ANY PRIVILEGE                               
DBA        IMP_FULL_DATABASE                                          81 GRANT ANY ROLE                                    
DBA        IMP_FULL_DATABASE                                          81 INSERT ANY TABLE                                  
DBA        IMP_FULL_DATABASE                                          81 MANAGE ANY QUEUE                                  
DBA        IMP_FULL_DATABASE                                          81 RESUMABLE                                         
DBA        IMP_FULL_DATABASE                                          81 SELECT ANY TABLE                                  
DBA        IMP_FULL_DATABASE                                          81 UPDATE ANY TABLE                                  
DBA        OLAP_DBA                                                   27 CREATE ANY CUBE                                   
DBA        OLAP_DBA                                                   27 CREATE ANY CUBE BUILD PROCESS                     
DBA        OLAP_DBA                                                   27 CREATE ANY CUBE DIMENSION                         
DBA        OLAP_DBA                                                   27 CREATE ANY MEASURE FOLDER                         
DBA        OLAP_DBA                                                   27 CREATE ANY TABLE                                  
DBA        OLAP_DBA                                                   27 CREATE ANY VIEW                                   
DBA        OLAP_DBA                                                   27 CREATE JOB                                        
DBA        OLAP_DBA                                                   27 CREATE SEQUENCE                                   
DBA        OLAP_DBA                                                   27 DELETE ANY CUBE DIMENSION                         
DBA        OLAP_DBA                                                   27 DELETE ANY MEASURE FOLDER                         
DBA        OLAP_DBA                                                   27 DELETE ANY TABLE                                  
DBA        OLAP_DBA                                                   27 DROP ANY CUBE                                     
DBA        OLAP_DBA                                                   27 DROP ANY CUBE BUILD PROCESS                       
DBA        OLAP_DBA                                                   27 DROP ANY CUBE DIMENSION                           
DBA        OLAP_DBA                                                   27 DROP ANY MEASURE FOLDER                           
DBA        OLAP_DBA                                                   27 DROP ANY TABLE                                    
DBA        OLAP_DBA                                                   27 DROP ANY VIEW                                     
DBA        OLAP_DBA                                                   27 INSERT ANY CUBE DIMENSION                         
DBA        OLAP_DBA                                                   27 INSERT ANY MEASURE FOLDER                         
DBA        OLAP_DBA                                                   27 INSERT ANY TABLE                                  
DBA        OLAP_DBA                                                   27 SELECT ANY CUBE                                   
DBA        OLAP_DBA                                                   27 SELECT ANY CUBE DIMENSION                         
DBA        OLAP_DBA                                                   27 SELECT ANY TABLE                                  
DBA        OLAP_DBA                                                   27 UPDATE ANY CUBE                                   
DBA        OLAP_DBA                                                   27 UPDATE ANY CUBE BUILD PROCESS                     
DBA        OLAP_DBA                                                   27 UPDATE ANY CUBE DIMENSION                         
DBA        OLAP_DBA                                                   27 UPDATE ANY TABLE                                  
DBA        EM_EXPRESS_ALL                                             23 ADMINISTER ANY SQL TUNING SET                     
DBA        EM_EXPRESS_ALL                                             23 ADMINISTER RESOURCE MANAGER                       
DBA        EM_EXPRESS_ALL                                             23 ADMINISTER SQL MANAGEMENT OBJECT                  
DBA        EM_EXPRESS_ALL                                             23 ADMINISTER SQL TUNING SET                         
DBA        EM_EXPRESS_ALL                                             23 ADVISOR                                           
DBA        EM_EXPRESS_ALL                                             23 ALTER ANY ROLE                                    
DBA        EM_EXPRESS_ALL                                             23 ALTER PROFILE                                     
DBA        EM_EXPRESS_ALL                                             23 ALTER SYSTEM                                      
DBA        EM_EXPRESS_ALL                                             23 ALTER TABLESPACE                                  
DBA        EM_EXPRESS_ALL                                             23 ALTER USER                                        
DBA        EM_EXPRESS_ALL                                             23 CREATE JOB                                        
DBA        EM_EXPRESS_ALL                                             23 CREATE PROFILE                                    
DBA        EM_EXPRESS_ALL                                             23 CREATE ROLE                                       
DBA        EM_EXPRESS_ALL                                             23 CREATE TABLESPACE                                 
DBA        EM_EXPRESS_ALL                                             23 CREATE USER                                       
DBA        EM_EXPRESS_ALL                                             23 DROP ANY ROLE                                     
DBA        EM_EXPRESS_ALL                                             23 DROP PROFILE                                      
DBA        EM_EXPRESS_ALL                                             23 DROP TABLESPACE                                   
DBA        EM_EXPRESS_ALL                                             23 DROP USER                                         
DBA        EM_EXPRESS_ALL                                             23 GRANT ANY OBJECT PRIVILEGE                        
DBA        EM_EXPRESS_ALL                                             23 GRANT ANY PRIVILEGE                               
DBA        EM_EXPRESS_ALL                                             23 GRANT ANY ROLE                                    
DBA        EM_EXPRESS_ALL                                             23 SET CONTAINER                                     
DBA        EXP_FULL_DATABASE                                          14 ADMINISTER RESOURCE MANAGER                       
DBA        EXP_FULL_DATABASE                                          14 ADMINISTER SQL MANAGEMENT OBJECT                  
DBA        DATAPUMP_IMP_FULL_DATABASE                                 14 ALTER DATABASE                                    
DBA        DATAPUMP_IMP_FULL_DATABASE                                 14 ALTER PROFILE                                     
DBA        DATAPUMP_IMP_FULL_DATABASE                                 14 ALTER RESOURCE COST                               
DBA        DATAPUMP_IMP_FULL_DATABASE                                 14 ALTER USER                                        
DBA        EXP_FULL_DATABASE                                          14 ANALYZE ANY                                       
DBA        DATAPUMP_IMP_FULL_DATABASE                                 14 AUDIT ANY                                         
DBA        DATAPUMP_IMP_FULL_DATABASE                                 14 AUDIT SYSTEM                                      
DBA        EXP_FULL_DATABASE                                          14 BACKUP ANY TABLE                                  
DBA        DATAPUMP_IMP_FULL_DATABASE                                 14 CREATE PROFILE                                    
DBA        EXP_FULL_DATABASE                                          14 CREATE SESSION                                    
DBA        DATAPUMP_IMP_FULL_DATABASE                                 14 CREATE SESSION                                    
DBA        EXP_FULL_DATABASE                                          14 CREATE TABLE                                      
DBA        DATAPUMP_IMP_FULL_DATABASE                                 14 DELETE ANY TABLE                                  
DBA        DATAPUMP_IMP_FULL_DATABASE                                 14 EXECUTE ANY OPERATOR                              
DBA        EXP_FULL_DATABASE                                          14 EXECUTE ANY PROCEDURE                             
DBA        EXP_FULL_DATABASE                                          14 EXECUTE ANY TYPE                                  
DBA        EXP_FULL_DATABASE                                          14 EXEMPT REDACTION POLICY                           
DBA        EXP_FULL_DATABASE                                          14 FLASHBACK ANY TABLE                               
DBA        DATAPUMP_IMP_FULL_DATABASE                                 14 GRANT ANY OBJECT PRIVILEGE                        
DBA        DATAPUMP_IMP_FULL_DATABASE                                 14 GRANT ANY PRIVILEGE                               
DBA        DATAPUMP_IMP_FULL_DATABASE                                 14 GRANT ANY ROLE                                    
DBA        EXP_FULL_DATABASE                                          14 READ ANY FILE GROUP                               
DBA        EXP_FULL_DATABASE                                          14 RESUMABLE                                         
DBA        EXP_FULL_DATABASE                                          14 SELECT ANY SEQUENCE                               
DBA        DATAPUMP_IMP_FULL_DATABASE                                 14 SELECT ANY TABLE                                  
DBA        EXP_FULL_DATABASE                                          14 SELECT ANY TABLE                                  
DBA        SCHEDULER_ADMIN                                             8 CREATE ANY CREDENTIAL                             
DBA        SCHEDULER_ADMIN                                             8 CREATE ANY JOB                                    
DBA        SCHEDULER_ADMIN                                             8 CREATE CREDENTIAL                                 
DBA        SCHEDULER_ADMIN                                             8 CREATE EXTERNAL JOB                               
DBA        SCHEDULER_ADMIN                                             8 CREATE JOB                                        
DBA        SCHEDULER_ADMIN                                             8 EXECUTE ANY CLASS                                 
DBA        SCHEDULER_ADMIN                                             8 EXECUTE ANY PROGRAM                               
DBA        SCHEDULER_ADMIN                                             8 MANAGE SCHEDULER                                  
DBA        DATAPUMP_EXP_FULL_DATABASE                                  2 CREATE SESSION                                    
DBA        EM_EXPRESS_BASIC                                            2 CREATE SESSION                                    
DBA        DATAPUMP_EXP_FULL_DATABASE                                  2 CREATE TABLE                                      
DBA        EM_EXPRESS_BASIC                                            2 EM EXPRESS CONNECT                                

171行が選択されました。 

operatiton単位でprivilegeをさまって見る

listagg2を使わせてもらっています。。ありがとございます。自分でも南下作ってみたくなるような記事でした!!脱線するけど、そもそもディクショナリの分類意識していないから、ここでまとめておく。

ユーザ定義「集計」関数でDISTINCTや分析関数のウインドウが使えるLISTAGGを作る  

role_sys_privsテーブルではロールに付与されている権限を確認できる。表示される情報は、ユーザーがアクセス可能なロールに関するもののみ。

コード表示

SET PAGESIZE 50000
SET LINESIZE 1000
SET TAB OFF
SET TRIMSPOOL ON
COL ope FOR a15
COL cnt FOR 99
COL prv FOR a155

SELECT
	ope
	,COUNT(DISTINCT PRIVILEGE) AS cnt
	,listagg2(DISTINCT PRIVILEGE) AS prv
FROM
	(
		SELECT
			s1.*
			, row_number() OVER(
				PARTITION BY s1.ROLE, s1.PRIVILEGE
				ORDER BY
					ROWNUM
			) AS rn
			, COLUMN_VALUE AS ope
		FROM
			(
				SELECT
					ROLE
					, PRIVILEGE
					, item_liz.segregate(REPLACE(PRIVILEGE, ' ', ',')
					              || ',') AS liz
				FROM
					role_sys_privs
			) s1
			, TABLE ( liz ) s2
	)
WHERE
	rn = 1
GROUP BY
	ope
ORDER BY
	COUNT(DISTINCT PRIVILEGE) DESC
	,ope
;

ちなみにDEBUGはplsqlのprocedureのデバッグ機能を使用するときに必要な権限たち。DEBUG ANY PROCEDURE,DEBUG CONNECT ANY,DEBUG CONNECT SESSION。
create drop alter execute select は充実しているんだな。ここら辺から覚えていくのかな。おおくね??

コード表示

OPE             CNT PRV                                                                                                                                                        
--------------- --- -----------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE           74 CREATE ANALYTIC VIEW,CREATE ANY ANALYTIC VIEW,CREATE ANY ASSEMBLY,CREATE ANY ATTRIBUTE DIMENSION,CREATE ANY CLUSTER,CREATE ANY CONTEXT,CREATE ANY CREDENTIA
                    L,CREATE ANY CUBE,CREATE ANY CUBE BUILD PROCESS,CREATE ANY CUBE DIMENSION,CREATE ANY DIMENSION,CREATE ANY DIRECTORY,CREATE ANY EDITION,CREATE ANY EVALUATIO
                    N CONTEXT,CREATE ANY HIERARCHY,CREATE ANY INDEX,CREATE ANY INDEXTYPE,CREATE ANY JOB,CREATE ANY LIBRARY,CREATE ANY MATERIALIZED VIEW,CREATE ANY MEASURE FOLD
                    ER,CREATE ANY MINING MODEL,CREATE ANY OPERATOR,CREATE ANY OUTLINE,CREATE ANY PROCEDURE,CREATE ANY RULE,CREATE ANY RULE SET,CREATE ANY SEQUENCE,CREATE ANY S
                    QL PROFILE,CREATE ANY SQL TRANSLATION PROFILE,CREATE ANY SYNONYM,CREATE ANY TABLE,CREATE ANY TRIGGER,CREATE ANY TYPE,CREATE ANY VIEW,CREATE ASSEMBLY,CREATE
                     ATTRIBUTE DIMENSION,CREATE CLUSTER,CREATE CREDENTIAL,CREATE CUBE,CREATE CUBE BUILD PROCESS,CREATE CUBE DIMENSION,CREATE DATABASE LINK,CREATE DIMENSION,CRE
                    ATE EVALUATION CONTEXT,CREATE EXTERNAL JOB,CREATE HIERARCHY,CREATE INDEXTYPE,CREATE JOB,CREATE LIBRARY,CREATE LOCKDOWN PROFILE,CREATE MATERIALIZED VIEW,CRE
                    ATE MEASURE FOLDER,CREATE MINING MODEL,CREATE OPERATOR,CREATE PLUGGABLE DATABASE,CREATE PROCEDURE,CREATE PROFILE,CREATE PUBLIC DATABASE LINK,CREATE PUBLIC 
                    SYNONYM,CREATE ROLE,CREATE ROLLBACK SEGMENT,CREATE RULE,CREATE RULE SET,CREATE SEQUENCE,CREATE SESSION,CREATE SQL TRANSLATION PROFILE,CREATE SYNONYM,CREATE
                     TABLE,CREATE TABLESPACE,CREATE TRIGGER,CREATE TYPE,CREATE USER,CREATE VIEW                                                                                

DROP             40 DROP ANY ANALYTIC VIEW,DROP ANY ASSEMBLY,DROP ANY ATTRIBUTE DIMENSION,DROP ANY CLUSTER,DROP ANY CONTEXT,DROP ANY CUBE,DROP ANY CUBE BUILD PROCESS,DROP ANY 
                    CUBE DIMENSION,DROP ANY DIMENSION,DROP ANY DIRECTORY,DROP ANY EDITION,DROP ANY EVALUATION CONTEXT,DROP ANY HIERARCHY,DROP ANY INDEX,DROP ANY INDEXTYPE,DROP
                     ANY LIBRARY,DROP ANY MATERIALIZED VIEW,DROP ANY MEASURE FOLDER,DROP ANY MINING MODEL,DROP ANY OPERATOR,DROP ANY OUTLINE,DROP ANY PROCEDURE,DROP ANY ROLE,D
                    ROP ANY RULE,DROP ANY RULE SET,DROP ANY SEQUENCE,DROP ANY SQL PROFILE,DROP ANY SQL TRANSLATION PROFILE,DROP ANY SYNONYM,DROP ANY TABLE,DROP ANY TRIGGER,DRO
                    P ANY TYPE,DROP ANY VIEW,DROP LOCKDOWN PROFILE,DROP PROFILE,DROP PUBLIC DATABASE LINK,DROP PUBLIC SYNONYM,DROP ROLLBACK SEGMENT,DROP TABLESPACE,DROP USER  

ALTER            38 ALTER ANY ANALYTIC VIEW,ALTER ANY ASSEMBLY,ALTER ANY ATTRIBUTE DIMENSION,ALTER ANY CLUSTER,ALTER ANY CUBE,ALTER ANY CUBE BUILD PROCESS,ALTER ANY CUBE DIMEN
                    SION,ALTER ANY DIMENSION,ALTER ANY EDITION,ALTER ANY EVALUATION CONTEXT,ALTER ANY HIERARCHY,ALTER ANY INDEX,ALTER ANY INDEXTYPE,ALTER ANY LIBRARY,ALTER ANY
                     MATERIALIZED VIEW,ALTER ANY MEASURE FOLDER,ALTER ANY MINING MODEL,ALTER ANY OPERATOR,ALTER ANY OUTLINE,ALTER ANY PROCEDURE,ALTER ANY ROLE,ALTER ANY RULE,A
                    LTER ANY RULE SET,ALTER ANY SEQUENCE,ALTER ANY SQL PROFILE,ALTER ANY SQL TRANSLATION PROFILE,ALTER ANY TABLE,ALTER ANY TRIGGER,ALTER ANY TYPE,ALTER DATABAS
                    E,ALTER LOCKDOWN PROFILE,ALTER PROFILE,ALTER RESOURCE COST,ALTER ROLLBACK SEGMENT,ALTER SESSION,ALTER SYSTEM,ALTER TABLESPACE,ALTER USER                   

EXECUTE          12 EXECUTE ANY ASSEMBLY,EXECUTE ANY CLASS,EXECUTE ANY EVALUATION CONTEXT,EXECUTE ANY INDEXTYPE,EXECUTE ANY LIBRARY,EXECUTE ANY OPERATOR,EXECUTE ANY PROCEDURE,
                    EXECUTE ANY PROGRAM,EXECUTE ANY RULE,EXECUTE ANY RULE SET,EXECUTE ANY TYPE,EXECUTE ASSEMBLY                                                                

SELECT            9 SELECT ANY CUBE,SELECT ANY CUBE BUILD PROCESS,SELECT ANY CUBE DIMENSION,SELECT ANY DICTIONARY,SELECT ANY MEASURE FOLDER,SELECT ANY MINING MODEL,SELECT ANY 
                    SEQUENCE,SELECT ANY TABLE,SELECT ANY TRANSACTION                                                                                                           

ADMINISTER        5 ADMINISTER ANY SQL TUNING SET,ADMINISTER DATABASE TRIGGER,ADMINISTER RESOURCE MANAGER,ADMINISTER SQL MANAGEMENT OBJECT,ADMINISTER SQL TUNING SET           
MANAGE            5 MANAGE ANY FILE GROUP,MANAGE ANY QUEUE,MANAGE FILE GROUP,MANAGE SCHEDULER,MANAGE TABLESPACE                                                                
UPDATE            4 UPDATE ANY CUBE,UPDATE ANY CUBE BUILD PROCESS,UPDATE ANY CUBE DIMENSION,UPDATE ANY TABLE                                                                   
DEBUG             3 DEBUG ANY PROCEDURE,DEBUG CONNECT ANY,DEBUG CONNECT SESSION                                                                                                
DELETE            3 DELETE ANY CUBE DIMENSION,DELETE ANY MEASURE FOLDER,DELETE ANY TABLE                                                                                       
GRANT             3 GRANT ANY OBJECT PRIVILEGE,GRANT ANY PRIVILEGE,GRANT ANY ROLE                                                                                              
INSERT            3 INSERT ANY CUBE DIMENSION,INSERT ANY MEASURE FOLDER,INSERT ANY TABLE                                                                                       
UNDER             3 UNDER ANY TABLE,UNDER ANY TYPE,UNDER ANY VIEW                                                                                                              
ANALYZE           2 ANALYZE ANY,ANALYZE ANY DICTIONARY                                                                                                                         
AUDIT             2 AUDIT ANY,AUDIT SYSTEM                                                                                                                                     
COMMENT           2 COMMENT ANY MINING MODEL,COMMENT ANY TABLE                                                                                                                 
FLASHBACK         2 FLASHBACK ANY TABLE,FLASHBACK ARCHIVE ADMINISTER                                                                                                           
FORCE             2 FORCE ANY TRANSACTION,FORCE TRANSACTION                                                                                                                    
READ              2 READ ANY FILE GROUP,READ ANY TABLE                                                                                                                         
USE               2 USE ANY JOB RESOURCE,USE ANY SQL TRANSLATION PROFILE                                                                                                       
ADVISOR           1 ADVISOR                                                                                                                                                    
BACKUP            1 BACKUP ANY TABLE                                                                                                                                           
BECOME            1 BECOME USER                                                                                                                                                
CHANGE            1 CHANGE NOTIFICATION                                                                                                                                        
DEQUEUE           1 DEQUEUE ANY QUEUE                                                                                                                                          
EM                1 EM EXPRESS CONNECT                                                                                                                                         
ENQUEUE           1 ENQUEUE ANY QUEUE                                                                                                                                          
EXEMPT            1 EXEMPT REDACTION POLICY                                                                                                                                    
EXPORT            1 EXPORT FULL DATABASE                                                                                                                                       
GLOBAL            1 GLOBAL QUERY REWRITE                                                                                                                                       
IMPORT            1 IMPORT FULL DATABASE                                                                                                                                       
LOCK              1 LOCK ANY TABLE                                                                                                                                             
LOGMINING         1 LOGMINING                                                                                                                                                  
MERGE             1 MERGE ANY VIEW                                                                                                                                             
ON                1 ON COMMIT REFRESH                                                                                                                                          
QUERY             1 QUERY REWRITE                                                                                                                                              
REDEFINE          1 REDEFINE ANY TABLE                                                                                                                                         
RESTRICTED        1 RESTRICTED SESSION                                                                                                                                         
RESUMABLE         1 RESUMABLE                                                                                                                                                  
SET               1 SET CONTAINER                                                                                                                                              

40行が選択されました。 


test___role_prv_mst___の作成

コード表示

DROP TABLE test___role_prv_mst___ PURGE;
CREATE TABLE test___role_prv_mst___ AS
WITH liz___source___ AS(
SELECT
	SUM(CASE WHEN LEVEL <> 1 THEN 0 ELSE 1 END) OVER (ORDER BY ROWNUM) AS grp
	,LEVEL AS lv
	,CONNECT_BY_ROOT grantee AS root_role
	,grantee
	,granted_role
	,CONNECT_BY_ISLEAF AS isleaf
	,sys_connect_by_path(granted_role,',') AS liz
FROM
	dba_role_privs
START WITH
	grantee = 'DBA'
CONNECT BY
	PRIOR granted_role = grantee
)
,liz___create___ AS(
SELECT
	root_role
	,item_liz.ecl_dupli_liz(item_liz.segregate(substr(LISTAGG(liz)WITHIN GROUP (ORDER BY grp+grpseq),2))) AS liz
FROM
	(
	SELECT
		s1.grp
		,row_number()OVER(PARTITION BY s1.grp ORDER BY lv) AS grpseq
		,s1.lv
		,s1.root_role
		,s1.liz
	FROM
		liz___source___ s1
	WHERE
		s1.isleaf = 1
	)
GROUP BY
	root_role
)
,mst___role____ AS(
SELECT
	root_role AS root_role
	, column_value AS related_role
FROM
	liz___create___
	, TABLE ( liz )
)
,mst___role_prv____ AS(
SELECT
	grantee
	,RTRIM(XMLAGG(XMLELEMENT(E,PRIVILEGE,',').EXTRACT('//text()')).getclobval(),',') AS liz
FROM
	dba_sys_privs
GROUP BY
	grantee
)
,summury___role_prv___ AS (
SELECT
	s1.root_role
	,s1.related_role
	,s2.liz
FROM
	mst___role____ s1
		LEFT OUTER JOIN mst___role_prv____ s2
			ON
				s1.related_role = s2.grantee
),detail___role_prv___ AS(
	SELECT
		root_role
		,related_role
		,liz
		,COLUMN_VALUE AS related_prv
	FROM
		summury___role_prv___
		,TABLE(item_liz.segregate(liz))
)SELECT
	s1.root_role
	,s1.related_role
	,s1.related_prv
FROM
	detail___role_prv___ s1
ORDER BY
	s1.related_role
	,s1.related_prv
;
select * from test___role_prv_mst___;

test___ope_prv_mst___の作成

コード表示

DROP TABLE test___ope_prv_mst___ PURGE;
CREATE TABLE test___ope_prv_mst___ AS
SELECT DISTINCT
	ope
	,PRIVILEGE as prv
FROM
	(
		SELECT
			s1.*
			, row_number() OVER(
				PARTITION BY s1.ROLE, s1.PRIVILEGE
				ORDER BY
					ROWNUM
			) AS rn
			, COLUMN_VALUE AS ope
		FROM
			(
				SELECT
					ROLE
					, PRIVILEGE
					, item_liz.segregate(REPLACE(PRIVILEGE, ' ', ',')
					              || ',') AS liz
				FROM
					role_sys_privs
			) s1
			, TABLE ( liz ) s2
	)
WHERE
	rn = 1
ORDER BY
	ope
	,PRIVILEGE
;

select * from TEST___OPE_PRV_MST___;

パスカルの三角形に関して

参考文献

やっと理解できた気がする。。オマージュしてみる。別のやり方で。

関数から作成、、そのまえに。

引数に渡すものと返却するものを考える。前段の段数番号と前段で構築した文字列。この2つを引数にする。返却値は次の段数で構築する文字列とその段数番号。まずは関数作成のまえに単一クエリで動きを捉えるところから始まる。

コード表示

col rn for 999
col ln for a10
col seq for 999
col seg for a10
col pre_seg for a10

WITH sub AS(
SELECT
	s1.rn
	,s1.ln
	,ROWNUM AS seq
	,decode(ROWNUM,1,substr(s1.ln,1,1),substr(s1.ln,instr(s1.ln,'-',1,ROWNUM - 1) + 1,nvl(nullif(instr(s1.ln,'-',1,ROWNUM),0),4000) - instr(s1.ln,'-',1,ROWNUM - 1) - 1)) AS seg
FROM
	TABLE ( liz(item(3, '1-2-1') ) ) s1
--	TABLE ( liz(item(3, '1-2-1-0') ) ) s1
CONNECT BY
	LENGTH(s1.ln) - LENGTH(REPLACE(s1.ln,'-','')) + 1 >= LEVEL
)
SELECT
	rn
	,ln
	,seq
	,seg
	,LAG(seg,1,0)OVER(ORDER BY seq) AS pre_seg
FROM
	sub
;

どうして、-0を加えているのか最初分からなかった。次の段数計算に使用するためだったのかとトレースしてやっと分かる。

コード表示

  RN LN          SEQ SEG        PRE_SEG   
---- ---------- ---- ---------- ----------
   3 1-2-1         1 1          0         
   3 1-2-1         2 2          1         
   3 1-2-1         3 1          2         

-0を追加したもの。

コード表示

col rn for 999
col ln for a10
col seq for 999
col seg for a10
col pre_seg for a10

WITH sub AS(
SELECT
	s1.rn
	,s1.ln
	,ROWNUM AS seq
	,decode(ROWNUM,1,substr(s1.ln,1,1),substr(s1.ln,instr(s1.ln,'-',1,ROWNUM - 1) + 1,nvl(nullif(instr(s1.ln,'-',1,ROWNUM),0),4000) - instr(s1.ln,'-',1,ROWNUM - 1) - 1)) AS seg
FROM
	TABLE ( liz(item(3, '1-2-1-0') ) ) s1
CONNECT BY
	LENGTH(s1.ln) - LENGTH(REPLACE(s1.ln,'-','')) + 1 >= LEVEL
)
SELECT
	rn
	,ln
	,seq
	,seg
	,LAG(seg,1,0)OVER(ORDER BY seq) AS pre_seg
FROM
	sub
;

この規則性はわからんかった。パスカルの三角形ってきれいだな。直前の文字列が1であることを信じている感じが分かる。

コード表示

  RN LN          SEQ SEG        PRE_SEG   
---- ---------- ---- ---------- ----------
   3 1-2-1-0       1 1          0         
   3 1-2-1-0       2 2          1         
   3 1-2-1-0       3 1          2         
   3 1-2-1-0       4 0          1         

んでもって次段数と次段数に渡す文字列を構築する。

サマリ処理で実現。

コード表示

WITH sub AS(
SELECT
	s1.rn
	,s1.ln
	,ROWNUM AS seq
	,decode(ROWNUM,1,substr(s1.ln,1,1),substr(s1.ln,instr(s1.ln,'-',1,ROWNUM - 1) + 1,nvl(nullif(instr(s1.ln,'-',1,ROWNUM),0),4000) - instr(s1.ln,'-',1,ROWNUM - 1) - 1)) AS seg
FROM
	TABLE ( liz(item(3, '1-2-1-0') ) ) s1
CONNECT BY
	LENGTH(s1.ln) - LENGTH(REPLACE(s1.ln,'-','')) + 1 >= LEVEL
)
SELECT
	rn + 1 AS rn
	,LISTAGG(to_number(seg) + to_number(pre_seg),'-')WITHIN GROUP (ORDER BY seq)  AS seg
FROM
	(
	SELECT
		rn
		,seq
		,seg
		,LAG(seg,1,0)OVER(ORDER BY seq) AS pre_seg
	FROM
		sub
	)
GROUP BY
	rn + 1
;

できた。あとはこいつをコレクションに変換してやるだけ。

コード表示

  RN SEG       
---- ----------
   4 1-3-3-1   

コレクションに変換してやる

コード表示

col liz for a100

WITH sub AS(
SELECT
	s1.rn
	,s1.ln
	,ROWNUM AS seq
	,decode(ROWNUM,1,substr(s1.ln,1,1),substr(s1.ln,instr(s1.ln,'-',1,ROWNUM - 1) + 1,nvl(nullif(instr(s1.ln,'-',1,ROWNUM),0),4000) - instr(s1.ln,'-',1,ROWNUM - 1) - 1)) AS seg
FROM
	TABLE ( liz(item(3, '1-2-1-0') ) ) s1
CONNECT BY
	LENGTH(s1.ln) - LENGTH(REPLACE(s1.ln,'-','')) + 1 >= LEVEL
)
SELECT
	CAST(COLLECT(item(rn,seg)) AS liz) AS liz
FROM
	(
	SELECT
		rn + 1 AS rn
		,LISTAGG(to_number(seg) + to_number(pre_seg),'-')WITHIN GROUP (ORDER BY seq)  AS seg
	FROM
		(
		SELECT
			rn
			,seq
			,seg
			,LAG(seg,1,0)OVER(ORDER BY seq) AS pre_seg
		FROM
			sub
		)
	GROUP BY
		rn + 1
	)
;

コレクションできた!

コード表示

LIZ(RN, LN)                                                                                         
----------------------------------------------------------------------------------------------------
LIZ(ITEM(4, '1-3-3-1'))

ファンクションに落とし込む。with functionでやってみようかな。あとスキーマレベルでのファンクションも作ってみる。多分コード長くなるからファンクションに切り出したほうがいい。

12cからwith functionが使えます。bulk collectしてみる

コード表示

CREATE OR REPLACE TYPE item IS OBJECT(rn NUMBER,ln VARCHAR2(4000));
/
CREATE OR REPLACE TYPE liz IS TABLE OF item;
/
WITH
	FUNCTION calc(p_liz liz) RETURN liz IS
	rt liz :=NULL;
BEGIN
	SELECT
		item(rn,seg)
	BULK COLLECT
	INTO rt
	FROM
		(
		WITH sub AS(
		SELECT
			s1.rn
			,s1.ln
			,ROWNUM AS seq
			,decode(ROWNUM,1,substr(s1.ln,1,1),substr(s1.ln,instr(s1.ln,'-',1,ROWNUM - 1) + 1,nvl(nullif(instr(s1.ln,'-',1,ROWNUM),0),4000) - instr(s1.ln,'-',1,ROWNUM - 1) - 1)) AS seg
		FROM
			(SELECT rn,ln || '-0' AS ln FROM TABLE(p_liz)) s1
		CONNECT BY
			LENGTH(s1.ln) - LENGTH(REPLACE(s1.ln,'-','')) + 1 >= LEVEL
		)
		SELECT
			rn + 1 AS rn
			,LISTAGG(to_number(seg) + to_number(pre_seg),'-')WITHIN GROUP (ORDER BY seq)  AS seg
		FROM
			(
			SELECT
				rn
				,seq
				,seg
				,LAG(seg,1,0)OVER(ORDER BY seq) AS pre_seg
			FROM
				sub
			)
		GROUP BY
			rn + 1
		);
	RETURN rt;
END;
SELECT calc(liz(item(3,'1-2-1'))) as liz FROM dual
/

スキーマレベルのファンクション

コード表示

CREATE OR REPLACE TYPE item IS OBJECT(rn NUMBER,ln VARCHAR2(4000));
/
CREATE OR REPLACE TYPE liz IS TABLE OF item;
/

CREATE OR REPLACE FUNCTION calc(p_liz liz)
RETURN liz
AS rt liz;
BEGIN
	SELECT
		item(rn,seg)
	BULK COLLECT
	INTO rt
	FROM
		(
		WITH sub AS(
		SELECT
			s1.rn
			,s1.ln
			,ROWNUM AS seq
			,decode(ROWNUM,1,substr(s1.ln,1,1),substr(s1.ln,instr(s1.ln,'-',1,ROWNUM - 1) + 1,nvl(nullif(instr(s1.ln,'-',1,ROWNUM),0),4000) - instr(s1.ln,'-',1,ROWNUM - 1) - 1)) AS seg
		FROM
			(SELECT rn,ln || '-0' AS ln FROM TABLE(p_liz)) s1
		CONNECT BY
			LENGTH(s1.ln) - LENGTH(REPLACE(s1.ln,'-','')) + 1 >= LEVEL
		)
		SELECT
			rn + 1 AS rn
			,LISTAGG(to_number(seg) + to_number(pre_seg),'-')WITHIN GROUP (ORDER BY seq)  AS seg
		FROM
			(
			SELECT
				rn
				,seq
				,seg
				,LAG(seg,1,0)OVER(ORDER BY seq) AS pre_seg
			FROM
				sub
			)
		GROUP BY
			rn + 1
		);
	RETURN rt;
END;
/

SELECT calc(liz(item(3,'1-2-1'))) as liz FROM dual;

LIZ(RN, LN)                                                                                         
----------------------------------------------------------------------------------------------------
LIZ(ITEM(4, '1-3-3-1'))

再帰してみる。まずはイメージから。

5段ぐらいで。

コード表示

SELECT * FROM table(calc(liz(item(1,'1'))));
SELECT * FROM table(calc(liz(item(2,'1-1'))));
SELECT * FROM table(calc(liz(item(3,'1-2-1'))));
SELECT * FROM table(calc(liz(item(4,'1-3-3-1'))));

  RN LN                                                
---- --------------------------------------------------
   2 1-1                                               


  RN LN                                                
---- --------------------------------------------------
   3 1-2-1                                             


  RN LN                                                
---- --------------------------------------------------
   4 1-3-3-1                                           


  RN LN                                                
---- --------------------------------------------------
   5 1-4-6-4-1                                         

スキーマレベルのファンクションでグルグルする

スキーマレベルのファンクションを使用している。おおぉ

コード表示

COL ln FOR a10
COL triangle FOR a50

WITH sub AS(
SELECT LEVEL AS rn FROM dual CONNECT BY LEVEL <= 5
),rec(rn,ln)AS(
SELECT s1.rn,to_char(s1.rn) FROM sub s1 WHERE s1.rn = 1
UNION ALL
SELECT s2.rn,s2.ln FROM rec s1,TABLE(calc(liz(item(s1.rn,to_char(s1.ln)))))s2
WHERE
	s1.rn < 5
)SELECT
	s1.*
	,MAX(LENGTH(s1.ln))OVER() AS mx
	,LENGTH(s1.ln) AS len
	,MAX(LENGTH(s1.ln))OVER() - LENGTH(s1.ln) AS dif
	,(MAX(LENGTH(s1.ln))OVER() - LENGTH(s1.ln))/2 AS div
	,TRUNC((MAX(LENGTH(s1.ln))OVER() - LENGTH(s1.ln))/2,0) AS tnc
	,lpad(' ',TRUNC((MAX(LENGTH(s1.ln))OVER() - LENGTH(s1.ln))/2,0),' ') || s1.ln AS triangle
FROM
	rec s1
;

lpadの使い方がパズルのピースみたいに気持ちよかった。

コード表示

  RN LN                 MX        LEN        DIF        DIV        TNC TRIANGLE                                          
---- ---------- ---------- ---------- ---------- ---------- ---------- --------------------------------------------------
   1 1                   9          1          8          4          4     1                                             
   2 1-1                 9          3          6          3          3    1-1                                            
   3 1-2-1               9          5          4          2          2   1-2-1                                           
   4 1-3-3-1             9          7          2          1          1  1-3-3-1                                          
   5 1-4-6-4-1           9          9          0          0          0 1-4-6-4-1                                         

即時レベルのwithファンクションでグルグルする

即時レベルのwithファンクションを使用している。おおぉ。再帰でないwithと再帰withとwithファンクションのコラボ。再帰でないwithは,(カンマ)つけなくていいんだね。勉強になった。最後は;(セミコロン)でなくて/(スラッシュ)で、締める。

コード表示

CREATE OR REPLACE TYPE item IS OBJECT(rn NUMBER,ln VARCHAR2(4000));
/
CREATE OR REPLACE TYPE liz IS TABLE OF item;
/
WITH
	FUNCTION calc(p_liz liz) RETURN liz IS
	rt liz :=NULL;
BEGIN
	SELECT
		item(rn,seg)
	BULK COLLECT
	INTO rt
	FROM
		(
		WITH sub AS(
		SELECT
			s1.rn
			,s1.ln
			,ROWNUM AS seq
			,decode(ROWNUM,1,substr(s1.ln,1,1),substr(s1.ln,instr(s1.ln,'-',1,ROWNUM - 1) + 1,nvl(nullif(instr(s1.ln,'-',1,ROWNUM),0),4000) - instr(s1.ln,'-',1,ROWNUM - 1) - 1)) AS seg
		FROM
			(SELECT rn,ln || '-0' AS ln FROM TABLE(p_liz)) s1
		CONNECT BY
			LENGTH(s1.ln) - LENGTH(REPLACE(s1.ln,'-','')) + 1 >= LEVEL
		)
		SELECT
			rn + 1 AS rn
			,LISTAGG(to_number(seg) + to_number(pre_seg),'-')WITHIN GROUP (ORDER BY seq)  AS seg
		FROM
			(
			SELECT
				rn
				,seq
				,seg
				,LAG(seg,1,0)OVER(ORDER BY seq) AS pre_seg
			FROM
				sub
			)
		GROUP BY
			rn + 1
		);
	RETURN rt;
END;
sub AS(
SELECT LEVEL AS rn FROM dual CONNECT BY LEVEL <= 5
),rec(rn,ln)AS(
SELECT s1.rn,to_char(s1.rn) FROM sub s1 WHERE s1.rn = 1
UNION ALL
SELECT s2.rn,s2.ln FROM rec s1,TABLE(calc(liz(item(s1.rn,to_char(s1.ln)))))s2
WHERE
	s1.rn < 5
)SELECT
	s1.*
	,MAX(LENGTH(s1.ln))OVER() AS mx
	,LENGTH(s1.ln) AS len
	,MAX(LENGTH(s1.ln))OVER() - LENGTH(s1.ln) AS dif
	,(MAX(LENGTH(s1.ln))OVER() - LENGTH(s1.ln))/2 AS div
	,TRUNC((MAX(LENGTH(s1.ln))OVER() - LENGTH(s1.ln))/2,0) AS tnc
	,lpad(' ',TRUNC((MAX(LENGTH(s1.ln))OVER() - LENGTH(s1.ln))/2,0),' ') || s1.ln AS triangle
FROM
	rec s1
/

lpadの使い方がパズルのピースみたいに気持ちよかった。

コード表示

  RN LN                 MX        LEN        DIF        DIV        TNC TRIANGLE                                          
---- ---------- ---------- ---------- ---------- ---------- ---------- --------------------------------------------------
   1 1                   9          1          8          4          4     1                                             
   2 1-1                 9          3          6          3          3    1-1                                            
   3 1-2-1               9          5          4          2          2   1-2-1                                           
   4 1-3-3-1             9          7          2          1          1  1-3-3-1                                          
   5 1-4-6-4-1           9          9          0          0          0 1-4-6-4-1                                         

再帰段数をバインド変数にしておしまい。

バインド変数stkを入力して使う。

コード表示

SET PAGESIZE 50000
SET LINESIZE 1000
SET TAB OFF
SET TRIMSPOOL ON
COL triangle FOR a170
DEFINE stk=15


WITH sub AS(
SELECT LEVEL AS rn FROM dual CONNECT BY LEVEL <= &stk
),rec(rn,ln)AS(
SELECT s1.rn,to_char(s1.rn) FROM sub s1 WHERE s1.rn = 1
UNION ALL
SELECT s2.rn,s2.ln FROM rec s1,TABLE(calc(liz(item(s1.rn,to_char(s1.ln)))))s2
WHERE
	s1.rn < &stk
)SELECT
	lpad(' ',TRUNC((MAX(LENGTH(s1.ln))OVER() - LENGTH(s1.ln))/2,0),' ') || s1.ln AS triangle
FROM
	rec s1
;

tlokwegさんすごすぎ。。。

コード表示

TRIANGLE                                                                                                                                                                  
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                            1
                           1-1
                          1-2-1
                         1-3-3-1
                        1-4-6-4-1
                      1-5-10-10-5-1
                     1-6-15-20-15-6-1
                   1-7-21-35-35-21-7-1
                  1-8-28-56-70-56-28-8-1
               1-9-36-84-126-126-84-36-9-1
           1-10-45-120-210-252-210-120-45-10-1
         1-11-55-165-330-462-462-330-165-55-11-1
       1-12-66-220-495-792-924-792-495-220-66-12-1
   1-13-78-286-715-1287-1716-1716-1287-715-286-78-13-1
1-14-91-364-1001-2002-3003-3432-3003-2002-1001-364-91-14-1

15行が選択されました。 

再帰関数でも実現できた!

バインド変数stkを入力して使う。

コード表示

SET PAGESIZE 50000
SET LINESIZE 1000
SET TAB OFF
SET TRIMSPOOL ON
COL triangle FOR a170

DEFINE stk=15
/
CREATE OR REPLACE TYPE item IS OBJECT(rn NUMBER,ln VARCHAR2(4000));
/
CREATE OR REPLACE TYPE liz IS TABLE OF item;
/
CREATE OR REPLACE FUNCTION calc_rec (
	p_liz liz
	, p_stk NUMBER
) RETURN liz AS
	rt   liz := p_liz;
BEGIN
	SELECT
		CASE
			WHEN p_stk = 0 THEN liz(item(NULL, NULL) )
			ELSE rt MULTISET UNION ALL calc_rec(CAST(collect(item(rn, seg) ) AS liz), p_stk - 1)
		END
	INTO rt
	FROM
		(
			WITH sub AS (
				SELECT
					s1.rn
					, s1.ln
					, ROWNUM AS seq
					, DECODE(ROWNUM, 1, substr(s1.ln, 1, 1), substr(s1.ln, instr(s1.ln, '-', 1, ROWNUM - 1) + 1, nvl(nullif(instr(s1.ln, '-', 1, ROWNUM), 0), 4000) - instr(s1.ln, '-', 1, ROWNUM - 1) - 1) ) AS seg
				FROM
					(
						SELECT
							rn
							, ln || '-0' AS ln
						FROM
							TABLE ( p_liz )
					) s1
				CONNECT BY
					length(s1.ln) - length(replace(s1.ln, '-', '') ) + 1 >= level
			) SELECT
				rn + 1 AS rn
				, LISTAGG(to_number(seg) + to_number(pre_seg), '-') WITHIN GROUP(
					ORDER BY
						seq
				) AS seg
			  FROM
				(
					SELECT
						rn
						, seq
						, seg
						, LAG(seg, 1, 0) OVER(
							ORDER BY
								seq
						) AS pre_seg
					FROM
						sub
				)
			  GROUP BY
				rn + 1
		);

	RETURN rt;
END;
/

SELECT
	lpad(' ',TRUNC((MAX(LENGTH(ln))OVER() - LENGTH(ln))/2,0),' ') || ln AS triangle
FROM
	TABLE(calc_rec(liz(item(1,'1')),&stk))
WHERE
	rn IS NOT NULL
/

clob型のカンマ区切り文字をちょっとずつ切り取ってちょろちょろcollection型にしていく話

qiitaに書きました。

clob型のカンマ区切り文字をちょっとずつ切り取ってちょろちょろcollection型にしていく話

clob型をcollectionで返却する

参考文献

現在のところ、PL/SQLのブロック、サブプログラムまたはパッケージ内ではオブジェクト型を定義できません。

PL/SQLのオブジェクト型の使用  
 

LOB属性を含むオブジェクト型を作成してから、そのオブジェクト型を基にネストした表を作成する必要があります。

LOBに対するDDL文とDML文  
 

PLS-00642: SQL文ではローカル・コレクション型は使用できません。

コレクション型をスキーマ・レベルで定義している(CREATE TYPEで定義している)場合。コレクション型をローカル・レベルで定義している(PL/SQLブロック内で定義している)場合。基本的にスキーマレベルで宣言しておくと、エラーとか出ずに済むはず。スキーマがタイプだらけになるかもだけど。。

単一sqlで使う場合

クエリ

コード表示

SET SERVEROUTPUT ON

CREATE OR REPLACE TYPE item IS OBJECT (val CLOB);
/

CREATE OR REPLACE TYPE liz IS TABLE OF item;
/

SELECT CAST(COLLECT(item('CREATE SESSION,CREATE TABLE,')) AS liz) as liz FROM dual;

実行例

コード表示

[oracle@f285aba0589a ~]$ sqlplus aine/ORACLE_PWD@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Thu Mar 21 13:18:08 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Last Successful login time: Thu Mar 21 2019 13:17:23 +09:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

AINE@pdb1> col liz for a170
AINE@pdb1> SELECT CAST(COLLECT(item('CREATE SESSION,CREATE TABLE,')) AS liz) as liz FROM dual;

LIZ(VAL)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
LIZ(ITEM('CREATE SESSION,CREATE TABLE,'))

1 row selected.

Elapsed: 00:00:00.02

パッケージでファンクションくるんで使用する場合

クエリ

コード表示

SET SERVEROUTPUT ON

CREATE OR REPLACE TYPE item IS OBJECT (val CLOB);
/

CREATE OR REPLACE TYPE liz IS TABLE OF item;
/

CREATE OR REPLACE PACKAGE test___clbliz
IS
	FUNCTION clbliz(p_clb IN CLOB) RETURN liz;
END;
/
CREATE OR REPLACE PACKAGE BODY test___clbliz
IS
	FUNCTION clbliz(p_clb IN CLOB) RETURN liz
	IS
		rt liz;
	BEGIN
		SELECT CAST(COLLECT(item(p_clb)) AS liz) INTO rt FROM dual;
		RETURN rt;
	END;
END;
/

SELECT test___clbliz.clbliz(to_clob('CREATE SESSION,CREATE TABLE,')) as liz FROM dual;

実行例

コード表示

AINE@pdb1> SELECT test___clbliz.clbliz(to_clob('CREATE SESSION,CREATE TABLE,')) as liz FROM dual;

LIZ(VAL)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
LIZ(ITEM('CREATE SESSION,CREATE TABLE,'))

1 row selected.

Elapsed: 00:00:00.01

bulk collect句に関して

まえがき

bulk collectについて少し調べたので。。

テストデータ

rnは数値型、alpは文字列型。

コード表示

DROP TABLE test___$___ PURGE;
CREATE TABLE test___$___ AS
SELECT
	LEVEL AS rn
	,CHR(64 + LEVEL) AS alp
FROM
	dual
CONNECT BY
	LEVEL <= 26
;

AINE@pdb1> SELECT * FROM test___$___;

           RN ALP
------------- ----
            1 A
            2 B
            3 C
            4 D
            5 E
            6 F
            7 G
            8 H
            9 I
           10 J
           11 K
           12 L
           13 M
           14 N
           15 O
           16 P
           17 Q
           18 R
           19 S
           20 T
           21 U
           22 V
           23 W
           24 X
           25 Y
           26 Z

26 rows selected.

Elapsed: 00:00:00.00

collection型 単一

collection型 単一はbulk collectできない。単一だから。たぶんね。複数列ある場合のアドバンテージだとおもう。

コード表示

CREATE OR REPLACE TYPE liz_single IS TABLE OF VARCHAR2(10);
/

CREATE OR REPLACE FUNCTION rt_nobulk_coll_single
RETURN liz_single
AS rt liz_single;
BEGIN
    SELECT CAST(COLLECT(alp) AS liz_single) INTO rt
    FROM test___$___;
	RETURN rt;
END;
/

テーブルからカーサ取得

雰囲気とイメージとフィーリングで見出しつけてます

コード表示

SELECT collect(alp) AS liz FROM test___$___;
SELECT collect(rn) AS liz FROM test___$___;

実行例

コード表示

AINE@pdb1> SELECT collect(alp) AS liz FROM test___$___;

LIZ
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ST000016AdvYGuV4fgUwPIqMDcQg=('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z')

1 row selected.

Elapsed: 00:00:00.01

AINE@pdb1> SELECT collect(rn) AS liz FROM test___$___;

LIZ
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ST000016AdvYF0V4fgUwPIqMDcQg=(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26)

1 row selected.

Elapsed: 00:00:00.00

テーブルからオブジェクト作って取得

雰囲気とイメージとフィーリングで見出しつけてます

コード表示

SELECT liz_single(alp) AS liz FROM test___$___;

実行例

コード表示

AINE@pdb1> SELECT liz_single(alp) AS liz FROM test___$___;

LIZ
----------------------------------------------------------------------------------------------------
LIZ_SINGLE('A')
LIZ_SINGLE('B')
LIZ_SINGLE('C')
LIZ_SINGLE('D')
LIZ_SINGLE('E')
LIZ_SINGLE('F')
LIZ_SINGLE('G')
LIZ_SINGLE('H')
LIZ_SINGLE('I')
LIZ_SINGLE('J')
LIZ_SINGLE('K')
LIZ_SINGLE('L')
LIZ_SINGLE('M')
LIZ_SINGLE('N')
LIZ_SINGLE('O')
LIZ_SINGLE('P')
LIZ_SINGLE('Q')
LIZ_SINGLE('R')
LIZ_SINGLE('S')
LIZ_SINGLE('T')
LIZ_SINGLE('U')
LIZ_SINGLE('V')
LIZ_SINGLE('W')
LIZ_SINGLE('X')
LIZ_SINGLE('Y')
LIZ_SINGLE('Z')

26 rows selected.

Elapsed: 00:00:00.00

テーブルからオブジェクト作ってコレクションにキュッと詰めて取得

雰囲気とイメージとフィーリングで見出しつけてます

コード表示

SELECT CAST(COLLECT(alp) AS liz_single) as liz FROM test___$___;

実行例

コード表示

AINE@pdb1> col liz for a170
AINE@pdb1> SELECT CAST(COLLECT(alp) AS liz_single) AS liz FROM test___$___;

LIZ
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
LIZ_SINGLE('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z')

1 row selected.

Elapsed: 00:00:00.00

テーブルからオブジェクト作ってコレクションにキュッと詰めたあと、テーブルに変換して返却

雰囲気とイメージとフィーリングで見出しつけてます

コード表示

SELECT COLUMN_VALUE as liz FROM TABLE(SELECT CAST(COLLECT(alp) AS liz_single) FROM test___$___);

実行例

コード表示

AINE@pdb1> col liz for a10
AINE@pdb1> SELECT COLUMN_VALUE as liz FROM TABLE(SELECT CAST(COLLECT(alp) AS liz_single) FROM test___$___);

LIZ
----------
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
T
U
V
W
X
Y
Z

26 rows selected.

Elapsed: 00:00:00.01

コレクションを返すファンクションの戻り値をテーブルに変換して取得

雰囲気とイメージとフィーリングで見出しつけてます

コード表示

SELECT COLUMN_VALUE as liz FROM TABLE(rt_nobulk_coll_single());

実行例

コード表示

AINE@pdb1> SELECT COLUMN_VALUE as liz FROM TABLE(rt_nobulk_coll_single());

LIZ
----------
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
T
U
V
W
X
Y
Z

26 rows selected.

Elapsed: 00:00:00.00

collection型 複数

複数列はbulk collectいいかんじ。

コード表示

CREATE OR REPLACE TYPE liz IS OBJECT (rn NUMBER, alp VARCHAR2(10));
/

CREATE OR REPLACE TYPE liz_multi IS TABLE OF liz;
/

CREATE OR REPLACE FUNCTION rt_bulk_coll_multi
RETURN liz_multi
AS rt liz_multi;
BEGIN
    -- テーブルからBULK COLLECTでコレクションに代入
    SELECT liz(rn, alp) BULK COLLECT INTO rt
    FROM test___$___;
	RETURN rt;
END;
/

テーブルからカーサ取得

雰囲気とイメージとフィーリングで見出しつけてます

コード表示

SELECT collect(rn,alp) AS liz FROM test___$___;
SELECT collect(liz(rn,alp)) AS liz FROM test___$___;
SELECT cast(collect(liz(rn,alp)) as liz_multi)  AS liz FROM test___$___;

実行例

コード表示

AINE@pdb1> SELECT collect(rn,alp) AS liz FROM test___$___;
SELECT collect(rn,alp) AS liz FROM test___$___
       *
ERROR at line 1:
ORA-06553: PLS-306: wrong number or types of arguments in call to 'SYS_NT_COLLECT'


Elapsed: 00:00:00.01
AINE@pdb1> SELECT collect(liz(rn,alp)) AS liz FROM test___$___;

LIZ(RN, ALP)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
LIZ_MULTI(LIZ(1, 'A'), LIZ(2, 'B'), LIZ(3, 'C'), LIZ(4, 'D'), LIZ(5, 'E'), LIZ(6, 'F'), LIZ(7, 'G'), LIZ(8, 'H'), LIZ(9, 'I'), LIZ(10, 'J'), LIZ(11, 'K'), LIZ(12, 'L'), L
IZ(13, 'M'), LIZ(14, 'N'), LIZ(15, 'O'), LIZ(16, 'P'), LIZ(17, 'Q'), LIZ(18, 'R'), LIZ(19, 'S'), LIZ(20, 'T'), LIZ(21, 'U'), LIZ(22, 'V'), LIZ(23, 'W'), LIZ(24, 'X'), LIZ
(25, 'Y'), LIZ(26, 'Z'))


1 row selected.

Elapsed: 00:00:00.00
AINE@pdb1> SELECT cast(collect(liz(rn,alp)) as liz_multi)  AS liz FROM test___$___;

LIZ(RN, ALP)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
LIZ_MULTI(LIZ(1, 'A'), LIZ(2, 'B'), LIZ(3, 'C'), LIZ(4, 'D'), LIZ(5, 'E'), LIZ(6, 'F'), LIZ(7, 'G'), LIZ(8, 'H'), LIZ(9, 'I'), LIZ(10, 'J'), LIZ(11, 'K'), LIZ(12, 'L'), L
IZ(13, 'M'), LIZ(14, 'N'), LIZ(15, 'O'), LIZ(16, 'P'), LIZ(17, 'Q'), LIZ(18, 'R'), LIZ(19, 'S'), LIZ(20, 'T'), LIZ(21, 'U'), LIZ(22, 'V'), LIZ(23, 'W'), LIZ(24, 'X'), LIZ
(25, 'Y'), LIZ(26, 'Z'))


1 row selected.

Elapsed: 00:00:00.01

テーブルからオブジェクト作って取得

雰囲気とイメージとフィーリングで見出しつけてます

コード表示

SELECT liz(rn, alp) AS liz FROM test___$___;

実行例

コード表示

AINE@pdb1> col liz for a100
AINE@pdb1> SELECT liz(rn, alp) AS liz FROM test___$___;

LIZ(RN, ALP)
----------------------------------------------------------------------------------------------------
LIZ(1, 'A')
LIZ(2, 'B')
LIZ(3, 'C')
LIZ(4, 'D')
LIZ(5, 'E')
LIZ(6, 'F')
LIZ(7, 'G')
LIZ(8, 'H')
LIZ(9, 'I')
LIZ(10, 'J')
LIZ(11, 'K')
LIZ(12, 'L')
LIZ(13, 'M')
LIZ(14, 'N')
LIZ(15, 'O')
LIZ(16, 'P')
LIZ(17, 'Q')
LIZ(18, 'R')
LIZ(19, 'S')
LIZ(20, 'T')
LIZ(21, 'U')
LIZ(22, 'V')
LIZ(23, 'W')
LIZ(24, 'X')
LIZ(25, 'Y')
LIZ(26, 'Z')

26 rows selected.

Elapsed: 00:00:00.01

テーブルからオブジェクト作ってコレクションにキュッと詰めて取得

雰囲気とイメージとフィーリングで見出しつけてます

コード表示

SELECT CAST(COLLECT(liz(rn, alp)) AS liz_multi) AS liz FROM test___$___;

実行例

コード表示

AINE@pdb1> col liz for a170
AINE@pdb1> SELECT CAST(COLLECT(liz(rn, alp)) AS liz_multi) AS liz FROM test___$___;

LIZ(RN, ALP)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
LIZ_MULTI(LIZ(1, 'A'), LIZ(2, 'B'), LIZ(3, 'C'), LIZ(4, 'D'), LIZ(5, 'E'), LIZ(6, 'F'), LIZ(7, 'G'), LIZ(8, 'H'), LIZ(9, 'I'), LIZ(10, 'J'), LIZ(11, 'K'), LIZ(12, 'L'), L
IZ(13, 'M'), LIZ(14, 'N'), LIZ(15, 'O'), LIZ(16, 'P'), LIZ(17, 'Q'), LIZ(18, 'R'), LIZ(19, 'S'), LIZ(20, 'T'), LIZ(21, 'U'), LIZ(22, 'V'), LIZ(23, 'W'), LIZ(24, 'X'), LIZ
(25, 'Y'), LIZ(26, 'Z'))


1 row selected.

Elapsed: 00:00:00.00

テーブルからオブジェクト作ってコレクションにキュッと詰めたあと、テーブルに変換して返却

雰囲気とイメージとフィーリングで見出しつけてます

コード表示

SELECT * FROM TABLE(SELECT CAST(COLLECT(liz(rn,alp)) AS liz_multi) FROM test___$___);

実行例

コード表示

AINE@pdb1> SELECT * FROM TABLE(SELECT CAST(COLLECT(liz(rn,alp)) AS liz_multi) FROM test___$___);

           RN ALP
------------- ----------
            1 A
            2 B
            3 C
            4 D
            5 E
            6 F
            7 G
            8 H
            9 I
           10 J
           11 K
           12 L
           13 M
           14 N
           15 O
           16 P
           17 Q
           18 R
           19 S
           20 T
           21 U
           22 V
           23 W
           24 X
           25 Y
           26 Z

26 rows selected.

Elapsed: 00:00:00.00

コレクションを返すファンクションの戻り値をテーブルに変換して取得

雰囲気とイメージとフィーリングで見出しつけてます

コード表示

SELECT * FROM TABLE(rt_bulk_coll_multi());

実行例

コード表示

AINE@pdb1> SELECT * FROM TABLE(rt_bulk_coll_multi());

           RN ALP
------------- ----------
            1 A
            2 B
            3 C
            4 D
            5 E
            6 F
            7 G
            8 H
            9 I
           10 J
           11 K
           12 L
           13 M
           14 N
           15 O
           16 P
           17 Q
           18 R
           19 S
           20 T
           21 U
           22 V
           23 W
           24 X
           25 Y
           26 Z

26 rows selected.

Elapsed: 00:00:00.01

あとがき

基本的にオブジェクトタイプとコレクションタイプ知ってればいいと思う。(きっと困んないはず。)オブジェクトタイプdropするときは参照先からdrop。childからdrop。

collection型 multiset演算子 単一と複数

まえがき

collection使いこなせるようになるための練習。表現の幅を増やすため。multiset演算子の動きを確かめる

collection型 単一

単一カラムのみをコレクションにするだけなら、オブジェクト型の宣言は不要。

コード表示

create or replace type liz_single as table of number;
/

WITH src_a AS(
SELECT LEVEL AS rn,LEVEL * 5 AS qty FROM dual WHERE MOD(LEVEL,2) = 0 OR MOD(LEVEL,3) = 0 CONNECT BY LEVEL <= 10
),src_b AS (
SELECT LEVEL AS rn,LEVEL * 5 AS qty FROM dual WHERE MOD(LEVEL,3) = 0 OR MOD(LEVEL,5) = 0 CONNECT BY LEVEL <= 10
)
,sub AS (
SELECT
    A
    ,b
    ,A MULTISET UNION b AS a_union_b
    ,b MULTISET UNION A AS b_union_a
    ,A MULTISET UNION ALL b AS a_union_all_b
    ,b MULTISET UNION ALL A AS b_union_all_a
    ,A MULTISET UNION DISTINCT b AS a_union_distinct_b
    ,b MULTISET UNION DISTINCT A AS b_union_distinct_a
    ,A MULTISET INTERSECT b AS a_intersect_b
    ,b MULTISET INTERSECT A AS b_intersect_a
    ,A MULTISET INTERSECT DISTINCT b AS a_intersect_distinct_b
    ,b MULTISET INTERSECT DISTINCT A AS b_intersect_distinct_a
    ,A MULTISET EXCEPT b AS a_except_b
    ,b MULTISET EXCEPT A AS b_except_a
    ,A MULTISET EXCEPT DISTINCT b AS a_except_distinct_b
    ,b MULTISET EXCEPT DISTINCT A AS b_except_distinct_a
FROM
(SELECT CAST(COLLECT(rn) AS liz_single) AS A FROM src_a) 
    CROSS JOIN (SELECT CAST(COLLECT(rn) AS liz_single) AS b FROM src_b)
)
SELECT
    *
FROM
    sub
    UNPIVOT(cols FOR sts IN (A, b, a_union_b, b_union_a, a_union_all_b, b_union_all_a, a_union_distinct_b, b_union_distinct_a, a_intersect_b, b_intersect_a, a_intersect_distinct_b, b_intersect_distinct_a, a_except_b, b_except_a, a_except_distinct_b, b_except_distinct_a))
;


実行例

コード表示

AINE@pdb1> col sts for a50
AINE@pdb1> col cols for a100
AINE@pdb1> WITH src_a AS( SELECT LEVEL AS rn,LEVEL * 5 AS qty FROM dual WHERE MOD(LEVEL,2) = 0 OR MOD(LEVEL,3) = 0 CONNECT BY LEVEL <= 10 ),src_b AS ( SELECT LEVEL AS rn,LEVEL * 5 AS qty FROM dual WHERE MOD(LEVEL,3) = 0 OR MOD(LEVEL,5) = 0 CONNECT BY LEVEL <= 10 ) ,sub AS ( SELECT A ,b ,A MULTISET UNION b AS a_union_b ,b MULTISET UNION A AS b_union_a ,A MULTISET UNION ALL b AS a_union_all_b ,b MULTISET UNION ALL A AS b_union_all_a ,A MULTISET UNION DISTINCT b AS a_union_distinct_b ,b MULTISET UNION DISTINCT A AS b_union_distinct_a ,A MULTISET INTERSECT b AS a_intersect_b ,b MULTISET INTERSECT A AS b_intersect_a ,A MULTISET INTERSECT DISTINCT b AS a_intersect_distinct_b ,b MULTISET INTERSECT DISTINCT A AS b_intersect_distinct_a ,A MULTISET EXCEPT b AS a_except_b ,b MULTISET EXCEPT A AS b_except_a ,A MULTISET EXCEPT DISTINCT b AS a_except_distinct_b ,b MULTISET EXCEPT DISTINCT A AS b_except_distinct_a FROM (SELECT CAST(COLLECT(rn) AS liz_single) AS A FROM src_a) CROSS JOIN (SELECT CAST(COLLECT(rn) AS liz_single) AS b FROM src_b) ) SELECT * FROM sub UNPIVOT(cols FOR sts IN (A, b, a_union_b, b_union_a, a_union_all_b, b_union_all_a, a_union_distinct_b, b_union_distinct_a, a_intersect_b, b_intersect_a, a_intersect_distinct_b, b_intersect_distinct_a, a_except_b, b_except_a, a_except_distinct_b, b_except_distinct_a)) ;

STS                                                COLS
-------------------------------------------------- ----------------------------------------------------------------------------------------------------
A                                                  LIZ_SINGLE(2, 3, 4, 6, 8, 9, 10)
B                                                  LIZ_SINGLE(3, 5, 6, 9, 10)
A_UNION_B                                          LIZ_SINGLE(2, 3, 4, 6, 8, 9, 10, 3, 5, 6, 9, 10)
B_UNION_A                                          LIZ_SINGLE(3, 5, 6, 9, 10, 2, 3, 4, 6, 8, 9, 10)
A_UNION_ALL_B                                      LIZ_SINGLE(2, 3, 4, 6, 8, 9, 10, 3, 5, 6, 9, 10)
B_UNION_ALL_A                                      LIZ_SINGLE(3, 5, 6, 9, 10, 2, 3, 4, 6, 8, 9, 10)
A_UNION_DISTINCT_B                                 LIZ_SINGLE(2, 3, 4, 6, 8, 9, 10, 5)
B_UNION_DISTINCT_A                                 LIZ_SINGLE(3, 5, 6, 9, 10, 2, 4, 8)
A_INTERSECT_B                                      LIZ_SINGLE(3, 6, 9, 10)
B_INTERSECT_A                                      LIZ_SINGLE(3, 6, 9, 10)
A_INTERSECT_DISTINCT_B                             LIZ_SINGLE(3, 6, 9, 10)
B_INTERSECT_DISTINCT_A                             LIZ_SINGLE(3, 6, 9, 10)
A_EXCEPT_B                                         LIZ_SINGLE(2, 4, 8)
B_EXCEPT_A                                         LIZ_SINGLE(5)
A_EXCEPT_DISTINCT_B                                LIZ_SINGLE(2, 4, 8)
B_EXCEPT_DISTINCT_A                                LIZ_SINGLE(5)

16 rows selected.

Elapsed: 00:00:00.00

collection型 複数

複数カラムをコレクションにするなら、オブジェクト型の宣言は必要。

コード表示

create or replace type liz_typ is object (id number,qty number);
/
create or replace type liz_multi is table of liz_typ;
/

WITH src_a AS(
SELECT LEVEL AS rn,LEVEL * 5 AS qty FROM dual WHERE MOD(LEVEL,2) = 0 OR MOD(LEVEL,3) = 0 CONNECT BY LEVEL <= 10
),src_b AS (
SELECT LEVEL AS rn,LEVEL * 5 AS qty FROM dual WHERE MOD(LEVEL,3) = 0 OR MOD(LEVEL,5) = 0 CONNECT BY LEVEL <= 10
)
,sub AS (
SELECT
    A
    ,b
    ,A MULTISET UNION b AS a_union_b
    ,b MULTISET UNION A AS b_union_a
    ,A MULTISET UNION ALL b AS a_union_all_b
    ,b MULTISET UNION ALL A AS b_union_all_a
    ,A MULTISET UNION DISTINCT b AS a_union_distinct_b
    ,b MULTISET UNION DISTINCT A AS b_union_distinct_a
    ,A MULTISET INTERSECT b AS a_intersect_b
    ,b MULTISET INTERSECT A AS b_intersect_a
    ,A MULTISET INTERSECT DISTINCT b AS a_intersect_distinct_b
    ,b MULTISET INTERSECT DISTINCT A AS b_intersect_distinct_a
    ,A MULTISET EXCEPT b AS a_except_b
    ,b MULTISET EXCEPT A AS b_except_a
    ,A MULTISET EXCEPT DISTINCT b AS a_except_distinct_b
    ,b MULTISET EXCEPT DISTINCT A AS b_except_distinct_a
FROM
(SELECT CAST(COLLECT(liz_typ(rn,qty)) AS liz_multi) AS A FROM src_a) 
    CROSS JOIN (SELECT CAST(COLLECT(liz_typ(rn,qty)) AS liz_multi) AS b FROM src_b)
)
SELECT
    *
FROM
    sub
    UNPIVOT(cols FOR sts IN (A, b, a_union_b, b_union_a, a_union_all_b, b_union_all_a, a_union_distinct_b, b_union_distinct_a, a_intersect_b, b_intersect_a, a_intersect_distinct_b, b_intersect_distinct_a, a_except_b, b_except_a, a_except_distinct_b, b_except_distinct_a))
;

実行例

コード表示

AINE@pdb1> col sts for a25
AINE@pdb1> col cols for a145
AINE@pdb1> WITH src_a AS( SELECT LEVEL AS rn,LEVEL * 5 AS qty FROM dual WHERE MOD(LEVEL,2) = 0 OR MOD(LEVEL,3) = 0 CONNECT BY LEVEL <= 10 ),src_b AS ( SELECT LEVEL AS rn,LEVEL * 5 AS qty FROM dual WHERE MOD(LEVEL,3) = 0 OR MOD(LEVEL,5) = 0 CONNECT BY LEVEL <= 10 ) ,sub AS ( SELECT A ,b ,A MULTISET UNION b AS a_union_b ,b MULTISET UNION A AS b_union_a ,A MULTISET UNION ALL b AS a_union_all_b ,b MULTISET UNION ALL A AS b_union_all_a ,A MULTISET UNION DISTINCT b AS a_union_distinct_b ,b MULTISET UNION DISTINCT A AS b_union_distinct_a ,A MULTISET INTERSECT b AS a_intersect_b ,b MULTISET INTERSECT A AS b_intersect_a ,A MULTISET INTERSECT DISTINCT b AS a_intersect_distinct_b ,b MULTISET INTERSECT DISTINCT A AS b_intersect_distinct_a ,A MULTISET EXCEPT b AS a_except_b ,b MULTISET EXCEPT A AS b_except_a ,A MULTISET EXCEPT DISTINCT b AS a_except_distinct_b ,b MULTISET EXCEPT DISTINCT A AS b_except_distinct_a FROM (SELECT CAST(COLLECT(liz_typ(rn,qty)) AS liz_multi) AS A FROM src_a) CROSS JOIN (SELECT CAST(COLLECT(liz_typ(rn,qty)) AS liz_multi) AS b FROM src_b) ) SELECT * FROM sub UNPIVOT(cols FOR sts IN (A, b, a_union_b, b_union_a, a_union_all_b, b_union_all_a, a_union_distinct_b, b_union_distinct_a, a_intersect_b, b_intersect_a, a_intersect_distinct_b, b_intersect_distinct_a, a_except_b, b_except_a, a_except_distinct_b, b_except_distinct_a)) ;

STS                       COLS(ID, QTY)
------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------
A                         LIZ_MULTI(LIZ_TYP(2, 10), LIZ_TYP(3, 15), LIZ_TYP(4, 20), LIZ_TYP(6, 30), LIZ_TYP(8, 40), LIZ_TYP(9, 45), LIZ_TYP(10, 50))
B                         LIZ_MULTI(LIZ_TYP(3, 15), LIZ_TYP(5, 25), LIZ_TYP(6, 30), LIZ_TYP(9, 45), LIZ_TYP(10, 50))
A_UNION_B                 LIZ_MULTI(LIZ_TYP(2, 10), LIZ_TYP(3, 15), LIZ_TYP(4, 20), LIZ_TYP(6, 30), LIZ_TYP(8, 40), LIZ_TYP(9, 45), LIZ_TYP(10, 50), LIZ_TYP(3, 15), LIZ_TY
                          P(5, 25), LIZ_TYP(6, 30), LIZ_TYP(9, 45), LIZ_TYP(10, 50))

B_UNION_A                 LIZ_MULTI(LIZ_TYP(3, 15), LIZ_TYP(5, 25), LIZ_TYP(6, 30), LIZ_TYP(9, 45), LIZ_TYP(10, 50), LIZ_TYP(2, 10), LIZ_TYP(3, 15), LIZ_TYP(4, 20), LIZ_TY
                          P(6, 30), LIZ_TYP(8, 40), LIZ_TYP(9, 45), LIZ_TYP(10, 50))

A_UNION_ALL_B             LIZ_MULTI(LIZ_TYP(2, 10), LIZ_TYP(3, 15), LIZ_TYP(4, 20), LIZ_TYP(6, 30), LIZ_TYP(8, 40), LIZ_TYP(9, 45), LIZ_TYP(10, 50), LIZ_TYP(3, 15), LIZ_TY
                          P(5, 25), LIZ_TYP(6, 30), LIZ_TYP(9, 45), LIZ_TYP(10, 50))

B_UNION_ALL_A             LIZ_MULTI(LIZ_TYP(3, 15), LIZ_TYP(5, 25), LIZ_TYP(6, 30), LIZ_TYP(9, 45), LIZ_TYP(10, 50), LIZ_TYP(2, 10), LIZ_TYP(3, 15), LIZ_TYP(4, 20), LIZ_TY
                          P(6, 30), LIZ_TYP(8, 40), LIZ_TYP(9, 45), LIZ_TYP(10, 50))

A_UNION_DISTINCT_B        LIZ_MULTI(LIZ_TYP(2, 10), LIZ_TYP(3, 15), LIZ_TYP(4, 20), LIZ_TYP(6, 30), LIZ_TYP(8, 40), LIZ_TYP(9, 45), LIZ_TYP(10, 50), LIZ_TYP(5, 25))
B_UNION_DISTINCT_A        LIZ_MULTI(LIZ_TYP(3, 15), LIZ_TYP(5, 25), LIZ_TYP(6, 30), LIZ_TYP(9, 45), LIZ_TYP(10, 50), LIZ_TYP(2, 10), LIZ_TYP(4, 20), LIZ_TYP(8, 40))
A_INTERSECT_B             LIZ_MULTI(LIZ_TYP(3, 15), LIZ_TYP(6, 30), LIZ_TYP(9, 45), LIZ_TYP(10, 50))
B_INTERSECT_A             LIZ_MULTI(LIZ_TYP(3, 15), LIZ_TYP(6, 30), LIZ_TYP(9, 45), LIZ_TYP(10, 50))
A_INTERSECT_DISTINCT_B    LIZ_MULTI(LIZ_TYP(3, 15), LIZ_TYP(6, 30), LIZ_TYP(9, 45), LIZ_TYP(10, 50))
B_INTERSECT_DISTINCT_A    LIZ_MULTI(LIZ_TYP(3, 15), LIZ_TYP(6, 30), LIZ_TYP(9, 45), LIZ_TYP(10, 50))
A_EXCEPT_B                LIZ_MULTI(LIZ_TYP(2, 10), LIZ_TYP(4, 20), LIZ_TYP(8, 40))
B_EXCEPT_A                LIZ_MULTI(LIZ_TYP(5, 25))
A_EXCEPT_DISTINCT_B       LIZ_MULTI(LIZ_TYP(2, 10), LIZ_TYP(4, 20), LIZ_TYP(8, 40))
B_EXCEPT_DISTINCT_A       LIZ_MULTI(LIZ_TYP(5, 25))

16 rows selected.

Elapsed: 00:00:00.01