SQL*Plus: Release 8.0.6.0.0 - Production on 星期四 11月 4 18:28:30 2010(c) Copyright 1999 Oracle Corporation. All rights reserved. Connected to: Oracle Database 10g Release 10.1.0.2.0 - ProductionSQL> set linesize 1000 SQL> --创建过程 SQL> CREATE OR REPLACE PROCEDURE update_test (oldone IN NUMBER ,newone IN NUMBER ) 2 IS 3 CURSOR cur IS SELECT one,two FROM test WHERE two=oldone; 4 rs test%ROWTYPE ; 5 BEGIN 6 UPDATE test SET one=newone WHERE one=oldone; 7 OPEN cur; 8 LOOP 9 FETCH cur INTO rs ; 10 EXIT WHEN cur%NOTFOUND; 11 UPDATE test SET two=newone WHERE two=rs.two; 12 END LOOP; 13 COMMIT; 14 CLOSE cur; 15 END; 16 /Procedure created.SQL> select * from test; ONE TWO ---------- ---------- 143 155 143 132 155 127 155 156 143 187 156 234 1567 rows selected.SQL> --执行过程,更新ONE=156的记录为200 SQL> BEGIN 2 update_test(156,200); 3 END; 4 /PL/SQL procedure successfully completed.SQL> --查看结果: SQL> select * from test; ONE TWO ---------- ---------- 143 155 143 132 155 127 155 200 143 187 200 --变化 234 200 --变化7 rows selected.SQL>
更新后,数据变成: ONE TWO ---------- ---------- 100 101 143 102 155 103 155 104 143 105 156 106 156 要求就是:把现在的无序编号改成从100开始的编号,但还要保留ONE和TWO的关系,最后处理的数据能形成树结构(每条记录是一个节点的话,最后要形成一个目录树)。
在外面在包一层 按照one字段排序后 然后调用1楼的方法 不就可以了
SQL*Plus: Release 8.0.6.0.0 - Production on 星期五 11月 5 09:53:56 2010(c) Copyright 1999 Oracle Corporation. All rights reserved. Connected to: Oracle Database 10g Release 10.1.0.2.0 - ProductionSQL> --原表TEST数据: SQL> select * from test; ONE TWO ---------- ---------- 100 101 100 102 101 103 101 104 100 105 104 106 1047 rows selected.SQL> --创建过程实现连续编号的更新,并更新相应子节点: SQL> CREATE OR REPLACE PROCEDURE update_test 2 IS 3 CURSOR cur1 IS SELECT one,two FROM test FOR UPDATE OF one; 4 CURSOR cur2(oldone number) IS SELECT one,two FROM test WHERE two=oldone; 5 rs1 test%ROWTYPE ; 6 rs2 test%ROWTYPE; 7 newone NUMBER; 8 BEGIN 9 newone:=100; 10 OPEN cur1; 11 LOOP 12 FETCH cur1 INTO rs1 ; 13 EXIT WHEN cur1%NOTFOUND; 14 UPDATE test SET one=newone WHERE current of cur1; 15 OPEN cur2(rs1.one); 16 LOOP 17 FETCH cur2 INTO rs2 ; 18 EXIT WHEN cur2%NOTFOUND; 19 UPDATE test SET two=newone WHERE two=rs2.two; 20 END LOOP; 21 CLOSE cur2; 22 newone:=newone+1; 23 END LOOP; 24 COMMIT; 25 CLOSE cur1; 26 END; 27 /Procedure created.SQL> --执行过程: SQL> BEGIN 2 update_test; 3 END; 4 /PL/SQL procedure successfully completed.SQL> --查询结果: SQL> select * from test; ONE TWO ---------- ---------- 100 101 100 102 101 103 101 104 100 105 104 106 1047 rows selected.SQL>
SQL> --不好意思,上面的原表数据贴错了,是这个,跟你的数据一样, SQL> -- 其他过程和执行都是正确的 SQL> select * from test; ONE TWO ---------- ---------- 143 155 143 132 155 127 155 156 143 187 156 234 1567 rows selected.SQL> SQL> BEGIN 2 update_test; 3 END; 4 /PL/SQL procedure successfully completed.SQL> select * from test; ONE TWO ---------- ---------- 100 101 100 102 101 103 101 104 100 105 104 106 1047 rows selected.SQL>
已写入文件 afiedt.buf 1 create or replace procedure updatepro(oldno in emp.empno%type,newno emp.empno%type)
2 as
3 begin
4 update emp set empno=newno where empno=oldno;
5 update emp set mgr=newno where mgr=oldno;
6 commit;
7* end;
SQL> /过程已创建。SQL> exec updatepro(7839,1111);PL/SQL 过程已成功完成。SQL> select empno,mgr from emp; EMPNO MGR
---------- ----------
7369 7902
7499 7698
7521 7698
7566 1111
7654 7698
7698 1111
7782 1111
7788 7566
1111
7844 7698
7876 7788
7900 7698
7902 7566
7934 7782已选择14行。
ONE中内容通过自动编号重置了,这是什么意思?
你是想表达说,ONE中一个节点被更新了,那么其所有子节点都得相应的做更新?
最好举个例子来看看
Connected to:
Oracle Database 10g Release 10.1.0.2.0 - ProductionSQL> set linesize 1000
SQL> --创建过程
SQL> CREATE OR REPLACE PROCEDURE update_test (oldone IN NUMBER ,newone IN NUMBER )
2 IS
3 CURSOR cur IS SELECT one,two FROM test WHERE two=oldone;
4 rs test%ROWTYPE ;
5 BEGIN
6 UPDATE test SET one=newone WHERE one=oldone;
7 OPEN cur;
8 LOOP
9 FETCH cur INTO rs ;
10 EXIT WHEN cur%NOTFOUND;
11 UPDATE test SET two=newone WHERE two=rs.two;
12 END LOOP;
13 COMMIT;
14 CLOSE cur;
15 END;
16 /Procedure created.SQL> select * from test; ONE TWO
---------- ----------
143
155 143
132 155
127 155
156 143
187 156
234 1567 rows selected.SQL> --执行过程,更新ONE=156的记录为200
SQL> BEGIN
2 update_test(156,200);
3 END;
4 /PL/SQL procedure successfully completed.SQL> --查看结果:
SQL> select * from test; ONE TWO
---------- ----------
143
155 143
132 155
127 155
200 143
187 200 --变化
234 200 --变化7 rows selected.SQL>
ONE TWO
---------- ----------
100
101 143
102 155
103 155
104 143
105 156
106 156
要求就是:把现在的无序编号改成从100开始的编号,但还要保留ONE和TWO的关系,最后处理的数据能形成树结构(每条记录是一个节点的话,最后要形成一个目录树)。
然后调用1楼的方法 不就可以了
Connected to:
Oracle Database 10g Release 10.1.0.2.0 - ProductionSQL> --原表TEST数据:
SQL> select * from test; ONE TWO
---------- ----------
100
101 100
102 101
103 101
104 100
105 104
106 1047 rows selected.SQL> --创建过程实现连续编号的更新,并更新相应子节点:
SQL> CREATE OR REPLACE PROCEDURE update_test
2 IS
3 CURSOR cur1 IS SELECT one,two FROM test FOR UPDATE OF one;
4 CURSOR cur2(oldone number) IS SELECT one,two FROM test WHERE two=oldone;
5 rs1 test%ROWTYPE ;
6 rs2 test%ROWTYPE;
7 newone NUMBER;
8 BEGIN
9 newone:=100;
10 OPEN cur1;
11 LOOP
12 FETCH cur1 INTO rs1 ;
13 EXIT WHEN cur1%NOTFOUND;
14 UPDATE test SET one=newone WHERE current of cur1;
15 OPEN cur2(rs1.one);
16 LOOP
17 FETCH cur2 INTO rs2 ;
18 EXIT WHEN cur2%NOTFOUND;
19 UPDATE test SET two=newone WHERE two=rs2.two;
20 END LOOP;
21 CLOSE cur2;
22 newone:=newone+1;
23 END LOOP;
24 COMMIT;
25 CLOSE cur1;
26 END;
27 /Procedure created.SQL> --执行过程:
SQL> BEGIN
2 update_test;
3 END;
4 /PL/SQL procedure successfully completed.SQL> --查询结果:
SQL> select * from test; ONE TWO
---------- ----------
100
101 100
102 101
103 101
104 100
105 104
106 1047 rows selected.SQL>
SQL> -- 其他过程和执行都是正确的
SQL> select * from test; ONE TWO
---------- ----------
143
155 143
132 155
127 155
156 143
187 156
234 1567 rows selected.SQL>
SQL> BEGIN
2 update_test;
3 END;
4 /PL/SQL procedure successfully completed.SQL> select * from test; ONE TWO
---------- ----------
100
101 100
102 101
103 101
104 100
105 104
106 1047 rows selected.SQL>