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![]()
/
CREATE OR REPLACE PACKAGE pkg_pivot2
AS3
/******************************************************************************
4
NAME: pkg_pivot5
PURPOSE:6
7
REVISIONS:8
Ver Date Author Description9
--------- ---------- --------------- ------------------------------------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 as28
procedure pivot(p_tablename varchar2, --表名，也可以输入查询29
p_anchor varchar2, --不变的列名，对于多个列可以用逗号分隔30
p_pivot varchar2, --将取值转换成列的列名31
p_value varchar2, --填充的值字段32
p_cursor out refcursor --返回结果集33
) as34
/******************************************************************************35
NAME: pivot36
PURPOSE: 竖表转横表，此过程返回结果集，仅处理组合后32767字节以下的语句。37
即大约可以处理200列的转换。38
39
REVISIONS:40
Ver Date Author Description41
--------- ---------- --------------- ------------------------------------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
begin50
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
loop57
exit when p_cursor%NOTFOUND;58
n_cnt := n_cnt + 1;59
fetch p_cursor60
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 loop66
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 loop76
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) as113
/******************************************************************************114
NAME: pivot_long115
PURPOSE: 竖表转横表，此过程建立_ext表，用于处理组合后查询语句超过32676116
字节的情况；对超长语句必须用dbms_sql进行解析、执行。但此处还受117
聚集过程中限制，对于过长的分组聚集语句会报ORA-01467，内部机制是118
受块大小影响，除非使用16K的大块，否则无法绕过此问题。119
120
REVISIONS:121
Ver Date Author Description122
--------- ---------- --------------- ------------------------------------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
begin137
-- 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
loop153
exit when p_cursor%NOTFOUND;154
n_cnt := n_cnt + 1;155
fetch p_cursor156
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 loop165
l_tmp := 'max(val' || to_char(i) || ') "' || ar_col(i) || '",';166
-- if NVL(length(l_query(n_ind)), 0) + length(l_tmp) <= 255 then167
if NVL(length(l_query(n_ind)), 0) + length(l_tmp) <= 100 then168
-- 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
else172
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 loop188
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 then193
l_query(n_ind) := l_query(n_ind) || l_tmp;194
else195
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 loop229
-- 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
引用此项的日志
浙公网安备 33010602011771号