oracle如何查找非法字符,ORACLE存储过程判断非法字符

---------------------------调用方法

--验证非法字符

isMg := fun_issensitivity(v_arr);

if not isMg then

v_sendmsg := '很好,你的信息是正常的。';

else

v_sendmsg := '对不起，您的信息含有敏感字，请修改后再发！';

end if;

---------------------------中间过程

create or replace function fun_issensitivity(arr in splitlongstr) return boolean is

Result boolean;

i number;

v_mg_msg varchar2(4000);

begin

i:=1;

Result:=false;

while (i<=arr.count) loop

syn_sp_unlawful(arr(i),v_mg_msg);

v_mg_msg:=replace(v_mg_msg,' ','');

if v_mg_msg='0' then

null;

else

Result:=true;

exit;

end if;

i:=i+1;

end loop;

return(Result);

end fun_issensitivity;

--------------------------敏感字符判断过程

create or replace procedure sp_unlawful

(

in_msg in varchar2,

out_msg out varchar2

) is

/*

敏感字符判断过程

*/

v_Result varchar2(1024);

v_location number;

v_word varchar2(1024);

v_i number;

cursor c_unlawful is

select msg

from word_unlawful

where is_valid=1;

begin

v_i:=0;

open c_unlawful;

loop

fetch c_unlawful into v_word;

exit when c_unlawful%notfound;

select instr(in_msg,v_word) into v_location

from dual;

if v_location>0 then

v_i:=v_i+1;

v_Result:=v_Result||','||v_word;

end if;

end loop;

close c_unlawful;

if v_i>0 then

out_msg:=ltrim(v_Result,',');

else

out_msg:='0';

end if;

end sp_unlawful;

------------------------------创建收集的非法字符列表

-- Create table

create table WORD_UNLAWFUL

(

MSG VARCHAR2(100) not null,

IS_VALID NUMBER(1) not null,

INS_DATE DATE default sysdate not null

);

-----------------------------需要非法字符库的请到附件下载

来源：优特产     个人博客：优特品