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的透视表。也就是如下的效果：

  1 CREATE   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 );
 25 END ;
 26 /
 27 create   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 ;
242 end  pkg_pivot;
243 /

此项的引用通告 URL 是：

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

引用此项的日志

转载于:https://www.cnblogs.com/guola/archive/2007/02/07/643802.html