oracle 定义带参数的视图

1、定义包

CREATE OR REPLACE package p_view_param  is
--定义开始日期--
   function set_beginTime(beginTime varchar2) return varchar2;
   function get_beginTime return varchar2;
--定义结束日期--
   function set_endTime(endTime varchar2) return varchar2;
   function get_endTime return varchar2;
   
end p_view_param;
/

2、定义包体

create or replace package body p_view_param is  
       parambeginTime varchar2(20);  
       paramendTime varchar2(20);  
       -- Param  
       function set_beginTime(beginTime varchar2) return varchar2 is  
       begin  
         parambeginTime:=beginTime;  
         return beginTime;  
        end;  
  
       function get_beginTime return varchar2 is  
       begin  
         return parambeginTime;  
       end;  
       -- Type  
       function set_endTime(endTime varchar2) return varchar2 is  
       begin  
         paramendTime:=endTime;  
         return endTime;  
        end;  
  
       function get_endTime return varchar2 is  
       begin  
         return paramendTime;  
       end;  
    
   end p_view_param; 

3、定义参数视图

create or replace view p_view_num as  
select mxmc,max(xssl) as xssl,max(mwje) as mwje,max(qtje) as qtje,max(mwje +nvl(qtje,0)) as hjje ,round((max(mwje)+max(nvl(qtje,0)))/max(xssl),2) as pjje, max(mwzs) as mwzs,max(mwye) as mwye,max(azyw) as azyw,max(qcyw) as qcyw from
(
  select mxmc
           , (select count(*) from ywdjb where t1.mxmc=mxmc and to_char(rq,'yyyy-mm-dd')>=p_view_param.get_beginTime() and to_char(rq,'yyyy-mm-dd')<=p_view_param.get_endTime()  )  as xssl --销售数量
           ,(select sum(fyb_mx.ssje) from fyb_mx  ,ywdjb, fmdm  where  ywdjb.djh=fyb_mx.ywdjh and fmdm.FMDM=fyb_mx.fmdm and ywdjb.mxmc=t1.mxmc and fyb_mx.zxflag='0'
                and to_char(ywdjb.rq,'yyyy-mm-dd')>=p_view_param.get_beginTime() and to_char(ywdjb.rq,'yyyy-mm-dd')<=p_view_param.get_endTime()  and  fmdm.mwje_flag='1'    )  as mwje  -- 墓位金额
           ,(select sum(fyb_mx.ssje) from fyb_mx  ,ywdjb, fmdm  where  ywdjb.djh=fyb_mx.ywdjh and fmdm.FMDM=fyb_mx.fmdm and ywdjb.mxmc=t1.mxmc and fyb_mx.zxflag='0'
                and to_char(ywdjb.rq,'yyyy-mm-dd')>=p_view_param.get_beginTime() and to_char(ywdjb.rq,'yyyy-mm-dd')<=p_view_param.get_endTime() and  (fmdm.mwje_flag<>'1' or fmdm.mwje_flag is null)     )  as qtje -- 其他金额
          , (select count(*) from mwdmxx  where mxdmmc=t1.mxmc) as mwzs --墓位总数
          , (select count(*) from mwdmxx  where mxdmmc=t1.mxmc and mwzt='0') as mwye --待售墓位数量
          , ( select count(*) from wrxxb,ywdjb where  ywdjb.mxmc=t1.mxmc  and wrxxb.djh=ywdjb.djh and to_char(azrq,'yyyy-mm-dd')>=p_view_param.get_beginTime() and to_char(azrq,'yyyy-mm-dd')<=p_view_param.get_endTime() ) as azyw -- 安葬数量
          , ( select count(*) from qtywb where    qtywb.mxmc=t1.mxmc and ywdm='06'  and  azfsdm='02'  and to_char(rq,'yyyy-mm-dd')>=p_view_param.get_beginTime() and to_char(rq,'yyyy-mm-dd')<=p_view_param.get_endTime() ) as qcyw -- 迁出数量
  from
  (
  select distinct mxmc   from ywdjb
      where  azfsdm='02' and to_char(rq,'yyyy-mm-dd')>=p_view_param.get_beginTime() and to_char(rq,'yyyy-mm-dd')<=p_view_param.get_endTime() and zxflag<>'1'
  )  t1
 
  ) group by  mxmc
union all  --加入退墓和迁出
select mxmc,max(xssl) as xssl,max(mwje) as mwje,max(qtje) as qtje,max(mwje +nvl(qtje,0)) as hjje ,0 as pjje, max(mwzs) as mwzs,max(mwye) as mwye,max(azyw) as azyw,max(qcyw) as qcyw from
(
  select mxmc
           , (select count(*) from qtywb where t1.mxmc=mxmc and to_char(rq,'yyyy-mm-dd')>=p_view_param.get_beginTime() and to_char(rq,'yyyy-mm-dd')<=p_view_param.get_endTime() and (ywdm='06' or ywdm='12')  ) * -1  as xssl --销售数量
           ,(select sum(fyb_mx.ssje) from fyb_mx  ,qtywb, fmdm  where  qtywb.ywdjh=fyb_mx.ywdjh and fmdm.FMDM=fyb_mx.fmdm and qtywb.mxmc=t1.mxmc and fyb_mx.zxflag='0'
                and to_char(qtywb.rq,'yyyy-mm-dd')>=p_view_param.get_beginTime() and to_char(qtywb.rq,'yyyy-mm-dd')<=p_view_param.get_endTime() and  fmdm.mwje_flag='1'    and (qtywb.ywdm='06' or qtywb.ywdm='12')    )  as mwje  -- 墓位金额
           ,(select sum(fyb_mx.ssje) from fyb_mx  ,qtywb, fmdm  where  qtywb.ywdjh=fyb_mx.ywdjh and fmdm.FMDM=fyb_mx.fmdm and qtywb.mxmc=t1.mxmc and fyb_mx.zxflag='0'
                and to_char(qtywb.rq,'yyyy-mm-dd')>=p_view_param.get_beginTime() and to_char(qtywb.rq,'yyyy-mm-dd')<=p_view_param.get_endTime()  and  (fmdm.mwje_flag<>'1' or fmdm.mwje_flag is null)    and (qtywb.ywdm='06' or qtywb.ywdm='12')     )  as qtje -- 其他金额
          , 0 as mwzs --墓位总数
          , 0 as mwye --待售墓位数量
          , 0 as azyw -- 安葬数量
          , 0 as qcyw -- 迁出数量
  from
  (
  select distinct mxmc   from qtywb
      where  azfsdm='02' and to_char(rq,'yyyy-mm-dd')>=p_view_param.get_beginTime() and to_char(rq,'yyyy-mm-dd')<=p_view_param.get_endTime() and zxflag<>'1' and (ywdm='06' or ywdm='12')
  )  t1
  ) group by  mxmc order by mxmc
  

查询方法：

SELECT * FROM p_view_num WHERE p_view_param.set_beginTime('2017-01-01')='2017-01-01' and p_view_param.set_endTime('2017-05-01')='2017-05-01'