oracle to_char nvarchar2,ORACLE数据库中NVARCHAR2、VARCHAR2以及VARCHAR 的区别

NVARCHAR2 Datatype

The NVARCHAR2 datatype is a Unicode-only datatype. When youcreate a table with an NVARCHAR2 column, you supply the maximum number ofcharacters it can hold. Oracle subsequentlystores each value in the column exactly as you specify it, provided the valuedoes not exceed the maximum length of the column.

The maximum length of the column is determined by the national character setdefinition. Width specifications of character datatype NVARCHAR2 refer to thenumber of characters. The maximum column size allowed is 4000 bytes. Pleaserefer to Oracle Database Globalization Support Guide for information on Unicodedatatype support.

VARCHAR2 Datatype

The VARCHAR2 datatype specifies a variable-length characterstring. When you create a VARCHAR2 column, you supply the maximum number ofbytes or characters of data that it can hold. Oracle subsequently stores eachvalue in the column exactly as you specify it, provided the value does notexceed the column’s maximum length of the column. If you try to insert a valuethat exceeds the specified length, then Oracle returns an error.

You must specify a maximum length for a VARCHAR2 column. This maximum must beat least 1 byte, although the actual string stored is permitted to be azero-length string (”). You can use the CHAR qualifier, for exampleVARCHAR2(10 CHAR), to give the maximum length in characters instead of bytes. Acharacter is technically a code point of the database character set. CHAR andBYTE qualifiers override the setting of the NLS_LENGTH_SEMANTICS parameter,which has a default of bytes. For performance reasons, Oracle recommends thatyou use the NLS_LENGTH_SEMANTICS parameter to set length semantics and that youuse the BYTE and CHAR qualifiers only when necessary to override the parameter.The maximum length of VARCHAR2 data is 4000 bytes. Oracle compares VARCHAR2values using nonpadded comparison semantics.

To ensure proper data conversion between databases with different charactersets, you must ensure that VARCHAR2 data consists of well-formed strings. SeeOracle Database Globalization Support Guide for more information on characterset support.

VARCHAR Datatype

Do not use the VARCHAR datatype. Use the VARCHAR2 datatype instead. Althoughthe VARCHAR datatype is currently synonymous with VARCHAR2, the VARCHARdatatype is scheduled to be redefined as a separate datatype used forvariable-length character strings compared with different comparison semantics.

【注释】

VARCHAR2是Oracle提供的特定数据类型，Oracle可以保证VARCHAR2在任何版本中该数据类型都可以向上和向下兼容。

VARCHAR在Oracle中不建议使用。

通过对上文的阅读我们了解到NVARCHAR2允许的最大列的大小是4000字节，在计算长度时汉字和英文都只占一个字符，比如NVARCHAR2(6)可以存储6个汉字，如果用来存英文也就只能存6个字母。

而VARCHAR2在计算长度时汉字占2个字符，英文占一个字符，比如VARCHAR2(6)的话，能存进去3个汉字，而英文字母却可以存6个。

ORACLE官方文档解释：