如何在plsql的过程体内创建游标 [英] How to create cursor inside procedure body in plsql

问题描述

我想在过程主体内部动态创建光标，我也必须使用 for循环，而不是下面的代码.我做了动态光标，但是我不能使用for循环.

I want create cursor inside procedure body dynamically also i have to use for loop instead of below code. i did the dynamic cursor but i cannot use the for loop.

PROCEDURE myprocedure
AS
  LV_TEST_CUR SYS_REFCURSOR;
  LV_QUERY VARCHAR2(200);
  LV_DATE  DATE;
BEGIN
  LV_QUERY:='select sysdate as mydate from dual';
  OPEN LV_TEST_CUR FOR LV_QUERY;
  /*FOR CUR_VAR IN LV_TEST_CUR
  LOOP
  dbms_output.put_line(CUR_VAR.mydate);
  end LOOP;
  */
  LOOP
    FETCH LV_TEST_CUR INTO LV_DATE;
    EXIT
  WHEN LV_TEST_CUR%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(LV_DATE);
  END LOOP;
  CLOSE LV_TEST_CUR;
END myprocedure;

如果我使用注释的代码(用于循环)，我会报错

if i am using commented code(for loop), i getting error

PLS-00221:不是过程或未定义.

PLS-00221: is not a procedure or is undefined.

是否可以在动态游标中使用for循环?

Is it possible using for loop in dynamic cursor?

推荐答案

您不能在游标FOR循环中引用游标变量

you cannot reference a cursor variable in a cursor FOR loop

但是您可以直接使用选择语句:

but you can use the select statment direct:

create or replace PROCEDURE myprocedure
AS
  LV_TEST_CUR SYS_REFCURSOR;
  LV_QUERY VARCHAR2(200);
  LV_DATE  DATE;
BEGIN
  FOR CUR_VAR IN (select sysdate as mydate from dual)
  LOOP
  dbms_output.put_line(CUR_VAR.mydate);
  end LOOP;

END myprocedure;
/

这篇关于如何在plsql的过程体内创建游标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！