参考:http://czmmiao.iteye.com/blog/2142705
如下的例子,将拼接好的sql放入文件444.txt中
--dba授权
grant create any directory to SCOTT;
--在SCOTT创建目录
create or replace directory clob_dir as 'F:\b_lob_dir';
declare
--所有属性
v_sql varchar2(500);
type v_tab_type is table of varchar2(32767);
v_tab v_tab_type;
v_lfile utl_file.file_type;
v_dir varchar2(100) := 'CLOB_DIR';
begin
dbms_output.enable(null);
select attr bulk collect into v_tab from attr_temp ;
v_lfile := utl_file.fopen(location => v_dir,
filename => '444.txt',
open_mode => 'w');
--dbms_output.put_line(v_tab.count);
for x in 1.. v_tab.count loop
if x=v_tab.count then
dbms_output.put_line(v_tab(x));
v_sql:='select regexp_substr(aa.包含属性,'''||v_tab(x)||''') 属性,sum(aa.成本) 总成本 from prod_temp2 aa
where regexp_substr(aa.包含属性,'''||v_tab(x)||''') is not null
group by regexp_substr(aa.包含属性,'''||v_tab(x)||''') ;';
else
v_sql:='select regexp_substr(aa.包含属性,'''||v_tab(x)||''') 属性,sum(aa.成本) 总成本 from prod_temp2 aa
where regexp_substr(aa.包含属性,'''||v_tab(x)||''') is not null
group by regexp_substr(aa.包含属性,'''||v_tab(x)||''')
union all
';
end if;
--写文件
utl_file.put(file => v_lfile,
buffer => v_sql);
end loop;
utl_file.fflush(v_lfile);
utl_file.fclose(v_lfile);
end;
/
或者你也可以直接在控制台输出拼好的sql:
这种对于小数量没有问题,对于大数据的话,可能会打印缺失数据
declare
--所有属性
v_sql varchar2(500);
--用clob是为了防止sql数据量过大
v_colb_sql clob;
type v_tab_type is table of varchar2(4000);
v_tab v_tab_type;
v_count pls_integer:=0;
v_limit pls_integer:=32767;
begin
--初始化
dbms_output.enable(null);
dbms_lob.createtemporary(v_colb_sql,true,dbms_lob.call);
select attr bulk collect into v_tab from attr_temp ;
--dbms_output.put_line(v_tab.count);
for x in 1.. v_tab.count loop
if x=v_tab.count then
v_sql:='select regexp_substr(aa.包含属性,'''||v_tab(x)||''') 属性,sum(aa.成本) 总成本 from prod_temp2 aa
where regexp_substr(aa.包含属性,'''||v_tab(x)||''') is not null
group by regexp_substr(aa.包含属性,'''||v_tab(x)||''') ;';
else
v_sql:='select regexp_substr(aa.包含属性,'''||v_tab(x)||''') 属性,sum(aa.成本) 总成本 from prod_temp2 aa
where regexp_substr(aa.包含属性,'''||v_tab(x)||''') is not null
group by regexp_substr(aa.包含属性,'''||v_tab(x)||''')
union all
';
end if;
dbms_lob.writeappend(v_colb_sql,length(v_sql),v_sql);
end loop;
-- dbms_output.put_line(dbms_lob.getlength(v_colb_sql));
--向上取整
v_count:=ceil(dbms_lob.getlength(v_colb_sql)/v_limit);
for i in 1..v_count loop
--循环输出sql
dbms_output.put_line(dbms_lob.substr(v_colb_sql,v_limit,(i-1)*v_limit+1));
end loop;
--释放资源
dbms_lob.freetemporary(v_colb_sql);
end;
/