时间戳

首页 > 解决方案 > 在 ORACLE 中设置多行作为结果集的“select”语句的电子邮件结果

oracle - 在 ORACLE 中设置多行作为结果集的“select”语句的电子邮件结果

问题描述

我是 ORACLE PL/SQL 世界的新手。我试图找出一种计算方法，如下所示。

假设您有一个MASTER_TABLE，如下所示：

SELECT * FROM MASTER_TABLE;

+----------+----------+------------------+-----------------------+
|  SCHEMA  | TABLE_NM | REQUIRED_COLUMNS |     TABLE_FILTER      |
+----------+----------+------------------+-----------------------+
| USER_SCH | A        | A1,A2,A3         | EXAM_DT > SYSDATE - 1 |
| USER_SCH | B        | B1,B2            | TRUNC(SYSDATE)        |
+----------+----------+------------------+-----------------------+

我想SELECT从上表生成查询，如下所示：

SELECT 'SELECT SCHEMA || '.' || TABLE_NM ||' WHERE '|| TABLE_FILTER FROM MASTER_TABLE;

显然，上述查询的结果会产生多条select语句。现在，我想执行所有此类SELECT语句并通过电子邮件发送结果集。

棘手的部分是，变量中提到的列MASTER_TABLE（即对于表'A'可以REQUIRED_COLUMNS选择3个，对于表'B'可以REQUIRED_COLUMNS选择2个-如图所示MASTER_TABLE）

我已经准备好电子邮件实用程序，它基本上将参数作为your_message并通过电子邮件发送出去。

这是我尝试过的：

 SELECT
    'SELECT '
    || replace(required_columns, ',', '||'',''||')
    || ' AS MSG_BDY'
    || ' FROM '
    || schema
    || '.'
    || table_nm
    || ' WHERE '
    || table_filter
as my_select_stmt
FROM
    master_table;

在这之后我被困住了。

你能帮帮我吗？还是有什么方法可以实现这一目标？

注意：MASTER_TABLE中提到的表可以有 1 行或更多行。

标签： oracleplsql

解决方案

我没有你的桌子，所以我用了斯科特的。

主表：

SQL> select * From master_table;

SCHEM TABL REQUIRED_COLUMN TABLE_FILTER
----- ---- --------------- ----------------------
scott emp  ename, job, sal hiredate < sysdate - 1
scott dept dname, loc      deptno = 20

SQL>

模拟您的邮寄程序的程序；我将只显示这些值。

SQL> CREATE OR REPLACE PROCEDURE p_mail (par_result IN SYS.odcivarchar2list)
  2  AS
  3  BEGIN
  4     FOR i IN par_result.FIRST .. par_result.LAST
  5     LOOP
  6        DBMS_OUTPUT.put_line (par_result (i));
  7     END LOOP;
  8  END;
  9  /

Procedure created.

SQL>

您实际需要的程序；当您编写select语句时，现在您只需运行它们。为此，请使用动态 SQL（例如execute immediate）：

SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
  2     retval  SYS.odcivarchar2list;
  3  BEGIN
  4     FOR cur_r
  5        IN (SELECT    'SELECT '
  6                   || REPLACE (required_columns, ',', '||'',''||')
  7                   || ' AS MSG_BDY'
  8                   || ' FROM '
  9                   || schema
 10                   || '.'
 11                   || table_nm
 12                   || ' WHERE '
 13                   || table_filter
 14                      AS my_select_stmt
 15              FROM master_table)
 16     LOOP
 17        EXECUTE IMMEDIATE cur_r.my_select_stmt BULK COLLECT INTO retval;
 18
 19        -- you'd call your mailing procedure here
 20        p_mail (retval);
 21     END LOOP;
 22  END;
 23  /
SMITH,CLERK,920
ALLEN,SALESMAN,1600
WARD,SALESMAN,1250
JONES,MANAGER,2975
MARTIN,SALESMAN,1250
BLAKE,MANAGER,2850
CLARK,MANAGER,2450
SCOTT,ANALYST,3000
KING,PRESIDENT,10000
TURNER,SALESMAN,1500
ADAMS,CLERK,1300
JAMES,CLERK,950
FORD,ANALYST,3000
MILLER,CLERK,1300
RESEARCH,DALLAS

PL/SQL procedure successfully completed.

SQL>

[编辑：如果您想为缺失值显示“null”怎么办？]

嗯，这是一个新的时刻——可能不是很简单。看看这是否有帮助。

为了帮助自己，我修改master_table并添加了ID列以唯一标识每一行。它将用于将所需列的列表拆分为行，应用于NVL它们，应用于CAST列（因为NVL如果数据类型不匹配会抱怨），使用listagg. 由于要做的事情很多，我将创建一个视图并使用它而不是表本身。

SQL> CREATE OR REPLACE VIEW v_master_table
  2  AS
  3       SELECT id,
  4              schema,
  5              table_nm,
  6              LISTAGG ('NVL(cast(' || col || ' as varchar2(20)), ''null'')', '||'',''||')
  7                 WITHIN GROUP (ORDER BY lvl)
  8                 required_columns,
  9              table_filter
 10         FROM (SELECT id,
 11                      schema,
 12                      table_nm,
 13                      table_filter,
 14                      COLUMN_VALUE lvl,
 15                      TRIM (REGEXP_SUBSTR (required_columns,
 16                                           '[^,]+',
 17                                           1,
 18                                           COLUMN_VALUE))
 19                         col
 20                 FROM master_table
 21                      CROSS JOIN
 22                      TABLE (
 23                         CAST (
 24                            MULTISET (
 25                                   SELECT LEVEL
 26                                     FROM DUAL
 27                               CONNECT BY LEVEL <=
 28                                               REGEXP_COUNT (required_columns,
 29                                                             ',')
 30                                             + 1) AS SYS.odcinumberlist)))
 31     GROUP BY id,
 32              schema,
 33              table_nm,
 34              table_filter;

View created.

例如，它现在看起来像这样：

SQL> select * from v_master_table where id = 2;

 ID SCHEM TABL REQUIRED_COLUMNS                                                                      TABLE_FILTER
--- ----- ---- ------------------------------------------------------------------------------------- ------------
  2 scott dept NVL(cast(dname as varchar2(20)), 'null')||','||NVL(cast(loc as varchar2(20)), 'null') deptno = 20

SQL>

邮寄程序保持不变，没有变化。

匿名 PL/SQL 块稍有更改 - 我删除了REPLACE您以前使用的视图，现在这样做了；另外，源是视图，而不是表。

SQL> alter session set nls_date_format = 'dd.mm.yyyy';

Session altered.

SQL> DECLARE
  2     retval  SYS.odcivarchar2list;
  3  BEGIN
  4     FOR cur_r
  5        IN (SELECT    'SELECT '
  6                   || required_columns
  7                   || ' AS MSG_BDY'
  8                   || ' FROM '
  9                   || schema
 10                   || '.'
 11                   || table_nm
 12                   || ' WHERE '
 13                   || table_filter
 14                      AS my_select_stmt
 15              FROM v_master_table)
 16     LOOP
 17        EXECUTE IMMEDIATE cur_r.my_select_stmt BULK COLLECT INTO retval;
 18
 19        -- you'd call your mailing procedure here
 20        p_mail (retval);
 21     END LOOP;
 22  END;
 23  /
CLARK,09.06.1981,null
KING,17.11.1981,null
MILLER,23.01.1982,null
RESEARCH,DALLAS

PL/SQL procedure successfully completed.

SQL>

推荐阅读