Oracle 中英文字符集转换解决方案

Oracle 中英文字符集转换解决 技术文档 Oracle 中英文字符集转换解决方案 背景: 目前国内用户使用的ORACLE字符集，大部分所采用的都是 ZHS16GBK – GBK 16位简体中文 ，而很少采用US7ASCII – ASCII 7位美国英语 。 现在所遇到的问题就是，如何解决在US7ASCII字符集和ZHS16GBK字符集的数据库实例下进行数据访问，以及进行导入、导出数据的时候出现汉字乱码的问题。 解决方案: 目前比较好的解决方案在网上几乎是没有的，也是行不通的，对于其他单位或许会有相应的解决方案，但是也会因为是内部资料或者保密，或者流失。经过近期的研究，总算有了一个比较好的解决方案。其中采用的主要方法是利用ORACLE的utl_raw包中的cast_to_varchar2()、cast_to_raw()两个方法。服务器架构如下图所示: 应用服务器源数据库实例业务数据库实例 (US7ASCII)(ZHS16GBK) dblink 源数据库服务器目标数据库服务器 图1.1 服务器数据存储结构 如上图所示，有两台服务器，目标要求在“应用服务器”访问的“目标数据库服务器”上能读取“源数据库服务器”上的数据，但是由于“目标数据库服务器”和“源数据服务器”的数据库字符集不一致，所以简单的建立一个dblink进行数据的访问就会出现汉字乱码的问题，所以还要进行一些字符的转换工作，步骤如下: 1、首先在“源数据库服务器”上建立视图，供“目标数据库服务器”调用。视图建立的规则:将视图中中文汉字的字段进行转换，转换函数为cast_to_raw()， 如 a表中有a1,a2,a3三个字段，其中a2字段为中文汉字，那么建立的视图的语句就可以是: create view v_a as select a1 as a1, utl_raw.cast_to_raw(a2) as a2, a3 as a3 from a; 2、在“目标数据库服务器”建立dblink: 如: create database link dbl_1 connect to test identified by test using 'oradb'; 3、在“目标数据库服务器”直接使用dblink进行数据访问，当然需要加上中文汉字转换函数了。格式如下: QQ- 154955209 技术文档 select a1, utl_raw.cast_to_varchar2(a2), a3 from v_a@bdl_1; 如上所示，我们基本就能完成在“目标数据服务器”对“源数据库服务器”的数据访问，但是目前网站程序不允许实时去访问“源数据库服务器”，而且也不能在“源数据库服务器”建立视图。所以要在自己的“目标数据库服务器”上同时建立一个ZHS16GBK业务数据库实例和一个US7ASCII的中间转换数据库实例，通过“中间转换数据库实例”使用dblink连接“源数据库服务器”实现数据的共享和实时连接，然后在“业务数据库实例”下通过dblink再连接“中间转换数据库实例”进行字符集的转换。具体图示如下: 应用服务器 中间转换数据库实例业务数据库实例源数据库实例(US7ASCII)(ZHS16GBK)(US7ASCII)dblinkdblink 源数据库服务器目标数据库服务器 图1.2 新版网站数据库服务器结构 具体实现方法如下: 1、在“目标数据库服务器”建立两个不同字符集的数据库实例; 注:这里在安装数据库实例的时候，最好先安装 US7ASCII ，再安装 ZHS16GBK。这样能保证操作系 统最后的字符集是ZHS16GBK，如果相反的话，则需要修改操作系统的注册表或者配置文件 (window注册表修改位置: HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE ,搜索 NLS_LANG 键值，将其修改为 SIMPLIFIED CHINESE_CHINA.ZHS16GBK) 2、在“中间转换数据库实例”建立dblink链接“源数据库实例”; create database link CB_DBAST.REGRESS.RDBMS.DEV.US.ORACLE.COM connect to JYZX identified by "xzyj" using '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.128.5.43)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = dbast) ) )'; 3、在“中间转换数据库实例”建立视图访问“源数据库实例”的数据;以其中一个表作为例 子。 QQ- 154955209 技术文档 注:将所有字段类型为 字符型(varcha2)的字段全部使用 UTL_RAW.CAST_TO_RAW() 方法进行转 换;存储过程如下: create or replace procedure pro_createview is cbsql varchar2(3000); cb_cname varchar2(30); cb_coltype varchar2(106); sbsql varchar2(3000); scbdata varchar2(3000); type cur_data is ref cursor; cdata cur_data; cursor cur_cb is select view_name from all_views@cb_dbast where owner = 'JYZX'; begin for rec_cb IN cur_cb loop cbsql := 'select cname , coltype from col@cb_dbast where tname= ''' || rec_cb.view_name || ''''; scbdata := ''; open cdata for cbsql; loop fetch cdata into cb_cname, cb_coltype; exit when cdata%NOTFOUND; if cb_coltype = 'VARCHAR2' then scbdata := scbdata || ' UTL_RAW.CAST_TO_RAW(' || cb_cname || ') ' || cb_cname || ','; elsif cb_coltype = 'LONG' then scbdata := scbdata; else scbdata := scbdata || cb_cname || ','; end if; end loop; close cdata; scbdata := substr(scbdata, 0, length(scbdata) - 1); sbsql := ' create or replace view v_' || rec_cb.view_name || ' as select ' || scbdata || ' from ' || rec_cb.view_name || '@cb_dbast'; execute immediate sbsql; commit; end loop; end pro_createview; 4、在“业务数据库实例”建立dblink链接“中间转换数据库实例”; create database link CB.REGRESS.RDBMS.DEV.US.ORACLE.COM QQ- 154955209 技术文档 connect to CB identified by "cb" using 'ora_cb'; 5、在“业务数据库实例”建立数据转换临时表和存储过程 (1)建立临时表 CB_OBJ(船舶转换数据表) CB_LOG(船舶数据转换日志表) -------------------------------------------------------------------------------- create table CB_OBJ ( CB_TBL VARCHAR2(30), CB_VIEW VARCHAR2(30) ) tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); -- Add comments to the table comment on table CB_OBJ is '船舶转换数据'; -- Add comments to the columns comment on column CB_OBJ.CB_TBL is '船舶转换数据表名'; -- Add comments to the columns comment on column CB_OBJ.CB_VIEW is '船舶转换来源视图'; -------------------------------------------------------------------------------- create table CB_LOG ( CB_OBJ VARCHAR2(30), CB_DATE DATE, CB_NUM NUMBER ) tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( QQ- 154955209 技术文档 initial 64K minextents 1 maxextents unlimited ); -- Add comments to the table comment on table CB_LOG is '船舶转换数据日志表'; -- Add comments to the columns comment on column CB_LOG.CB_OBJ is '船舶数据表名'; comment on column CB_LOG.CB_DATE is '转换时间'; comment on column CB_LOG.CB_NUM is '转换数量'; -------------------------------------------------------------------------------- (2)向临时表中插入数据 insert into CB_OBJ (CB_TBL, CB_VIEW) values ('BWXX', 'V_BWXX@CB'); insert into CB_OBJ (CB_TBL, CB_VIEW) values ('B_00031', 'V_B_00031@CB'); insert into CB_OBJ (CB_TBL, CB_VIEW) values ('B_00038', 'V_B_00038@CB'); insert into CB_OBJ (CB_TBL, CB_VIEW) values ('B_00158', 'V_B_00158@CB'); insert into CB_OBJ (CB_TBL, CB_VIEW) values ('CBDA', 'V_CBDA@CB'); insert into CB_OBJ (CB_TBL, CB_VIEW) values ('CBDT_DTJH', 'V_CBDT_DTJH@CB'); insert into CB_OBJ (CB_TBL, CB_VIEW) values ('CBDT_DTSJ', 'V_CBDT_DTSJ@CB'); insert into CB_OBJ (CB_TBL, CB_VIEW) values ('CBDT_JCZ', 'V_CBDT_JCZ@CB'); insert into CB_OBJ (CB_TBL, CB_VIEW) values ('CBDT_XCDT', 'V_CBDT_XCDT@CB'); insert into CB_OBJ (CB_TBL, CB_VIEW) values ('CBZY_FCZY', 'V_CBZY_FCZY@CB'); insert into CB_OBJ (CB_TBL, CB_VIEW) values ('CBZY_PG', 'V_CBZY_PG@CB'); insert into CB_OBJ (CB_TBL, CB_VIEW) values ('CBZY_PG_FB', 'V_CBZY_PG_FB@CB'); insert into CB_OBJ (CB_TBL, CB_VIEW) values ('CBZY_ZYJH', 'V_CBZY_ZYJH@CB'); insert into CB_OBJ (CB_TBL, CB_VIEW) values ('CCMB', 'V_CCMB@CB'); insert into CB_OBJ (CB_TBL, CB_VIEW) values ('CDXX', 'V_CDXX@CB'); insert into CB_OBJ (CB_TBL, CB_VIEW) values ('CGSXX', 'V_CGSXX@CB'); insert into CB_OBJ (CB_TBL, CB_VIEW) values ('TBSJ', 'V_TBSJ@CB'); insert into CB_OBJ (CB_TBL, CB_VIEW) values ('VIEW_ZZJG_CDXX', 'V_VIEW_ZZJG_CDXX@CB'); insert into CB_OBJ (CB_TBL, CB_VIEW) values ('WQD', 'V_WQD@CB'); insert into CB_OBJ (CB_TBL, CB_VIEW) values ('YQB', 'V_YQB@CB'); insert into CB_OBJ (CB_TBL, CB_VIEW) values ('ZZJG', 'V_ZZJG@CB'); commit; (3)建立船舶数据表 建立如下存储过程进行执行 create or replace procedure pro_cb_createtable is cbsql varchar2(3000); sbsql varchar2(3000); QQ- 154955209 技术文档 cbdata varchar2(3000); cb_cname varchar2(30); cb_coltype varchar2(106); cb_width varchar2(30); cb_nulls varchar2(30); type cur_data is ref cursor; cdata cur_data; cursor cur_cb is select cb_tbl, cb_view from cb_obj; begin for rec_cb IN cur_cb loop cbsql := ' select cname,coltype,width ,nulls from col@cb_dbast where tname= ''' || rec_cb.cb_tbl || ''''; cbdata := ''; open cdata for cbsql; loop fetch cdata into cb_cname, cb_coltype, cb_width, cb_nulls; exit when cdata%NOTFOUND; if cb_coltype in ('VARCHAR2', 'CHAR') then cbdata := cbdata || cb_cname || ' ' || cb_coltype || '(' || cb_width || ') ' || cb_nulls || ' ,'; else cbdata := cbdata || cb_cname || ' ' || cb_coltype || ' ' || cb_nulls || ' ,'; end if; end loop; close cdata; cbdata := substr(cbdata, 0, length(cbdata) - 1); sbsql := ' create table ' || rec_cb.cb_tbl || ' (' || cbdata || ') '; execute immediate sbsql; end loop; end pro_cb_createtable; (4)船舶数据转换存储过程 create or replace procedure pro_cb is cbsql varchar2(3000); --执行取表字段名称和字段类型的动态sql cb_cname varchar2(30); -- 取表的字段名称 cb_coltype varchar2(106); --取表的字段类型 sbsql varchar2(3000); --执行插入操作的动态sql scbdata varchar2(3000); --拼写插入动态sql select的字段 QQ- 154955209 技术文档 icbdata varchar2(3000); --拼写插入动态sql into的字段 type cur_data is ref cursor; --定义动态sql类型 cdata cur_data; --临时表游标 cursor cur_cb is select cb_tbl, cb_view from cb_obj; begin --循环游标 循环临时表中需要转换的表单 for rec_cb IN cur_cb loop --定义读取字段名称和字段类型的动态sql cbsql := 'select cname , coltype from col where tname= ''' || rec_cb.cb_tbl || ''''; --初始化变量 icbdata := ''; scbdata := ''; --打开动态sql游标 open cdata for cbsql; --循环动态sql loop fetch cdata into cb_cname, cb_coltype; exit when cdata%NOTFOUND; if cb_coltype = 'VARCHAR2' then scbdata := scbdata || ' utl_raw.cast_to_varchar2(' || cb_cname || '), '; icbdata := icbdata || cb_cname || ','; else icbdata := icbdata || cb_cname || ','; scbdata := scbdata || cb_cname || ','; end if; end loop; --关闭动态sql游标 close cdata; --对拼写的字段进行处理去除最后的"," icbdata := substr(icbdata, 0, length(icbdata) - 1); scbdata := substr(scbdata, 0, length(scbdata) - 1); sbsql := ' insert into ' || rec_cb.cb_tbl || ' (' || icbdata || ') select ' || scbdata || ' from ' || rec_cb.cb_view; --清除原始数据 execute immediate ' truncate table ' || rec_cb.cb_tbl; commit; --此处必须提交，不然下面执行的时候会死锁 --执行插入操作 execute immediate sbsql; commit; QQ- 154955209 技术文档 end loop; end pro_cb; 6、在“业务数据库实例”建立job定期执行存储过程进行数据更新。 declare job number ; begin sys.dbms_job.SUBMIT(job => job, what => 'pro_cb;', next_date => to_date('20-10-2009 09:22:00', 'dd-mm-yyyy hh24:mi:ss'), interval => 'trunc(sysdate)+1+1/24'); --24小时执行一次 commit; end; / 附录:oracle JOB使用详解 每天1点执行的oracle JOB样例 DECLARE X NUMBER; BEGIN SYS.DBMS_JOB.SUBMIT ( job => X, what => 'ETL_RUN_D_Date;', next_date => to_date('2009-08-26 01:00:00','yyyy-mm-dd hh24:mi:ss'), interval => 'trunc(sysdate)+1+1/24', no_parse => FALSE ); SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x)); COMMIT; END; / 以上是明确指定每天的1点执行此job,如果指定是每天中午12点执行interval需要指定为 'trunc(sysdate)+1+12/24',如果仅仅指定interval为一天,这样当你手工用dbms_job.run(job)去运行一 次时,job每天的执行时间是会改变的,如果你想job每天在固定时间执行,可以参考上面的例子. 初始化相关参数job_queue_processes alter system set job_queue_processes=39 scope=spfile;//最大值不能超过1000 ;job_queue_interval = 10 //调度作业刷新频率秒为单位 QQ- 154955209 技术文档 job_queue_process 表示oracle能够并发的job的数量，可以通过语句 show parameter job_queue_process; 来查看oracle中job_queue_process的值。当job_queue_process值为0时表示全部停止oracle的 job，可以通过语句 ALTER SYSTEM SET job_queue_processes = 10; 来调整启动oracle的job。 相关视图: dba_jobs all_jobs user_jobs dba_jobs_running 包含正在运行job相关信息 ,,,,,,,,,,,,,,,,,,,,,,,,, 提交job语法: begin sys.dbms_job.submit(job => :job, what => 'P_CLEAR_PACKBAL;', next_date => to_date('04-08-2008 05:44:09', 'dd-mm-yyyy hh24:mi:ss'), interval => 'sysdate+ 1/360'); commit; end; / ,,,,,,,,,,,,,,,,,,,,,,,,, 创建JOB variable jobno number; begin dbms_job.submit(:jobno, 'P_CRED_PLAN;',SYSDATE,'SYSDATE+1/2880',TRUE); commit; 运行JOB SQL> begin dbms_job.run(:job1); end; / 删除JOB SQL> begin dbms_job.remove(:job1); end; / DBA_JOBS ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, ,, 字段(列) 类型 描述 JOB NUMBER 任务的唯一标示号 LOG_USER VARCHAR2(30) 提交任务的用户 PRIV_USER VARCHAR2(30) 赋予任务权限的用户 QQ- 154955209 技术文档 SCHEMA_USER VARCHAR2(30) 对任务作语法分析的用户模式 LAST_DATE DATE 最后一次成功运行任务的时间 LAST_SEC VARCHAR2(8) 如HH24:MM:SS格式的last_date日期的小时，分钟和秒 THIS_DATE DATE 正在运行任务的开始时间，如果没有运行任务则为null THIS_SEC VARCHAR2(8) 如HH24:MM:SS格式的this_date日期的小时，分钟和秒 NEXT_DATE DATE 下一次定时运行任务的时间 NEXT_SEC VARCHAR2(8) 如HH24:MM:SS格式的next_date日期的小时，分钟和秒 TOTAL_TIME NUMBER 该任务运行所需要的总时间，单位为秒 BROKEN VARCHAR2(1) 标志参数，Y标示任务中断，以后不会运行 INTERVAL VARCHAR2(200) 用于计算下一运行时间的表达式 FAILURES NUMBER 任务运行连续没有成功的次数 WHAT VARCHAR2(2000) 执行任务的PL/SQL块 CURRENT_SESSION_LABEL RAW MLSLABEL 该任务的信任Oracle会话符 CLEARANCE_HI RAW MLSLABEL 该任务可信任的Oracle最大间隙 CLEARANCE_LO RAW MLSLABEL 该任务可信任的Oracle最小间隙 NLS_ENV VARCHAR2(2000) 任务运行的NLS会话设置 MISC_ENV RAW(32) 任务运行的其他一些会话参数 ,,,,,,,,,,,,,,,,,,,,,,,,,, 描述 INTERVAL参数值 每天午夜12点 'TRUNC(SYSDATE + 1)' 每天早上8点30分 'TRUNC(SYSDATE + 1) + (8*60+30)/(24*60)' 每星期二中午12点 'NEXT_DAY(TRUNC(SYSDATE ), ''TUESDAY'' ) + 12/24' 每个月第一天的午夜12点 'TRUNC(LAST_DAY(SYSDATE ) + 1)' 每个季度最后一天的晚上11点 'TRUNC(ADD_MONTHS(SYSDATE + 2/24, 3 ), 'Q' ) -1/24' 每星期六和日早上6点10分 'TRUNC(LEAST(NEXT_DAY(SYSDATE, ''SATURDAY"), NEXT_DAY(SYSDATE, "SUNDAY"))) + (6×60+10)/(24×60)' ,,,,,,,,,,,,,,,,,,,,,,,,,, 1:每分钟执行 Interval => TRUNC(sysdate,'mi') + 1/ (24*60) 或 Interval => sysdate+1/1440 2:每天定时执行 例如:每天的凌晨1点执行 Interval => TRUNC(sysdate) + 1 +1/ (24) 3:每周定时执行 例如:每周一凌晨1点执行 Interval => TRUNC(next_day(sysdate,'星期一'))+1/24 4:每月定时执行 例如:每月1日凌晨1点执行 Interval =>TRUNC(LAST_DAY(SYSDATE))+1+1/24 5:每季度定时执行 例如每季度的第一天凌晨1点执行 Interval => TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 1/24 6:每半年定时执行 QQ- 154955209 技术文档 例如:每年7月1日和1月1日凌晨1点 Interval => ADD_MONTHS(trunc(sysdate,'yyyy'),6)+1/24 7:每年定时执行 例如:每年1月1日凌晨1点执行 Interval =>ADD_MONTHS(trunc(sysdate,'yyyy'),12)+1/24 QQ- 154955209