ORACLE PL / SQL：动态SQL选择行使荟萃

是否可以建设从现有荟萃中提取的动态SQL语句？

l_collection := pack.get_items(
                i_code => get_items_list.i_code,i_name => get_items_list.i_name );

此刻,假设我想行使动态SQL从该荟萃中选择COUNT.那也许吗？另外,我也但愿从该荟萃中举办子选择.

办理要领

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;
/

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!