分类目录归档：Oracle 开发

一、语法

大致写法：select * from some_table [where 条件1] connect by [条件2] start with [条件3];
其中 connect by 与 start with 语句摆放的先后顺序不影响查询的结果，[where 条件1]可以不需要。
[where 条件1]、[条件2]、[条件3]各自作用的范围都不相同：

[where 条件1]是在根据“connect by [条件2] start with [条件3]”选择出来的记录中进行过滤，是针对单条记录的过滤， 不会考虑树的结构；

[条件2]指定构造树的条件，以及对树分支的过滤条件，在这里执行的过滤会把符合条件的记录及其下的所有子节点都过滤掉；

[条件3]限定作为搜索起始点的条件，如果是自上而下的搜索则是限定作为根节点的条件，如果是自下而上的搜索则是限定作为叶子节点的条件；

示例：
假如有如下结构的表：some_table(id,p_id,name)，其中p_id保存父记录的id。
select * from some_table t where t.id!=123 connect by prior t.p_id=t.id and t.p_id!=321 start with t.p_id=33 or t.p_id=66;

对prior的说明：
prior存在于[条件2]中，可以不要，不要的时候只能查找到符合“start with [条件3]”的记录，不会在寻找这些记录的子节点。要的时候有两种写法：connect by prior t.p_id=t.id 或 connect by t.p_id=prior t.id，前一种写法表示采用自上而下的搜索方式（先找父节点然后找子节点），后一种写法表示采用自下而上的搜索方式（先找叶子节点然后找父节点）。

二、执行原理
connect by…start with…的执行原理可以用以下一段程序的执行以及对存储过程RECURSE()的调用来说明：

/* 遍历表中的每条记录，对比是否满足start with后的条件，如果不满足则继续下一条，
如果满足则以该记录为根节点，然后调用RECURSE()递归寻找该节点下的子节点，
如此循环直到遍历完整个表的所有记录 。*/
for rec in (select * from some_table) loop
if FULLFILLS_START_WITH_CONDITION(rec) then
RECURSE(rec, rec.child);
end if;
end loop;

/* 寻找子节点的存储过程*/
procedure RECURSE (rec in MATCHES_SELECT_STMT, new_parent IN field_type) is
begin
APPEND_RESULT_LIST(rec); /*把记录加入结果集合中*/
/*再次遍历表中的所有记录，对比是否满足connect by后的条件，如果不满足则继续下一条，
如果满足则再以该记录为根节点，然后调用RECURSE()继续递归寻找该节点下的子节点，
如此循环直到找至叶子节点。*/
for rec_recurse in (select * from some_table) loop
if FULLFILLS_CONNECT_BY_CONDITION(rec_recurse.child, new_parent) then
RECURSE(rec_recurse,rec_recurse.child);
end if;
end loop;
end procedure RECURSE;

三、使用探讨
从上面的执行原理可以看到“connect by…start with…”构造树的方式是：（1）如果是自上而下方式，则把表中的每一条记录都作为根节点来生成树，所以表中有多少条记录就会构造出多少棵树。（2）如果是自下而上的搜索方式，则把表中的每一条记录都作为叶子节点来生成分支，所以表中有多少条记录就会生成多少条分支。
因此如果表中的记录不是严格遵照每条记录都只能有一个父记录的原则，那么就可能有部分记录会存在于多棵树中，那么在查找记录的时候就可能会出现找到多条重复记录的异常情况。

来源：http://hi.baidu.com/haydo/blog/item/069298438e5c6d1073f05d46.html

现在有个案例，有一张很大的表t1，需要更新其中的一个列，然后这个列是参考另外一个表t2中的列
直接sql操作如：
update t1 ta set prov_code=(select area_code from t2 tb where ta.id=tb.id);

利用rowid，分批提交的思路写出如下sql，执行大表更新效率比较高

DECLARE
  CURSOR cur IS
    SELECT
     a.area_code, b.ROWID ROW_ID
      FROM t2 a, t1 b
     WHERE a.id = b.id
     ORDER BY b.ROWID;  
  V_COUNTER NUMBER;
BEGIN
  V_COUNTER := 0;
  FOR row IN cur LOOP
    UPDATE t1
       SET prov_code = row.area_code
     WHERE ROWID = row.ROW_ID;
    V_COUNTER := V_COUNTER + 1;
    IF (V_COUNTER >= 1000) THEN
      COMMIT;
      V_COUNTER := 0;
    END IF;
  END LOOP;
  COMMIT;
END;

这里利用了rowid排序，使得update操作是一个一个数据块的进行，减少逻辑读，分批提交减小undo压力

1、创建表
SQL> create table t(id number,name varchar2(20));

表已创建。

2、常规方式插入
SQL> insert into t values(1,’a&b’);
输入 b 的值: a&b
原值 1: insert into t values(1,’a&b’)
新值 1: insert into t values(1,’aa&b’)

已创建 1 行。

3、chr(38)插入
SQL> insert into t values(2,’c’||chr(38)||’d’);

已创建 1 行。

4、set define
SQL> set define `
SQL> insert into t values(3,’e&f’);

已创建 1 行。

5、查看结果
SQL> select * from t;

ID NAME
———- ——————–
1 aa&b
2 c&d
3 e&f

6、说明
第一种方法不能得到正常的，我想要的结果，而且第一种方法在应用程序中也比较难实现
第二种比较适合应用程序实现
第三种比较适合sqlplus进行批量处理数据