Oracle对用户敏感数据进行编码处理

由于系统运行时间比较长，没有对用户的身份证号、邮箱、手机号进行脱敏处理，后期对数据进行了编码。

更新表数据 sql

UPDATE sys_staff
SET MOBIL_PHONE = CASEWHEN MOBIL_PHONE IS NULL THEN ''ELSE utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(MOBIL_PHONE))) END,EMAIL       = CASEWHEN EMAIL IS NULL THEN ''ELSE utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(EMAIL))) END,EMP_CODE    = CASEWHEN EMP_CODE IS NULL THEN ''ELSE utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(EMP_CODE))) end;

查询 SQL 语句：

select ss.staff_id,ss.staff_name,ss.login_name,casewhen ss.mobil_phone is null then ''elseutl_raw.cast_to_varchar2(utl_encode.base64_decode(utl_raw.cast_to_raw(ss.mobil_phone))) end mobil_phone,ss.erp_code                                                       staff_erp_code,casewhen ss.email is null then ''elseutl_raw.cast_to_varchar2(utl_encode.base64_decode(utl_raw.cast_to_raw(ss.email))) end       email,ssr.is_default                                                    is_default_role,ssr.role_id,sr.role_name,sr.bg_id,sr.mg_id,sr.org_id
from sys_staff_role ssr,sys_staff ss,sys_role sr
where ssr.staff_id = ss.staff_idand ss.staff_status = '1'and ssr.role_id = sr.role_idand sr.role_status = '1'