mysql - Oracle中动态查询执行的表名串联

Question

我有 30 个表，即 secondary_data_0、second_data_1、.......、second_data_29。我将这些表与另一个表 first_data 逐一连接，并将结果存储在 Mysql 存储过程中的临时表中(如下所示)。有人可以告诉我如何在 Oracle 程序中执行此操作，特别是 SET @s = CONCAT("INSERT INTO firstTemp SELECT a.ID,b.CLSNO,b.FEES,b.FEES_T,b.FEES_DT FROM first_data a, second_data_",i," b WHERE a.CLASS_NO = b.CLSNO AND (b.FEES < a.FEES_AMOUNT) AND b.FEES_DT BETWEEN DATE('2017-11-20') AND DATE('2017-12-25')");部分？

    SET i = 1;
CREATE TEMPORARY TABLE firstTemp
SELECT a.ID,b.CLSNO,b.FEES,b.FEES_T,b.FEES_DT FROM first_data a, second_data_0 b WHERE a.CLASS_NO = b.CLSNO AND (b.FEES < a.FEES_AMOUNT) AND b.FEES_DT BETWEEN DATE('2017-11-20') AND DATE('2017-12-25');
label1: WHILE i < 30 DO
    SET @s = CONCAT("INSERT INTO firstTemp SELECT a.ID,b.CLSNO,b.FEES,b.FEES_T,b.FEES_DT FROM first_data a, second_data_",i," b WHERE a.CLASS_NO = b.CLSNO AND (b.FEES < a.FEES_AMOUNT) AND b.FEES_DT BETWEEN DATE('2017-11-20') AND DATE('2017-12-25')");
    PREPARE stmt1 FROM @s;
    EXECUTE stmt1;
    DEALLOCATE PREPARE stmt1; 
    SET i = i + 1;
END WHILE label1;

Answer 1

开始之前的重要提示:不要对 JOINS 使用逗号 (a,b) 语法，而使用 JOIN ON 语法。

您可以在 PL/SQL 中使用数字 FOR LOOP 来达到您的目的。

BEGIN
       EXECUTE IMMEDIATE
          '
    CREATE GLOBAL TEMPORARY TABLE firstTemp

    AS
       SELECT a.ID,
              b.CLSNO,
              b.FEES,
              b.FEES_T,
              b.FEES_DT
         FROM first_data a
              JOIN second_data_0 b
                 ON a.CLASS_NO = b.CLSNO AND b.FEES < a.FEES_AMOUNT
        WHERE b.FEES_DT BETWEEN DATE ''2017-11-20'' AND DATE ''2017-12-25''';

       FOR i IN 1 .. 30
       LOOP
          EXECUTE IMMEDIATE
                '
    INSERT INTO firstTemp
       SELECT a.ID,
              b.CLSNO,
              b.FEES,
              b.FEES_T,
              b.FEES_DT
         FROM first_data a JOIN second_data_'
         || i
         || ' b ON a.CLASS_NO = b.CLSNO AND b.FEES < a.FEES_AMOUNT WHERE b.FEES_DT
BETWEEN DATE ''2017-11-20'' AND DATE ''2017-12-25''';
   END LOOP;
END;