实验-where current of小测试.txt

于 2013-06-27 20:58:36 发布
阅读量73 收藏
点赞数
文章标签： 数据库
Connected to Oracle Database 10g Release 10.2.0.1.0
Connected as scott

SQL> drop table t;
Table dropped
SQL> select * from t;
TABLE_NAME                     COLUMN_NAME                     COLUMN_ID
------------------------------ ------------------------------ ----------
PERM                           X                                       1
PERM                           Y                                       2
PERM                           Z                                       3
DEPT                           DEPTNO                                  1
DEPT                           DNAME                                   2
DEPT                           LOC                                     3
EMP                            EMPNO                                   1
EMP                            ENAME                                   2
EMP                            JOB                                     3
EMP                            MGR                                     4
EMP                            HIREDATE                                5
EMP                            SAL                                     6
EMP                            COMM                                    7
EMP                            DEPTNO                                  8
BONUS                          ENAME                                   1
BONUS                          JOB                                     2
BONUS                          SAL                                     3
BONUS                          COMM                                    4
SALGRADE                       GRADE                                   1
19 rows selected
然后我创建一个简单的存储过程，对表T中数据进行更新。如果表名为'PERM',就将其列名改为“列名+column_id”的形式。
代码如下：
--script. 1
create or replace procedure p_temp(n_count out number) is
  numrows number;--存储column_id
  columnnm varchar2(100);--存储column_name
  cursor s is
    select column_id,column_name
      from t
     where table_name = 'PERM'
       for update of column_name;
begin
  n_count:=0;
 
  open s;
  loop
    fetch s
      into numrows,columnnm;
    exit when s%notfound;
    UPDATE t SET column_name = column_name || numrows WHERE CURRENT OF s;
    n_count := n_count + 1;
  end loop;
  commit;
  close s;
end;
SQL> set serveroutput on
SQL> declare
  2    n_count number;
  3  begin
  4    p_temp(n_count);
  5    dbms_output.put_line('更新行数为：'||n_count);
  6  end;
  7  /
更新行数为：3
PL/SQL procedure successfully completed
SQL> select * from t;
TABLE_NAME                     COLUMN_NAME                     COLUMN_ID
------------------------------ ------------------------------ ----------
PERM                           X1                                      1
PERM                           Y2                                      2
PERM                           Z3                                      3
DEPT                           DEPTNO                                  1
DEPT                           DNAME                                   2
DEPT                           LOC                                     3
EMP                            EMPNO                                   1
EMP                            ENAME                                   2
EMP                            JOB                                     3
EMP                            MGR                                     4
EMP                            HIREDATE                                5
EMP                            SAL                                     6
EMP                            COMM                                    7
EMP                            DEPTNO                                  8
BONUS                          ENAME                                   1
BONUS                          JOB                                     2
BONUS                          SAL                                     3
BONUS                          COMM                                    4
SALGRADE                       GRADE                                   1
19 rows selected
结果的前三行中column_name更新了。

将存储过程略微调整，改为对表名不等于PERM的数据进行修改：
--script. 2
create or replace procedure p_temp(n_count out number) is
  type typ_numrows is table of number;
  numrows typ_numrows;
  type typ_columnnm is table of varchar2(100);
  columnnm typ_columnnm;
  cursor s is
    select column_id,column_name
      from t
     where table_name <>'PERM'
       for update of column_name;
begin
  n_count:=0;
 
  open s;
  loop
    fetch s bulk collect
      into numrows,columnnm limit 3;
    exit when s%notfound;
    forall i in numrows.first..numrows.last
      UPDATE t SET column_name = column_name || numrows(i) WHERE CURRENT OF s;
    n_count := n_count + 1;
  end loop;
  commit;
  close s;
end;

我预期的结果是表名不为PERM的数据，column_name后面加上对应的column_id。
但结果却为：
SQL> declare
  2    n_count number;
  3  begin
  4    p_temp(n_count);
  5    dbms_output.put_line('fetch次数为：'||n_count);
  6  end;
  7  /
