declare块外部游标的内部使用

Question

我必须在特定块中使用游标，该块包含动态获取参数的query with WHERE子句...

因此，如何使用游标而不在DECLARE块中声明。

(我不能使用嵌套游标-我正在寻找示例)。

Answer 1

您可以使用对过程使用本地参数的隐式游标。下面是它的外观：

declare 
  v_parm varchar2(3) := 'Z';
begin
-- FIRST LOOP will display all objects owned by Z
  for x in ( select * from all_objects where owner=v_parm) loop
    dbms_output.put_line(x.object_name);
  end loop;
-- SECOND LOOP will display all objects owned by SYS until 100 is reached
--     (that would be a huge bunch otherwise!)
  v_parm := 'SYS';
  for x in ( select * from all_objects where owner=v_parm and rowid < 100) loop
    dbms_output.put_line(x.object_name);
  end loop;  
end;
/