ORACLE PL/SQL：使用集合的动态 SQL 选择
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5234922/
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
ORACLE PL/SQL: Dynamic SQL Select using a Collection
提问by jlrolin
Is it possible to create a dynamic SQL statement that pulls from an existing collection?
是否可以创建从现有集合中提取的动态 SQL 语句？
l_collection := pack.get_items(
i_code => get_items_list.i_code ,
i_name => get_items_list.i_name );
Now, let's say I want to select a COUNT from that collection using dynamic SQL. Is that possible? Furthermore, I want to do a sub select from that collection as well.
现在，假设我想使用动态 SQL 从该集合中选择一个 COUNT。那可能吗？此外，我还想从该集合中进行子选择。
回答by Dave Costa
If the collection type is declared at the schema level, it can be used in SQL statements, including dynamic ones. You need to explicitly cast it to the proper collection type, or the SQL engine has no idea what type it is.
如果集合类型在模式级别声明，则可以在 SQL 语句中使用，包括动态语句。您需要将其显式转换为正确的集合类型，否则 SQL 引擎不知道它是什么类型。
EXECUTE IMMEDIATE
'SELECT COUNT(*) FROM TABLE(CAST(:collection AS collection_type))'
INTO l_count
USING l_collection
;
I'm not sure if there's some other reason you want to use dynamic SQL, or if you're just assuming that it's necessary in this case. It shouldn't be necessary if all you want to do is select the count. This inline SQL should work fine:
我不确定您是否有其他原因想要使用动态 SQL，或者您是否只是假设在这种情况下它是必要的。如果您只想选择计数，则没有必要。这个内联 SQL 应该可以正常工作：
SELECT COUNT(*) INTO l_count FROM TABLE(CAST(l_collection AS collection_type));
Of course, if that's all you want you don't need SQL at all, just l_count := l_collection.COUNT.
当然，如果这就是你想要的，你根本不需要 SQL，只需要l_count := l_collection.COUNT.
Edit -- adding fully worked out example
编辑 - 添加完整的示例
CREATE OR REPLACE TYPE testtype AS OBJECT( x NUMBER, y NUMBER);
/
CREATE OR REPLACE TYPE testtypetab AS TABLE OF testtype;
/
DECLARE
t testtypetab := testtypetab();
l_count integer;
BEGIN
-- Populate the collection with some data
SELECT testtype(LEVEL, LEVEL) BULK COLLECT INTO t FROM dual CONNECT BY LEVEL<21;
-- Show that we can query it using inline SQL
SELECT count(*) INTO l_count FROM TABLE(CAST(t AS testtypetab));
dbms_output.put_line( l_count );
-- Clear the collection
t.DELETE;
-- Show that we can query it using dynamic SQL
EXECUTE IMMEDIATE 'select count(*) from table(cast(:collection as testtypetab))'
into l_count using t;
dbms_output.put_line( l_count );
END;
/
