将某列的多行数据汇聚为一个字符串时,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,星期,终止时间
而且在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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/117319/viewspace-1839913/