内部循环中的动态光标.多个游标

Question

考虑以下内容。

declare
v_name     person.name%TYPE;
v_surname  person.surname%TYPE;
cursor department_cursor is
    select * from department;
cursor person_cursor is 
    select * from person 
    where nvl(name, '')   = nvl(v_name, '');    
begin
  open department_cursor;  
  open person_cursor;
  for department_row in department_cursor loop
    v_name  := department_row.name;
    v_surname  := department_row.surname;
    for person_row in person_cursor loop
        DBMS_OUTPUT.PUT_LINE('I got here:'||person_row.p_id);
    end loop;   
  end loop;
  close person_cursor;
  close department_cursor;
end;
/

不要试图理解它的作用。这只是实际代码的一个被剥离/破坏的版本。然而果汁仍然存在。我想做的是有两个游标。第二个游标是动态的，它取决于第一个游标返回的行。

以上结果得到CCD_ 1。

Answer 1

问题是您有读取的行

open department_cursor;  
open person_cursor;

然后你会看到其他行读取

for department_row in department_cursor loop

和

for person_row in person_cursor loop

后面的行试图打开已经打开的游标。

我建议您将代码重写为：

declare
  v_name     person.name%TYPE;
  v_surname  person.surname%TYPE;
  cursor department_cursor is
    select * from department;
  cursor person_cursor is 
    select * from person 
    where nvl(name, '')   = nvl(v_name, '');    
begin
  for department_row in department_cursor loop
    v_name  := department_row.name;
    v_surname  := department_row.surname;
    for person_row in person_cursor loop
        DBMS_OUTPUT.PUT_LINE('I got here:'||person_row.p_id);
    end loop;   
  end loop;
end;

分享并享受。