我希望能够打印出一个查询的所有结果(应该由tablea中的pk过滤),并为tablea中的每个pk执行此操作。
这就是我目前所拥有的:

 DECLARE
    CURSOR Curs IS SELECT DISTINCT PKID FROM TABLEA;
    BEGIN
    FOR rec IN Curs
          LOOP
              EXECUTE IMMEDIATE
              'SELECT * FROM (
              SELECT cola,
              FKTABLEA,
              colc,
              lag (cold,1) OVER (ORDER BY cold) AS cold
              FROM tableB
              WHERE FKTABLEA = :1)
              WHERE colc != cold
              order by cola' using Curs.PKID;

              DBMS_OUTPUT.PUT_LINE('OUTPUT ALL RESULTS FROM THE QUERY HERE');
          END LOOP;
    END;

最佳答案

不需要使用execute immediate。只有完全手动打印所有结果的方法:

DECLARE
    CURSOR Curs IS SELECT DISTINCT PKID FROM TABLEA;
BEGIN
    FOR rec IN Curs LOOP
        FOR r IN (
            SELECT * FROM (
                SELECT cola,
                    FKTABLEA,
                    colc,
                    lag (cold,1) OVER (ORDER BY cold) AS cold
                FROM tableB
                WHERE FKTABLEA = rec.PKID)
            WHERE colc != cold
            order by cola )
        LOOP
            DBMS_OUTPUT.PUT_LINE(r.cola || ',' || r.colb || ',' || r.colc || ',' || r.cold);
        END LOOP;
    END LOOP;
END;

10-06 14:48