Oracle 显示字段繁体转换为简体

今天遇到客户,公司是台湾国际化公司,数据库采用字符集AL32UTF8,字段存储字符均为繁体字，

但在大陆,需要与外围系统对接,需要转换为简体.

从数据库自带函数等，无法实现,找了一个存储过程自定义转换，貌似不错，介绍如下：

1.导入繁简体转换字库

imp system/manager file=/tmp/dm_fanti_jianti_7434.dmp fromuser=SJCL touser=APPS;

dm_fanti_jianti_7434.dmp下载:

链接：https://pan.baidu.com/s/1Q91Q9Tl7A6eYBG17RQk7iQ
提取码：aa8x

2.创建函数F_FANTI_TO_JIANTI
sqlplus apps/apps
执行F_FANTI_TO_JIANTI.txt

-------------------

CREATE OR REPLACE FUNCTION F_FANTI_TO_JIANTI(OLD_STRING IN VARCHAR2)
RETURN VARCHAR2 AS
LENGTH_NUM NUMBER := NULL;
OLD_WORD VARCHAR2(500) := NULL;
NEW_WORD VARCHAR2(500) := NULL;
ZZ_WORD VARCHAR2(500) := NULL;
I NUMBER := 0;
BEGIN
LENGTH_NUM := LENGTH(OLD_STRING);
FOR I IN REVERSE 1 .. LENGTH_NUM LOOP
OLD_WORD := SUBSTR(OLD_STRING, I, 1);
BEGIN
SELECT t.simplified
INTO NEW_WORD
FROM dm_fanti_jianti_7434 T
WHERE t.traditional = OLD_WORD;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NEW_WORD := OLD_WORD;
END;
ZZ_WORD := NEW_WORD || ZZ_WORD;
END LOOP;
RETURN ZZ_WORD;
END;

--------------------------

3.创建同义词
sqlplus apps/apps
SQL>create public synonym DM_FANTI_JIANTI_7434 for apps.DM_FANTI_JIANTI_7434;
SQL>create public synonym F_FANTI_TO_JIANTI for apps.F_FANTI_TO_JIANTI;

4.繁体转简体
sqlplus apps/apps
select name,f_fanti_to_jianti(name) from ps_employee_all;

注：字库中有7434对常用转换字符，可自定义往dm_fanti_jianti_7434 表中添加