--oracle官网中这样描述varchar2和nvarchar2 /* NVARCHAR2 DatatypeThe NVARCHAR2 datatype is a Unicode-only datatype. When you create a table with an NVARCHAR2 column, you supply the maximum number of characters it can hold. Oracle subsequently stores each value in the column exactly as you specify it, provided the value does not exceed the maximum length of the column.The maximum length of the column is determined by the national character set definition. Width specifications of character datatype NVARCHAR2 refer to the number of characters. The maximum column size allowed is 4000 bytes. Please refer to Oracle Database Globalization Support Guide for information on Unicode datatype support.VARCHAR2 DatatypeThe VARCHAR2 datatype specifies a variable-length character string. When you create a VARCHAR2 column, you supply the maximum number of bytes or characters of data that it can hold. Oracle subsequently stores each value in the column exactly as you specify it, provided the value does not exceed the column's maximum length of the column. If you try to insert a value that exceeds the specified length, then Oracle returns an error.You must specify a maximum length for a VARCHAR2 column. This maximum must be at least 1 byte, although the actual string stored is permitted to be a zero-length string (''). You can use the CHAR qualifier, for example VARCHAR2(10 CHAR), to give the maximum length in characters instead of bytes. A character is technically a code point of the database character set. CHAR and BYTE qualifiers override the setting of the NLS_LENGTH_SEMANTICS parameter, which has a default of bytes. For performance reasons, Oracle recommends that you use the NLS_LENGTH_SEMANTICS parameter to set length semantics and that you use the BYTE and CHAR qualifiers only when necessary to override the parameter. The maximum length of VARCHAR2 data is 4000 bytes. Oracle compares VARCHAR2 values using nonpadded comparison semantics.To ensure proper data conversion between databases with different character sets, you must ensure that VARCHAR2 data consists of well-formed strings. See Oracle Database Globalization Support Guide for more information on character set support.VARCHAR DatatypeDo not use the VARCHAR datatype. Use the VARCHAR2 datatype instead. Although the VARCHAR datatype is currently synonymous with VARCHAR2, the VARCHAR datatype is scheduled to be redefined as a separate datatype used for variable-length character strings compared with different comparison semantics. */
--oracle官网中这样描述varchar2和nvarchar2
/*
NVARCHAR2 DatatypeThe NVARCHAR2 datatype is a Unicode-only datatype. When you create a table with an NVARCHAR2 column, you supply the maximum number of characters it can hold. Oracle subsequently stores each value in the column exactly as you specify it, provided the value does not exceed the maximum length of the column.The maximum length of the column is determined by the national character set definition. Width specifications of character datatype NVARCHAR2 refer to the number of characters. The maximum column size allowed is 4000 bytes. Please refer to Oracle Database Globalization Support Guide for information on Unicode datatype support.VARCHAR2 DatatypeThe VARCHAR2 datatype specifies a variable-length character string. When you create a VARCHAR2 column, you supply the maximum number of bytes or characters of data that it can hold. Oracle subsequently stores each value in the column exactly as you specify it, provided the value does not exceed the column's maximum length of the column. If you try to insert a value that exceeds the specified length, then Oracle returns an error.You must specify a maximum length for a VARCHAR2 column. This maximum must be at least 1 byte, although the actual string stored is permitted to be a zero-length string (''). You can use the CHAR qualifier, for example VARCHAR2(10 CHAR), to give the maximum length in characters instead of bytes. A character is technically a code point of the database character set. CHAR and BYTE qualifiers override the setting of the NLS_LENGTH_SEMANTICS parameter, which has a default of bytes. For performance reasons, Oracle recommends that you use the NLS_LENGTH_SEMANTICS parameter to set length semantics and that you use the BYTE and CHAR qualifiers only when necessary to override the parameter. The maximum length of VARCHAR2 data is 4000 bytes. Oracle compares VARCHAR2 values using nonpadded comparison semantics.To ensure proper data conversion between databases with different character sets, you must ensure that VARCHAR2 data consists of well-formed strings. See Oracle Database Globalization Support Guide for more information on character set support.VARCHAR DatatypeDo not use the VARCHAR datatype. Use the VARCHAR2 datatype instead. Although the VARCHAR datatype is currently synonymous with VARCHAR2, the VARCHAR datatype is scheduled to be redefined as a separate datatype used for variable-length character strings compared with different comparison semantics.
*/
/*
VARCHAR2是Oracle提供的特定数据类型，
Oracle可以保证VARCHAR2在任何版本中该数据类型都可以向上和向下兼容。
VARCHAR在Oracle中不建议使用。具体到NVARCHAR2和VARCHAR2的区别，从使用角度来看区别在于：NVARCHAR2在计算长度时和字符集相关的，
例如数据库是中文字符集时以长度10为例，则1、NVARCHAR2(10)是可以存进去10个汉字的，如果用来存英文也只能存10个字符。2、而VARCHAR2(10)的话，则只能存进5个汉字，英文则可以存10个。
*/
SQL> create table t(
2 id nvarchar2(10) primary key)
3 /Table createdSQL> insert into t(id) values('1103160001');1 row insertedSQL> select * from t;
/*
ID
--------------------
1103160001
*/
这两种类型的长度指的是字符数，而不是字节数。
NLS国家语言支持(National Language Support)
在oracle 9i及以后的版本，数据库的国家字符集可以是：utf-8和AL16UTF-16两种。
Oracle 9i是utf -8, Oralce 10g是AL16UTF-16. 1.新建一个表,有两列，类型分别为：nchar和nvarchar2.长度都为10
SQL> create table test_nvarchar(col_nchar nchar(10),col_nvarchar2 nvarchar2(10));
Table created 2.插入一些数据
SQL> insert into test_nvarchar values('袁','袁光东');
1 row inserted
SQL> insert into test_nvarchar values(N'袁',N'袁光东');
1 row inserted
(在9i之前的版本，插入时加上N时，在处理时跟普通方式有不同的方式。但是在10g的时候已经有了改变，加不加N都是一样,这里只是为了测试)
SQL> insert into test_nvarchar values('a','b'); 1 row inserted
插入一行英文字母 3. 查看每行的col_nchar列的存储方式。
SQL> select col_nchar, dump(col_nchar) from test_nvarchar; COL_NCHAR DUMP(COL_NCHAR)
-------------------- --------------------------------------------------------------------------------
袁 Typ=96 Len=20: 136,129,0,32,0,32,0,32,0,32,0,32,0,32,0,32,0,32,0,32
a Typ=96 Len=20: 0,97,0,32,0,32,0,32,0,32,0,32,0,32,0,32,0,32,0,32
袁 Typ=96 Len=20: 136,129,0,32,0,32,0,32,0,32,0,32,0,32,0,32,0,32,0,32 Typ=96 与char的类型编码一样。
Len=20 每一行的长度都是20字节。这一点跟char一样。都是定长的，会以空格填充。
需要注意的是：统统以两位来表示一个字符。
136,129 表示’袁’
0,97 表示’a’
0,32 表示空格。 4. nvarchar2的储存
SQL> select col_nvarchar2, dump(col_nvarchar2) from test_nvarchar; COL_NVARCHAR2 DUMP(COL_NVARCHAR2)
-------------------- --------------------------------------------------------------------------------
袁光东 Typ=1 Len=6: 136,129,81,73,78,28
b Typ=1 Len=2: 0,98
袁光东 Typ=1 Len=6: 136,129,81,73,78,28 Typ=1 与varchar2一样。
每一行的len值都不样同。不会使用空格进行填充。
每一个字符都占有两个字节两进行存储。
b 存储为： 0, 98
袁 存储为： 136,129 5.nchar和nvarchar2的数据定义。
SQL> desc test_nvarchar;
Name Type Nullable Default Comments
------------- ------------- -------- ------- --------
COL_NCHAR NCHAR(20) Y
COL_NVARCHAR2 NVARCHAR2(20) Y 虽然在定义nchar和nvarchar2时，指定的长度是指字符数。但是表结构的定义中，仍然是存储着它的字节数。
在定义时nchar(10)表示可以最大存储10个字符。
在查看数据表结构时，显示该列最大占用的字节数。
需要注意的是：在char和nchar中对汉字的实际存储值是不一样的。因为采用了不同的字符集，就有了不同的字符编码。 SQL> insert into test_varchar values('袁'); 1 row inserted
SQL> select col, dump(col) from test_varchar where col='袁'; COL DUMP(COL)
---------- --------------------------------------------------------------------------------
袁 Typ=1 Len=2: 212,172
这时采用的字符集系统默认字符集ZHS16GBK。
这里很容易的把它转换成ascii码。
高位 * 256(2的8次方) + 低位.
212 * 256 + 172 = 54444 SQL> select chr(54444) from dual; CHR(54444)
----------
袁 而在Nchar 和Nvarchar中，采用的是UTF-8或UTF-16的字符集。 SQL> insert into test_nvarchar values('袁','袁'); 1 row inserted SQL> select col_nvarchar2, dump(col_nvarchar2) from test_nvarchar where col_nvarchar2='袁'; COL_NVARCHAR2 DUMP(COL_NVARCHAR2)
-------------------- --------------------------------------------------------------------------------
袁 Typ=1 Len=2: 136,129 ‘袁’存储的值为：136,129
Oracle 10以上对nchar和nvarchar都采用utf-16字符集了。它的好处就是对字符采用固定长度的字节存储(2字节),支持多国字符，在操作效率上会更高。但是它却无法兼容于ascii码。