Question

oracle的存储过程中可以用with查询吗

Answer 1

可以使用的，with相当于建了个临时表。但是只能结合游标使用。
比如
procedure TEST(OUT_RESULT out MYTYPE) is
begin
open cur for with emp as (select * from scott.emp e where e.empno=7499)
select * from e;

Answer 2

这么玩。

Sql代码
procedure COLLECT_RESULT(OUT_RESULT out MYTYPE) is
begin
open OUT_RESULT for with Q1 as(
select 3 + 5 as S
from DUAL), Q2 as (select 3 * 5 as M from DUAL), Q3 as (select S,
M,
S + M,
S * M
from Q1,
Q2)
select * from Q3;
end COLLECT_RESULT;