ZHS16GBK，ZHS32GB18030，AL16UTF16一些字符的测试

Unicode Character Sets In The Oracle Database
Overview
Oracle started supporting Unicode based character sets in Oracle7. Here is a summary of the Unicode character sets supported in Oracle:
Characterset Name RDBMS version Unicode version
AL24UTFFSS 7.2-8.1 1.1
UTF8 8.0-11g 2.1 (8.0-8.1.6) , 3.0 (8.1.7-11g)
UTFE 8.0-11g 2.1 (8.0-8.1.6) , 3.0 (8.1.7-11g)
AL32UTF8* 9.0-11g 3.0 (9.0), 3.1 (9.2), 3.2 (10.1), 4.01 (10.2), 5.0 (11.1).
AL16UTF16** 9.0-11g 3.0 (9.0), 3.1 (9.2), 3.2 (10.1), 4.01 (10.2), 5.0 (11.1).

* cannot be used as NLS_NCHAR_CHARACTERSET , can only be used as NLS_CHARACTERSET
** can only be used as NLS_NCHAR_CHARACTERSET, cannot be used as NLS_CHARACTERSET



我们考虑几个字符集合

ZHS16CGB231280，ZHS16GBK，ZHS32GB18030，AL32UTF8

针对这几种字符集合，从左到右，能显示的字符集依次增加
在测试过程中，ZHS16CGB231280针对某些字符不能正常显示，ZHS16GBK，ZHS32GB18030可以正常显示，
ZHS32GB18030理论上比ZHS16GBK能显示更多的字符
AL32UTF8使用3个字节保留汉字，在空间上有浪费，但是显示的字符能够更多



1)------------------------------for 10g---------------------------------------------------
在这个case里，创建数据库的时候
CHARACTER SET zhs32gb18030
NATIONAL CHARACTER SET AL16UTF16
------------------------------------------------------------------------------------------
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

set lines 200
set pages 100

col parameter for a30
col value for a20
col ISDEPRECATED for a10
spool valid_character.txt
SELECT * FROM v$nls_valid_values WHERE parameter = 'CHARACTERSET';

col parameter for a30
col value for a60
select * from nls_database_parameters
PARAMETER VALUE
------------------------------ ------------------------------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET ZHS32GB18030
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
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_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_RDBMS_VERSION 10.2.0.4.0

create user scott identified by tiger;
grant connect,resource to scott;
conn scott/tiger
drop table testchar1;
drop table testchar2;
drop table testchar3;
drop table testchar4;
create table testchar1(a1 varchar2(4),a2 varchar2(4),a3 varchar2(4),a4 varchar2(4));
create table testchar2(a1 nvarchar2(4),a2 nvarchar2(4),a3 nvarchar2(4),a4 nvarchar2(4));
create table testchar3(a1 char(4),a2 char(4),a3 char(4),a4 char(4));
create table testchar4(a1 nchar(4),a2 nchar(4),a3 nchar(4),a4 nchar(4));
insert into testchar1 values ('秊','裏','隣','兀');
insert into testchar2 values ('秊','裏','隣','兀');
insert into testchar3 values ('秊','裏','隣','兀');
insert into testchar4 values ('秊','裏','隣','兀');
commit;
SQL> select * from testchar1;
A1 A2 A3 A4
---- ---- ---- ----
秊 裏 隣 兀

SQL> select * from testchar2;
A1 A2 A3 A4
-------- -------- -------- --------
秊 裏 隣 兀

SQL> select * from testchar3;
A1 A2 A3 A4
---- ---- ---- ----
秊 裏 隣 兀

SQL> select * from testchar4;
A1 A2 A3 A4
-------- -------- -------- --------
秊 裏 隣 兀


2)------------------------------for 11g---------------------------------------------------
CHARACTER SET ZHS16GBK
NATIONAL CHARACTER SET AL16UTF16
------------------------------------------------------------------------------------------
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

set lines 200
set pages 100

col parameter for a30
col value for a20
col ISDEPRECATED for a10
spool valid_character.txt
SELECT * FROM v$nls_valid_values WHERE parameter = 'CHARACTERSET';

col parameter for a30
col value for a60
select * from nls_database_parameters
PARAMETER VALUE
------------------------------ ------------------------------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET ZHS16GBK
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
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_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_RDBMS_VERSION 11.1.0.6.0

create user scott identified by tiger;
grant connect,resource to scott;
conn scott/tiger
drop table testchar1;
drop table testchar2;
drop table testchar3;
drop table testchar4;
create table testchar1(a1 varchar2(4),a2 varchar2(4),a3 varchar2(4),a4 varchar2(4));
create table testchar2(a1 nvarchar2(4),a2 nvarchar2(4),a3 nvarchar2(4),a4 nvarchar2(4));
create table testchar3(a1 char(4),a2 char(4),a3 char(4),a4 char(4));
create table testchar4(a1 nchar(4),a2 nchar(4),a3 nchar(4),a4 nchar(4));
insert into testchar1 values ('秊','裏','隣','兀');
insert into testchar2 values ('秊','裏','隣','兀');
insert into testchar3 values ('秊','裏','隣','兀');
insert into testchar4 values ('秊','裏','隣','兀');
commit;
SQL> select * from testchar1;
A1 A2 A3 A4
---- ---- ---- ----
秊 裏 隣 兀

SQL> select * from testchar2;
A1 A2 A3 A4
-------- -------- -------- --------
秊 裏 隣 兀

SQL> select * from testchar3;
A1 A2 A3 A4
---- ---- ---- ----
秊 裏 隣 兀

