英文:
PL/SQL open cursor for query string with varchar2 array
问题
以下是您要翻译的部分：
我想在PL/SQL中在`open c1 for sql-statement`中使用一个数组。
数组的类型是`varchar2`。
数组是由另一个函数填充的：
OPEN v_c1 FOR query_statement;
LOOP
v_hrc_list.extend;
FETCH v_c1 into v_hrc_list(v_hrc_list.count);
EXIT WHEN (v_c1%NOTFOUND);
END LOOP;
CLOSE v_c1;
我尝试过：
query_statement = `...||'WHERE MEMBER OF (select * from table (:table_hrcs))'`
OPEN v_c1 FOR query_statement USING table_hrcs;
但我收到了以下错误信息：
> Inconsistent datatypes: expected UDT got CHAR
我还尝试过：
...||'WHERE MEMBER OF (select * from table ('||table_hrcs||'))'
英文:
I want to use a array in open c1 for sql-statement (in plsql)
array is table of varchar2;.
array is filled by another function:
OPEN v_c1 FOR query_statement;
LOOP
v_hrc_list.extend;
FETCH v_c1 into v_hrc_list(v_hrc_list.count);
EXIT WHEN (v_c1%NOTFOUND);
END LOOP;
CLOSE v_c1;
I tried:
query_statement = `...||'WHERE MEMBER OF (select * from table (:table_hrcs))'`
OPEN v_c1 FOR query_statement USING table_hrcs;
and I got the following error:
> Inconsistent datatypes: expected UDT got CHAR
I also tried
...||'WHERE MEMBER OF (select * from table ('||table_hrcs||'))'
答案1
得分: 1
您混淆了两种引用集合成员的方式。
对于您的集合类型，您可以选择以下两种方式之一：
' ... WHERE your_column IN (select * from table (:table_hrcs))'
或者更简单地：
' ... WHERE dummy MEMBER OF :table_hrcs'
在处理大量数据时，这两种方法可能会导致性能差异。
还请注意，结果数组最后会有一个空的元素。
您的示例不需要使用游标和for循环，您可以直接将数据批量收集到目标集合中（然后目标集合不需要初始化，并且不会有空元素）：
EXECUTE IMMEDIATE query_statement
BULK COLLECT INTO v_hrc_list
USING table_hrcs;
英文:
You're mixing up two ways of referring to members of a collection.
With the collection type you appear to have you can either do:
' ... WHERE your_column IN (select * from table (:table_hrcs))'
or more simply:
' ... WHERE dummy MEMBER OF :table_hrcs'
You may see a performance difference between the two with large amounts of data.
Also notice that the result array ends up with an empty last element.
Your example doesn't need to use a cursor and for-loop, you can bulk-collect directly into the target collection (which then doesn't need to be initialised, and doesn't have the empty element):
EXECUTE IMMEDIATE query_statement
BULK COLLECT INTO v_hrc_list
USING table_hrcs;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论