ITPub博客

首页 > 数据库 > Oracle > Sys_Connect_By_Path字符汇聚函数在10.2.0.3上的BUG

Sys_Connect_By_Path字符汇聚函数在10.2.0.3上的BUG

原创 Oracle 作者:associate 时间:2015-11-17 16:18:53 0 删除 编辑
将某列的多行数据汇聚为一个字符串时,Oracle的WM_CONCAT函数没有排序,
而且在11.2.0.3及10.2.0.5中返回值为VARCHAR2变更为CLOB,会导致SQL出错。

如果不想在程序中排序,可以使用Sys_Connect_By_Path汇聚函数(效率不高,ZL的可以使用自定义函数f_List2str),
目前发现,10.2.0.3上,这种方式下树型查询的BUG:字符拼接结果差一些记录。
在10.2.0.4以及11.2.0.4上不存在这个BUG。
要避免这个问题,查询条件尽可能放在子查询中,并且加rownum 来实体化子查询。

例:
SQL> SELECT * FROM v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production


1.下面这种方式,拼接出的字符差一条记录的值“开始时间”
SQL> Select Table_Name, Constraint_Name, LTrim(Max(Sys_Connect_By_Path(Column_Name, ',')), ',') Cons_Cols
  2  From User_Cons_Columns D
  3  Where Table_Name = '部门安排'
  4  Start With Position = 1
  5  Connect By Prior Position + 1 = Position And Prior Constraint_Name = Constraint_Name
  6  Group By Table_Name, Constraint_Name;
TABLE_NAME                     CONSTRAINT_NAME                CONS_COLS
------------------------------ ------------------------------ --------------------------------------------------------------------------------
部门安排                        部门安排_UQ_开始时间            部门ID,星期
部门安排                        部门安排_UQ_终止时间            部门ID,星期


2.改为子查询方式,仍然存在问题
SQL> Select Table_Name, Constraint_Name, LTrim(Max(Sys_Connect_By_Path(Column_Name, ',')), ',') Cons_Cols
  2  From (Select Table_Name, Constraint_Name, Column_Name, Position
  3         From User_Cons_Columns D
  4         Where Table_Name = '部门安排')
  5  Start With Position = 1
  6  Connect By Prior Position + 1 = Position And Prior Constraint_Name = Constraint_Name
  7  Group By Table_Name, Constraint_Name;
TABLE_NAME                     CONSTRAINT_NAME                CONS_COLS
------------------------------ ------------------------------ --------------------------------------------------------------------------------


部门安排                        部门安排_UQ_开始时间            部门ID,星期
部门安排                        部门安排_UQ_终止时间            部门ID,星期


3.在子查询中加rownum来实体化后,得到了正确的结果。
SQL> Select Table_Name, Constraint_Name, LTrim(Max(Sys_Connect_By_Path(Column_Name, ',')), ',') Cons_Cols
  2  From (Select Rownum, Table_Name, Constraint_Name, Column_Name, Position
  3         From User_Cons_Columns D
  4         Where Table_Name = '部门安排')
  5  Start With Position = 1
  6  Connect By Prior Position + 1 = Position And Prior Constraint_Name = Constraint_Name
  7  Group By Table_Name, Constraint_Name;
TABLE_NAME                     CONSTRAINT_NAME                CONS_COLS
------------------------------ ------------------------------ --------------------------------------------------------------------------------
部门安排                        部门安排_UQ_开始时间            部门ID,星期,开始时间
部门安排                        部门安排_UQ_终止时间            部门ID,星期,终止时间


4.优化为不使用group by和max函数,直接用oracle 10g开始提供的虚拟列Connect_By_Isleaf来过滤只取树形的末级
SQL> Select Table_Name, Constraint_Name, LTrim(Sys_Connect_By_Path(Column_Name, ','), ',') Cons_Cols
  2  From (Select Rownum, Table_Name, Constraint_Name, Column_Name, Position
  3         From User_Cons_Columns D
  4         Where Table_Name = '部门安排')
  5  Where Connect_By_Isleaf = 1
  6  Start With Position = 1
  7  Connect By Prior Position + 1 = Position And Prior Constraint_Name = Constraint_Name
  8  ;
TABLE_NAME                     CONSTRAINT_NAME                CONS_COLS
------------------------------ ------------------------------ --------------------------------------------------------------------------------
部门安排                        部门安排_UQ_开始时间            部门ID,星期,开始时间
部门安排                        部门安排_UQ_终止时间            部门ID,星期,终止时间

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/117319/viewspace-1839913/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2013-12-31

  • 博文量
    24
  • 访问量
    232573