在讨论一个有关表的UPDATE时, 写了如下的SQL:<原始需求,请参考:
http://www.oracle.com.cn/viewthr ... ghlight=&page=2>
SQL> update test2 set spc = (
2 select substr(max(sys_connect_by_path(b.name, '-')),2) name
3 from (select rn, skycode id,
4 decode(rn, 1, 0, instr(skycode, '-', 1, rn-1)) + 1 sp,
5 decode(instr(skycode, '-', 1, rn), 0, length(skycode) + 1, instr(skycode, '-', 1, rn)) ep
6 from (select rownum rn from dual connect by rownum<=20) a, test2 b
7 where decode(rn, 1, 0, instr(skycode, '-', 1, rn-1)) + instr(skycode, '-', 1, rn) > 0
8 ) a, test b
9 where b.citycode = substr(a.id, sp, ep-sp) and a.id=test2.skycode
10 start with rn=1 connect by rn-1=prior rn and a.id = prior a.id );
当时是在9.2.0.7下做的,没有问题,可以有朋友在9.2.0.1下,就会出现:
ORA-03113:通信通道文件结束
ORA-03114:未连接ORACLE
的错误提示, 这是9.2.0.1的一个BUG, 在多次的子查询时出现, 我试过,在9.2.0.5已经没有了,但不知道从那个版本ORACLE做了更正.
前段时间在写类似的多子查询的SELECT语句时, ORACLE9I提供的一个新子句: WITH在某种程度上解决了部分这类错误的出现. 经测试,原来同样的写法,也可以用于UPDATE中, 如上面的语句,可以用WITH改写为:
SQL> update test2 set spc = (
2 with myque as (select rn, skycode id,
3 decode(rn, 1, 0, instr(skycode, '-', 1, rn-1)) + 1 sp,
4 decode(instr(skycode, '-', 1, rn), 0, length(skycode) + 1,
5 instr(skycode, '-', 1, rn)) ep
6 from (select rownum rn from dual connect by rownum<=20) a, test2 b
7 where decode(rn, 1, 0, instr(skycode, '-', 1, rn-1)) + instr(skycode, '-', 1, rn) > 0 )
8 select substr(max(sys_connect_by_path(b.name, '-')),2) name
9 from myque a, test b
10 where b.citycode = substr(a.id, sp, ep-sp) and a.id=test2.skycode
11 start with rn=1 connect by rn-1=prior rn and a.id = prior a.id );
已更新4行。
可见, ORACLE在支持子查询的地方,同时也支持WITH的操作, 本人认为,这样一来,可以让开发人员有更多的机会,写出高效的单个SQL语句. 特别是在多个子查询中多次对同一基表进行访问时.
SQL> update test2 set spc = (
2 select substr(max(sys_connect_by_path(b.name, '-')),2) name
3 from (select rn, skycode id,
4 decode(rn, 1, 0, instr(skycode, '-', 1, rn-1)) + 1 sp,
5 decode(instr(skycode, '-', 1, rn), 0, length(skycode) + 1, instr(skycode, '-', 1, rn)) ep
6 from (select rownum rn from dual connect by rownum<=20) a, test2 b
7 where decode(rn, 1, 0, instr(skycode, '-', 1, rn-1)) + instr(skycode, '-', 1, rn) > 0
8 ) a, test b
9 where b.citycode = substr(a.id, sp, ep-sp) and a.id=test2.skycode
10 start with rn=1 connect by rn-1=prior rn and a.id = prior a.id );
当时是在9.2.0.7下做的,没有问题,可以有朋友在9.2.0.1下,就会出现:
ORA-03113:通信通道文件结束
ORA-03114:未连接ORACLE
的错误提示, 这是9.2.0.1的一个BUG, 在多次的子查询时出现, 我试过,在9.2.0.5已经没有了,但不知道从那个版本ORACLE做了更正.
前段时间在写类似的多子查询的SELECT语句时, ORACLE9I提供的一个新子句: WITH在某种程度上解决了部分这类错误的出现. 经测试,原来同样的写法,也可以用于UPDATE中, 如上面的语句,可以用WITH改写为:
SQL> update test2 set spc = (
2 with myque as (select rn, skycode id,
3 decode(rn, 1, 0, instr(skycode, '-', 1, rn-1)) + 1 sp,
4 decode(instr(skycode, '-', 1, rn), 0, length(skycode) + 1,
5 instr(skycode, '-', 1, rn)) ep
6 from (select rownum rn from dual connect by rownum<=20) a, test2 b
7 where decode(rn, 1, 0, instr(skycode, '-', 1, rn-1)) + instr(skycode, '-', 1, rn) > 0 )
8 select substr(max(sys_connect_by_path(b.name, '-')),2) name
9 from myque a, test b
10 where b.citycode = substr(a.id, sp, ep-sp) and a.id=test2.skycode
11 start with rn=1 connect by rn-1=prior rn and a.id = prior a.id );
已更新4行。
可见, ORACLE在支持子查询的地方,同时也支持WITH的操作, 本人认为,这样一来,可以让开发人员有更多的机会,写出高效的单个SQL语句. 特别是在多个子查询中多次对同一基表进行访问时.