时间戳

首页 > 解决方案 > 如何在 EXECUTE IMMEDIATE 中使用 WITH 子句

oracle - 如何在 EXECUTE IMMEDIATE 中使用 WITH 子句

问题描述

我试图在执行立即语句之一中使用 with 子句。它运行良好，但不提供输出。它说匿名块已完成。我已经尝试了 SET SERVEROUTPUT ON 命令，但仍然无法正常工作。有人可以帮我解决这个问题。

begin
execute immediate 'WITH GG AS (
SELECT G46.PROV_NUM ,G46.SEQ_NUM, FM46G_ALTID_TYPE_1 AS ALTID_TYPE ,ALTID_1 AS ALTID ,FM46G_ALTID_SRC_1 AS ALTID_SRC FROM SPSMDMRW.SCW_CFF_46G G46
UNION
SELECT G46.PROV_NUM ,G46.SEQ_NUM, ALTID_TYPE_2 AS ALTID_TYPE,ALTID_2 AS ALTID,FM46G_ALTID_SRC_1 AS ALTID_SRC  FROM SPSMDMRW.SCW_CFF_46G  G46
UNION
SELECT G46.PROV_NUM ,G46.SEQ_NUM, ALTID_TYPE_3 AS ALTID_TYPE,ALTID_3 AS ALTID,ALTID_SRC_2 AS ALTID_SRC  FROM SPSMDMRW.SCW_CFF_46G G46 
UNION
SELECT G46.PROV_NUM ,G46.SEQ_NUM, ALTID_TYPE_4 AS ALTID_TYPE ,ALTID_4 AS ALTID ,ALTID_SRC_3 AS ALTID_SRC  FROM SPSMDMRW.SCW_CFF_46G G46
) 
select * from GG';
end;

标签： oracledynamic-sqlwith-statement

解决方案

这不是WITH因式分解子句，而是您没有将结果提取到任何东西中的事实。

应该是这样的：

SQL> set serveroutput on
SQL>
SQL> declare
  2    l_dname sys.odcivarchar2list;
  3  begin
  4    execute immediate 'with gg as
  5                         (select ename from emp where deptno = 10
  6                          union all
  7                          select ename from emp where deptno = 30
  8                         )
  9                       select ename from gg'
 10    bulk collect into l_dname;
 11
 12    for i in l_dname.first .. l_dname.last loop
 13      dbms_output.put_line(l_dname(i));
 14    end loop;
 15  end;
 16  /
ALLEN
WARD
MARTIN
BLAKE
TURNER
JAMES

PL/SQL procedure successfully completed.

SQL>

推荐阅读