错误:游标已在嵌套for循环中打开

Question

create or replace PROCEDURE template2(
template_id_in IN RTEMPLATE_CONFIGURE.TEMPLATE_ID%TYPE)
AS 
  source_table rtemplate_configure.sobject_name%type;
  source_column rtemplate_configure.scolumn_name%type;
  target_table rtemplate_configure.tobject_name%type;
  target_column rtemplate_configure.tcolumn_name%type;
  tmp VARCHAR2(2000);
  tmp2 VARCHAR2(2000);
  CURSOR c_template_configure is 
    SELECT * FROM rtemplate_configure WHERE template_id = template_id_in order by source_table, target_table;
BEGIN 
FOR record_line in c_template_configure LOOP
  FOR record_line2 in c_template_configure LOOP
    IF record_line.sobject_name = record_line2.sobject_name 
      and record_line.tobject_name = record_line2.tobject_name
      and record_line.tcolumn_name <> record_line2.tcolumn_name
      and record_line.scolumn_name <> record_line2.scolumn_name
      THEN
      tmp2 := 'INSERT INTO  '||record_line.tobject_name||'('||record_line.tcolumn_name||','||record_line2.tcolumn_name||')'||' 
        SELECT '||record_line.scolumn_name||','||record_line.scolumn_name||' 
        FROM '||record_line.sobject_name||'';
        DBMS_OUTPUT.put_line 
        (tmp2);
    END IF;
  END LOOP;
END LOOP;
--COMMIT;
END template2;

我得到错误：PL/SQL:游标已经打开，我想我正确地关闭了它？我不确定我是否也正确地使用了for循环，我只需要一个光标穿过嵌套的循环来检查if语句中的数据。

Answer 1

您已经打开光标c_template_configure两次了。你不能这样做，你需要创建一个副本，例如c_template_configure2。

这里有一个非常简单的例子来说明你所做的事情：

SQL> declare
  2    cursor c is select * from emp;
  3  begin
  4    for r1 in c loop -- Open cursor c once
  5      for r2 in c loop -- Open cursor c again, already open
  6        null;
  7      end loop;
  8    end loop;
  9  end;
 10  /
declare
*
ERROR at line 1:
ORA-06511: PL/SQL: cursor already open
ORA-06512: at line 2
ORA-06512: at line 5

现在是更正后的代码：

  1  declare
  2    cursor c1 is select * from emp;
  3    cursor c2 is select * from emp;
  4  begin
  5    for r1 in c1 loop
  6      for r2 in c2 loop
  7        null;
  8      end loop;
  9    end loop;
 10* end;
SQL> /

旁白：如果处理大量数据，这是一种效率非常低的方法。考虑在查询中加入数据，例如：

select e1.empno as empno1, e2.empno as empno2
  from emp e1
  cross join emp e2
 where e1.empno != e2.empno;

现在您只需要打开一个游标，它将返回所有成对的雇员。

Answer 2

declare
    cursor  c1 is
        select *from emp;
    r1 c1%rowtype;
begin 
    open  c1;
    fetch c1 into r1;
    close c1;
   null;
    commit;
end;