oracle PL/SQL - dbms 输出立即执行的结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6166513/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PL/SQL - dbms output the result of an execute immediate
提问by toop
I would like to be able to print out all results for a query (that should be filtered by the PK from TableA) and do this for each PK in TABLEA. This is what i have so far:
我希望能够打印出查询的所有结果（应该由 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;
回答by Codo
There is no need to use EXECUTE IMMEDIATE. And there's only the fully manual way of printing all the results:
无需使用 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;
