ORACLE存储过程判断非法字符

1---------------------------调用方法
2
3        --验证非法字符
4        isMg := fun_issensitivity(v_arr);
5        if not isMg then
6             v_sendmsg  := '很好,你的信息是正常的。';
7        else
8             v_sendmsg := '对不起，您的信息含有敏感字，请修改后再发！';
9        end if;
10
11---------------------------中间过程
12
13create or replace function fun_issensitivity(arr in  splitlongstr) return boolean is
14  Result          boolean;
15  i               number;
16  v_mg_msg        varchar2(4000);
17begin
18  i:=1;
19  Result:=false;
20  while (i<=arr.count) loop
21        syn_sp_unlawful(arr(i),v_mg_msg);
22        v_mg_msg:=replace(v_mg_msg,' ','');
23        if v_mg_msg='0' then
24            null;
25        else
26            Result:=true;
27            exit;
28        end if;
29        i:=i+1;
30  end loop;
31  return(Result);
32end fun_issensitivity;
33
34--------------------------敏感字符判断过程
35
36create or replace procedure sp_unlawful
37(
38    in_msg in varchar2,
39    out_msg out varchar2
40)   is
41/*
42    敏感字符判断过程
43
44*/
45
46    v_Result varchar2(1024);
47    v_location number;
48    v_word varchar2(1024);
49    v_i number;
50
51    cursor c_unlawful is
52        select  msg
53        from word_unlawful
54        where is_valid=1;
55
56begin
57    v_i:=0;
58    open c_unlawful;
59    loop
60        fetch c_unlawful into v_word;
61        exit when c_unlawful%notfound;
62        select instr(in_msg,v_word) into v_location
63         from dual;
64        if v_location>0 then
65            v_i:=v_i+1;
66            v_Result:=v_Result||','||v_word;
67        end if;
68    end loop;
69    close c_unlawful;
70    if v_i>0 then
71        out_msg:=ltrim(v_Result,',');
72    else
73        out_msg:='0';
74    end if;
75end sp_unlawful;
76
77------------------------------创建收集的非法字符列表
78
79-- Create table
80create table WORD_UNLAWFUL
81(
82  MSG      VARCHAR2(100) not null,
83  IS_VALID NUMBER(1) not null,
84  INS_DATE DATE default sysdate not null
85);
86-----------------------------需要非法字符库的请到附件下载

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