SQL> select * from t_like;
A
--------------------------------------------------------------------------------
山西省
北京
北京
SQL> create or replace procedure proc_like(in_a varchar2)
2 as
3 v_sql varchar2(4000);
4 begin
5 v_sql:='select a from t_like where a like '||''''||'%'||in_a||'%'||'''';
6 dbms_output.put_line(v_sql);
7 end;
8 /
Procedure created
2 as
3 v_sql varchar2(4000);
4 begin
5 v_sql:='select a from t_like where a like '||''''||'%'||in_a||'%'||'''';
6 dbms_output.put_line(v_sql);
7 end;
8 /
Procedure created
SQL> exec proc_like('北京');
select a from t_like where a like '%北京%'
PL/SQL procedure successfully completed
SQL> select a from t_like where a like '%北京%';
A
--------------------------------------------------------------------------------
北京
北京
select a from t_like where a like '%北京%'
PL/SQL procedure successfully completed
SQL> select a from t_like where a like '%北京%';
A
--------------------------------------------------------------------------------
北京
北京
SQL> create or replace procedure proc_like_instr(in_a varchar2)
2 as
3 v_sql varchar2(4000);
4 begin
5 v_sql:='select a from t_like where instr(a,in_a)>0
6 dbms_output.put_line(v_sql);
7 end;
8 /
Procedure created
2 as
3 v_sql varchar2(4000);
4 begin
5 v_sql:='select a from t_like where instr(a,in_a)>0
6 dbms_output.put_line(v_sql);
7 end;
8 /
Procedure created
小结:instr效率高于动态拼接sql,因为动态拼接每次要硬解析
最好思考用函数实现存储过程的功能而非动态sql
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9240380/viewspace-753202/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9240380/viewspace-753202/