运用oracle分解一个用户多个套餐

xiobvcekqe

　　create or replace function f_table_change(v_table_name varchar2,v_key_col varchar2,v_chang_collist varchar2,v_chang_colcnt integer) return varchar2 is
/*
函数功能：根据输入表名及相关字段信息进行库表数据横向处理
输入参数: v_table_name（源数据表,格式username.table_name）
v_key_col(分组字段,实际要进行分组的字段名)
v_chang_collist（需要横向的字段串多个字段以","分割，最后一个字段无分隔符。例“字段1,字段2,字段3）可以为空
v_chang_colcnt（需要横向的字段数），可以为0
输出参数: Result结果表
执行方式：命令行执行
set serveroutput on;
exec dbms_output.put_line(gmcc.f_table_change('用户名.表名','需要分组的字段','需要横向的字段序列，格式：字段1,字段2,字段3',字段数（与横向字段数一致）));
*/
　　vv_username varchar2(32);
vv_tabname varchar2(32);
vv_keycol varchar2(32);
vv_changcollist varchar2(1024);
vv_changcol varchar2(32);
vv_changcolcnt integer;
vv_maxcolcnt integer; --允许横向的最大字段数
　　vv_flag integer;
vv_colcnt integer;
　　vv_tabtmp varchar2(32); --临时表
vv_tabrsl varchar2(32); --结果表
　　vv_ifexists_tab integer; --标识是否存在库表
vv_sql varchar2(4000);
vv_sql1 varchar2(4000);
vv_sql2 varchar2(4000);
vv_sql3 varchar2(4000);
vv_sql1_mid varchar2(4000);
　　tb varchar2(32);
vv_rncnt integer; --分组字段记录数标识
max_rncnt integer; --标识分组字段最多的记录数
　　Result varchar2(1024);
　　begin
　　/*初始化参数*/
vv_tabtmp := 'TMP_TABLE';
vv_tabrsl := 'RESULT_TABLE';
　　vv_username := substr(upper(trim(v_table_name)),1,instr(trim(v_table_name),'.',1,1)-1);
vv_tabname := substr(upper(trim(v_table_name)),instr(trim(v_table_name),'.',1,1)+1);
vv_keycol := upper(trim(v_key_col));
vv_changcollist := upper(trim(v_chang_collist));
vv_changcolcnt := v_chang_colcnt;
vv_flag := 0;
vv_maxcolcnt := 10;
　　/*输入参数核查*/
begin
/*检查是否存在输入用户名*/
execute immediate 'select count(*) from all_users where username = '''||vv_username||'''' into vv_flag;
if( vv_flag = 0 ) then
Result := '输入用户'||vv_username||'不存在，请重新输入。输入格式：username.table_name';
return(Result);
end if;
　　/*检查是否存在输入表*/
execute immediate 'select count(*) from all_tables where owner = '''||vv_username||''' and table_name = '''||vv_tabname||'''' into vv_flag;
if( vv_flag = 0 ) then
Result := '输入表'||vv_tabname||'不存在，请重新输入。输入格式：username.table_name';
return(Result);
end if;
　　/*检查是否存在输入分组字段名*/
execute immediate 'select count(*) from all_tab_columns where owner = '''||vv_username||''' and table_name = '''||vv_tabname||''' and column_name = '''||vv_keycol||'''' into vv_flag;
if( vv_flag = 0 ) then
Result := '输入分组字段'||vv_keycol||'不存在，请重新输入。';
return(Result);
end if;
　　/*检查是否存在输入横向字段名*/
if( vv_changcollist is not null and vv_changcolcnt > 0 and vv_changcolcnt <= vv_maxcolcnt ) then
vv_colcnt := 1;
while( vv_colcnt <= vv_changcolcnt ) loop
/*拆分横向字段串*/
if( vv_colcnt < vv_changcolcnt ) then
if( vv_colcnt = 1 ) then
vv_changcol := substr(upper(trim(vv_changcollist)),1,instr(trim(vv_changcollist),',',1,vv_colcnt)-1);
else
vv_changcol := substr(upper(trim(vv_changcollist)),instr(trim(vv_changcollist),',',1,vv_colcnt-1)+1,instr(trim(vv_changcollist),',',1,vv_colcnt)-instr(trim(vv_changcollist),',',1,vv_colcnt-1)-1);
end if;
else
/*最后一个字段时处理*/
if( vv_changcolcnt = 1 ) then
vv_changcol := substr(upper(trim(vv_changcollist)),1);
else
vv_changcol := substr(upper(trim(vv_changcollist)),instr(trim(vv_changcollist),',',1,vv_changcolcnt-1)+1);
end if;
end if;
　　execute immediate 'select count(*) from all_tab_columns where owner = '''||vv_username||''' and table_name = '''||vv_tabname||''' and column_name = '''||vv_changcol||'''' into vv_flag;
if( vv_flag = 0 ) then
Result := '输入横向字段'||vv_changcol||'不存在，请重新输入。输入格式：字段1,字段2,字段3';
return(Result);
end if;
　　vv_colcnt := vv_colcnt+1;
end loop;
else
if ( vv_changcolcnt > vv_maxcolcnt ) then
Result := '只允许输入不超过'||vv_maxcolcnt||'个横向字段，请重新输入。输入格式：字段1,字段2,字段3';
return(Result);
end if;
end if;
end;
　　/*数据表横向sql语句生成*/
begin
if( vv_changcollist is not null and vv_changcolcnt > 0 ) then
/*有横向字段*/
execute immediate 'select count(1) from all_tables where table_name = '''||vv_tabtmp||''' and owner = '''||vv_username||'''' into vv_ifexists_tab;
if ( vv_ifexists_tab > 0 ) then
execute immediate 'truncate table '||vv_username||'.'||vv_tabtmp;
execute immediate 'drop table '||vv_username||'.'||vv_tabtmp||' purge';
end if;
execute immediate 'create table '||vv_username||'.'||vv_tabtmp||' nologging as
select '||vv_keycol||','||vv_changcollist||',
row_number() over (partition by '||vv_keycol||' order by '||vv_keycol||') rn
from '||vv_username||'.'||vv_tabname
;

vv_rncnt := 1;
execute immediate 'select max(rn) from '||vv_username||'.'||vv_tabtmp into max_rncnt;
while ( vv_rncnt <= max_rncnt ) loop
tb := 'tb'||vv_rncnt;
if ( vv_sql1 is null and vv_sql2 is null ) then
vv_colcnt := 1;
vv_sql1_mid := null;
while( vv_colcnt <= vv_changcolcnt ) loop
/*拆分横向字段串*/
if( vv_colcnt < vv_changcolcnt ) then
if( vv_colcnt = 1 ) then
vv_changcol := substr(upper(trim(vv_changcollist)),1,instr(trim(vv_changcollist),',',1,vv_colcnt)-1);
else
vv_changcol := substr(upper(trim(vv_changcollist)),instr(trim(vv_changcollist),',',1,vv_colcnt-1)+1,instr(trim(vv_changcollist),',',1,vv_colcnt)-instr(trim(vv_changcollist),',',1,vv_colcnt-1)-1);
end if;
else
/*最后一个字段时处理*/
if( vv_changcolcnt = 1 ) then
vv_changcol := substr(upper(trim(vv_changcollist)),1);
else
vv_changcol := substr(upper(trim(vv_changcollist)),instr(trim(vv_changcollist),',',1,vv_changcolcnt-1)+1);
end if;
end if;

if( vv_sql1_mid is null ) then
vv_sql1_mid := tb||'.'||vv_changcol||' '||vv_changcol||vv_rncnt;
else
vv_sql1_mid := vv_sql1_mid||','||tb||'.'||vv_changcol||' '||vv_changcol||vv_rncnt;
end if;
vv_colcnt := vv_colcnt+1;
end loop;
vv_sql1 := 'select '||tb||'.'||vv_keycol||','||vv_sql1_mid;
vv_sql2 := 'from (select * from '||vv_username||'.'||vv_tabtmp||' where rn = '||vv_rncnt||') '||tb;
else
vv_colcnt := 1;
vv_sql1_mid := null;
while( vv_colcnt <= vv_changcolcnt ) loop
/*拆分横向字段串*/
if( vv_colcnt < vv_changcolcnt ) then
if( vv_colcnt = 1 ) then
vv_changcol := substr(upper(trim(vv_changcollist)),1,instr(trim(vv_changcollist),',',1,vv_colcnt)-1);
else
vv_changcol := substr(upper(trim(vv_changcollist)),instr(trim(vv_changcollist),',',1,vv_colcnt-1)+1,instr(trim(vv_changcollist),',',1,vv_colcnt)-instr(trim(vv_changcollist),',',1,vv_colcnt-1)-1);
end if;
else
/*最后一个字段时处理*/
if( vv_changcolcnt = 1 ) then
vv_changcol := substr(upper(trim(vv_changcollist)),1);
else
vv_changcol := substr(upper(trim(vv_changcollist)),instr(trim(vv_changcollist),',',1,vv_changcolcnt-1)+1);
end if;
end if;

if( vv_sql1_mid is null ) then
vv_sql1_mid := 'case when '||tb||'.'||vv_keycol||' is not null then '||tb||'.'||vv_changcol||' end '||vv_changcol||vv_rncnt;
else
vv_sql1_mid := vv_sql1_mid||',case when '||tb||'.'||vv_keycol||' is not null then '||tb||'.'||vv_changcol||' end '||vv_changcol||vv_rncnt;
end if;
vv_colcnt := vv_colcnt+1;
end loop;

vv_sql1 := vv_sql1||','||vv_sql1_mid;
vv_sql2 := vv_sql2||','||'(select * from '||vv_username||'.'||vv_tabtmp||' where rn = '||vv_rncnt||') '||tb;
if ( vv_sql3 is null ) then
vv_sql3 := 'where tb1.'||vv_keycol||' = '||tb||'.'||vv_keycol||'(+)';
else
vv_sql3 := vv_sql3||' and tb1.'||vv_keycol||' = '||tb||'.'||vv_keycol||'(+)';
end if;
end if;
vv_rncnt := vv_rncnt+1;
end loop;

if ( vv_sql1 is not null and vv_sql2 is not null ) then
vv_sql := 'create table '||vv_username||'.'||vv_tabrsl||' nologging as '||vv_sql1||' '||vv_sql2||' '||vv_sql3;
end if;
else
/*无横向字段，只去重处理*/
vv_sql := 'create table '||vv_username||'.'||vv_tabrsl||' nologging as select distinct '||vv_keycol||' from '||vv_username||'.'||vv_tabname;
end if;
end;

/*横向数据表生成*/
begin
--return(vv_sql);
execute immediate 'select count(1) from all_tables where table_name = '''||vv_tabrsl||''' and owner = '''||vv_username||'''' into vv_ifexists_tab;
if ( vv_ifexists_tab > 0 ) then
execute immediate 'truncate table '||vv_username||'.'||vv_tabrsl;
execute immediate 'drop table '||vv_username||'.'||vv_tabrsl||' purge';
end if;
　　execute immediate vv_sql;
execute immediate 'select count(1) from all_tables where table_name = '''||vv_tabtmp||''' and owner = '''||vv_username||'''' into vv_ifexists_tab;
if ( vv_ifexists_tab > 0 ) then
execute immediate 'truncate table '||vv_username||'.'||vv_tabtmp;
execute immediate 'drop table '||vv_username||'.'||vv_tabtmp||' purge';
end if;
end;
　　/*返回最终结果表*/
Result := '返回处理后结果表：'||vv_username||'.'||vv_tabrsl;
return(Result);
　　end f_table_change;