Question

cursor already open???????????pl/sql

CREATE or REPLACE PROCEDURE show_unmarried
IS
v_name president.pres_name%type;
v_birth president.birth_yr%type;
cursor marriage_cursor IS
select pres_name, birth_yr
from president
where pres_name not in(
SELECT pres_name
from PRES_MARRIAGE)
order BY birth_yr;
marriage_rec marriage_cursor%ROWTYPE;
BEGIN
OPEN marriage_cursor;
FOR marriage_rec IN marriage_cursor LOOP
FETCH marriage_cursor INTO v_name,v_birth;
dbms_output.put_line(v_name||' '||v_birth);
end loop;
close marriage_cursor;
end;
----------------------------
创建没问题，可是每次调用的时候就出现
cursor already open的错误，很奇怪
请问怎么解决？

Answer 1

BEGIN
OPEN marriage_cursor;
FOR marriage_rec IN marriage_cursor LOOP
FETCH marriage_cursor INTO v_name,v_birth;
dbms_output.put_line(v_name||' '||v_birth);
end loop;

这个错误其实是因为你的cursor打开方式不对，顺序：begin open loop fetch into exit when notfound endloop close end;

Answer 2

OPEN marriage_cursor;

LOOP
FETCH marriage_cursor INTO v_name,v_birth;
EXIT WHEN marriage_cursor%NOTFOUND;
dbms_output.put_line(v_name||' '||v_birth);
end loop;
close marriage_cursor;

替换一下，这个是可以成功的。