不同字符集下字符串类型的比对以及存汉字的比较
今天上午，之前测试都没有问题的过程，今天报错ora-06052的异常，查询之后发现原来是同一个字段在不同的表中定义的长度不一样，一个表中定义的是512，另一个表中定义的是500，现在分别用这两个字段定义了两个变量，当用512的变量给500的变量赋值时，如果赋值超过500就出问题了（确实超过500）。
随后我查了一下char，varchar，varchar，nvarchar这四种字符类型，以及这四种类型在utf8和gbk两种字符集下存储中文的情况，length和lengthb函数。
ora-12899:是向表中插入的值超过了字段的长度。
ora-06052:是在赋值时值超过了变量的长度。
length：统计的是字符串中字符的个数。
lengthb：统计的是字符串的字节数。
char2(10)，varchar(10)，varchar2(10)的字段：
在gbk数据库中可以存5个汉字，lengthb统计到每个汉字是2个字节。
在utf8数据库中可以存3个汉字，lengthb统计到每个汉字是3个字节。
nvarchar2(10)的字段：
在gbk数据库中可以存10个汉字。
在utf8数据库中可以存10个汉字。
-----------------------------------------
char：定长字符型，[1,2000]字节，不足则在后面补空格。
varchar：不定长字符型，[1,2000]字节。
varchar2：不定长字符型，[1,4000]字节，在pl/sql中最大长度是32767（没有测试）。
nvarchar：不定长字符型，[1,4000]字符。从下面测试情况看，在这两个不同字符集中都是按照2个字节来存储数据。
--这个地方不明白，utf8下不是每个汉字三个字节吗，怎么这里是两个字节。
---下面是测试语句
SQL> --查看数据库服务端的字符集SQL> select userenv('language') from dual; USERENV('LANGUAGE')----------------------------------------------------SIMPLIFIED CHINESE_CHINA.UTF8SQL> --查看dmp的字符集SQL> select nls_charset_name(to_number('0354','xxxx')) from dual; NLS_CHARSET_NAME(TO_NUMBER('03----------------------------------------ZHS16GBKSQL> --查看数据库字符集SQL> select * from nls_database_parameters; PARAMETER VALUE------------------------------ --------------------------------------------NLS_LANGUAGE AMERICANNLS_TERRITORY AMERICANLS_CURRENCY $NLS_ISO_CURRENCY AMERICANLS_NUMERIC_CHARACTERS .,NLS_CHARACTERSET UTF8NLS_CALENDAR GREGORIANNLS_DATE_FORMAT DD-MON-RRNLS_DATE_LANGUAGE AMERICANNLS_SORT BINARYNLS_TIME_FORMAT HH.MI.SSXFF AMNLS_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 AL16UTF16NLS_RDBMS_VERSION 10.2.0.4.0 20 rows selectedSQL> --查看客户端字符集SQL> select * from nls_instance_parameters; PARAMETER VALUE-------------------------------------------- --------------------------------------------NLS_LANGUAGE AMERICANNLS_TERRITORY AMERICANLS_SORT NLS_DATE_LANGUAGE NLS_DATE_FORMAT NLS_CURRENCY NLS_NUMERIC_CHARACTERS NLS_ISO_CURRENCY NLS_CALENDAR NLS_TIME_FORMAT NLS_TIMESTAMP_FORMAT NLS_TIME_TZ_FORMAT NLS_TIMESTAMP_TZ_FORMAT NLS_DUAL_CURRENCY NLS_COMP NLS_LENGTH_SEMANTICS BYTENLS_NCHAR_CONV_EXCP FALSE 17 rows selectedSQL> --查看会话字符集SQL> select * from nls_session_parameters; PARAMETER VALUE-------------------------------------------- --------------------------------------------NLS_LANGUAGE SIMPLIFIED CHINESENLS_TERRITORY CHINANLS_CURRENCY ￥NLS_ISO_CURRENCY CHINANLS_NUMERIC_CHARACTERS .,NLS_CALENDAR GREGORIANNLS_DATE_FORMAT DD-MON-RRNLS_DATE_LANGUAGE SIMPLIFIED CHINESENLS_SORT BINARYNLS_TIME_FORMAT HH.MI.SSXFF AMNLS_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 FALSE 17 rows selectedSQL> drop table ttt1 purge; Table droppedSQL> drop table ttt2 purge; Table droppedSQL> drop table ttt3 purge; Table droppedSQL> drop table ttt4 purge; drop table ttt4 purge ORA-00054: resource busy and acquire with NOWAIT specifiedSQL> create table ttt1(name char(10)); Table createdSQL> insert into ttt1 values('123'); 1 row insertedSQL> insert into ttt1 values('我爱你'); 1 row insertedSQL> insert into ttt1 values('我爱你我你'); insert into ttt1 values('我爱你我你') ORA-12899: value too large for column "CDYX"."TTT1"."NAME" (actual: 15, maximum: 10)SQL> select name,length(name),lengthb(name),dump(name) from ttt1; NAME LENGTH(NAME) LENGTHB(NAME) DUMP(NAME)---------- ------------ ------------- --------------------------------------------123 10 10 Typ=96 Len=10: 49,50,51,32,32,32,32,32,32,32我爱你 4 10 Typ=96 Len=10: 230,136,145,231,136,177,228,189,160,32SQL> create table ttt2(name varchar(10)); Table createdSQL> insert into ttt2 values('123'); 1 row insertedSQL> insert into ttt2 values('我爱你'); 1 row insertedSQL> insert into ttt2 values('我爱你我爱你'); insert into ttt2 values('我爱你我爱你') ORA-12899: value too large for column "CDYX"."TTT2"."NAME" (actual: 18, maximum: 10)SQL> insert into ttt2 values('我爱你我你'); insert into ttt2 values('我爱你我你') ORA-12899: value too large for column "CDYX"."TTT2"."NAME" (actual: 15, maximum: 10)SQL> select name,length(name),lengthb(name),dump(name) from ttt2; NAME LENGTH(NAME) LENGTHB(NAME) DUMP(NAME)---------- ------------ ------------- --------------------------------------------123 3 3 Typ=1 Len=3: 49,50,51我爱你 3 9 Typ=1 Len=9: 230,136,145,231,136,177,228,189,160SQL> create table ttt3(name varchar2(10)); Table createdSQL> insert into ttt3 values('123'); 1 row insertedSQL> insert into ttt3 values('我爱你'); 1 row insertedSQL> insert into ttt3 values('我爱你我爱你'); insert into ttt3 values('我爱你我爱你') ORA-12899: value too large for column "CDYX"."TTT3"."NAME" (actual: 18, maximum: 10)SQL> insert into ttt3 values('我爱你我你'); insert into ttt3 values('我爱你我你') ORA-12899: value too large for column "CDYX"."TTT3"."NAME" (actual: 15, maximum: 10)SQL> select name,length(name),lengthb(name),dump(name) from ttt3; NAME LENGTH(NAME) LENGTHB(NAME) DUMP(NAME)---------- ------------ ------------- --------------------------------------------123 3 3 Typ=1 Len=3: 49,50,51我爱你 3 9 Typ=1 Len=9: 230,136,145,231,136,177,228,189,160SQL> create table ttt4(name nvarchar2(10)); create table ttt4(name nvarchar2(10)) ORA-00955: name is already used by an existing objectSQL> insert into ttt4 values('123'); 1 row insertedSQL> insert into ttt4 values('我爱你'); 1 row insertedSQL> insert into ttt4 values('我爱你我爱你'); 1 row insertedSQL> insert into ttt4 values('我爱你我你'); 1 row insertedSQL> insert into ttt4 values('我爱你我你我爱你我你'); 1 row insertedSQL> insert into ttt4 values('我爱你我你我爱你我你1'); insert into ttt4 values('我爱你我你我爱你我你1') ORA-12899: value too large for column "CDYX"."TTT4"."NAME" (actual: 11, maximum: 10)SQL> select name,length(name),lengthb(name),dump(name) from ttt4; NAME LENGTH(NAME) LENGTHB(NAME) DUMP(NAME)-------------------- ------------ ------------- --------------------------------------------123 3 6 Typ=1 Len=6: 0,49,0,50,0,51我爱你 3 6 Typ=1 Len=6: 98,17,114,49,79,96我爱你我爱你 6 12 Typ=1 Len=12: 98,17,114,49,79,96,98,17,114,49,79,96我爱你我你 5 10 Typ=1 Len=10: 98,17,114,49,79,96,98,17,79,96我爱你我你我爱你我你 10 20 Typ=1 Len=20: 98,17,114,49,79,96,98,17,79,96,98,17,114,49,79,96,98,17,79,96123 3 6 Typ=1 Len=6: 0,49,0,50,0,51我爱你 3 6 Typ=1 Len=6: 98,17,114,49,79,96我爱你我爱你 6 12 Typ=1 Len=12: 98,17,114,49,79,96,98,17,114,49,79,96我爱你我你 5 10 Typ=1 Len=10: 98,17,114,49,79,96,98,17,79,96我爱你我你我爱你我你 10 20 Typ=1 Len=20: 98,17,114,49,79,96,98,17,79,96,98,17,114,49,79,96,98,17,79,96 10 rows selectedSQL> commit; Commit complete SQL>