稍微清理一下代码，您可以执行以下操作：

begin
  for r in (
    select 'insert into table_name
select ''' || COLUMN_NAME || ''' counter_name, pre.country, pre.city,
  pre.stddev_col_name, pre.cnt_wk, post.counter_last_day, pre.var_wk
from (
  select country, city, stddev(nvl(' || COLUMN_NAME || ', 0)) stddev_col_name, 
    variance(nvl(' || COLUMN_NAME || ', 0)) var_wk,
    avg(nvl(' || COLUMN_NAME || ',0)) cnt_wk,
    count(*)
  from ' || OWNER || '.' || TABLE_NAME ||'
  where datetime >= trunc(sysdate) - 14
  and to_char(datetime, ''FMDAY'', ''NLS_DATE_LANGUAGE=ENGLISH'') NOT IN (''FRIDAY'', ''SATURDAY'')
  group by country, city
) pre
left outer join (
  select country, city, sum(nvl(' || COLUMN_NAME || ', 0)) counter_last_day 
  from ' || OWNER || '.' || TABLE_NAME || '
  where datetime >= trunc(sysdate) - 1
  group by country, city
) post
on pre.country = post.country
and pre.city = post.city
where counter_last_day not between pre.cnt_wk-(3*(pre.stddev_col_name))
and pre.cnt_wk+(3*(pre.stddev_col_name))' as stmt
from all_tab_cols where owner = 'HEXA'
and table_name in ('Z_STATS', 'Y_STATS', 'X_STATS')
and nullable = 'Y'
and rownum <= 10
  ) loop

    dbms_output.put_line(r.stmt);
    execute immediate r.stmt;

  end loop;
end;
/

我去掉了一些不必要的部分，简化了日检查，同时添加了一个用于该部分的显式语言。
它将生成并运行如下语句：

insert into table_name
select 'ABC' counter_name, pre.country, pre.city,
  pre.stddev_col_name, pre.cnt_wk, post.counter_last_day, pre.var_wk
from (
  select country, city, stddev(nvl(ABC, 0)) stddev_col_name, 
    variance(nvl(ABC, 0)) var_wk,
    avg(nvl(ABC,0)) cnt_wk,
    count(*)
  from HEXA.Z_STATS
  where datetime >= trunc(sysdate) - 14
  and to_char(datetime, 'FMDAY', 'NLS_DATE_LANGUAGE=ENGLISH') NOT IN ('FRIDAY', 'SATURDAY')
  group by country, city
) pre
left outer join (
  select country, city, sum(nvl(ABC, 0)) counter_last_day 
  from HEXA.Z_STATS
  where datetime >= trunc(sysdate) - 1
  group by country, city
) post
on pre.country = post.country
and pre.city = post.city
where counter_last_day not between pre.cnt_wk-(3*(pre.stddev_col_name))
and pre.cnt_wk+(3*(pre.stddev_col_name))

... 哪里 z_stats 有一个名为 `` .
db<>摆弄（用 user 而不是 HEXA 所以它运行）。
我拿出来的一件东西是 UPPER(TABLE_NAME) ; 只有当您的表名是大小写混合的，并且是用带引号的标识符创建的时，才需要这样做。如果它们是代码中的其他引用，则必须引用它们，因此可能是噪声。您可能需要添加一个数据类型检查，以便只选取数字列。

除了你的陈述很难阅读之外，它也是毫无意义的。我假设您尝试运行类似以下的过程：

DECLARE
    sqlstr VARCHAR2(10000);

    CURSOR SourceTables IS
    SELECT OWNER, TABLE_NAME, COLUMN_NAME
    FROM ALL_TAB_COLS 
    WHERE OWNER = 'HEXA' 
        AND TABLE_NAME IN ('Z_STATS','Y_STATS','X_STATS') 
        AND NULLABLE = 'Y' 
        AND ROWNUM <= 10;

BEGIN

    FOR aTable IN SourceTables LOOP
        sqlstr := 
            'INSERT INTO table_name 
            WITH pre AS (
                SELECT country, city,
                    STDDEV(NVL( '||aTable.COLUMN_NAME||',0)) stddev_col_name,  
                    VARIANCE(NVL('||aTable.COLUMN_NAME||',0)) var_wk, 
                    AVG(NVL('||aTable.COLUMN_NAME||',0)) cnt_wk, 
                    COUNT(*) AS N
                FROM '||aTable.OWNER||'.'||aTable.TABLE_NAME||'
                WHERE datetime >= TRUNC(SYSDATE)-14 
                    AND TO_CHAR(datetime,''DY'', ''NLS_DATE_LANGUAGE = American'') NOT IN (''FRI'',''SAT'')
                GROUP BY country, city),
            post AS (
                SELECT country, city,
                    SUM(NVL('||aTable.OLUMN_NAME||',0)) counter_last_day  
                FROM  '||aTable.OWNER||'.'||aTable.TABLE_NAME||' 
                WHERE datetime >= TRUNC(SYSDATE)-1 
                GROUP BY country, city)
            SELECT '||aTable.COLUMN_NAME||' COUNTER_NAME, 
                pre.country,
                pre.city, 
                pre.stddev_col_name, 
                pre.cnt_wk,
                post.counter_last_day,
                var_wk
            FROM pre
                LEFT OUTER JOIN post ON  pre.country = post.country AND pre.city = post.city 
            WHERE counter_last_day NOT BETWEEN pre.cnt_wk-(3*(pre.stddev_col_name)) AND pre.cnt_wk+(3*(pre.stddev_col_name))';

        EXECUTE IMMEDIATE sqlstr;
    END LOOP;
END;

这个查询没有多大意义，而且很可能pl/sql无法即时工作，但我希望您了解如何编写它。
你可以看看开窗条款。应该可以在没有子查询的情况下运行查询。cte公司。可能与此类似：

SELECT
    country, 
    city,
    STDDEV(COLUMN_NAME) OVER (PARTITION BY  country, city ORDER BY datetime RANGE INTERVAL '14' DAY PRECEDING) stddev_col_name,  
    VARIANCE(COLUMN_NAME) OVER (PARTITION BY  country, city ORDER BY datetime RANGE INTERVAL '14' DAY PRECEDING) var_wk, 
    AVG(COLUMN_NAME) OVER (PARTITION BY country, city ORDER BY datetime RANGE INTERVAL '14' DAY PRECEDING) nt_wk, 
    SUM(COLUMN_NAME) OVER (PARTITION BY country, city ORDER BY datetime RANGE INTERVAL '1' DAY PRECEDING) counter_last_day, 
    COUNT(*) AS N
FROM TABLE_NAME
WHERE datetime >= TRUNC(SYSDATE)-14 
    AND TO_CHAR(datetime,'DY', 'NLS_DATE_LANGUAGE = American') NOT IN ('FRI','SAT');