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

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

更新表数据 sql

UPDATE sys_staff
SET MOBIL_PHONE = CASE
                      WHEN MOBIL_PHONE IS NULL THEN ''
                      ELSE utl_raw.cast_to_varchar2(
                              utl_encode.base64_encode(
                                      utl_raw.cast_to_raw(MOBIL_PHONE))) END,
    EMAIL       = CASE
                      WHEN EMAIL IS NULL THEN ''
                      ELSE utl_raw.cast_to_varchar2(
                              utl_encode.base64_encode(
                                      utl_raw.cast_to_raw(EMAIL))) END,
    EMP_CODE    = CASE
                      WHEN 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,
       case
           when ss.mobil_phone is null then ''
           else
               utl_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,
       case
           when ss.email is null then ''
           else
               utl_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_id
  and ss.staff_status = '1'
  and ssr.role_id = sr.role_id
  and sr.role_status = '1'