使用程序包给嵌套视图传参可以实现,但当传多个参数时,要使用in查询,该如何修改
create or replace package p_view_param is
function set_param(var varchar) return varchar;
function get_param return varchar; end p_view_param;
create or replace package body p_view_param is
paramValue varchar;
function set_param(var varchar) return varcharis
begin
paramValue:=var ;
return var ;
end;
function get_param return varcharis
begin
return paramValue;
end;
end p_view_param;
视图的建立: create or replace view p_view_user as
select id,realName,address from tbl_info where id=p_view_param.get_param();
PL/SQL调用:
select * from p_view_user where p_view_param.set_param(‘1)=‘1实现in查询修改如下:
create or replace view p_view_user as
select id,realName,address from tbl_info where id in p_view_param.get_param(); PL/SQL调用:
select * from p_view_user where p_view_param.set_param(‘1,2’)=‘1,2‘
这样无法得到数据
create or replace package p_view_param is
function set_param(var varchar) return varchar;
function get_param return varchar; end p_view_param;
create or replace package body p_view_param is
paramValue varchar;
function set_param(var varchar) return varcharis
begin
paramValue:=var ;
return var ;
end;
function get_param return varcharis
begin
return paramValue;
end;
end p_view_param;
视图的建立: create or replace view p_view_user as
select id,realName,address from tbl_info where id=p_view_param.get_param();
PL/SQL调用:
select * from p_view_user where p_view_param.set_param(‘1)=‘1实现in查询修改如下:
create or replace view p_view_user as
select id,realName,address from tbl_info where id in p_view_param.get_param(); PL/SQL调用:
select * from p_view_user where p_view_param.set_param(‘1,2’)=‘1,2‘
这样无法得到数据
你可以建立一个嵌套表类型,写一个函数返回一个嵌表的表类型,
然后再使用in
然后用 instr(function,id)试试