create or replace package body pkg_pivot as
procedure pivot(p_tablename varchar2, --表名，也可以输入查询
p_anchor varchar2, --不变的列名，对于多个列可以用逗号分隔
p_pivot varchar2, --将取值转换成列的列名
p_value varchar2, --填充的值字段
p_cursor out refcursor --返回结果集
) as
/**//******************************************************************************
NAME: pivot
PURPOSE: 竖表转横表，此过程返回结果集，仅处理组合后32767字节以下的语句。
即大约可以处理200列的转换。
REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 2005-12-21 ERN 1. 创建
******************************************************************************/
ar_col array; --存放转换后的列名
n_cnt number;
l_query varchar2(32766); --最终的执行语句
l_query_pivot varchar2(200); --统计p_pivot转换后的列数与取值
begin
n_cnt := 0;
l_query := 'select ' || p_anchor || ',';
l_query_pivot := 'select distinct ' || p_pivot || ' from ' ||
p_tablename;
open p_cursor for l_query_pivot;
loop
exit when p_cursor%NOTFOUND;
n_cnt := n_cnt + 1;
fetch p_cursor
into ar_col(n_cnt);
end loop;
n_cnt := n_cnt - 1;
close p_cursor;
for i in 1 .. n_cnt - 1 loop
l_query := l_query || 'max(val' || to_char(i) || ') "' || ar_col(i) || '",';
end loop;
l_query := l_query || 'max(val' || to_char(n_cnt) || ') "' ||
ar_col(n_cnt) || '" ';
l_query := l_query || 'from (select ';
l_query := l_query || p_anchor || ', ';
for i in 1 .. n_cnt - 1 loop
l_query := l_query || 'decode(' || p_pivot || ',' || ar_col(i) ||
',rn, null) rn' || to_char(i) || ',';
l_query := l_query || 'decode(' || p_pivot || ',' || ar_col(i) || ',' ||
p_value || ',null) val' || to_char(i) || ',';
end loop;
l_query := l_query || 'decode(' || p_pivot || ',' || ar_col(n_cnt) ||
', rn, null) rn' || to_char(n_cnt) || ',';
l_query := l_query || 'decode(' || p_pivot || ',' || ar_col(n_cnt) || ',' ||
p_value || ',null) val' || to_char(n_cnt) || ' ';
l_query := l_query || 'from (select ';
l_query := l_query || p_anchor || ', ';
l_query := l_query || p_pivot || ', ' || p_value ||
', row_number() over(partition by ';
l_query := l_query || p_anchor || ', ';
l_query := l_query || p_pivot || ' ';
l_query := l_query || 'order by ' || p_value || ') rn from ' ||
p_tablename || ') t) t group by ';
l_query := l_query || p_anchor || ' ';
execute immediate 'alter session set cursor_sharing=force';
open p_cursor for l_query;
execute immediate 'alter session set cursor_sharing=exact';
end;
procedure pivot_long(p_tablename varchar2,
p_anchor varchar2,
p_pivot varchar2,
p_value varchar2) as
/**//******************************************************************************
NAME: pivot_long
PURPOSE: 竖表转横表，此过程建立_ext表，用于处理组合后查询语句超过32676
字节的情况；对超长语句必须用dbms_sql进行解析、执行。但此处还受
聚集过程中限制，对于过长的分组聚集语句会报ORA-01467，内部机制是
受块大小影响，除非使用16K的大块，否则无法绕过此问题。
REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 2005-12-22 ERN 1. 创建
******************************************************************************/
ar_col array;
n_cnt number;
l_tmp varchar2(3256);
p_cursor refcursor;
l_query dbms_sql.varchar2s;
n_ind number;
n_left number;
l_query_pivot varchar2(3200);
l_cursor integer default dbms_sql.open_cursor;
n_result number;
begin
-- l_query(1) :='1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890';
n_cnt := 0;
l_query(1) := 'create table ' ||
substr(p_tablename,
instr(p_tablename, '.') + 1,
instr(p_tablename, 'where') -
instr(p_tablename, '.') - 2) || 'ext as ';
n_ind := 2;
l_query(n_ind) := 'select ' || p_anchor || ',';
l_query_pivot := 'select distinct ' || p_pivot || ' from ' ||
p_tablename;
open p_cursor for l_query_pivot;
loop
exit when p_cursor%NOTFOUND;
n_cnt := n_cnt + 1;
fetch p_cursor
into ar_col(n_cnt);
end loop;
n_cnt := n_cnt - 1;
close p_cursor;
n_ind := n_ind + 1;
l_query(n_ind) := '';
for i in 1 .. n_cnt - 1 loop
l_tmp := 'max(val' || to_char(i) || ') "' || ar_col(i) || '",';
-- if NVL(length(l_query(n_ind)), 0) + length(l_tmp) <= 255 then
if NVL(length(l_query(n_ind)), 0) + length(l_tmp) <= 100 then
-- gl dbms_output.put_line(l_query(n_ind));
-- gl dbms_output.put_line(lengthb(l_query(n_ind)));
l_query(n_ind) := l_query(n_ind) || l_tmp;
else
n_left := 100 - length(l_query(n_ind));
l_query(n_ind) := l_query(n_ind) || substr(l_tmp, 1, n_left);
n_ind := n_ind + 1;
l_query(n_ind) := substr(l_tmp, n_left + 1);
end if;
end loop;
n_ind := n_ind + 1;
l_query(n_ind) := '';
l_query(n_ind) := 'max(val' || to_char(n_cnt) || ') "' || ar_col(n_cnt) || '" ';
l_query(n_ind) := l_query(n_ind) || 'from (select ';
l_query(n_ind) := l_query(n_ind) || p_anchor || ', ';
n_ind := n_ind + 1;
l_query(n_ind) := '';
for i in 1 .. n_cnt - 1 loop
l_tmp := 'decode(' || p_pivot || ',''' || ar_col(i) || ''',rn, null) rn' ||
to_char(i) || ',';
l_tmp := l_tmp || 'decode(' || p_pivot || ',''' || ar_col(i) || ''',' ||
p_value || ',null) val' || to_char(i) || ',';
if NVL(length(l_query(n_ind)), 0) + length(l_tmp) <= 100 then
l_query(n_ind) := l_query(n_ind) || l_tmp;
else
n_left := 100 - length(l_query(n_ind));
l_query(n_ind) := l_query(n_ind) || substr(l_tmp, 1, n_left);
n_ind := n_ind + 1;
l_query(n_ind) := substr(l_tmp, n_left + 1);
end if;
end loop;
n_ind := n_ind + 1;
l_query(n_ind) := '';
l_query(n_ind) := 'decode(' || p_pivot || ',''' || ar_col(n_cnt) ||
''', rn, null) rn' || to_char(n_cnt) || ',';
l_query(n_ind) := l_query(n_ind) || 'decode(' || p_pivot || ',''' ||
ar_col(n_cnt) || ''',' || p_value || ',null) val' ||
to_char(n_cnt) || ' ';
n_ind := n_ind + 1;
l_query(n_ind) := '';
l_query(n_ind) := 'from (select ';
l_query(n_ind) := l_query(n_ind) || p_anchor || ', ';
l_query(n_ind) := l_query(n_ind) || p_pivot || ', ' || p_value ||
', row_number() over(partition by ';
l_query(n_ind) := l_query(n_ind) || p_anchor || ', ';
l_query(n_ind) := l_query(n_ind) || p_pivot || ' ';
n_ind := n_ind + 1;
l_query(n_ind) := '';
l_query(n_ind) := l_query(n_ind) || 'order by ' || p_value ||
') rn from ' || p_tablename || ') t) t group by ';
l_query(n_ind) := l_query(n_ind) || p_anchor || ' ';
-- for i in 1..n_ind loop
-- dbms_output.put_line(l_query(i));
-- end loop;
dbms_sql.parse(c => l_cursor,
statement => l_query,
lb => l_query.first,
ub => l_query.last,
lfflg => false,
language_flag => 1);
n_result := dbms_sql.execute(c => l_cursor);
dbms_sql.close_cursor(c => l_cursor);
end;
end pkg_pivot;
|