oracle 存储过程中with ...as问题，急！！！

Question

我在写oracle存储中遇到以下问题：
begin
with  t1 as(...
）,
t2 as（select ...from t1
),
t3 as(select ...from t2
)
open cur1 for select ...from t3;
end;
cur1是我声明的一个动态输出游标，编译不通过，想知道oracle能不能这么用啊，我的查询语句里嵌套太深oracle

Answer 1

没见过这么用with as，嵌套深你可以动态建临时表啊

Answer 2

CREATE OR REPLACE PROCEDURE sp_statistic_gyjsydgyqktjb(startDate IN Date,endDate IN Date,cur1 out test.cursorType)
AS
BEGIN
 with national_jidu_table as(
select 行政区划
,土地来源,价款租金
,(case  to_char(procab) when  'XYCR' then 'CR' else to_char(procab) end) as procab
,是否招拍挂 
,nvl((case to_char(土地来源) when  '新增' then 宗地面积 end),0) as 新增面积
,nvl((case when 土地来源 in('存量','存量(新增)') then 宗地面积 end),0) as 存量面积
from 国有建设用地供应情况表
where  状态>=fn_getDataStatus() and 土地来源 in ('新增','存量','存量(新增)')
and (批复日期 between startDate and endDate )
--group by 行政区划
),
  nationalIndictor_year_table as(
select 行政区划
,nvl(sum(新增面积),0)+nvl(sum(存量面积),0) as 计划面积 
,sum(新增面积) as 计划新增
,sum(存量面积) as 计划存量
from 建设用地供应指标表
where  周期类型=1 and 周期=to_char(startDate,'YYYY') 
group by 行政区划
),
  subNational_jidu_table as(
select 行政区划
---供地类型:划拨 、 出让 、 招拍挂 、 租赁 --
,procab,土地来源,价款租金,是否招拍挂
,(case procab when 'hb' then 新增面积 end) as 划拨_新增
,(case procab when 'hb' then 存量面积 end) as 划拨_存量
,(case procab when 'cr' then 新增面积 end) as 出让_新增
,(case procab when 'cr' then 存量面积 end) as 出让_存量
,(case procab when 'cr' then case 是否招拍挂 when 1 then 新增面积 end end) as 招拍挂_新增
,(case procab when 'cr' then case 是否招拍挂 when 1 then 存量面积 end end) as 招拍挂_存量
,(case procab when 'zl' then 新增面积 end) as 租赁_新增
,(case procab when 'zl' then 存量面积 end) as 租赁_存量

from national_jidu_table
--where
),
  tprocessTable as(
select  xzqhinfo.VIEWID as 行政区划 ,计划面积,计划新增,计划存量 ,宗数,面积
,新增面积,存量面积 ,划拨宗数,划拨面积 
,划拨_新增,划拨_存量 

,出让宗数,出让面积
,出让_新增,出让_存量,出让价款 

,招拍挂宗数,招拍挂面积 
,招拍挂_新增,招拍挂_存量,招拍挂价款

,租赁宗数,租赁面积
,租赁_新增,租赁_存量,租赁租金 from xzqhinfo 
left join nationalIndictor_year_table 
on nationalIndictor_year_table.行政区划=xzqhinfo.viewid
  
   left join 
(
select  行政区划
,count(*) as 宗数
,(nvl(sum(新增面积),0) + nvl(sum(存量面积),0)) 面积
,sum(新增面积) 新增面积,sum(存量面积) 存量面积
from  national_jidu_table
group by 行政区划
) a 
on xzqhinfo.viewid=a.行政区划 

LEFT JOIN (
select  行政区划
,count(*) 划拨宗数
,(nvl(sum(新增面积),0) + nvl(sum(存量面积),0)) 划拨面积 
,sum(新增面积) 划拨_新增
,sum(存量面积) 划拨_存量 
from  national_jidu_table
WHERE procab='hb'
group by 行政区划
) b
ON xzqhinfo.viewid=b.行政区划 

LEFT JOIN (
select  行政区划
,count(*) 出让宗数
,(nvl(sum(新增面积),0) + nvl(sum(存量面积),0)) 出让面积
,sum(新增面积) 出让_新增
,sum(存量面积) 出让_存量
,sum(价款租金) 出让价款  
from  national_jidu_table
WHERE procab='cr' --AND 是否招拍挂=0
group by 行政区划
) c
ON xzqhinfo.viewid=c.行政区划 

LEFT JOIN (
select  行政区划
,count(*) 招拍挂宗数
,(nvl(sum(新增面积),0) + nvl(sum(存量面积),0)) 招拍挂面积 
,sum(新增面积) 招拍挂_新增
,sum(存量面积) 招拍挂_存量
,sum(价款租金) 招拍挂价款
from  national_jidu_table
WHERE procab='cr' AND 是否招拍挂=1
group by 行政区划
) d
ON xzqhinfo.viewid=d.行政区划 

LEFT JOIN (
select  行政区划
,count(*) 租赁宗数
,(nvl(sum(新增面积),0) + nvl(sum(存量面积),0))  租赁面积
,sum(新增面积) 租赁_新增
,sum(存量面积) 租赁_存量
,sum(价款租金) 租赁租金
from  national_jidu_table
WHERE procab='zl'
group by 行政区划
) e
ON xzqhinfo.viewid=e.行政区划 

),
  processTable as (
select  t.*,viewid 
FROM (
select x.name as 行政区划
,sum(计划面积)/10000 计划面积
,sum(计划新增)/10000 计划新增
,sum(计划存量)/10000 计划存量 ,sum(宗数) 宗数,sum(面积)/10000 面积
,sum(新增面积)/10000 新增面积,sum(存量面积)/10000 存量面积 ,sum(划拨宗数) 划拨宗数,sum(划拨面积)/10000 划拨面积 
,sum(划拨_新增)/10000 划拨_新增,sum(划拨_存量)/10000 划拨_存量 

,sum(出让宗数) 出让宗数,sum(出让面积)/10000 出让面积
,sum(出让_新增)/10000 出让_新增,sum(出让_存量)/10000 出让_存量
,sum(出让价款)/10000 出让价款 

,sum(招拍挂宗数) 招拍挂宗数,sum(招拍挂面积)/10000 招拍挂面积 
,sum(招拍挂_新增)/10000 招拍挂_新增,sum(招拍挂_存量)/10000 招拍挂_存量
,sum(招拍挂价款)/10000 招拍挂价款

,sum(租赁宗数) 租赁宗数,sum(租赁面积)/10000 租赁面积 
,sum(租赁_新增)/10000 租赁_新增,sum(租赁_存量)/10000 租赁_存量
,sum(租赁租金)/10000 租赁租金
from tprocessTable
right join xzqhinfo x
on x.viewid=tprocessTable.行政区划
group by x.name 
) t 
RIGHT join xzqhinfo xx on xx.name=t.行政区划 
where xx.issbj=0 
order by xx.viewid
),
  nonsbj_processTable as (
select  t.*,viewid 
FROM (
select x.name as 行政区划
,sum(计划面积)/10000 计划面积
,sum(计划新增)/10000 计划新增
,sum(计划存量)/10000 计划存量 ,sum(宗数) 宗数,sum(面积)/10000 面积
,sum(新增面积)/10000 新增面积,sum(存量面积)/10000 存量面积 ,sum(划拨宗数) 划拨宗数,sum(划拨面积)/10000 划拨面积 
,sum(划拨_新增)/10000 划拨_新增,sum(划拨_存量)/10000 划拨_存量 

,sum(出让宗数) 出让宗数,sum(出让面积)/10000 出让面积
,sum(出让_新增)/10000 出让_新增,sum(出让_存量)/10000 出让_存量
,sum(出让价款)/10000 出让价款 

,sum(招拍挂宗数) 招拍挂宗数,sum(招拍挂面积)/10000 招拍挂面积 
,sum(招拍挂_新增)/10000 招拍挂_新增,sum(招拍挂_存量)/10000 招拍挂_存量
,sum(招拍挂价款)/10000 招拍挂价款

,sum(租赁宗数) 租赁宗数,sum(租赁面积)/10000 租赁面积 
,sum(租赁_新增)/10000 租赁_新增,sum(租赁_存量)/10000 租赁_存量
,sum(租赁租金)/10000 租赁租金
from tprocessTable
right join xzqhinfo x
on x.viewid=tprocessTable.行政区划
WHERE x.issbj = 0
group by x.name 
) t 
RIGHT join xzqhinfo xx on xx.name=t.行政区划 
where xx.issbj=0 
order by xx.viewid
),
  sbj_processTable as (
select  t.*,viewid 
FROM (
select x.name as 行政区划
,sum(计划面积)/10000 计划面积
,sum(计划新增)/10000 计划新增
,sum(计划存量)/10000 计划存量 ,sum(宗数) 宗数,sum(面积)/10000 面积
,sum(新增面积)/10000 新增面积,sum(存量面积)/10000 存量面积 ,sum(划拨宗数) 划拨宗数,sum(划拨面积)/10000 划拨面积 
,sum(划拨_新增)/10000 划拨_新增,sum(划拨_存量)/10000 划拨_存量 

,sum(出让宗数) 出让宗数,sum(出让面积)/10000 出让面积
,sum(出让_新增)/10000 出让_新增,sum(出让_存量)/10000 出让_存量
,sum(出让价款)/10000 出让价款 

,sum(招拍挂宗数) 招拍挂宗数,sum(招拍挂面积)/10000 招拍挂面积 
,sum(招拍挂_新增)/10000 招拍挂_新增,sum(招拍挂_存量)/10000 招拍挂_存量
,sum(招拍挂价款)/10000 招拍挂价款

,sum(租赁宗数) 租赁宗数,sum(租赁面积)/10000 租赁面积 
,sum(租赁_新增)/10000 租赁_新增,sum(租赁_存量)/10000 租赁_存量
,sum(租赁租金)/10000 租赁租金
from tprocessTable
right join xzqhinfo x
on x.viewid=tprocessTable.行政区划
WHERE x.issbj = 1
group by x.name 
) t 
RIGHT join xzqhinfo xx on xx.name=t.行政区划 
where xx.issbj=1
order by xx.viewid
)
  /***start ****查询数据*/
