Oracle sql function 对数据进行处理

要求

用数据库function实现数据处理

1. 身分证字号：开发环境(DEV)至少遮5-8字符byte，使用者测试(UAT)环境之假资料若由测试单位自行编造输入可不遮。
2. 姓名(客户之姓名、英文姓名、紧急联络人、代理人)：中文至少遮第二中文字，英文至少遮第3-4字符(byte)。
3. 地址：第七字(14 byte)起屏蔽。
4. 出生年月日：随机数加减五十天，随机数不可取用0。如计算需要时，得不遮。
5. 银行(邮局账号)：至少遮第6-8字符 (byte)。账号栏位存入数据库时，需要先做转码处理，加密算法采用DES．
6. 信用卡号：至少遮第7-12字符 (byte)。卡号栏位存入数据库时，需要先做转码处理，加密算法采用DES．
7. 电话：至少遮第6-8字符 (byte)。
8. 电子邮件：至少遮第2-5字符 (byte)。
9. 保单号码：至少遮第5-8字符 (byte)。
10. 放款账号：至少遮第5-8字符 (byte)。
11. 职业（服务机构、职务）：屏蔽后2位字符（byte）
12. 健康类（指纹、病历、医疗、基因、性生活、健康检查等）：屏蔽不显示原始资料，使用屏蔽的字符替换原始资料。
13. 家庭类（婚姻、家庭、教育）：屏蔽不显示原始资料，使用屏蔽的字符替换原始资料。
14. 犯罪前科、社会活动：屏蔽不显示原始资料，使用屏蔽的字符替换原始资料。

实现

之前写过身份证的，这次看大部分大同小异。先分类

• 替换字符：1.2.3.7.8.9.10.11.
• 替换+加密：5，6
• 屏蔽全部替换：12.13.14.
• 待定：4.

1.身份证号

create or replace function fIdNum(id varchar2)
return varchar2   
is   
Result varchar2(20); 
begin   
select replace(id,substr(id,5,length(id)-8),'*') into Result from dual;   
return(Result); 
end fIdNum;
--验证
select fIdNum('111111222222223333') from dual

2.姓名

create or replace function fName(name varchar2)
return varchar2   
is   
Result varchar2(20);
begin   
 if  lengthb(name)=length(name)  then 
		select regexp_replace(name,'\w','*',3)into Result   from dual; 
return(Result);
  else
		
		select regexp_replace(name,'[^\x00-\xff]','*',2)into Result  from dual;
return(Result); 
  end if;
end fName;

--验证
select fName('马大姐') from dual

3.地址

create or replace function fAddress(address varchar2)
return varchar2   
is   
Result varchar2(20); 
begin   
select replace(address,substr(address,7),'*') into Result from dual;   
return(Result); 
end fAddress;
--验证
select fAddress('一二三四五六七八九十十一') from dual

4.出生年月日

待定，因为有别的任务，所以这个先搁置了

5.6.des加密，解密

create or replace function
encrypt_des(p_text varchar2, p_key varchar2) return varchar2 is
        v_text varchar2(4000);
        v_enc varchar2(4000);
        raw_input RAW(128) ;
        key_input RAW(128) ;
        decrypted_raw RAW(2048);
begin
        v_text := rpad( p_text, (trunc(length(p_text)/8)+1)*8, chr(0));
        raw_input := UTL_RAW.CAST_TO_RAW(v_text);
        key_input := UTL_RAW.CAST_TO_RAW(p_key);
        dbms_obfuscation_toolkit.DESEncrypt(input => raw_input,key => key_input,encrypted_data =>decrypted_raw);
        v_enc := rawtohex(decrypted_raw);
        dbms_output.put_line(v_enc);
    return v_enc;
end;

select encrypt_des('dsds'，'test#5&124*!de') from dual  ---加密测试
select decrypt_des('2B1DB64F882C68F9','test#5&124*!de')from dual ----解密测试
update tb_salarysign_staff s set s.staffpwd =encrypt_des(s.staffpwd, 'test#5&124*!de');----对表内列加密-
select decrypt_des(s.staffpwd, 'test#5&124*!de') from tb_salarysign_staff s-----------解密

