包的定义:create or replace package Test is
TYPE RSCUROSR IS REF CURSOR;
procedure test(v_sql in varchar2,mycs out RSCUROSR )
end Test; 包的body代码如下:
create or replace package body Test is
procedure test(v_sql in varchar2,mycs out RSCUROSR )
IS strV_SQL varchar2(4000);
BEGIN
strV_SQL :='select * from table where || v_sql||'';
OPEN R_MYCS FOR
strV_SQL;
END;end Test;现在我向v_sql传入的是带有like '% %'的字符串来进行模糊查询要怎么做呢?比如v_sql=tableName like '%name%'
请高手指点。
TYPE RSCUROSR IS REF CURSOR;
procedure test(v_sql in varchar2,mycs out RSCUROSR )
end Test; 包的body代码如下:
create or replace package body Test is
procedure test(v_sql in varchar2,mycs out RSCUROSR )
IS strV_SQL varchar2(4000);
BEGIN
strV_SQL :='select * from table where || v_sql||'';
OPEN R_MYCS FOR
strV_SQL;
END;end Test;现在我向v_sql传入的是带有like '% %'的字符串来进行模糊查询要怎么做呢?比如v_sql=tableName like '%name%'
请高手指点。
create table ttt( cname varchar2(100) );
insert into ttt
select '1123222' from dual union all
select '12234123' from dual union all
select '123' from dual union all
select 'fas' from dual union all
select 'fdsa' from dual;
--存储过程
create or replace procedure sp_aa( s varchar2 )
is
sqlstr varchar2(1000);
i int;
begin
--s:='cname like ''%123%''';
sqlstr:='select count(*) from ttt where '||s;
execute immediate sqlstr into i;
dbms_output.put_line( i );
end;
--测试
declare
s varchar2(100);
begin
s:='cname like ''%123%''';
sp_aa( s );
end;
--输出结果
3
is
sqlstr varchar2(1000);
i int;
begin
--s:='cname like ''%123%''';
sqlstr:='select * from ttt where '||s;
open rst for sqlstr;
end;
你使用这个再测试一下,你用我上边的测试数据