我想根据传入的值是否为空来拼接条件查询语句,但是字符串拼出来了,执行有问题。 --1.创建包,定义游标
create or replace package curspack as
type refcurs is ref cursor;
end; --2.条件查询的存储过程
create or replace procedure sp_condition_search
(
v_id t_user.id%type,
v_name t_user.username%type,
v_password t_user.password%type,
v_refcurs out curspack.refcurs
)
is
v_sql varchar2(500);
begin
v_sql := 'select * into v_refcurs from t_user where 1=1';
if(v_id is not null or nvl(v_id,'null') <> 'null')
then v_sql := v_sql || ' and id='||v_id;
end if;
if(v_name is not null or nvl(v_name,'null') <> 'null')
then v_sql := v_sql || ' and username='||v_name;
end if;
if(v_password is not null or nvl(v_password,'null') <> 'null')
then v_sql := v_sql || ' and password='||v_password;
end if;
dbms_output.put_line(v_sql);
open v_refcurs for v_sql;--这句出错
end; --3.调用
declare
v_refcurs curspack.refcurs;
begin
sp_condition_search(1,null,null,v_refcurs);
end; 错误信息:
ORA-00905: 缺失关键字
ORA-06512: 在 "TEST.SP_CONDITION_SEARCH", line 22
ORA-06512: 在 line 5
create or replace package curspack as
type refcurs is ref cursor;
end; --2.条件查询的存储过程
create or replace procedure sp_condition_search
(
v_id t_user.id%type,
v_name t_user.username%type,
v_password t_user.password%type,
v_refcurs out curspack.refcurs
)
is
v_sql varchar2(500);
begin
v_sql := 'select * into v_refcurs from t_user where 1=1';
if(v_id is not null or nvl(v_id,'null') <> 'null')
then v_sql := v_sql || ' and id='||v_id;
end if;
if(v_name is not null or nvl(v_name,'null') <> 'null')
then v_sql := v_sql || ' and username='||v_name;
end if;
if(v_password is not null or nvl(v_password,'null') <> 'null')
then v_sql := v_sql || ' and password='||v_password;
end if;
dbms_output.put_line(v_sql);
open v_refcurs for v_sql;--这句出错
end; --3.调用
declare
v_refcurs curspack.refcurs;
begin
sp_condition_search(1,null,null,v_refcurs);
end; 错误信息:
ORA-00905: 缺失关键字
ORA-06512: 在 "TEST.SP_CONDITION_SEARCH", line 22
ORA-06512: 在 line 5
解决方案 »
- 用SQL语句实现如下格式的统计
- 请教高手:如何实现xml文件到oracle数据库表的批量转化?
- 求助,oracle obdb driver for linux 驱动 liboraodbc.so ???
- 各位大哥,我如何下载oracle9i的补丁9.2.0.4 for windows
- 急急急急急急
- 如何书写SQL查询语句
- ?Oracle在一个用户下面最多可以创建多少个Table?
- 求一简单触发器语句
- 初学者关于存储过程(创建表)的简单问题(在线等待)
- ORACLE920 FOR LINUX7.2安装问题-请高手帮忙(在线等待)!
- oracle如何实现把一个数据表中的数据复制到备份数据表中
- 求一个sql语句
v_sql := 'select * from t_user where 1=1';