如何在Oracle pl/sql电子邮件发送中运行For循环时使用变量作为表名

Question

我无法编译这段Oracle代码，因为编译器报告“PL / SQL：ORA-00942：表或视图不存在”。

Oracle表存在，但此过程必须根据“Order_ID”参数将表名传递给For Loop过程。我正在使用表存在的模式下工作，因此我不会指定模式名称。

例如：TEMP_TBL_123存在于数据库中，并通过传递123的order_ID来尝试使用变量TMP_TBL_NM来保存表名“TEMP_TBL_123”。

.................................................

CREATE OR REPLACE PROCEDURE EMAIL_DEPT_BLAST_TEST (SUBJECT VARCHAR2,MAIL_FROM VARCHAR2, MAIL_TO VARCHAR2,
                                               L_MESSAGE VARCHAR2, L_MESSAGE2 VARCHAR2, ORDER_ID NUMBER)
IS
MAIL_HOST VARCHAR2(30):='XX.XX.XX.XX';
MAIL_CONN UTL_SMTP.CONNECTION;
TMP_TBL_NM VARCHAR2(30);

BEGIN

TMP_TBL_NM := 'TEMP_TBL_' || ORDER_ID;

MAIL_CONN := UTL_SMTP.OPEN_CONNECTION(MAIL_HOST, 25);
UTL_SMTP.HELO(MAIL_CONN, MAIL_HOST);
UTL_SMTP.MAIL(MAIL_CONN,'[e-mail]');
UTL_SMTP.RCPT(MAIL_CONN, MAIL_TO);
UTL_SMTP.OPEN_DATA(MAIL_CONN);
UTL_SMTP.WRITE_DATA(MAIL_CONN, 'Date: '||to_char(trunc(SYSDATE))||utl_tcp.crlf);
UTL_SMTP.WRITE_DATA(MAIL_CONN, 'From: '|| mail_from ||utl_tcp.crlf);
UTL_SMTP.WRITE_DATA(MAIL_CONN, 'To: '|| mail_to || utl_tcp.crlf);
UTL_SMTP.WRITE_DATA(MAIL_CONN, 'Subject: '||subject||utl_tcp.crlf);
UTL_SMTP.WRITE_DATA(MAIL_CONN, UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(MAIL_CONN, ''|| L_MESSAGE || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(MAIL_CONN, UTL_TCP.CRLF);

UTL_SMTP.WRITE_DATA(MAIL_CONN, 'Order details:' || UTL_TCP.crlf);
UTL_SMTP.WRITE_DATA(MAIL_CONN, 'Quantity - Location ID - Address' || UTL_TCP.crlf);

BEGIN
    FOR I IN (SELECT LOCATION_ID, TRIM(TO_CHAR(COUNT(*),'9,999')) AS QUANTITY FROM TMP_TBL_NM GROUP BY LOCATION_ID)
    LOOP 
    UTL_SMTP.WRITE_DATA(MAIL_CONN, I.QUANTITY  || ' - ');
    UTL_SMTP.WRITE_DATA(MAIL_CONN, I.LOCATION_ID || ' ');
    UTL_SMTP.WRITE_DATA(MAIL_CONN,UTL_TCP.CRLF);
    END LOOP;
END;

UTL_SMTP.WRITE_DATA(MAIL_CONN, L_MESSAGE2 || UTL_TCP.CRLF);  

UTL_SMTP.WRITE_DATA(MAIL_CONN,UTL_TCP.CRLF);
UTL_SMTP.CLOSE_DATA(MAIL_CONN);
UTL_SMTP.QUIT(MAIL_CONN);

END SCT_CNTS_EMAIL_DEPT_BLAST_TEST;

- user1536113

Answer 1

我尝试了John的实例，但没有太好的运气。这是一个能用的示例：

DECLARE
    C SYS_REFCURSOR;
    stmt VARCHAR2(1000); 
    tmp_tbl_nm  VARCHAR2(64) := 'USER_TABLES';
    the_name    VARCHAR2(64);
BEGIN
    stmt := 'SELECT table_name  FROM ' || TMP_TBL_NM || ' ORDER BY 1';
    OPEN C FOR stmt;
    LOOP
      FETCH C INTO the_name;
      EXIT WHEN C%NOTFOUND;
      dbms_output.put_line(the_name);
    END LOOP;
END;
/
CONTINENT
COUNTRY
COUNTRYINFOIMPORT
COUNTRY_LANGUAGE
GEONAME
LANGUAGE

PL/SQL procedure successfully completed.

SQL>

我认为您无法使用EXECUTE IMMEDIATE语句返回一个游标。这个文档页面的第二段似乎表明不行。无论如何，您并不需要它，只需使用OPEN - FOR语法即可。

- DCookie

Answer 2

你遇到 PL/SQL: ORA-00942 的原因是因为在你的原始代码中，编译器正在寻找一个名为“TMP_TBL_NM”的表。这在你的模式中不存在，实际上是一个变量，它保存了你的 PL/SQL 中表的名称。

我借鉴了之前的答案，并使用了一些动态 SQL 来得出你的答案的变体。

CREATE OR REPLACE PROCEDURE EMAIL_DEPT_BLAST_TEST (SUBJECT VARCHAR2,MAIL_FROM VARCHAR2, MAIL_TO VARCHAR2,
                                               L_MESSAGE VARCHAR2, L_MESSAGE2 VARCHAR2, ORDER_ID NUMBER)
IS
MAIL_HOST VARCHAR2(30):='XX.XX.XX.XX';
MAIL_CONN UTL_SMTP.CONNECTION;
TMP_TBL_NM VARCHAR2(30);

BEGIN

TMP_TBL_NM := 'TEMP_TBL_' || ORDER_ID;

MAIL_CONN := UTL_SMTP.OPEN_CONNECTION(MAIL_HOST, 25);
UTL_SMTP.HELO(MAIL_CONN, MAIL_HOST);
UTL_SMTP.MAIL(MAIL_CONN,'[e-mail]');
UTL_SMTP.RCPT(MAIL_CONN, MAIL_TO);
UTL_SMTP.OPEN_DATA(MAIL_CONN);
UTL_SMTP.WRITE_DATA(MAIL_CONN, 'Date: '||to_char(trunc(SYSDATE))||utl_tcp.crlf);
UTL_SMTP.WRITE_DATA(MAIL_CONN, 'From: '|| mail_from ||utl_tcp.crlf);
UTL_SMTP.WRITE_DATA(MAIL_CONN, 'To: '|| mail_to || utl_tcp.crlf);
UTL_SMTP.WRITE_DATA(MAIL_CONN, 'Subject: '||subject||utl_tcp.crlf);
UTL_SMTP.WRITE_DATA(MAIL_CONN, UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(MAIL_CONN, ''|| L_MESSAGE || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(MAIL_CONN, UTL_TCP.CRLF);

UTL_SMTP.WRITE_DATA(MAIL_CONN, 'Order details:' || UTL_TCP.crlf);
UTL_SMTP.WRITE_DATA(MAIL_CONN, 'Quantity - Location ID - Address' || UTL_TCP.crlf);

TYPE ItemRec IS RECORD (
loc_id NUMBER,
qty_cnt NUMBER);

TYPE ItemSet IS TABLE OF ItemRec;
all_items ItemSet;

BEGIN

    stmt := 'SELECT LOCATION_ID, TRIM(TO_CHAR(COUNT(*),'9,999')) AS QUANTITY FROM '||TMP_TBL_NM ||' GROUP BY LOCATION_ID';
    EXECUTE IMMEDIATE stmt
    BULK COLLECT
    INTO all_items;

    FOR i IN all_items.FIRST..all_items.LAST
    LOOP
        UTL_SMTP.WRITE_DATA(MAIL_CONN, I.QUANTITY  || ' - ');
        UTL_SMTP.WRITE_DATA(MAIL_CONN, I.LOCATION_ID || ' ');
        UTL_SMTP.WRITE_DATA(MAIL_CONN,UTL_TCP.CRLF);
    END LOOP;
END;

UTL_SMTP.WRITE_DATA(MAIL_CONN, L_MESSAGE2 || UTL_TCP.CRLF);  

UTL_SMTP.WRITE_DATA(MAIL_CONN,UTL_TCP.CRLF);
UTL_SMTP.CLOSE_DATA(MAIL_CONN);
UTL_SMTP.QUIT(MAIL_CONN);

END SCT_CNTS_EMAIL_DEPT_BLAST_TEST;

- OraNob

Answer 3

将变量传递到类似这样的SQL语句的唯一方法是运行动态SQL查询。

您的代码可能如下所示：

DECLARE
    query_output SYS_REFCURSOR;
    query_statement VARCHAR2(1000); 
BEGIN
    query_statement := 'SELECT LOCATION_ID, TRIM(TO_CHAR(COUNT(*),'9,999')) AS QUANTITY FROM ' || TMP_TBL_NM || ' GROUP BY LOCATION_ID';
    execute immediate query_statement using out query_output;
    ....
END

现在您已经有了一个光标，获取内容，循环计数并执行其他操作。

- John D

---

“传递变量的唯一方法…将是动态SQL”。这并不完全正确。PL/SQL变量可以嵌入到内联SQL语句中的任何位置，就像放置字面值(如字符串或数字)一样。但在没有动态sql的情况下，无法将变量用作标识符的位置，例如表名或列名。这是因为在绑定变量值之前，SQL语句在解析时必须知道标识符名称。 - Dave Costa