###10. Oracle字符集
####10.1 Toad client显示乱码错误
-------------------------------------Server端：字符集如下select userenv('language') from dual;-------------------------------------AMERICAN_AMERICA.WE8ISO8859P1-------------------------------------Client端：字符集如下select userenv('language') from dual;-------------------------------------SIMPLIFIED CHINESE_CHINA.WE8ISO8859P1#编译改procedure，错误显示乱码CREATE OR REPLACE PROCEDURE TEST."SP_LMGETSITEIDLIST" (iSiteID NUMBER ,sMtgDoaminName varchar2,RC1 IN OUT SP_LMGETSITEIDPkg1.RCT1)ASBEGIN begin if iSiteID > 0 then OPEN RC1 FOR select s.siteid from wbxsite s, wbxsitemeetingdomain m, wbxmeetingdomain a where s.siteid = m.siteid and s.active = 1 and s.siteid > iSiteID and m.domainid = a.domainid and a.domainname = sMtgDoaminName union select s.siteid from wbxsite s, wbxsitemeetingdomain m, GSB_PRI_GSB_mtgdomain g, wbxmeetingdomain a where s.siteid = m.siteid and s.active = 1 and s.siteid > iSiteID and g.primtgdomainid=a.domainid and m.domainid=g.gsbmtgdomainid and a.domainname=sMtgDoaminName; else OPEN RC1 FOR select s.siteid from wbxsite s, wbxsitemeetingdomain m, wbxmeetingdomain a where s.siteid = m.siteid and s.active = 1 and m.domainid = a.domainid and a.domainname = sMtgDoaminName union select s.siteid from wbxsite s, wbxsitemeetingdomain m, GSB_PRI_GSB_mtgdomain g, wbxmeetingdomain a where s.siteid = m.siteid and s.active = 1 and g.primtgdomainid=a.domainid and m.domainid=g.gsbmtgdomainid and a.domainname = sMtgDoaminName; end if; exception when others then null; end;END sp_lmGetSiteidList;/[Error] ORA-00942 (17: 63): PL/SQL: ORA-00942: ¿¿¿¿¿¿¿#修改注册表，Oracle 字符集regedit ---> HKEY_LOCAL_MACHINE\SOFTWARE\oracle\KEY_OraClient11g_home1 ---> NLS_LANG = AMERICAN_AMERICA.WE8ISO8859P1#重新编译procedurePL/SQL: ORA-00942: table or view does not exist ####10.2 Client端显示中文
ORACLE数据库有国家字符集（national character set）与数据库字符集(database character set)之分。两者都是在创建数据库时需要设置的。国家字符集主要是用于NCHAR、NVARCHAR、NCLOB类型的字段数据，而数据库字符集使用很广泛，它用于：CHAR、VARCHAR、CLOB、LONG类型的字段数据；
ORACLE的字符集名字一般由以下部分组成：语言或区域、表示一个字符的比特位数、标准字符集名称（可选项，S或C，表示服务器或客户端）。ORACLE字符集UTF8与UTFE不符合此规定，其它基本都是这种格式。NLS_LANG=<Language>_<Territory>.<Clients Characterset>
NLS( National Language Support)国家语言支持。NLS是数据库的一个非常强大的特性，它控制着数据的许多方面：比如数据如何存储，一般来说它控制着以下两个方面：
- 文本数据持久存储在磁盘上时如何编码
- 透明的将数据从一个字符集转换到另外一个字符集。
对于中文字符集ZHS16GBK，表示简体中文（ZHT为繁体中文），一个字符需要16位比特，标准的字符集名称为GBK。而ZHS16CGB231280表示简体中文，一个字符需要16位比特，标准的字符集名称为GB231280，属于我们前面提过的1981年发布的GB2312－80标准。虽然我们说，GBK编码标准是GB2312编码标准的扩展，但是数据库字符集ZHS16GBK与ZHS16CGB231280之间却不是严格的超集与子集的关系，主要是有些汉字的编码在两个字符集中的数值是不同的，因此它们进行字符集转换时会出现问题。
查看字符集参数
#查看NLS_CHARACTERSET：字符集，NLS_NCHAR_CHARACTERSET：国家字符集#实例字符集环境SQL> select * from nls_instance_parameters;PARAMETER VALUE------------------------------ ------------------------------NLS_LANGUAGE SIMPLIFIED CHINESENLS_TERRITORY CHINANLS_SORTNLS_DATE_LANGUAGENLS_DATE_FORMATNLS_CURRENCYNLS_NUMERIC_CHARACTERSNLS_ISO_CURRENCYNLS_CALENDARNLS_TIME_FORMATNLS_TIMESTAMP_FORMATPARAMETER VALUE------------------------------ ------------------------------NLS_TIME_TZ_FORMATNLS_TIMESTAMP_TZ_FORMATNLS_DUAL_CURRENCYNLS_COMP BINARYNLS_LENGTH_SEMANTICS BYTENLS_NCHAR_CONV_EXCP FALSE17 rows selected.#数据库可用字符集参数设置SQL> select * from V$NLS_VALID_VALUES;PARAMETER VALUE ISDEP------------------------------ ------------------------------ -----LANGUAGE AMERICAN FALSELANGUAGE GERMAN FALSELANGUAGE FRENCH FALSELANGUAGE CANADIAN FRENCH FALSELANGUAGE SPANISH FALSE#数据库服务器字符集SQL> select * from nls_database_parameters;PARAMETER VALUE------------------------------ ------------------------------NLS_LANGUAGE AMERICANNLS_TERRITORY AMERICANLS_CURRENCY $NLS_ISO_CURRENCY AMERICANLS_NUMERIC_CHARACTERS .,NLS_CHARACTERSET ZHS16GBKNLS_CALENDAR GREGORIANNLS_DATE_FORMAT DD-MON-RRNLS_DATE_LANGUAGE AMERICANNLS_SORT BINARYNLS_TIME_FORMAT HH.MI.SSXFF AMPARAMETER VALUE------------------------------ ------------------------------NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AMNLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZRNLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZRNLS_DUAL_CURRENCY $NLS_COMP BINARYNLS_LENGTH_SEMANTICS BYTENLS_NCHAR_CONV_EXCP FALSENLS_NCHAR_CHARACTERSET UTF8NLS_RDBMS_VERSION 11.2.0.2.020 rows selected.#客户端字符集环境#USERENV、 V$NLS_PARAMETERS表示当前字符集环境。如果你在客户端执行，则表示客户端字符集环境。SELECT * FROM V$NLS_PARAMETERS;PARAMETER VALUE ------------------------- ------------------------------NLS_LANGUAGE AMERICAN NLS_TERRITORY AMERICA NLS_CURRENCY $ NLS_ISO_CURRENCY AMERICA NLS_NUMERIC_CHARACTERS ., NLS_CALENDAR GREGORIAN NLS_DATE_FORMAT DD-MON-RR NLS_DATE_LANGUAGE AMERICAN NLS_CHARACTERSET ZHS16GBK NLS_SORT BINARY NLS_TIME_FORMAT HH.MI.SSXFF AM NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR NLS_DUAL_CURRENCY $ NLS_NCHAR_CHARACTERSET UTF8 NLS_COMP BINARY NLS_LENGTH_SEMANTICS BYTE NLS_NCHAR_CONV_EXCP FALSE 19 rows selected.select userenv('language') from dual;USERENV('LANGUAGE') ----------------------------AMERICAN_AMERICA.ZHS16GBK 客户端的字符集要求与服务器一致，才能正确显示数据库的非Ascii字符。如果多个设置存在的时候，优先级关系为：SQL Function >Alter session>环境变量>注册表>参数文件 字符集要求一致，但是语言设置却可以不同，语言设置建议用英文。如字符集是zhs16gbk，则nls_lang可以是American_America.zhs16gbk。
#数据库服务器字符集SQL> select * from nls_database_parameters;PARAMETER VALUE------------------------------ ------------------------------NLS_LANGUAGE AMERICANNLS_TERRITORY AMERICANLS_CHARACTERSET ZHS16GBK#客户端字符集环境SQL> select * from V$NLS_PARAMETERS;PARAMETER VALUE ------------------------- ------------------------------NLS_LANGUAGE AMERICAN NLS_TERRITORY AMERICA NLS_CHARACTERSET ZHS16GBK #客户端查看数据显示中文select * from t1; ID NAME ---------- -------------------- 2 开发 3 测试