在 PL/SQL 中的 With 子句之后使用 for 循环 [英] Use for loop after the With Clause in PL/SQL

问题描述

我使用 PL/SQL.我正在尝试在 with 子句中定义临时表后立即进行 for 循环.但是，我在先进行 SELECT 查询时遇到错误.

Im using PL/SQL. I am trying to have a for loop right after I define my temporary tables in the with clause. However, Im getting an error to have a SELECT query first.

例如

WITH TMP1 AS (.....), TMP2 AS (......), TMP3 AS (......)

FOR R IN (SELECT DISTINCT ..... FROM TMP1 WHERE .....)
LOOP
SELECT .... FROM TMP2, TMP2 WHERE TMP2.... = R..... ....

我该怎么做?

谢谢

推荐答案

您不能在整个语句之外访问 CTE.并且您无法在 CTE 的最终 SELECT 之外访问 CTE 的各个部分.

You can't access a CTE outside of the whole statement. And you can't access individual parts of a CTE outside of the final SELECT for a CTE.

您需要将整个 CTE(包括最后的 SELECT 语句)放入游标循环中:

You need to put the whole CTE (including the final SELECT statement) into the cursor loop:

FOR R IN (WITH TMP1 AS (.....), 
               TMP2 AS (......), 
               TMP3 AS (......)
          SELECT DISTINCT ..... 
          FROM TMP1 
             JOIN temp2 ON ... 
             JOIN temp3 ON ... 
          WHERE .....)
LOOP
   -- here goes the code that processes each row of the query
END LOOP;

这篇关于在 PL/SQL 中的 With 子句之后使用 for 循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！