X_SQL :='select user_id,user_name,email from user_mst where user_id like ''%' || P_USER_ID_I ||'%'''; 改为 X_SQL :='select user_id,user_name,email from user_mst where user_id like %' || P_USER_ID_I ||'%';
楼主,杯具啊,我的可以~~~~ SQL> create or replace function aaaaa ( 2 p_user_id_i in varchar2 3 ) return sys_refcursor 4 as 5 x_sql varchar2(200); 6 o_cur sys_refcursor; 7 begin 8 x_sql :='select * from test where t1 like ''%'|| p_user_id_i || '%'''; 9 open o_cur for x_sql; 10 return o_cur; 11 end aaaaa; 12 /函数已创建。SQL> SQL> select aaaaa('a') from dual;AAAAA('A') -------------------- CURSOR STATEMENT : 1CURSOR STATEMENT : 1T1 T2 T3 -------------------- -------------------- ---------- Aa asdf 10
老大还是不行呢,报的这个错: ORA-00911: 无效字符 ORA-06512: 在 "SYSTEM.AAAAA", line 10 ORA-06512: 在 line 1
不如这样试试吧。 cstmt.setString(2, "%mb2%"); SQL> create or replace function aaaaa1 ( 2 p_user_id_i in varchar2 3 ) return sys_refcursor 4 as 5 x_sql varchar2(200); 6 o_cur sys_refcursor; 7 begin 8 x_sql :='select * from test where t1 like '''|| p_user_id_i ||''''; 9 open o_cur for x_sql; 10 return o_cur; 11 end aaaaa1; 12 /函数已创建。SQL> select aaaaa1('%a%') from dual;AAAAA1('%A%') -------------------- CURSOR STATEMENT : 1CURSOR STATEMENT : 1T1 T2 T3 -------------------- -------------------- ---------- Aa asdf 10
貌似这样更简单 SQL> create or replace function aaaaa1 ( 2 p_user_id_i in varchar2 3 ) return sys_refcursor 4 as 5 x_sql varchar2(200); 6 o_cur sys_refcursor; 7 begin 8 open o_cur for select * from test where t1 like p_user_id_i; 9 return o_cur; 10 end aaaaa1; 11 /函数已创建。SQL> select aaaaa1('%a%') from dual;AAAAA1('%A%') -------------------- CURSOR STATEMENT : 1CURSOR STATEMENT : 1T1 T2 T3 -------------------- -------------------- ---------- Aa asdf 10
改为
X_SQL :='select user_id,user_name,email from user_mst where user_id like %' || P_USER_ID_I ||'%';
SQL> create or replace function aaaaa (
2 p_user_id_i in varchar2
3 ) return sys_refcursor
4 as
5 x_sql varchar2(200);
6 o_cur sys_refcursor;
7 begin
8 x_sql :='select * from test where t1 like ''%'|| p_user_id_i || '%''';
9 open o_cur for x_sql;
10 return o_cur;
11 end aaaaa;
12 /函数已创建。SQL>
SQL> select aaaaa('a') from dual;AAAAA('A')
--------------------
CURSOR STATEMENT : 1CURSOR STATEMENT : 1T1 T2 T3
-------------------- -------------------- ----------
Aa asdf 10
ORA-00911: 无效字符
ORA-06512: 在 "SYSTEM.AAAAA", line 10
ORA-06512: 在 line 1
cstmt.setString(2, "%mb2%");
SQL> create or replace function aaaaa1 (
2 p_user_id_i in varchar2
3 ) return sys_refcursor
4 as
5 x_sql varchar2(200);
6 o_cur sys_refcursor;
7 begin
8 x_sql :='select * from test where t1 like '''|| p_user_id_i ||'''';
9 open o_cur for x_sql;
10 return o_cur;
11 end aaaaa1;
12 /函数已创建。SQL> select aaaaa1('%a%') from dual;AAAAA1('%A%')
--------------------
CURSOR STATEMENT : 1CURSOR STATEMENT : 1T1 T2 T3
-------------------- -------------------- ----------
Aa asdf 10
SQL> create or replace function aaaaa1 (
2 p_user_id_i in varchar2
3 ) return sys_refcursor
4 as
5 x_sql varchar2(200);
6 o_cur sys_refcursor;
7 begin
8 open o_cur for select * from test where t1 like p_user_id_i;
9 return o_cur;
10 end aaaaa1;
11 /函数已创建。SQL> select aaaaa1('%a%') from dual;AAAAA1('%A%')
--------------------
CURSOR STATEMENT : 1CURSOR STATEMENT : 1T1 T2 T3
-------------------- -------------------- ----------
Aa asdf 10