问题描述
我们有一个包含db_name和用户名的表。在输出中，我们需要每个DB的用户列表，即列数将等于不同的db_name。
示例输出格式:
DB1 DB2
用户1用户4
用户2用户5
用户3
数据库版本:11g
以下是供参考的示例脚本:
请帮忙。
示例输出格式:
DB1 DB2
用户1用户4
用户2用户5
用户3
数据库版本:11g
以下是供参考的示例脚本:
CREATE TABLE app_users (
username VARCHAR2(40),
db_name VARCHAR2(40)
);
insert all
into app_users values ('USER1','DB1')
into app_users values ('USER2','DB1')
into app_users values ('USER3','DB1')
into app_users values ('USER4','DB2')
into app_users values ('USER5','DB2')
select * from dual;复制请帮忙。
专家解答
我们目前没有动态枢轴，但是AMIS的好人不久前写了一个不错的动态枢轴函数，详细信息如下:
https://technology.amis.nl/2006/05/24/dynamic-sql-pivoting-stealing-antons-thunder/
它可以用来实现你想要的。创建后，您只需要最后选择 :-)
https://technology.amis.nl/2006/05/24/dynamic-sql-pivoting-stealing-antons-thunder/
它可以用来实现你想要的。创建后，您只需要最后选择 :-)
SQL> CREATE OR REPLACE
2 type PivotImpl as object
3 (
4 ret_type anytype, -- The return type of the table function
5 stmt varchar2(32767),
6 fmt varchar2(32767),
7 cur integer,
8 static function ODCITableDescribe( rtype out anytype, p_stmt in varchar2, p_fmt in varchar2 := 'upper(@p@)', dummy in number := 0 )
9 return number,
10 static function ODCITablePrepare( sctx out PivotImpl, ti in sys.ODCITabFuncInfo, p_stmt in varchar2, p_fmt in varchar2 := 'upper(@p@)', dummy in number := 0 )
11 return number,
12 static function ODCITableStart( sctx in out PivotImpl, p_stmt in varchar2, p_fmt in varchar2 := 'upper(@p@)', dummy in number := 0 )
13 return number,
14 member function ODCITableFetch( self in out PivotImpl, nrows in number, outset out anydataset )
15 return number,
16 member function ODCITableClose( self in PivotImpl )
17 return number
18 )
19 /
Type created.
SQL>
SQL> create or replace type body PivotImpl as
2 static function ODCITableDescribe( rtype out anytype, p_stmt in varchar2, p_fmt in varchar2 := 'upper(@p@)', dummy in number )
3 return number
4 is
5 atyp anytype;
6 cur integer;
7 numcols number;
8 desc_tab dbms_sql.desc_tab2;
9 rc sys_refcursor;
10 t_c2 varchar2(32767);
11 t_fmt varchar2(1000);
12 begin
13 cur := dbms_sql.open_cursor;
14 dbms_sql.parse( cur, p_stmt, dbms_sql.native );
15 dbms_sql.describe_columns2( cur, numcols, desc_tab );
16 dbms_sql.close_cursor( cur );
17 --
18 anytype.begincreate( dbms_types.typecode_object, atyp );
19 for i in 1 .. numcols - 2
20 loop
21 atyp.addattr( desc_tab( i ).col_name
22 , case desc_tab( i ).col_type
23 when 1 then dbms_types.typecode_varchar2
24 when 2 then dbms_types.typecode_number
25 when 9 then dbms_types.typecode_varchar2
26 when 11 then dbms_types.typecode_varchar2 -- show rowid as varchar2
27 when 12 then dbms_types.typecode_date
28 when 208 then dbms_types.typecode_varchar2 -- show urowid as varchar2
29 when 96 then dbms_types.typecode_char
30 when 180 then dbms_types.typecode_timestamp
31 when 181 then dbms_types.typecode_timestamp_tz
32 when 231 then dbms_types.typecode_timestamp_ltz
33 when 182 then dbms_types.typecode_interval_ym
34 when 183 then dbms_types.typecode_interval_ds
35 end
36 , desc_tab( i ).col_precision
37 , desc_tab( i ).col_scale
38 , case desc_tab( i ).col_type
39 when 11 then 18 -- for rowid col_max_len = 16, and 18 characters are shown
40 else desc_tab( i ).col_max_len
41 end
42 , desc_tab( i ).col_charsetid
43 , desc_tab( i ).col_charsetform
44 );
45 end loop;
46 if instr( p_fmt, '@p@' ) > 0
47 then
48 t_fmt := p_fmt;
49 else
50 t_fmt := '@p@';
51 end if;
52 open rc for replace( 'select distinct ' || t_fmt || '
53 from( ' || p_stmt || ' )
54 order by ' || t_fmt
55 , '@p@'
56 , desc_tab( numcols - 1 ).col_name
57 );
58 loop
59 fetch rc into t_c2;
60 exit when rc%notfound;
61 atyp.addattr( t_c2
62 , case desc_tab( numcols ).col_type
63 when 1 then dbms_types.typecode_varchar2
64 when 2 then dbms_types.typecode_number
65 when 9 then dbms_types.typecode_varchar2
66 when 11 then dbms_types.typecode_varchar2 -- show rowid as varchar2
67 when 12 then dbms_types.typecode_date
68 when 208 then dbms_types.typecode_urowid
69 when 96 then dbms_types.typecode_char
70 when 180 then dbms_types.typecode_timestamp
71 when 181 then dbms_types.typecode_timestamp_tz
72 when 231 then dbms_types.typecode_timestamp_ltz
73 when 182 then dbms_types.typecode_interval_ym
74 when 183 then dbms_types.typecode_interval_ds
75 end
76 , desc_tab( numcols ).col_precision
77 , desc_tab( numcols ).col_scale
78 , case desc_tab( numcols ).col_type
79 when 11 then 18 -- for rowid col_max_len = 16, and 18 characters are shown
80 else desc_tab( numcols ).col_max_len
81 end
82 , desc_tab( numcols ).col_charsetid
83 , desc_tab( numcols ).col_charsetform
84 );
85 end loop;
86 close rc;
87 atyp.endcreate;
88 anytype.begincreate( dbms_types.typecode_table, rtype );
89 rtype.SetInfo( null, null, null, null, null, atyp, dbms_types.typecode_object, 0 );
90 rtype.endcreate();
91 return odciconst.success;
92 exception
93 when others then
94 return odciconst.error;
95 end;
96 --
97 static function ODCITablePrepare( sctx out PivotImpl, ti in sys.ODCITabFuncInfo, p_stmt in varchar2, p_fmt in varchar2 := 'upper(@p@)', dummy in number )
98 return number
99 is
100 prec pls_integer;
101 scale pls_integer;
102 len pls_integer;
103 csid pls_integer;
104 csfrm pls_integer;
105 elem_typ anytype;
106 aname varchar2(30);
107 tc pls_integer;
108 begin
109 tc := ti.RetType.GetAttrElemInfo( 1, prec, scale, len, csid, csfrm, elem_typ, aname );
110 --
111 if instr( p_fmt, '@p@' ) > 0
112 then
113 sctx := PivotImpl( elem_typ, p_stmt, p_fmt, null );
114 else
115 sctx := PivotImpl( elem_typ, p_stmt, '@p@', null );
116 end if;
117 return odciconst.success;
118 end;
119 --
120 static function ODCITableStart( sctx in out PivotImpl, p_stmt in varchar2, p_fmt in varchar2 := 'upper(@p@)', dummy in number )
121 return number
122 is
123 cur integer;
124 numcols number;
125 desc_tab dbms_sql.desc_tab2;
126 t_stmt varchar2(32767);
127 type_code pls_integer;
128 prec pls_integer;
129 scale pls_integer;
130 len pls_integer;
131 csid pls_integer;
132 csfrm pls_integer;
133 schema_name varchar2(30);
134 type_name varchar2(30);
135 version varchar2(30);
136 attr_count pls_integer;
137 attr_type anytype;
138 attr_name varchar2(100);
139 dummy2 integer;
140 begin
141 cur := dbms_sql.open_cursor;
142 dbms_sql.parse( cur, p_stmt, dbms_sql.native );
143 dbms_sql.describe_columns2( cur, numcols, desc_tab );
144 dbms_sql.close_cursor( cur );
145 --
146 for i in 1 .. numcols - 2
147 loop
148 t_stmt := t_stmt || ', "' || desc_tab( i ).col_name || '"';
149 end loop;
150 --
151 type_code := sctx.ret_type.getinfo( prec
152 , scale
153 , len
154 , csid
155 , csfrm
156 , schema_name
157 , type_name
158 , version
159 , attr_count
160 );
161 for i in numcols - 1 .. attr_count
162 loop
163 type_code := sctx.ret_type.getattreleminfo( i
164 , prec
165 , scale
166 , len
167 , csid
168 , csfrm
169 , attr_type
170 , attr_name
171 );
172 t_stmt := t_stmt || replace( ', max( decode( ' || sctx.fmt || ', ''' || attr_name || ''', ' || desc_tab( numcols ).col_name || ' ) )'
173 , '@p@'
174 , desc_tab( numcols - 1 ).col_name
175 );
176 end loop;
177 t_stmt := 'select ' || substr( t_stmt, 2 ) || ' from ( ' || sctx.stmt || ' )';
178 for i in 1 .. numcols - 2
179 loop
180 if i = 1
181 then
182 t_stmt := t_stmt || ' group by "' || desc_tab( i ).col_name || '"';
183 else
184 t_stmt := t_stmt || ', "' || desc_tab( i ).col_name || '"';
185 end if;
186 end loop;
187 --
188 dbms_output.put_line( t_stmt );
189 sctx.cur := dbms_sql.open_cursor;
190 dbms_sql.parse( sctx.cur, t_stmt, dbms_sql.native );
191 for i in 1 .. attr_count
192 loop
193 type_code := sctx.ret_type.getattreleminfo( i
194 , prec
195 , scale
196 , len
197 , csid
198 , csfrm
199 , attr_type
200 , attr_name
201 );
202 case type_code
203 when dbms_types.typecode_char then dbms_sql.define_column( sctx.cur, i, 'x', 32767 );
204 when dbms_types.typecode_varchar2 then dbms_sql.define_column( sctx.cur, i, 'x', 32767 );
205 when dbms_types.typecode_number then dbms_sql.define_column( sctx.cur, i, cast( null as number ) );
206 when dbms_types.typecode_date then dbms_sql.define_column( sctx.cur, i, cast( null as date ) );
207 when dbms_types.typecode_urowid then dbms_sql.define_column( sctx.cur, i, cast( null as urowid ) );
208 when dbms_types.typecode_timestamp then dbms_sql.define_column( sctx.cur, i, cast( null as timestamp ) );
209 when dbms_types.typecode_timestamp_tz then dbms_sql.define_column( sctx.cur, i, cast( null as timestamp with time zone ) );
210 when dbms_types.typecode_timestamp_ltz then dbms_sql.define_column( sctx.cur, i, cast( null as timestamp with local time zone ) );
211 when dbms_types.typecode_interval_ym then dbms_sql.define_column( sctx.cur, i, cast( null as interval year to month ) );
212 when dbms_types.typecode_interval_ds then dbms_sql.define_column( sctx.cur, i, cast( null as interval day to second ) );
213 end case;
214 end loop;
215 dummy2 := dbms_sql.execute( sctx.cur );
216 return odciconst.success;
217 end;
218 --
219 member function ODCITableFetch( self in out PivotImpl, nrows in number, outset out anydataset )
220 return number
221 is
222 c1_col_type pls_integer;
223 type_code pls_integer;
224 prec pls_integer;
225 scale pls_integer;
226 len pls_integer;
227 csid pls_integer;
228 csfrm pls_integer;
229 schema_name varchar2(30);
230 type_name varchar2(30);
231 version varchar2(30);
232 attr_count pls_integer;
233 attr_type anytype;
234 attr_name varchar2(100);
235 v1 varchar2(32767);
236 n1 number;
237 d1 date;
238 ur1 urowid;
239 ids1 interval day to second;
240 iym1 interval year to month;
241 ts1 timestamp;
242 tstz1 timestamp with time zone;
243 tsltz1 timestamp with local time zone;
244 begin
245 outset := null;
246 if nrows < 1
247 then
248 -- is this possible???
249 return odciconst.success;
250 end if;
251 --
252 dbms_output.put_line( 'fetch' );
253 if dbms_sql.fetch_rows( self.cur ) = 0
254 then
255 return odciconst.success;
256 end if;
257 --
258 dbms_output.put_line( 'done' );
259 type_code := self.ret_type.getinfo( prec
260 , scale
261 , len
262 , csid
263 , csfrm
264 , schema_name
265 , type_name
266 , version
267 , attr_count
268 );
269 anydataset.begincreate( dbms_types.typecode_object, self.ret_type, outset );
270 outset.addinstance;
271 outset.piecewise();
272 for i in 1 .. attr_count
273 loop
274 type_code := self.ret_type.getattreleminfo( i
275 , prec
276 , scale
277 , len
278 , csid
279 , csfrm
280 , attr_type
281 , attr_name
282 );
283 dbms_output.put_line( attr_name );
284 case type_code
285 when dbms_types.typecode_char then
286 dbms_sql.column_value( self.cur, i, v1 );
287 outset.setchar( v1 );
288 when dbms_types.typecode_varchar2 then
289 dbms_sql.column_value( self.cur, i, v1 );
290 outset.setvarchar2( v1 );
291 when dbms_types.typecode_number then
292 dbms_sql.column_value( self.cur, i, n1 );
293 outset.setnumber( n1 );
294 when dbms_types.typecode_date then
295 dbms_sql.column_value( self.cur, i, d1 );
296 outset.setdate( d1 );
297 when dbms_types.typecode_urowid then
298 dbms_sql.column_value( self.cur, i, ur1 );
299 outset.seturowid( ur1 );
300 when dbms_types.typecode_interval_ds then
301 dbms_sql.column_value( self.cur, i, ids1 );
302
303 outset.setintervalds( ids1 );
304 when dbms_types.typecode_interval_ym then
305 dbms_sql.column_value( self.cur, i, iym1 );
306 outset.setintervalym( iym1 );
307 when dbms_types.typecode_timestamp then
308 dbms_sql.column_value( self.cur, i, ts1 );
309 outset.settimestamp( ts1 );
310 when dbms_types.typecode_timestamp_tz then
311 dbms_sql.column_value( self.cur, i, tstz1 );
312 outset.settimestamptz( tstz1 );
313 when dbms_types.typecode_timestamp_ltz then
314 dbms_sql.column_value( self.cur, i, tsltz1 );
315 outset.settimestampltz( tsltz1 );
316 end case;
317 end loop;
318 outset.endcreate;
319 return odciconst.success;
320 end;
321 --
322 member function ODCITableClose( self in PivotImpl )
323 return number
324 is
325 c integer;
326 begin
327 c := self.cur;
328 dbms_sql.close_cursor( c );
329 return odciconst.success;
330 end;
331 end;
332 /
Type body created.
SQL>
SQL> create or replace
2 function pivot( p_stmt in varchar2, p_fmt in varchar2 := 'upper(@p@)', dummy in number := 0 )
3 return anydataset pipelined using PivotImpl;
4 /
Function created.
SQL> CREATE TABLE app_users (
2 username VARCHAR2(40),
3 db_name VARCHAR2(40)
4 );
Table created.
SQL>
SQL> insert all
2 into app_users values ('USER1','DB1')
3 into app_users values ('USER2','DB1')
4 into app_users values ('USER3','DB1')
5 into app_users values ('USER4','DB2')
6 into app_users values ('USER5','DB2')
7 select * from dual;
5 rows created.
SQL>
SQL>
SQL> select * from table(pivot('select row_number() over ( partition by db_name order by username) as x, db_name, username from app_users'));
X DB1 DB2
---------- ---------------------------------------- ----------------------------------------
1 USER1 USER4
2 USER2 USER5
3 USER3
复制文章转载自ASKTOM，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
评论
相关阅读
【纯干货】Oracle 19C RU 19.27 发布，如何快速升级和安装？
Lucifer三思而后行
655次阅读
2025-04-18 14:18:38
Oracle RAC 一键安装翻车？手把手教你如何排错！
Lucifer三思而后行
625次阅读
2025-04-15 17:24:06
Oracle数据库一键巡检并生成HTML结果，免费脚本速来下载!
陈举超
530次阅读
2025-04-20 10:07:02
【ORACLE】记录一些ORACLE的merge into语句的BUG
DarkAthena
479次阅读
2025-04-22 00:20:37
【活动】分享你的压箱底干货文档，三篇解锁进阶奖励！
墨天轮编辑部
475次阅读
2025-04-17 17:02:24
一页概览：Oracle GoldenGate
甲骨文云技术
457次阅读
2025-04-30 12:17:56
【ORACLE】你以为的真的是你以为的么?--ORA-38104: Columns referenced in the ON Clause cannot be updated
DarkAthena
449次阅读
2025-04-22 00:13:51
火焰图--分析复杂SQL执行计划的利器
听见风的声音
405次阅读
2025-04-17 09:30:30
3月“墨力原创作者计划”获奖名单公布
墨天轮编辑部
369次阅读
2025-04-15 14:48:05
Oracle数据库Hint大全，31个使用案例，速来下载！
陈举超
343次阅读
2025-04-16 21:25:19