--所有区统计合计
  open  cur1 for 
select '合计' as 行政区划 ,cast(sum(计划面积) as decimal(38,2)) 计划面积
,cast(sum(计划新增) as decimal(38,2)) 计划新增
,cast(sum(计划存量) as decimal(38,2)) 计划存量 ,sum(宗数) 宗数,cast(sum(面积) as decimal(38,2)) 面积
,cast(sum(新增面积) as decimal(38,2)) 新增面积,cast(sum(存量面积) as decimal(38,2)) 存量面积 ,sum(划拨宗数) 划拨宗数,cast(sum(划拨面积)as decimal(38,2)) 划拨面积 
,cast(sum(划拨_新增) as decimal(38,2)) 划拨_新增,cast(sum(划拨_存量) as decimal(38,2)) 划拨_存量 

,sum(出让宗数) 出让宗数,cast(sum(出让面积) as decimal(38,2)) 出让面积
,cast(sum(出让_新增) as decimal(38,2)) 出让_新增,cast(sum(出让_存量) as decimal(38,2)) 出让_存量
,cast(sum(出让价款) as decimal(38,2)) 出让价款 

,sum(招拍挂宗数) 招拍挂宗数,cast(sum(招拍挂面积) as decimal(38,2)) 招拍挂面积 
,cast(sum(招拍挂_新增) as decimal(38,2)) 招拍挂_新增,cast(sum(招拍挂_存量) as decimal(38,2)) 招拍挂_存量
,cast(sum(招拍挂价款) as decimal(38,2)) 招拍挂价款

