oracle 创建带参数视图

创建包：

create or replace package p_view_param  is
   function set_param(num number) return number;
   function get_param  return number;
   function set_material_no(materiralNo varchar2) return varchar2;
   function get_material_no return varchar2;
   
end p_view_param;

实现包体：

create or replace package body p_view_param is
       paramValue number;
       materialNo wms_inventory.material_no%type;
       function set_param(num number) return number is
       begin
         paramValue:=num;
         return num;
        end;


       function get_param return number is
       begin
         return paramValue;
       end;
       
       function set_material_no(materiralNo varchar2) return varchar2 is
       begin
         materialNo:=materiralNo;
         return materiralNo;
        end;


       function get_material_no return varchar2 is
       begin
         return materialNo;
       end;


       
   end p_view_param;

创建视图：

create or replace view v_wms_inventory as
select MATERIAL_NO from WMS_INVENTORY@SSL_DB WHERE quantity=p_view_param.get_param()
union all
select MATERIAL_NO from WMS_INVENTORY@SSL_DB WHERE MATERIAL_NO=p_view_param.get_material_no();

查询SQL：

select * from v_wms_inventory where p_view_param.set_param(3050)=3050;
select * from wms_inventory@ssl_db where p_view_param.set_material_no('L07343')='L07343';