如何对变量使用Like操作符(ORACLE)

Question

你能告诉我如何在oracleLIKE语句中传递变量吗?

下面是PLSQL:

declare
variables VARCHAR2 (200):= 'exemple';
V1 integer ;
BEGIN
select COUNT(*)into V1 from user_source 
where name LIKE '%'+variables+'%'
;
DBMS_OUTPUT.PUT_LINE(V1);

END;

PS:我需要在like sql语句中使用%。它不只是:在名称像变量的地方，%操作符是必要的!谢谢你的帮助!

Answer 1

像这样:

DECLARE
variables VARCHAR2 (200):= 'exemple';
V1 integer;
BEGIN
select COUNT(*)
into V1 
from user_source 
where name like '%'||variables||'%';
DBMS_OUTPUT.PUT_LINE(V1);

END;

Answer 2

连接，或者使用INSTR函数:

SQL> set serveroutput on
SQL> DECLARE
2     variables  VARCHAR2 (200) := 'TEST';
3     v1         INTEGER;
4  BEGIN
5     SELECT COUNT (*)
6       INTO v1
7       FROM user_source
8      WHERE UPPER (name) LIKE '%' || UPPER (variables) || '%';
9
10     DBMS_OUTPUT.put_line (v1);
11
12     --
13
14     SELECT COUNT (*)
15       INTO v1
16       FROM user_source
17      WHERE INSTR (UPPER (name), UPPER (variables)) > 0;
18
19     DBMS_OUTPUT.put_line (v1);
20  END;
21  /
90
90
PL/SQL procedure successfully completed.
SQL>