我希望能够打印出一个查询的所有结果(应该由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;