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

oracle procedure儲存過程輸入引數用於sql like模糊匹配2演算法

相關文章