- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
87.
88. 89. 90. 91. 92. 93. 94. 95. 96. 97. 98. 99.
shown
when 208 then dbms_types.typecode_urowid when 96 then dbms_types.typecode_char when 180 then dbms_types.typecode_timestamp when 181 then dbms_types.typecode_timestamp_tz when 231 then dbms_types.typecode_timestamp_ltz when 182 then dbms_types.typecode_interval_ym when 183 then dbms_types.typecode_interval_ds end , desc_tab( numcols ).col_precision , desc_tab( numcols ).col_scale , case desc_tab( numcols ).col_type when 11 then 18 -- for rowid col_max_len = 16, and 18 characters are
众所周知，静态SQL的输出结构必须也是静态的。对于经典的行转列问题，如果行数不定导致输出的列数不定，标准的答案 就是使用动态SQL, 到11G里面则有XML结果的PIVOT。 今天在asktom看到的一篇贴子彻底颠覆了我的看法！贴子里的链接指向另一个牛人辈出的荷兰公司： http://technology.amis.nl/2006/0 ... ing-antons-thunder/ 还记得Anton Scheffer吗？这位神人先是用10G的MODEL写了SUDOKU的一句SQL的解法， 在11GR2推出之后又率先用 递归WITH写了个只有短短几行的SUDOKU解法。他的作品还有EXCEL文件生成器。 早在2006年他就发明了真正动态的行转列办法，用的是一系列神秘的函数，如同自定义聚合函数STRAGG里面用的那些。 这个神秘的对象代码如下：
100. 101. 102. 103. 104. 105. 106. 107. 108. 109. 110. 111. 112. 113. 114. 115. 116. 117. 118. 119. 120. 121. 122. 123. 124. 125. 126. 127. 128. 129. 130. 131. 132. 133. 134. 135. 136. 137.
47. 48.
varchar2
when 208 then dbms_types.typecode_varchar2 when 96 then dbms_types.typecode_char
49. 50. 51. 52. 53. 54. 55. 56. 57. 58. 59.
shown
when 180 then dbms_types.typecode_timestamp when 181 then dbms_types.typecode_timestamp_tz when 231 then dbms_types.typecode_timestamp_ltz when 182 then dbms_types.typecode_interval_ym when 183 then dbms_types.typecode_interval_ds end , desc_tab( i ).col_precision , desc_tab( i ).col_scale , case desc_tab( i ).col_type when 11 then 18 -- for rowid col_max_len = 16, and 18 characters are
else desc_tab( numcols ).col_max_len end , desc_tab( numcols ).col_charsetid , desc_tab( numcols ).col_charsetform ); end loop; close rc; atyp.endcreate; anytype.begincreate( dbms_types.typecode_table, rtype ); rtype.SetInfo( null, null, null, null, null, atyp, dbms_types.typecode_object, 0 ); rtype.endcreate(); return odciconst.success; exception when others then return odciconst.error; end; --
40. 41. 42. 43. 44. 45. 46.
varchar2
loop atyp.addattr( desc_tab( i ).col_name , case desc_tab( i ).col_type when 1 when 2 when 9 when 11 when 12 then dbms_types.typecode_varchar2 then dbms_types.typecode_number then dbms_types.typecode_varchar2 then dbms_types.typecode_varchar2 then dbms_types.typecode_date -- show urowid as -- show rowid as
static function ODCITablePrepare( sctx out PivotImpl, ti in sys.ODCITabFuncInfo, p_stmt in varchar2, p_fmt in varchar2 := 'upper(@p@)', dummy in number ) return number is prec scale len csid csfrm aname tc begin tc := ti.RetType.GetAttrElemInfo( 1, prec, scale, len, csid, csfrm, elem_typ, aname ); -if instr( p_fmt, '@p@' ) > 0 then sctx := PivotImpl( elem_typ, p_stmt, p_fmt, null ); else sctx := PivotImpl( elem_typ, p_stmt, '@p@', null ); end if; return odciconst.success; pls_integer; pls_integer; pls_integer; pls_integer; pls_integer; varchar2(30); pls_integer;
wk.baidu.com
9. 10. 11. 12. 13. 14. 15. 16. 17. 18. 19. 20. 21. 22. 23. 24. 25. 26. 27. 28. 29. 30. 31. 32. 33. 34. 35. 36. 37. 38. 39.
static function ODCITableDescribe( rtype out anytype, p_stmt in varchar2, p_fmt in varchar2 := 'upper(@p@)', dummy in number ) return number is atyp anytype; cur integer; numcols number; desc_tab dbms_sql.desc_tab2; rc sys_refcursor; t_c2 varchar2(32767); t_fmt varchar2(1000); begin cur := dbms_sql.open_cursor; dbms_sql.parse( cur, p_stmt, dbms_sql.native ); dbms_sql.describe_columns2( cur, numcols, desc_tab ); dbms_sql.close_cursor( cur ); -anytype.begincreate( dbms_types.typecode_object, atyp ); for i in 1 .. numcols - 2
1. 2. 3. 4. 5. 6. 7. 8.
CREATE OR REPLACE type PivotImpl as object ( ret_type anytype, stmt varchar2(32767), fmt varchar2(32767), cur integer, static function ODCITableDescribe( rtype out anytype, p_stmt in varchar2, p_fmt in varchar2 := 'upper(@p@)', dummy in number := 0 ) return number, static function ODCITablePrepare( sctx out PivotImpl, ti in sys.ODCITabFuncInfo, p_stmt in varchar2, p_fmt in varchar2 := 'upper(@p@)', dummy in number := 0 ) return number, static function ODCITableStart( sctx in out PivotImpl, p_stmt in varchar2, p_fmt in varchar2 := 'upper(@p@)', dummy in number := 0 ) return number, member function ODCITableFetch( self in out PivotImpl, nrows in number, outset out anydataset ) return number, member function ODCITableClose( self in PivotImpl ) return number ) / create or replace type body PivotImpl as -- The return type of the table function
60. 61. 62. 63. 64. 65. 66. 67. 68. 69. 70. 71. 72. 73. 74. 75. 76. 77. 78. 79. 80. 81. 82. 83. 84. 85. 86.
varchar2
else desc_tab( i ).col_max_len end , desc_tab( i ).col_charsetid , desc_tab( i ).col_charsetform ); end loop; if instr( p_fmt, '@p@' ) > 0 then t_fmt := p_fmt; else t_fmt := '@p@'; end if; open rc for replace( 'select distinct ' || t_fmt || ' from( ' || p_stmt || ' ) order by ' || t_fmt , '@p@' , desc_tab( numcols - 1 ).col_name ); loop fetch rc into t_c2; exit when rc%notfound; atyp.addattr( t_c2 , case desc_tab( numcols ).col_type when 1 when 2 when 9 when 11 when 12 then dbms_types.typecode_varchar2 then dbms_types.typecode_number then dbms_types.typecode_varchar2 then dbms_types.typecode_varchar2 then dbms_types.typecode_date -- show rowid as
