oracle - 如何从执行立即语句的输出中获取结果
问题描述
该过程仅执行以下输出,但我试图从执行立即语句的输出中获取结果。
当前输出:
PL/SQL procedure successfully completed.
select 'PRJA' AS "PRJ_ID", EVENT, email,modified_by,modified from PRJA.TableX UNION ALL
select 'PRJB' AS "PRJ_ID", EVENT, email,modified_by,modified from PRJB.TableX UNION ALL
select 'PRJC' AS "PRJ_ID", EVENT, email,modified_by,modified from PRJC.TableX UNION ALL
select 'PRJD' AS "PRJ_ID", EVENT, email,modified_by,modified from PRJD.TableX;
我期待上面的结果 select / output :
SET SERVEROUTPUT ON
Declare
TYPE T IS TABLE OF MYTABLE.ID%TYPE INDEX BY PLS_INTEGER;
MYROW T;
v_sql varchar2(500);
v_sql2 varchar2(500);
v_prj_id varchar2(4000):='PRJA,PRJB,PRJC,PRJD';
BEGIN
FOR i IN (SELECT trim(regexp_substr(v_prj_id, '[^,]+', 1, LEVEL)) l
FROM dual
CONNECT BY LEVEL <= regexp_count(v_prj_id, ',') + 1
) LOOP
v_sql := v_sql || 'select '''|| i.l ||''' AS "PRJ_ID", EVENT, email,modified_by,modified from '
|| i.l || '.TableX UNION ALL ' || chr(10) ;
END LOOP;
v_sql2 := RTRIM(v_sql, 'UNION ALL ' || chr(10) ) || ';';
EXECUTE IMMEDIATE v_sql2 BULK COLLECT INTO MYROW;
DBMS_OUTPUT.PUT_LINE(MYROW.XXX);
END;
/
解决方案
您的代码有两个明显的问题:
- 目标集合的投影与动态查询的投影不匹配。它们必须具有相同数量(和数据类型)的列。
- 该
dbms_output
语句将失败,因为 MYROW 是一个集合,并且put_line()
只接受标量值。
组装查询的循环也很笨拙。我们可以使用 PL/SQL 集合来使其更整洁。
declare
-- record type to match projection of required output
type r is record (
prj_id varchar2(30)
, event PRJA.TableX.event%type
, email PRJA.TableX.email%type
, modified_by PRJA.TableX.modified_by%type
, modified PRJA.TableX.modified%type
);
TYPE T IS TABLE OF R;
MYROWS T; -- plural because it's a table not a record variable
v_sql varchar2(32767);
-- collection of schemas to query ...
v_prj_ids sys.dbms_debug_vc2coll := sys.dbms_debug_vc2coll('PRJA','PRJB','PRJC','PRJD');
BEGIN
FOR i IN 1 .. v_prj_ids.count() LOOP
if i > 1 then
v_sql := v_sql || chr(10) || ' UNION ALL ' || chr(10)
end if
v_sql := v_sql || 'select '''|| v_prj_ids(i) ||''' AS "PRJ_ID", EVENT, email,modified_by,modified from '
|| v_prj_ids(i) || '.TableX ' ;
END LOOP;
EXECUTE IMMEDIATE v_sql2 BULK COLLECT INTO MYROWS;
DBMS_OUTPUT.PUT_LINE(' number of records =' || MYROWS.count());
END;
/
注意:未测试,因为我无权访问多用户环境
推荐阅读
- botframework - .BOT 文件未部署到 Azure Bot Service v4
- swift - 总结一个数组中的独特项目组合 swift 4
- matlab - 为什么 webread 失败但 urlread 有效?
- python - 为什么我的 Python 代码显示错误的结果?
- node.js - Firebase - 验证会话 cookie 或 idtoken
- c++ - 类继承中的 C++ 变量不起作用
- bash - bash - 从不同目录运行时获取脚本所在的目录
- xcode - 未声明标识符的使用:Xcode、C 代码
- ssas - 从今天的 SSAS 时间维度中选择一年中的某一天
- php - PHP 将多维数组分割成更小的数组