创建带参数的视图

--创建包
create or replace package p_view_param is

  function set_start_date(startDate date) return date;
  function get_start_date return date;
  
  function set_end_date(endDate date) return date;
  function get_end_date return date;
  
end p_view_param;

--实现包体
create or replace package body p_view_param is

  paramStartDate date;
  paramEndDate date;
  function set_start_date(startDate date) return date is
  begin
    paramStartDate:=startDate;
    return startDate;
  end;

  function get_start_date return date is
  begin
    return paramStartDate;
  end;
  
  function set_end_date(endDate date) return date is
  begin
    paramEndDate:=endDate;
    return endDate;
  end;

  function get_end_date return date is
  begin
    return paramEndDate;
  end;
      
end p_view_param;

-- 创建视图
 create view ccs_view_nb_fish_manager as 
 select  d.jobno,
    (select listagg(ws.surveyor || '(' || ws.major || ')', ',') within group  (order by ws.surveyor) from ccs_work_surveyor ws where ws.jobno = d.jobno and ws.header = '1') as surveyor
  FROM ccs_view_nb_step_query d
 inner join worklist_view twl on d.jobno = twl.v_ship_survey_no
 left join ccs_work_list wl on d.jobno = wl.jobno
 where wl.job_status in (1,5) and d.status in ('完工','发证')
 and d.enddate >= p_view_param.get_start_date
 and d.enddate <= p_view_param.get_end_date
 
 -- 查询语句
 select * from ccs_view_nb_fish_manager where p_view_param.set_start_date(to_date('2019-01-01','yyyy-mm-dd')) = to_date('2019-01-01','yyyy-mm-dd')
and p_view_param.set_end_date(to_date('2022-04-02','yyyy-mm-dd')) = to_date('2022-04-02','yyyy-mm-dd')