,sum(租赁宗数) 租赁宗数,cast(sum(租赁面积) as decimal(38,2)) 租赁面积 
,cast(sum(租赁_新增) as decimal(38,2)) 租赁_新增,cast(sum(租赁_存量) as decimal(38,2)) 租赁_存量
,cast(sum(租赁租金) as decimal(38,2)) 租赁租金 --add by zhangwei 20120516 匹配中间表年度与季度
,to_char(startDate,'YYYY') as 年度,(to_char(startDate,'MM')-1)/3+1 as 季度,to_char(startDate,'MM') as 月,'00' as xzqh
from processTable 
  
  union all  --所有区统计
select 行政区划
,cast(计划面积 as decimal(38,2)) 计划面积
,cast(计划新增 as decimal(38,2)) 计划新增
,cast(计划存量 as decimal(38,2)) 计划存量 ,宗数,cast(面积 as decimal(38,2)) 面积
,cast(新增面积 as decimal(38,2)) 新增面积,cast(存量面积 as decimal(38,2)) 存量面积 ,划拨宗数,cast(划拨面积 as decimal(38,2)) 划拨面积
,cast(划拨_新增 as decimal(38,2)) 划拨_新增,cast(划拨_存量 as decimal(38,2)) 划拨_存量

,出让宗数,cast(出让面积 as decimal(38,2)) 出让面积
,cast(出让_新增 as decimal(38,2)) 出让_新增,cast(出让_存量 as decimal(38,2)) 出让_存量,cast(出让价款 as decimal(38,2)) 出让价款

,招拍挂宗数,cast(招拍挂面积 as decimal(38,2)) 招拍挂面积
,cast(招拍挂_新增 as decimal(38,2)) 招拍挂_新增,cast(招拍挂_存量 as decimal(38,2)) 招拍挂_存量,cast(招拍挂价款 as decimal(38,2)) 招拍挂价款

