为了创建带参数的视图,在网上搜索了一下,基本可以实现,但是如果我的条件是in不是=,就搞不定了。具体做法如下:
1. 建立package
create or replace package p_view_param is
function set_param(lottype in varchar2) return varchar2;
function get_param return varchar2;
end p_view_param;create or replace package body p_view_param is
paramValue varchar2(100); function set_param(lottype in varchar2) return varchar2 is
begin
paramValue:=lottype;
return lottype;
end; function get_param return varchar2 is
begin
return paramValue;
end;
end p_view_param;2.建立view
create or replace view p_view_user as
select lotid,lottype from sdb_tB_info_wip where lottype = p_view_param.get_param(); 3. 使用view
select * from p_view_user where p_view_param.set_param('LF')='LF',这样可以出来想要的结果问题是如果把view脚本改成下面的:
create or replace view p_view_user as
select lotid,lottype from sdb_tB_info_wip where lottype in p_view_param.get_param(); 运行下面语句返回的结果是('LF','L')
SELECT p_view_param.set_param('(''LF'',''L'')') FROM DUAL但是通过访问view却没有任何结果:
select * from p_view_user where p_view_param.set_param('(''LF'',''L'')')=('LF','L')请高手指点一下,这里应该如何操作了喃?
1. 建立package
create or replace package p_view_param is
function set_param(lottype in varchar2) return varchar2;
function get_param return varchar2;
end p_view_param;create or replace package body p_view_param is
paramValue varchar2(100); function set_param(lottype in varchar2) return varchar2 is
begin
paramValue:=lottype;
return lottype;
end; function get_param return varchar2 is
begin
return paramValue;
end;
end p_view_param;2.建立view
create or replace view p_view_user as
select lotid,lottype from sdb_tB_info_wip where lottype = p_view_param.get_param(); 3. 使用view
select * from p_view_user where p_view_param.set_param('LF')='LF',这样可以出来想要的结果问题是如果把view脚本改成下面的:
create or replace view p_view_user as
select lotid,lottype from sdb_tB_info_wip where lottype in p_view_param.get_param(); 运行下面语句返回的结果是('LF','L')
SELECT p_view_param.set_param('(''LF'',''L'')') FROM DUAL但是通过访问view却没有任何结果:
select * from p_view_user where p_view_param.set_param('(''LF'',''L'')')=('LF','L')请高手指点一下,这里应该如何操作了喃?
解决方案 »
- 安装11g时总卡住
- Access中的是/否类型
- oracle如何把varchar转换成date格式
- 806库的单表导入导出,请教大家!
- 这是求一天每个区域的上班时间数.现在我想求按每个月每个分区的平均小时数应如何写
- 简单的ORACLE 存储过程 哪里错了?
- 在线等待 帮我看下下面这个触发器嘛,不知道是怎么了,编译总通不过哦
- 关于执行SQL语句的时间问题(初级)
- Oracle多表关联查询问题!解决立即给分
- 使用RMAN进行恢复时,备份文件的路径问题
- oracle数据库连不上,报错输入口令: ERROR: ORA-01033: ORACLE initialization or shutdown in prog
- 【疑问】查询的to_number问题。
它会查找的值 = 1,2 而 不是 =1 or =2
所以我最后的做法是:
1. 建立一张表lot_type(lottype,varchar2(5))
程序中根据每次查询条件的不同,先truncate该表,然后再往里面插入数据
2. 建立view
create or replace view vw_test as
select lotid,lottype from sdb_tB_info_wip
where lottype in (select lottype from lot_type)