【oracle】Oracle创建带参数视图

 

--创建包
create or replace package p_view_param is

  function set_orgid_param(orgId varchar2) return varchar2;
  function get_orgid_param return varchar2;
  
  function set_org_param(org varchar2) return varchar2;
  function get_org_param return varchar2;
  
end p_view_param;

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

  paramValue varchar2(10);
  paramOrg varchar2(10);
  function set_orgid_param(orgId varchar2) return varchar2 is
  begin
    paramValue:=orgId;
    return orgId;
  end;

  function get_orgid_param return varchar2 is
  begin
    return paramValue;
  end;
  
  function set_org_param(org varchar2) return varchar2 is
  begin
    paramOrg:=org;
    return org;
  end;

  function get_org_param return varchar2 is
  begin
    return paramOrg;
  end;
      
end p_view_param;

--创建视图
create or replace view v_view as
select MATERIAL_NO from WMS_INVENTORY WHERE quantity = p_view_param.get_org_param()
union all
select MATERIAL_NO from WMS_INVENTORY WHERE MATERIAL_NO = p_view_param.get_orgid_param();

--查询SQL
select * from v_view where p_view_param.set_param(3050)=3050;
select * from v_view where p_view_param.set_material_no('L07343')='L07343';