Oracle创建带字符串参数的视图

--创建包
create or replace package p_view_param is

  function set_budget_date_param(budget_date varchar2) return varchar2;
  function get_budget_date_param return varchar2;
  
end p_view_param;

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

  paramValue varchar2(10);
  
  function set_budget_date_param(budget_date varchar2) return varchar2 is
  begin
    paramValue:=budget_date;
    return budget_date;
  end;

  function get_budget_date_param return varchar2 is
  begin
    return paramValue;
  end;   
end p_view_param;

--创建视图
create or replace view zx_xxb_budget_month_sum参数 as
select so.code as 机构代码,
       so.name as 机构名称,
       so.area 区域,
       bs.budget_date 预算年月,
       decode(bs.status,null,'未汇总','0','未汇总','1','审批不通过','2','审批中','3','审批通过') as 汇总情况 ,
       bs.status as 汇总状态,
       bs.user_name as 预算汇总人,
       bs.post_id as 岗位ID,
       bs.post_name as 岗位名称,
       bs.total_amount as 总金额,
       bs.cancel_instructions as 取消汇总说明,
       bs.type_name as 申请类型名称,
       decode(so.status,'enabled','启用','disabled','禁用') as 是否启用
  from sec_organization so 
  left join budget_summary bs
    on so.id = bs.org_id and bs.budget_date = p_view_param.get_budget_date_param()
 order by so.code
 
--查询数据

select * from zx_xxb_budget_month_sum参数 t where p_view_param.set_budget_date_param('参数') = '参数'
select * from zx_xxb_budget_month_sum参数 t where p_view_param.set_budget_date_param('201903') = '201903'