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

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
--------------------------------------------------------------------------------
北京
北京

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

小结:instr效率高于动态拼接sql,因为动态拼接每次要硬解析

最好思考用函数实现存储过程的功能而非动态sql

ITPub博客

oracle procedure存储过程输入参数用于sql like模糊匹配2算法

最新文章