如:
create or replace proc_test(v_field in varchar)
is
strsql varchar2(2000);
begin
strsql:='select * from table1 where field1 like ''%'' || :v_field || ''%''';
end如果传入v_field='123',怎样得到'select * from table1 where field1 like '123' '
create or replace proc_test(v_field in varchar)
is
strsql varchar2(2000);
begin
strsql:='select * from table1 where field1 like ''%'' || :v_field || ''%''';
end如果传入v_field='123',怎样得到'select * from table1 where field1 like '123' '
因为记录集中的结果有错误,所以想将存储过程中所执行的sql也拿到,看是否有误,
如果直接将strsql作为输出参数返回的话,后台得到的也是''select * from table1 where field1 like ''%'' ¦ ¦ :v_field ¦ ¦ ''%' 这个串,并不是真实的sql,我怀疑动态sql的写法可能有问题。
DBMS_OUTPUT.put_line输出sqlcreate or replace proc_test(v_field in varchar)
is
strsql varchar2(2000);
begin
strsql:='select * from table1 where field1 like ''%'' ¦ ¦ :v_field ¦ ¦ ''%''';
DBMS_OUTPUT.put_line(strsql);
execute iimmediate strsql;
end
select * from table1 where field1 like ''%'' ¦ ¦ :v_field ¦ ¦ ''%'
strsql:='select * from table1 where field1 like ''%'¦ ¦ :v_field ¦ ¦'%''';
假设v_field=1234
select *
from table1
where field1 like '%1234%'
这样的话 like '%1234%'这部分要写成 'like ''%'||v_field||'%''
原来一个单引号的地方变成两个单引号,不是双引号
SQL> edit
已写入 file afiedt.buf 1 create or replace procedure proc_test(v_field in varchar)
2 is
3 strsql varchar2(2000);
4 begin
5 strsql:='select * from table1 where field1 like ''%' || v_field ||'%''';
6 dbms_output.put_line(strsql);
7* end;
SQL> /过程已创建。SQL> exec proc_test('123');
select * from table1 where field1 like '%123%'PL/SQL 过程已成功完成。SQL>
比如这句,设123是变量
select * from table1 where field1 like '%123'
那 select * from table1 where field1 like '%前后本来没有'的,就各加一个,原来里面的那个'变成两个,最后那个变成四个,这句变成了
'select * from table1 where field1 like ''%'||变量123||''''如果是select * from table1 where field1 like '123'变成了 'select * from table1 where field1 like '''||123||''''