SQL> select * from testchar4;
A1 A2 A3 A4
-------- -------- -------- --------
秊 裏 隣 兀

3)------------------------------for 11g---------------------------------------------------
CHARACTER SET zhs16cgb231280
NATIONAL CHARACTER SET AL16UTF16

由于创建数据库的时候采用的是ZHS16GBK，这里将字符集合修改成ZHS16CGB231280
------------------------------------------------------------------------------------------
一 首先查看数据库的字符集
sqlplus system/manager
col parameter for a40
col value for a40
set lines 200
select * from nls_database_parameters where PARAMETER='NLS_CHARACTERSET';

PARAMETER VALUE
---------------------------------------- ----------------------------------------
NLS_CHARACTERSET ZHS16GBK


二 如不对可按以下方法修改（建议先备份）

To change the database character set, perform the following step:

1、SHUTDOWN IMMEDIATE; -- or NORMAL
2、
3、STARTUP MOUNT;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
ALTER DATABASE OPEN;
ALTER DATABASE CHARACTER SET ZHS16CGB231280;
ALTER DATABASE CHARACTER SET INTERNAL_USE ZHS16CGB231280;
4、SHUTDOWN IMMEDIATE; -- or NORMAL
5、STARTUP;

三 再次确定数据库的字符集合

sqlplus system/manager
col parameter for a40
col value for a40
set lines 200
select * from nls_database_parameters where PARAMETER='NLS_CHARACTERSET';

PARAMETER VALUE
---------------------------------------- ----------------------------------------
NLS_CHARACTERSET ZHS16CGB231280

export NLS_LANG=AMERICAN_AMERICA.ZHS16CGB231280

conn scott/tiger
drop table testchar1;
drop table testchar2;
drop table testchar3;
drop table testchar4;
create table testchar1(a1 varchar2(4),a2 varchar2(4),a3 varchar2(4),a4 varchar2(4));
create table testchar2(a1 nvarchar2(4),a2 nvarchar2(4),a3 nvarchar2(4),a4 nvarchar2(4));
create table testchar3(a1 char(4),a2 char(4),a3 char(4),a4 char(4));
create table testchar4(a1 nchar(4),a2 nchar(4),a3 nchar(4),a4 nchar(4));
insert into testchar1 values ('秊','裏','隣','兀');
insert into testchar2 values ('秊','裏','隣','兀');
insert into testchar3 values ('秊','裏','隣','兀');
insert into testchar4 values ('秊','裏','隣','兀');
commit;

SQL> select * from testchar1;

A1 A2 A3 A4
---- ---- ---- ----
y? y? y? t@

SQL> select * from testchar2;

A1 A2 A3 A4
-------- -------- -------- --------
￡?￡? ￡?￡? ￡?￡? ￡?@

SQL> select * from testchar3;

A1 A2 A3 A4
---- ---- ---- ----
y? y? y? t@

SQL> select * from testchar4;

A1 A2 A3 A4
-------- -------- -------- --------
￡?￡? ￡?￡? ￡?￡? ￡?@

4)------------------------------for 11g---------------------------------------------------
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16

由于创建数据库的时候采用的是ZHS16GBK，这里将字符集合修改成AL32UTF8
export NLS_LANG=american_america.AL32UTF8
------------------------------------------------------------------------------------------
ALTER DATABASE CHARACTER SET INTERNAL_USE AL32UTF8;

col parameter for a40
col value for a40
set lines 200
select * from nls_database_parameters where PARAMETER='NLS_CHARACTERSET';
PARAMETER VALUE
---------------------------------------- ----------------------------------------
NLS_CHARACTERSET AL32UTF8


SQL> insert into testchar1 values ('y?','y?','y?','t@')
*
ERROR at line 1:
ORA-12899: value too large for column "SCOTT"."TESTCHAR1"."A1" (actual: 6, maximum: 4)


SQL>
1 row created.

SQL> insert into testchar3 values ('y?','y?','y?','t@')
*
ERROR at line 1:
ORA-12899: value too large for column "SCOTT"."TESTCHAR3"."A1" (actual: 6, maximum: 4)


SQL>
1 row created.

conn scott/tiger
drop table testchar1;
drop table testchar2;
drop table testchar3;
drop table testchar4;
create table testchar1(a1 varchar2(6),a2 varchar2(6),a3 varchar2(6),a4 varchar2(6));
create table testchar2(a1 nvarchar2(6),a2 nvarchar2(6),a3 nvarchar2(6),a4 nvarchar2(6));
create table testchar3(a1 char(6),a2 char(6),a3 char(6),a4 char(6));
create table testchar4(a1 nchar(6),a2 nchar(6),a3 nchar(6),a4 nchar(6));
insert into testchar1 values ('秊','裏','隣','兀');
insert into testchar2 values ('秊','裏','隣','兀');
insert into testchar3 values ('秊','裏','隣','兀');
insert into testchar4 values ('秊','裏','隣','兀');
commit;

SQL> select * from testchar1;

A1 A2 A3 A4
------ ------ ------ ------
秊 裏 隣 兀

SQL> select * from testchar2;

A1 A2 A3 A4
------------ ------------ ------------ ------------
秊 裏 隣 兀

SQL> select * from testchar3;

A1 A2 A3 A4
------ ------ ------ ------
秊 裏 隣 兀

SQL> select * from testchar4;

A1 A2 A3 A4
------------ ------------ ------------ ------------
秊 裏 隣 兀[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/48010/viewspace-1030324/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/48010/viewspace-1030324/