视图带参数查询，以及使用DBLINK查询package的写法

当数据量较多时可以使用带参数的视图提高查询效率

一 创建带参数的视图

1创建程序包，定义get,set方法

create or replace package p_history_sale_sum_param  is
--参数
   function set_year_start(yearnum varchar2) return varchar2;
   function get_year_start  return varchar2;

   function set_year_end(yearnum varchar2) return varchar2;
   function get_year_end  return varchar2;

   function set_cstcode(cstcode varchar2) return varchar2;
   function get_cstcode  return varchar2;
   end p_history_sale_sum_param;

2创建package boby实现get set 方法

create or replace package body p_history_sale_sum_param is
       paramYearStart varchar2(32);
       paramYearEnd varchar2(32);
       paramCstcode varchar2(32);
       -- yearStart
       function set_year_start(yearnum varchar2) return varchar2 is
       begin
         paramYearStart:=yearnum;
         return yearnum;
        end;

       function get_year_start return varchar2 is
       begin
         return paramYearStart;
       end;

       -- yearEnd
       function set_year_end(yearnum varchar2) return varchar2 is
       begin
         paramYearEnd:=yearnum;
         return yearnum;
        end;

       function get_year_end return varchar2 is
       begin
         return paramYearEnd;
       end;

       -- Type
       function set_cstcode(cstcode varchar2) return varchar2 is
       begin
         paramCstcode:=cstcode;
         return cstcode;
        end;

       function get_cstcode return varchar2 is
       begin
         return paramCstcode;
       end;

   end p_history_sale_sum_param;

3创建带参数的视图

create or replace view v_history_sale_sum as
SELECT to_char(v1.profit_date,'yyyy') as yearnum,v1.datas_customer,
sum(case when v1.billtype_name='销售退货验收' then v1.data_bweight else 0 end) as sbill_thweight,
sum(v1.data_bweight) as data_bweight,
sum(case when v1.jc>0 then v1.gm else 0 end) as gm FROM v_forbi_xs v1 
where to_char(v1.profit_date,'yyyy')>=p_history_sale_sum_param.get_year_start()
and  to_char(v1.profit_date,'yyyy')<=p_history_sale_sum_param.get_year_end()
and v1.datas_customer=p_history_sale_sum_param.get_cstcode()
group by to_char(v1.profit_date,'yyyy'),v1.datas_customer;

4查询该视图需要先传入参数

select * from v_history_sale_sum where p_history_sale_sum_param.set_year_start('2019')='2019' and p_history_sale_sum_param.set_year_end('2020')='2020' and 
p_history_sale_sum_param.get_cstcode('012634')='012634'

二 当需要使用dbLink查询该视图时

select * from 包所属用户名.表名@dblink
where 用户名.包名.函数名@dblink(参数)

CREATE OR REPLACE VIEW V_HISTORY_SALE_SUM AS
SELECT v1.yearnum,v1.datas_customer,v1.sbill_weight,v1.data_bweight,v1.gm
FROM czzhd.v_history_sale_sum@crmstat_erp19 v1
where czzhd.p_history_sale_sum_param.set_cstcode@crmstat_erp19(czzhd.p_history_sale_sum_param.get_cstcode@crmstat_erp19())=czzhd.p_history_sale_sum_param.get_cstcode@crmstat_erp19()
and czzhd.p_history_sale_sum_param.set_year_start@crmstat_erp19(czzhd.p_history_sale_sum_param.get_year_start@crmstat_erp19())=czzhd.p_history_sale_sum_param.get_year_start@crmstat_erp19()
and czzhd.p_history_sale_sum_param.set_year_end@crmstat_erp19(czzhd.p_history_sale_sum_param.get_year_end@crmstat_erp19())=czzhd.p_history_sale_sum_param.get_year_end@crmstat_erp19()
;