创建代参数的视图,按照以下四步操作即可
1,创建包
create or replace package p_view_university_code is
function set_param(UNIVERSITYCODE varchar2) return varchar2;
function get_param return varchar2;
end p_view_university_code;
2,创建包主题
create or replace package body p_view_university_code is
paramValue varchar2(16);
function set_param(UNIVERSITYCODE varchar2) return varchar2 is
begin
paramValue:=UNIVERSITYCODE;
return UNIVERSITYCODE;
end;
function get_param return varchar2 is
begin
return paramValue;
end;
end p_view_university_code;
3,创建视图
create or replace view view_eqinfo as
select eq_id,p1.* from sf_eq_basic_info p
inner join (select '11079' UNIVERSITYCODE,'123456' access_key from dual) p1 on p.universitycode=p1.universitycode
where p.UNIVERSITYCODE||'#'||p1.access_key=p_view_university_code.get_param() ;
4,查询使用
select * from view_eqinfo where p_view_university_code.set_param('11079#123456')='11079#123456';
爆款云服务器s6 2核4G 低至0.46/天,具体规则查看活动详情