fetch次数为：5
PL/SQL procedure successfully completed
SQL> select * from t;
TABLE_NAME                     COLUMN_NAME                     COLUMN_ID
------------------------------ ------------------------------ ----------
PERM                           X1                                      1
PERM                           Y2                                      2
PERM                           Z3                                      3
DEPT                           DEPTNO                                  1
DEPT                           DNAME                                   2
DEPT                           LOC123                                  3
EMP                            EMPNO                                   1
EMP                            ENAME                                   2
EMP                            JOB123                                  3
EMP                            MGR                                     4
EMP                            HIREDATE                                5
EMP                            SAL456                                  6
EMP                            COMM                                    7
EMP                            DEPTNO                                  8
BONUS                          ENAME781                                1
BONUS                          JOB                                     2
BONUS                          SAL                                     3
BONUS                          COMM234                                 4
SALGRADE                       GRADE                                   1
19 rows selected
将上述代码中的
“    forall i in numrows.first..numrows.last
      UPDATE t SET column_name = column_name || numrows(i) WHERE CURRENT OF s;”
两行替换为
“delete from t WHERE CURRENT OF s;”
就变成了分批删除数据。
执行结果为：
SQL> declare
  2    n_count number;
  3  begin
  4    p_temp(n_count);
  5    dbms_output.put_line('删除操作次数为：'||n_count);
  6  end;
  7  /
删除操作次数为：5
PL/SQL procedure successfully completed
SQL> select * from t;
TABLE_NAME                     COLUMN_NAME                     COLUMN_ID
------------------------------ ------------------------------ ----------
PERM                           X1                                      1
PERM                           Y2                                      2
PERM                           Z3                                      3
DEPT                           DEPTNO                                  1
DEPT                           DNAME                                   2
EMP                            EMPNO                                   1
EMP                            ENAME                                   2
EMP                            MGR                                     4
EMP                            HIREDATE                                5
EMP                            COMM                                    7
EMP                            DEPTNO                                  8
BONUS                          JOB                                     2
BONUS                          SAL                                     3
SALGRADE                       GRADE                                   1
14 rows selected

由于在代码中制定了每次提取三行
    fetch s bulk collect
      into numrows,columnnm limit 3;
    n_count := n_count + 1;
  end loop;
  commit;
  close s;
end;
这时候删除的是当前行：
delete from t WHERE CURRENT OF s;
每次提取的最后一行就是当前行，被删除掉了。
因此，除非想要得到上面例子中的特殊效果（每隔几行进行删除或更新操作），
否则，不应该批量操作中出现where current of cursor_name这样的表示（也就是说不要使用script. 2 类似的代码）。
上述代码略显啰嗦，用游标for循环代替：
--script. 3
create or replace procedure p_test is
n_count number:=0;
CURSOR t_cur IS
SELECT * FROM t FOR UPDATE;
BEGIN
FOR t_row IN t_cur
LOOP
IF t_row.table_name='PERM' THEN
UPDATE t SET t.column_name=t.column_name||t.column_id WHERE CURRENT OF t_cur;
n_count:=n_count+1;
ELSE
null;
END IF;
END LOOP;
dbms_output.put_line(n_count);
END;
至于使用where current of的好处，通过以上几个例子也有所体会。
可能实际用到的存储过程会是这样：
--script. 4
create or replace procedure p_temp(table_nm varchar2, n_count out number) is
  numrows  number; --存储column_id
  columnnm varchar2(100); --存储column_name
  cursor s is
    select column_id, column_name
      from t
     where table_name = table_nm
       for update of column_name;
begin
  n_count := 0;
  open s;
  loop
    fetch s
      into numrows, columnnm;
    exit when s%notfound;
    UPDATE t SET column_name = column_name || numrows WHERE CURRENT OF s;
    n_count := n_count + 1;
  end loop;
  commit;
  close s;
end;
其实就是在“script. 1”基础上把table_name的值作为输入参数了。
这样，每次更新或删除操作都只是局部的，相对于全局的update或delete操作来说范围小，处理快，被锁住的时间短。
这是where current of的好处。
另外有一点比较容易忽略：在script. 4中不要试图在update语句后立即commit。
这样会导致锁的释放，后续的fetch操作就找不到current of s的列，因此报错“ORA-01002: 提取违反顺序”

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

转载于:http://blog.itpub.net/26451536/viewspace-764967/

cuili9760
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值