是否可以创建从现有集合中提取的动态sql语句？
l_collection := pack.get_items(
i_code => get_items_list.i_code,i_name => get_items_list.i_name );
现在,假设我想使用动态sql从该集合中选择COUNT.那可能吗？此外,我也希望从该集合中进行子选择.
解决方法
如果在模式级别声明集合类型,则可以在sql语句中使用它,包括动态语句.您需要将其显式地转换为正确的集合类型,否则sql引擎不知道它是什么类型.
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM TABLE(CAST(:collection AS collection_type))' INTO l_count USING l_collection ;
我不确定是否有其他原因要使用动态sql,或者如果你只是假设在这种情况下它是必要的.如果你想要做的就是选择计数,那就没有必要了.这个内联sql应该可以正常工作：
SELECT COUNT(*) INTO l_count FROM TABLE(CAST(l_collection AS collection_type));
当然,如果这就是你想要的全部,那么只需要l_count：= l_collection.COUNT.
编辑 – 添加完整的实例
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; /