create or replace function decrypt_des(p_text varchar2,p_key varchar2) return varchar2 is
v_text varchar2(2000);                       
begin
    dbms_obfuscation_toolkit.DESDECRYPT(input_string => UTL_RAW.CAST_TO_varchar2(p_text),key_string =>p_key, decrypted_string=> v_text);
    v_text := rtrim(v_text,chr(0));
    dbms_output.put_line(v_text);
    return v_text;
end;

参考ORACLE定义DES加密解密及MD5加密函数

5.银行账号

create or replace function fBankNum(bankNum varchar2)
return varchar2   
is   
Result varchar2(20); 
begin   
select replace(bankNum,substr(bankNum,6,4),'*') into Result from dual;   
return(Result); 
end fBankNum;

--验证
select fBankNum(1111222233334444) from dual

6.信用卡号

create or replace function fCreditNum(creditNum varchar2)
return varchar2   
is   
Result varchar2(20); 
begin   
select replace(creditNum,substr(creditNum,7,5),'*') into Result from dual;   
return(Result); 
end fCreditNum;

--验证
select fCreditNum('1111222233334444') from dual

7.电话

create or replace function fPhone(phone varchar2)
return varchar2   
is   
Result varchar2(20); 
begin   
select replace(phone,substr(phone,4,4),'*') into Result from dual;   
return(Result); 
end fPhone;

--验证
select fPhone('19834526016') from dual

8.邮件

create or replace function fEmail(email varchar2)
return varchar2   
is   
Result varchar2(20); 
begin   
select replace(email,substr(email,2,5),'*') into Result from dual;   
return(Result); 
end fEmail;

--验证
select fEmail('lining@qq.com') from dual

9.保单

create or replace function fGuarantee(guarantee varchar2)
return varchar2   
is   
Result varchar2(20); 
begin   
select replace(guarantee,substr(guarantee,5,4),'*') into Result from dual;   
return(Result); 
end fGuarantee;

--验证
select fGuarantee('12345678') from dual

10.放款账号

create or replace function fLoanAccount(account varchar2)
return varchar2   
is   
Result varchar2(20); 
begin   
select replace(account,substr(account,5,4),'*') into Result from dual;   
return(Result); 
end fLoanAccount;

--验证
select fLoanAccount('12345678') from dual

11.职业

create or replace function fJob(job varchar2)
return varchar2   
is   
Result varchar2(20); 
begin   
select replace(job,substr(job,3),'*') into Result from dual;   
return(Result); 
end fJob;

--验证
select fJob('美国总统') from dual

12.13.14 健康类，家庭类，犯罪前科，社会活动

create or replace function fHide(msg varchar2)
return varchar2   
is   
Result varchar2(20); 
begin   
select replace(msg,substr(msg,1),'*') into Result from dual;   
return(Result); 
end fHide;

--验证
select fHide('美国总统') from dual

笔记

oracle中的正则表达式

匹配次数和选项一般采用默认值，省略

regexp_like() --返回满足条件的字段

regexp_like(搜索值，正则，匹配选项)
select * from test where regexp_like(mc,‘^a{1,3}’);

匹配选项

c 匹配时，大小写敏感，默认值
i 匹配时，大小写不敏感
n 允许使用原点（.）匹配任何新增字符
m 允许将源字符作为多个字符串对待

regexp_instr() --返回满足条件的字符或字符串的位置

regexp_instr(搜索值，正则，开始，匹配次数，return_option，匹配选项)
return_option 指定返回的类型，若该参数为0，则返回值为匹配位置的第一个字符，若为非0，则返回匹配值的最后一个位置+1
regexp_instr(zip, ’ [^ [: digit : ] ] ')zip中第一个非数字字符

regexp_replace() --返回替换后的字符串

regexp_replace(字符串,正则,替换字符[,开始,匹配次数，匹配选项)
select regexp_replace(name,‘\w’,‘*’,3)into Result from dual;
匹配中文，第三个字符后替换为 *

regexp_substr() --返回满足条件的字符或字符串

regexp_substr(字符串,正则,开始,匹配次数，匹配选项)
regexp_substr(‘I love oracle very much’,‘o[[:alpha:]]{4}’)
以o开始的，后面至少接4个字母的字符串
参考Oracle 支持正则表达式的函数

正则匹配中文

• lengthb(name)！=length(name) 说明是中文
• 正则匹配中文’[^\x00-\xff]’

des加密

参考ORACLE定义DES加密解密及MD5加密函数