Oracle中实现透视表 根据ERN的修改后的在Block_size 16K下我实现了56万行数据透视.

 

EXCEL的透视表是非常棒的一个功能，对于竖表转横表比较有用，我个人认为是除了那些公式计算以外EXCEL最吸引人的功能了。但是EXCEL有着65535行的限制，对于我们这些经常要为经营分析取数、做报表的人显然是远远不够的。Oracle中当然可以实现表的pivot，在Expert One on One Oracle中讲分析函数的一章中也有一个例子讲解如何Pivot，但是Tom的pivot是类似于完全的转换，将几列全部打散（具体请到asktom.oracle.com搜索“pivot”或者参考上述书籍）。而我们需要的是一个Oracle的透视表。也就是如下的效果：

  1CREATE OR REPLACE PACKAGE pkg_pivot
  2 AS
  3  /**//******************************************************************************
  4     NAME:       pkg_pivot
  5     PURPOSE:
  6  
  7     REVISIONS:
  8     Ver        Date        Author           Description
  9     ---------  ----------  ---------------  ------------------------------------
 10     1.0        2005-12-21  ERN           1. 创建包
 11     2.0        2005-12-22  ERN           2. 增加pivot_long过程，处理超过200列
 12                                                情况，但仍存在限制
 13  ******************************************************************************/
 14  TYPE refcursor IS REF CURSOR;
 15  TYPE ARRAY IS TABLE OF VARCHAR2(30) index by binary_integer;
 16  PROCEDURE pivot(p_tablename varchar2,
 17                  p_anchor    varchar2,
 18                  p_pivot     varchar2,
 19                  p_value     varchar2,
 20                  p_cursor    OUT refcursor);
 21  PROCEDURE pivot_long(p_tablename varchar2,
 22                       p_anchor    varchar2,
 23                       p_pivot     varchar2,
 24                       p_value     varchar2);
 25END;
 26/
 27create or replace package body pkg_pivot as
 28  procedure pivot(p_tablename varchar2, --表名，也可以输入查询
 29                  p_anchor    varchar2, --不变的列名，对于多个列可以用逗号分隔
 30                  p_pivot     varchar2, --将取值转换成列的列名
 31                  p_value     varchar2, --填充的值字段
 32                  p_cursor    out refcursor --返回结果集
 33                  ) as
 34    /**//******************************************************************************
 35       NAME:       pivot
 36       PURPOSE:    竖表转横表，此过程返回结果集，仅处理组合后32767字节以下的语句。
 37                   即大约可以处理200列的转换。
 38    
 39       REVISIONS:
 40       Ver        Date        Author           Description
 41       ---------  ----------  ---------------  ------------------------------------
 42       1.0        2005-12-21   ERN          1. 创建
 43    
 44    ******************************************************************************/
 45    ar_col        array; --存放转换后的列名
 46    n_cnt         number;
 47    l_query       varchar2(32766); --最终的执行语句
 48    l_query_pivot varchar2(200); --统计p_pivot转换后的列数与取值
 49  begin
 50    n_cnt         := 0;
 51    l_query       := 'select ' || p_anchor || ',';
 52    l_query_pivot := 'select distinct ' || p_pivot || ' from ' ||
 53                     p_tablename;
 54    open p_cursor for l_query_pivot;
 55  
 56    loop
 57      exit when p_cursor%NOTFOUND;
 58      n_cnt := n_cnt + 1;
 59      fetch p_cursor
 60        into ar_col(n_cnt);
 61    end loop;
 62    n_cnt := n_cnt - 1;
 63    close p_cursor;
 64  
 65    for i in 1 .. n_cnt - 1 loop
 66      l_query := l_query || 'max(val' || to_char(i) || ') "' || ar_col(i) || '",';
 67    end loop;
 68  
 69    l_query := l_query || 'max(val' || to_char(n_cnt) || ') "' ||
 70               ar_col(n_cnt) || '" ';
 71    l_query := l_query || 'from (select ';
 72  
 73    l_query := l_query || p_anchor || ', ';
 74  
 75    for i in 1 .. n_cnt - 1 loop
 76      l_query := l_query || 'decode(' || p_pivot || ',' || ar_col(i) ||
 77                 ',rn, null) rn' || to_char(i) || ',';
 78      l_query := l_query || 'decode(' || p_pivot || ',' || ar_col(i) || ',' ||
 79                 p_value || ',null) val' || to_char(i) || ',';
 80    
 81    end loop;
 82    l_query := l_query || 'decode(' || p_pivot || ',' || ar_col(n_cnt) ||
 83               ', rn, null) rn' || to_char(n_cnt) || ',';
 84    l_query := l_query || 'decode(' || p_pivot || ',' || ar_col(n_cnt) || ',' ||
 85               p_value || ',null) val' || to_char(n_cnt) || ' ';
 86  
 87    l_query := l_query || 'from (select ';
 88  
 89    l_query := l_query || p_anchor || ', ';
 90  
 91    l_query := l_query || p_pivot || ', ' || p_value ||
 92               ', row_number() over(partition by ';
 93  
 94    l_query := l_query || p_anchor || ', ';
 95  
 96    l_query := l_query || p_pivot || ' ';
 97    l_query := l_query || 'order by ' || p_value || ') rn from ' ||
 98               p_tablename || ') t) t group by ';
 99  
