oracle存储过程无效字符_Oracle存储过程查询无法使用游标

I am new to oracle and trying to create a stored procedure with the following pl/sql code.

What is wrong with the code?when i replace the searchString with hard code value of '1605%' then the query is executed but not with the variable.

create or replace PACKAGE BODY MAT_INFO AS

PROCEDURE mat_details(mat_id IN varchar, mat_cur OUT SYS_REFCURSOR) AS

searchString varchar2(700);

BEGIN

searchString := ''''||mat_id||'%''';

OPEN mat_cur FOR SELECT mi.* FROM S_JAMM_MAT_INFO mi WHERE mi.MAT_ID LIKE searchString;

END mat_details;

END MAT_INFO;

Can anyone help me what am i doing wrong here?

Thanks

解决方案

You are using unnecessary quotes:

SQL> declare

2 mat_id varchar2(100) := 'X';

3 searchString varchar2(100) := ''''||mat_id||'%''';

4 searchString2 varchar2(100) := ''||mat_id||'%';

5 num number;

6 begin

7 select count(1)

8 into num

9 from dual

10 where 'X' like searchString;

11 --

12 dbms_output.put_line('1. num= ' || num);

13 --

14 select count(1)

15 into num

16 from dual

17 where 'X' like searchString2;

18 --

19 dbms_output.put_line('2. num= ' || num);

20 end;

21 /

1. num= 0

2. num= 1

PL/SQL procedure successfully completed.

SQL>

Your double quoting could be useful if you would use a dynamic SQL, but in your static query it means that you are looking for a pattern containing quotes as part of the string to look for.