动态游标Oracle [英] Dynamic cursor Oracle

问题描述

我想创建一个动态游标，但是我的代码没有为我带来正确的数据.我在做什么错了?

I want to create a dynamic cursor, but my code does not bring me the correct data. What am I doing wrong?

DECLARE
 VAR1 VARCHAR2(500);
 CURSOR CUR1 IS
  SELECT T.COL1 FROM TABLE1 T WHERE T.COL1 IN (VAR1);

 BEGIN
  VAR1 := q'['V1','V2']';
  FOR REG IN CUR1 LOOP
   DBMS_OUTPUT.PUT_LINE(REG.COL1);
  END LOOP;
END;

推荐答案

简而言之，IN子句不支持绑定变量.它仅支持使用的值. c0>;

In short, IN clause doesn't support bind variables.. It supports for only value,in the way you used.. You need to specify it like IN (var1, var2);

在不了解您的情况下，您使用了绑定变量.一种解决方法是使用REFCURSOR通过动态形成查询字符串.

Without knowing you , you have used bind variables. One workaround is use REFCURSOR By forming a query string dynamically.

DECLARE
 VAR1 VARCHAR2(500);
 CUR1 SYs_REFCURSOR;
 QUERY_STRING VARCHAR2(2000) := 'SELECT T.COL1 FROM TABLE1 T WHERE T.COL1 IN';

 MYREC IS RECORD 
 (
   COL1 VARCHAR(1000);
 );

 myrecord MYREC;

 BEGIN
  VAR1 := q'['V1','V2']';
  QUERY_STRING:= QUERY_STRING||'('||VAR1||')';

  OPEN CUR1 FOR QUERy_STRING;

    LOOP
      FETCH CUR1 INTO myrecord;
      DBMS_OUTPUT.PUT_LINE(myrecord.COL1);
      EXIT WHEN v_my_ref_cursor%NOTFOUND;
        ..
      -- your processing
    END LOOP;

  CLOSE CUR1;

END;

我的另一个答案对于更大的IN子句列表，也可以使用其他方式使用集合.

One of my other answer also has other way using collections, for bigger IN clause list.

这篇关于动态游标Oracle的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！