100    l_query := l_query || p_anchor || ' ';
101  
102    execute immediate 'alter session set cursor_sharing=force';
103  
104    open p_cursor for l_query;
105  
106    execute immediate 'alter session set cursor_sharing=exact';
107  
108  end;
109  procedure pivot_long(p_tablename varchar2,
110                       p_anchor    varchar2,
111                       p_pivot     varchar2,
112                       p_value     varchar2) as
113    /**//******************************************************************************
114       NAME:       pivot_long
115       PURPOSE:    竖表转横表，此过程建立_ext表，用于处理组合后查询语句超过32676
116                   字节的情况；对超长语句必须用dbms_sql进行解析、执行。但此处还受
117                   聚集过程中限制，对于过长的分组聚集语句会报ORA-01467，内部机制是
118                   受块大小影响，除非使用16K的大块，否则无法绕过此问题。
119    
120       REVISIONS:
121       Ver        Date        Author           Description
122       ---------  ----------  ---------------  ------------------------------------
123       1.0        2005-12-22   ERN          1. 创建
124    
125    ******************************************************************************/
126    ar_col        array;
127    n_cnt         number;
128    l_tmp         varchar2(3256);
129    p_cursor      refcursor;
130    l_query       dbms_sql.varchar2s;
131    n_ind         number;
132    n_left        number;
133    l_query_pivot varchar2(3200);
134    l_cursor      integer default dbms_sql.open_cursor;
135    n_result      number;
136  begin
137   -- l_query(1) :='1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890';
138    n_cnt := 0;
139  
140    l_query(1) := 'create table ' ||
141                  substr(p_tablename,
142                         instr(p_tablename, '.') + 1,
143                         instr(p_tablename, 'where') -
144                         instr(p_tablename, '.') - 2) || 'ext as ';
145  
146    n_ind := 2;
147    l_query(n_ind) := 'select ' || p_anchor || ',';
148    l_query_pivot := 'select distinct ' || p_pivot || ' from ' ||
149                     p_tablename;
150    open p_cursor for l_query_pivot;
151  
152    loop
153      exit when p_cursor%NOTFOUND;
154      n_cnt := n_cnt + 1;
155      fetch p_cursor
156        into ar_col(n_cnt);
157    end loop;
158    n_cnt := n_cnt - 1;
159    close p_cursor;
160  
161
162    n_ind := n_ind + 1;
163    l_query(n_ind) := '';
164    for i in 1 .. n_cnt - 1 loop
165      l_tmp := 'max(val' || to_char(i) || ') "' || ar_col(i) || '",';
166--      if NVL(length(l_query(n_ind)), 0) + length(l_tmp) <= 255 then
167      if NVL(length(l_query(n_ind)), 0) + length(l_tmp) <= 100 then
168--  gl      dbms_output.put_line(l_query(n_ind));
169--  gl      dbms_output.put_line(lengthb(l_query(n_ind)));
170        l_query(n_ind) := l_query(n_ind) || l_tmp;
171      else
172        n_left := 100 - length(l_query(n_ind)); 
173        l_query(n_ind) := l_query(n_ind) || substr(l_tmp, 1, n_left);
174        n_ind := n_ind + 1;
175        l_query(n_ind) := substr(l_tmp, n_left + 1);
176      end if;
177    end loop;
178    n_ind := n_ind + 1;
179    l_query(n_ind) := '';
180
181    l_query(n_ind) := 'max(val' || to_char(n_cnt) || ') "' || ar_col(n_cnt) || '" ';
182    l_query(n_ind) := l_query(n_ind) || 'from (select ';
183  
184    l_query(n_ind) := l_query(n_ind) || p_anchor || ', ';
185    n_ind := n_ind + 1;
186    l_query(n_ind) := '';
187    for i in 1 .. n_cnt - 1 loop
188      l_tmp := 'decode(' || p_pivot || ',''' || ar_col(i) || ''',rn, null) rn' ||
189               to_char(i) || ',';
190      l_tmp := l_tmp || 'decode(' || p_pivot || ',''' || ar_col(i) || ''',' ||
191               p_value || ',null) val' || to_char(i) || ',';
192      if NVL(length(l_query(n_ind)), 0) + length(l_tmp) <= 100 then
193        l_query(n_ind) := l_query(n_ind) || l_tmp;
194      else
195        n_left := 100 - length(l_query(n_ind)); 
196        l_query(n_ind) := l_query(n_ind) || substr(l_tmp, 1, n_left);
197        n_ind := n_ind + 1;
198        l_query(n_ind) := substr(l_tmp, n_left + 1);
199      end if;
200
201    end loop;
202    n_ind := n_ind + 1;
203    l_query(n_ind) := '';
204    l_query(n_ind) := 'decode(' || p_pivot || ',''' || ar_col(n_cnt) ||
205                      ''', rn, null) rn' || to_char(n_cnt) || ',';
206    l_query(n_ind) := l_query(n_ind) || 'decode(' || p_pivot || ',''' ||
207                      ar_col(n_cnt) || ''',' || p_value || ',null) val' ||
208                      to_char(n_cnt) || ' ';
209    n_ind := n_ind + 1;
210    l_query(n_ind) := '';
211    l_query(n_ind) := 'from (select ';
212  
213    l_query(n_ind) := l_query(n_ind) || p_anchor || ', ';
214  
215    l_query(n_ind) := l_query(n_ind) || p_pivot || ', ' || p_value ||
216                      ', row_number() over(partition by ';
217  
218    l_query(n_ind) := l_query(n_ind) || p_anchor || ', ';
219  
220    l_query(n_ind) := l_query(n_ind) || p_pivot || ' ';
221    n_ind := n_ind + 1;
222    l_query(n_ind) := '';
223    l_query(n_ind) := l_query(n_ind) || 'order by ' || p_value ||
224                      ') rn from ' || p_tablename || ') t) t group by ';
225  
226    l_query(n_ind) := l_query(n_ind) || p_anchor || ' ';
227    
228--    for i in 1..n_ind loop
229--    dbms_output.put_line(l_query(i));
230--    end loop;
231  
232    dbms_sql.parse(c             => l_cursor,
233                   statement     => l_query,
234                   lb            => l_query.first,
235                   ub            => l_query.last,
236                   lfflg         => false,
237                   language_flag => 1);
238  
239    n_result := dbms_sql.execute(c => l_cursor);
240    dbms_sql.close_cursor(c => l_cursor);
241  end;
242end pkg_pivot;
243/

此项的引用通告 URL 是：

http://yaoyp.spaces.live.com/blog/cns!ac5fd97b8a549660!534.trak

引用此项的日志