,租赁宗数,cast(租赁面积 as decimal(38,2))
,cast(租赁_新增 as decimal(38,2)) 租赁_新增,cast(租赁_存量 as decimal(38,2)) 租赁_存量,cast(租赁租金 as decimal(38,2)) 租赁租金 --add by zhangwei 20120516 匹配中间表年度与季度
,to_char(startDate,'YYYY') as 年度,(to_char(startDate,'MM')-1)/3+1  as 季度,to_char(startDate,'MM') as 月,viewid as xzqh
from nonsbj_processTable
  
   UNION ALL

--市本级
select 行政区划
,cast(计划面积 as decimal(38,2)) 计划面积
,cast(计划新增 as decimal(38,2)) 计划新增
,cast(计划存量 as decimal(38,2)) 计划存量 ,宗数,cast(面积 as decimal(38,2)) 面积
,cast(新增面积 as decimal(38,2)) 新增面积,cast(存量面积 as decimal(38,2)) 存量面积 ,划拨宗数,cast(划拨面积 as decimal(38,2)) 划拨面积
,cast(划拨_新增 as decimal(38,2)) 划拨_新增,cast(划拨_存量 as decimal(38,2)) 划拨_存量

,出让宗数,cast(出让面积 as decimal(38,2)) 出让面积
,cast(出让_新增 as decimal(38,2)) 出让_新增,cast(出让_存量 as decimal(38,2)) 出让_存量,cast(出让价款 as decimal(38,2)) 出让价款

,招拍挂宗数,cast(招拍挂面积 as decimal(38,2)) 招拍挂面积
,cast(招拍挂_新增 as decimal(38,2)) 招拍挂_新增,cast(招拍挂_存量 as decimal(38,2)) 招拍挂_存量,cast(招拍挂价款 as decimal(38,2)) 招拍挂价款

,租赁宗数,cast(租赁面积 as decimal(38,2))
,cast(租赁_新增 as decimal(38,2)) 租赁_新增,cast(租赁_存量 as decimal(38,2)) 租赁_存量,cast(租赁租金 as decimal(38,2)) 租赁租金 --add by zhangwei 20120516 匹配中间表年度与季度
 ,to_char(startDate,'YYYY') as 年度,(to_char(startDate,'MM')-1)/3+1  as 季度,to_char(startDate,'MM') as 月,viewid as xzqh
from sbj_processTable;

END; 
 报错：ORA-00928 missing from keyword 报错行数（open cur1 for）

Answer 3

CREATE OR REPLACE PROCEDURE LEON_TEST IS
  CUR1 SYS_REFCURSOR;
  NUM  NUMBER;
BEGIN
  OPEN CUR1 FOR WITH T1 AS(
    SELECT 1 A
      FROM DUAL
    UNION ALL
    SELECT 4 A
      FROM DUAL
    UNION ALL
    SELECT 5 A
      FROM DUAL), T2 AS (SELECT * FROM T1), T3 AS (SELECT * FROM T2)
    SELECT * FROM T3;  FETCH CUR1
    INTO NUM;
  WHILE CUR1%FOUND LOOP
  
    FETCH CUR1
      INTO NUM;
    DBMS_OUTPUT.PUT_LINE(NUM);
  END LOOP;
  CLOSE CUR1;
END LEON_TEST;
这个是写的一个测试的PROCEDURE,能成功执行，你可以试试，代码太多了，没法看啊

Answer 4

修改了一下：
CREATE OR REPLACE PROCEDURE LEON_TEST IS
  CUR1 SYS_REFCURSOR;
  NUM  NUMBER;
BEGIN
  OPEN CUR1 FOR WITH T1 AS(
    SELECT 1 A
      FROM DUAL
    UNION ALL
    SELECT 4 A
      FROM DUAL
    UNION ALL
    SELECT 5 A
      FROM DUAL), T2 AS (SELECT * FROM T1 WHERE A < 5), T3 AS (SELECT *
                                                                 FROM T2)
    SELECT * FROM T3;
  LOOP
    FETCH CUR1
      INTO NUM;
    EXIT WHEN CUR1%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(NUM);
  END LOOP;
  CLOSE CUR1;
END LEON_TEST;结果
1
4

Answer 5

@zlloct，按照您的思路，成功了啊，只需要把open cur1 for放到begin后面，with ..as前面就ok了，其他都不变。问题已解决，非常感谢！！

Answer 6

with...as..select是一个整体，open cur1 for with..as...  select就ok

Answer 7

我是将sqlserver的存储过程移到oracle，sqlserver不需要用游标，直接查询就ok