动态游标 Oracle
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26046482/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Dynamic cursor Oracle
提问by Luis Felipe Londo?o
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;
回答by Maheswaran Ravisankar
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);
简而言之，IN 子句不支持绑定变量。它只支持值，按照您使用的方式。您需要像IN (var1, var2);
Without knowing you , you have used bind variables. One workaround is use REFCURSORBy forming a query string dynamically.
在不了解您的情况下，您使用了绑定变量。一种解决方法是使用REFCURSOR通过动态形成查询字符串。
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;
One of my other answeralso has other way using collections, for bigger IN clause list.
我的其他答案之一也有使用集合的其他方式，用于更大的 IN 子句列表。
回答by Mark Hughs
--I USED A TABLE IN OUR DB ORACLE 11G R2 --there are some syntax issues above that I corrected --I moved the QUERY_STRING after the BEGIN and added output
--我在我们的数据库 ORACLE 11G R2 中使用了一个表 -- 我纠正了上面的一些语法问题 -- 我在 BEGIN 之后移动了 QUERY_STRING 并添加了输出
DECLARE
VAR1 VARCHAR2(500);
CUR1 SYS_REFCURSOR;
QUERY_STRING VARCHAR2(2000);
TYPE MYREC IS RECORD
(
WO_NBR VARCHAR(1000)
);
myrecord MYREC;
BEGIN
VAR1 := q'['45466','45432']';
QUERY_STRING := 'SELECT T.WO_NBR FROM WO_SCHED T WHERE T.WO_NBR IN ('||VAR1||')';
DBMS_OUTPUT.PUT_LINE(VAR1);
DBMS_OUTPUT.PUT_LINE(QUERY_STRING);
OPEN CUR1 FOR QUERY_STRING;
LOOP
FETCH CUR1 INTO myrecord;
DBMS_OUTPUT.PUT_LINE(myrecord.WO_NBR);
EXIT WHEN CUR1%NOTFOUND;
-- your processing
END LOOP;
CLOSE CUR1;
END;
