plsql 往视图传参数_oracle 通过包给视图传参数,带参数的视图 package -view

描述:

视图是个查询语句,是不带参数的,但是,在某些特殊要求的情况下是需要传递参数进去;

解决方法一:

将条件放在视图外边,比如:

创建视图:

create or replace view p_view_user as

select id,class,sname from tb_score

调用视图:

select p_view_user  where id = 1

解决方法二:

创建包,在视图里面调用包

创建包头:

create or replace package pack_report_bank_head1 is

function set_param(param1 varchar2) return varchar2;

function get_param return varchar2;

end pack_report_bank_head1;

创建包体:

create or replace package body pack_report_bank_head1 is

paramValue varchar2(100);

function set_param(param1 varchar2) return varchar2 is

begin

paramValue:=param1;

return param1;

end;

function get_param return varchar2 is

begin

return paramValue;

end;

end pack_report_bank_head1;

视图的建立:

create or replace view p_view_user as

select id,class,sname from tb_score where class="p_view_param".get_param();

视图的调用:

select * from p_view_user where p_view_param.set_param('数学')='数学'

ps:包的头和包的体的创建语句要分开执行,要不然会报 "出现''creat''" 错误