如何在 PL/SQL 中的 EXECUTE IMMEDIATE 语句中使用外部变量

Question

我需要使用 EXECUTE IMMEDIATE 命令编写一个查询，以对我在嵌套查询中计算的两个值求和。我想知道我是否必须使用 USING 子句或其他子句，以及如何将我创建的变量放入语句中。查询如下：

编辑：这是新查询，绑定变量仍然存在问题（Ora-01008：并非所有变量都被绑定）。你能帮我解决这个问题吗？

     EXECUTE IMMEDIATE 'SELECT sum (n_record_trovati_p) FROM (
         SELECT count(*) as n_record_trovati_p FROM od_pv_trading_day_orders partition (' || partition_current_month || ') WHERE DATETIME = :1 AND orderid = :2 AND broker = :3 
           UNION
         SELECT count(*) as n_record_trovati_p FROM od_pv_trading_day_orders partition (' || partition_previous_month || ') WHERE DATETIME = :1 AND orderid = :2 AND broker = :3 
    )' INTO n_record_trovati
    USING d_datetime, n_orderid, cur.broker;

     //old query
     sys_current_date VARCHAR2 (7);
     sys_previous_date VARCHAR2 (7);
     partition_current_month VARCHAR2 (8);
     partition_previous_month VARCHAR2 (8);
     BEGIN
     ...
     SELECT TO_CHAR(ADD_MONTHS(d_datetime,0),'yyyymm') INTO sys_current_date FROM dual;
     SELECT TO_CHAR(ADD_MONTHS(d_datetime,-1),'yyyymm') INTO sys_previous_date FROM dual;
     partition_current_month := 'P'|| sys_current_date;
     partition_previous_month := 'P'|| sys_previous_date;
     EXECUTE IMMEDIATE 'SELECT SUM (found_records) INTO ' || n_record_trovati || ' FROM (
         SELECT COUNT(*) as found_records FROM example_table PARTITION (' || partition_current_month || ') WHERE DATETIME = ' || d_datetime || ' AND orderid = ' || n_orderid || ' AND broker = ' || cur.broker || '
           UNION
         SELECT COUNT(*) as found_records FROM example_table PARTITION (' || partition_previous_month || ') WHERE DATETIME = ' || d_datetime || ' AND orderid = ' || n_orderid || ' AND broker = ' ||cur.broker || '
    )';
    ...
    end

我尝试使用 || 运算符将变量名称与执行即时字符串的其余部分分开，但弹出错误。你能告诉我我的查询有什么问题，我该如何解决它吗？谢谢

Answer 1

不要在字符串中使用INTO。它应该是

EXECUTE IMMEDIATE 'SELECT SUM (found_records)  FROM (
         SELECT COUNT(*) as found_records FROM example_table PARTITION (' || partition_current_month || ') WHERE DATETIME = ' || d_datetime || ' AND orderid = ' || n_orderid || ' AND broker = ' || cur.broker || '
           UNION
         SELECT COUNT(*) as found_records FROM example_table PARTITION (' || partition_previous_month || ') WHERE DATETIME = ' || d_datetime || ' AND orderid = ' || n_orderid || ' AND broker = ' ||cur.broker || '
    )' INTO n_record_trovati;