我在尝试以下代码的过程中发现使用CURRENT OF游标取值发生跳跃,百思不得其解,请大家指教。1.建立测试表:
create table test(id number, user_name varchar2(20));
insert into test values(1, 'hzk');
insert into test values(2, 'zak');
insert into test values(3, 'zaglzd');2.须实现的目标:遍历TEST表,对每条记录的ID值,增加10,结果应该如下:
id user_name
11 hzk
12 zak
13 zaglzd3.使用传统的游标编程:
DECLARE
2 V_ID TEST.ID%TYPE;
3 V_USER_NAME TEST.USER_NAME%TYPE;
4 old_id number;
5 CURSOR TEST_CUR IS
6 SELECT ID, USER_NAME FROM TEST;
7 BEGIN
8 OPEN TEST_CUR;
9 LOOP
10 FETCH TEST_CUR INTO V_ID, V_USER_NAME;
11 EXIT WHEN TEST_CUR%NOTFOUND;
12 dbms_output.put_line(' now id is '||v_id||' now user is '||v_user_name);
13 old_id:=v_id;
14 V_ID:=V_ID+10;
15 update test
16 set id=v_id
17 where id=old_id;
18 DBMS_OUTPUT.PUT_LINE(' NEW ID IS '||V_ID||' USER_NMAE IS '||V_USER_NAME);
19 END LOOP;
20 CLOSE TEST_CUR;
21 END;输出如下:
now id is 1 now user is hzk
NEW ID IS 11 USER_NMAE IS hzk
now id is 2 now user is zak
NEW ID IS 12 USER_NMAE IS zak
now id is 3 now user is zaglzd
NEW ID IS 13 USER_NMAE IS zaglzdPL/SQL procedure successfully completedSQL> select * from test; ID USER_NAME
---------- --------------------
11 hzk
12 zak
13 zaglzd4.使用update 语句的CURRENT OF 游标编程:DECLARE
V_ID TEST.ID%TYPE;
V_USER_NAME TEST.USER_NAME%TYPE;
CURSOR TEST_CUR IS
SELECT ID, USER_NAME FROM TEST
FOR UPDATE OF id;
BEGIN
FOR y IN test_cur
LOOP
FETCH test_cur INTO v_id, v_user_name;
EXIT WHEN test_cur%NOTFOUND;
dbms_output.put_line(' now id is '||v_id||' now user is '||v_user_name);
V_ID:=V_ID+10;
UPDATE test
SET id = v_id
WHERE CURRENT OF test_cur;
DBMS_OUTPUT.PUT_LINE(' NEW ID IS '||V_ID||' USER_NMAE IS '||V_USER_NAME);
END LOOP;
END;
/所得的输出如下:
now id is 2 now user is zak
NEW ID IS 12 USER_NMAE IS zakPL/SQL procedure successfully completedSQL> select * from test; ID USER_NAME
---------- --------------------
1 hzk
12 zak----------------注意:这里只更新了一条记录!!!!为什么????
3 zaglzd这就是二者的差异, 但是其原理我始终不解,如此用来,那游标的取值不是乱了吗????请大家一起来探讨.
此外,我觉得此例中最为重要的就是FOR循环中的循环变量y ,所以我本想在输出时打印y值:
dbms_output.put_line(' now id is '||v_id||' now user is '||v_user_name||' '||y);
但是执行时总是报错:
pls-00306:调用||时参数个数或类型错误!
ora-06550:第18行,第7列
pl/sql: statement ignored
这时只要去掉' '||y 就好了,所以还想问下,如何在PL/SQL 中打印或监视循环变量!
谢谢大家!
create table test(id number, user_name varchar2(20));
insert into test values(1, 'hzk');
insert into test values(2, 'zak');
insert into test values(3, 'zaglzd');2.须实现的目标:遍历TEST表,对每条记录的ID值,增加10,结果应该如下:
id user_name
11 hzk
12 zak
13 zaglzd3.使用传统的游标编程:
DECLARE
2 V_ID TEST.ID%TYPE;
3 V_USER_NAME TEST.USER_NAME%TYPE;
4 old_id number;
5 CURSOR TEST_CUR IS
6 SELECT ID, USER_NAME FROM TEST;
7 BEGIN
8 OPEN TEST_CUR;
9 LOOP
10 FETCH TEST_CUR INTO V_ID, V_USER_NAME;
11 EXIT WHEN TEST_CUR%NOTFOUND;
12 dbms_output.put_line(' now id is '||v_id||' now user is '||v_user_name);
13 old_id:=v_id;
14 V_ID:=V_ID+10;
15 update test
16 set id=v_id
17 where id=old_id;
18 DBMS_OUTPUT.PUT_LINE(' NEW ID IS '||V_ID||' USER_NMAE IS '||V_USER_NAME);
19 END LOOP;
20 CLOSE TEST_CUR;
21 END;输出如下:
now id is 1 now user is hzk
NEW ID IS 11 USER_NMAE IS hzk
now id is 2 now user is zak
NEW ID IS 12 USER_NMAE IS zak
now id is 3 now user is zaglzd
NEW ID IS 13 USER_NMAE IS zaglzdPL/SQL procedure successfully completedSQL> select * from test; ID USER_NAME
---------- --------------------
11 hzk
12 zak
13 zaglzd4.使用update 语句的CURRENT OF 游标编程:DECLARE
V_ID TEST.ID%TYPE;
V_USER_NAME TEST.USER_NAME%TYPE;
CURSOR TEST_CUR IS
SELECT ID, USER_NAME FROM TEST
FOR UPDATE OF id;
BEGIN
FOR y IN test_cur
LOOP
FETCH test_cur INTO v_id, v_user_name;
EXIT WHEN test_cur%NOTFOUND;
dbms_output.put_line(' now id is '||v_id||' now user is '||v_user_name);
V_ID:=V_ID+10;
UPDATE test
SET id = v_id
WHERE CURRENT OF test_cur;
DBMS_OUTPUT.PUT_LINE(' NEW ID IS '||V_ID||' USER_NMAE IS '||V_USER_NAME);
END LOOP;
END;
/所得的输出如下:
now id is 2 now user is zak
NEW ID IS 12 USER_NMAE IS zakPL/SQL procedure successfully completedSQL> select * from test; ID USER_NAME
---------- --------------------
1 hzk
12 zak----------------注意:这里只更新了一条记录!!!!为什么????
3 zaglzd这就是二者的差异, 但是其原理我始终不解,如此用来,那游标的取值不是乱了吗????请大家一起来探讨.
此外,我觉得此例中最为重要的就是FOR循环中的循环变量y ,所以我本想在输出时打印y值:
dbms_output.put_line(' now id is '||v_id||' now user is '||v_user_name||' '||y);
但是执行时总是报错:
pls-00306:调用||时参数个数或类型错误!
ora-06550:第18行,第7列
pl/sql: statement ignored
这时只要去掉' '||y 就好了,所以还想问下,如何在PL/SQL 中打印或监视循环变量!
谢谢大家!
id user_name
11 hzk
12 zak
13 zaglzd
用得着游标吗?
直接一个UPDATE语句不就行了?
FOR y IN test_cur
LOOP
FETCH test_cur ---------------这里是用test_cur吗????用y
INTO v_id, v_user_name;
EXIT WHEN test_cur%NOTFOUND;
2 CURSOR TEST_CUR IS
3 SELECT ID, USER_NAME FROM TEST
4 FOR UPDATE OF id;
5 BEGIN
6 FOR y IN test_cur
7 LOOP
8 dbms_output.put_line(' now id is '||y.id||' now user is '||y.user_name);
9 y.ID:=y.ID+10;
10 UPDATE test
11 SET id = y.id
12 WHERE CURRENT OF test_cur;
13 DBMS_OUTPUT.PUT_LINE(' NEW ID IS '||y.ID||' USER_NMAE IS '||y.USER_NAME);
14 END LOOP;
15 END;
16 / now id is 1 now user is hzk
NEW ID IS 11 USER_NMAE IS hzk
now id is 2 now user is zak
NEW ID IS 12 USER_NMAE IS zak
now id is 3 now user is zaglzd
NEW ID IS 13 USER_NMAE IS zaglzdPL/SQL procedure successfully completed已经用for 循环打开了就不需要fethc ... exit when ...
要不然当for 循环打开游标,指向第一条记录,
然后fetch 记录,就变成指向第二条记录,
所以楼主只能更新第二条,第四条,...纪录
谢谢大家。
但是我还想问下,如何能在DEBUG过程中监视循环变量和游标指针呢?
我用的是plsql developer6.03,但是我还不会用它的DEBUG ,我想如果能用它监视循环变量和游标指针的步进值,就可以避免写出上面的错误代码了,请大家赐教!
谢谢!
基本上就是返回游标,然后print x(x定义为 refcursor)
或者在循环中DBMS_OUTPUT.PUT_LINE
呵呵,大家要有好方法的话,共享一下