oracle 输入带*的为模糊查询，不带*的为精确查询的存储过程.

create or replace procedure up_dt(
ocr_sn varchar2,
cur_dt OUT SYS_REFCURSOR
)

is
T_SQL varchar2(5000);
begin
 
  T_SQL:='select * from tboqcreturn T  where 1=1';
   dbms_output.put_line('T_SQLddddddddddd');
  
    IF ocr_sn is not null  THEN      
                    BEGIN
                     -- T_SQL := T_SQL || ' AND T.OCR_SN like ';
                      IF  (substr(ocr_sn,1,1)) != '*' and (substr(ocr_sn,length(ocr_sn),1)) != '*' then
                       T_SQL := T_SQL || 'AND T.OCR_SN =''' || ocr_sn || '''';
                      
                      ELSIF (substr(ocr_sn,1,1)) = '*' and (substr(ocr_sn,length(ocr_sn),1)) = '*' then
                      T_SQL := T_SQL ||'AND T.OCR_SN like''%' ||  substr(ocr_sn,2,LENGTH(ocr_sn)-2) || '%''';  
                     
                      ELSIF  (substr(ocr_sn,1,1)) != '*' and (substr(ocr_sn,length(ocr_sn),1)) = '*' then
                       T_SQL := T_SQL || 'AND T.OCR_SN like'''|| substr(ocr_sn,1,LENGTH(ocr_sn)-1) || '%''';
                        
                      ELSIF (substr(ocr_sn,1,1)) = '*' and (substr(ocr_sn,length(ocr_sn),1)) != '*' then
                       T_SQL := T_SQL || 'AND T.OCR_SN like'''|| substr(ocr_sn,2,LENGTH(ocr_sn)-1) || '%'''; 
                       
                      END IF;     
                    END;
   END IF;
OPEN cur_dt FOR T_SQL;
       -- select * from tboqcreturn;   1003240925595454
      dbms_output.put_line(T_SQL);
       -- OPEN cur_result FOR T_SQL;  
END;

--SELECT substr('*ABXCD*',2,LENGTH('*ABXCD*')-2) FROM DUAL

--SELECT substr('ABCDE*',1,LENGTH('ABCDE*')-1) FROM DUAL
--SELECT substr('*ABCDEF',2,LENGTH('*ABCDEF')-1) FROM DUAL

转载于:https://www.cnblogs.com/wj-wangjun/archive/2010/03/30/1700718.html