Oracle 繁体转简体函数

01 创建代码表SQL

create table DM_FANTI_JIANTI_7434
(
  traditional VARCHAR2(100),
  simplified  VARCHAR2(100)
)
tablespace SJCL_DATA
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

02 函数SQL

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;

03 代码表

代码表下载