<>关于NLS_LENGTH_SEMANTICS说明如下:
1,NLS_LENGTH_SEMANTICS仅用于CHAR、VARCHAR2类型的新建列的语义说明(字节、字符),并且不影响已有列。
2,NCHAR, NVARCHAR2, CLOB, 和 NCLOB 总是基于字符的。
3,NLS_LENGTH_SEMANTICS 不适用于SYS、SYSTEM下的表,数据字典总使用字节语义。
测试如下:
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE10.2.0.1.0Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> select * from nls_database_parameters where parameter like '%CHARACTERSET%';
PARAMETER VALUE
------------------------------ --------------------------------------------------------------------------------
NLS_CHARACTERSET AL32UTF8
NLS_NCHAR_CHARACTERSET AL16UTF16
SQL> show parameter length
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_length_semantics string BYTE
SQL> create table test_char(
x_char char(10),
x_varchar2 varchar2(10),
x_nchar nchar(10),
x_nvarchar2 nvarchar2(10));
SQL> insert into test_char values('1234567890','1234567890','1234567890','1234567890');
1 row inserted
SQL> commit;
Commit complete
SQL> SELECT length(x_char),
2 lengthb(x_char),
3 length(x_varchar2),
4 lengthb(x_varchar2),
5 length(x_nchar),
6 lengthb(x_nchar),
7 length(x_nvarchar2),
8 lengthb(x_nvarchar2)
9 FROM test_char;
LENGTH(X_CHAR) LENGTHB(X_CHAR) LENGTH(X_VARCHAR2) LENGTHB(X_VARCHAR2) LENGTH(X_NCHAR) LENGTHB(X_NCHAR) LENGTH(X_NVARCHAR2) LENGTHB(X_NVARCHAR2)
-------------- --------------- ------------------ ------------------- --------------- ---------------- ------------------- --------------------
10 10 10 10 10 20 10 20
SQL> update test_char set x_char='一二三四';
update test_char set x_char='一二三四'
ORA-12899: value too large for column "BOCNET"."TEST_CHAR"."X_CHAR" (actual: 12, maximum: 10)
SQL> update test_char set x_char='一二三';
1 row updated
SQL> rollback;
Rollback complete
SQL> update test_char set x_varchar2='一二三四';
update test_char set x_varchar2='一二三四'
ORA-12899: value too large for column "BOCNET"."TEST_CHAR"."X_VARCHAR2" (actual: 12, maximum: 10)
SQL> update test_char set x_varchar2='一二三';
1 row updated
SQL> rollback;
Rollback complete
SQL> update test_char set x_nchar='一二三四五六七八九十A';
update test_char set x_nchar='一二三四五六七八九十A'
ORA-12899: value too large for column "BOCNET"."TEST_CHAR"."X_NCHAR" (actual: 11, maximum: 10)
SQL> update test_char set x_nchar='一二三四五六七八九十';
1 row updated
SQL> rollback;
Rollback complete
SQL> update test_char set x_nvarchar2='一二三四五六七八九十A';
update test_char set x_nvarchar2='一二三四五六七八九十A'
ORA-12899: value too large for column "BOCNET"."TEST_CHAR"."X_NVARCHAR2" (actual: 11, maximum: 10)
SQL> update test_char set x_nvarchar2='一二三四五六七八九十';
1 row updated
SQL> rollback;
Rollback complete
SQL> update test_char set x_nchar='一二三四五六七八九AB';
update test_char set x_nchar='一二三四五六七八九AB'
ORA-12899: value too large for column "BOCNET"."TEST_CHAR"."X_NCHAR" (actual: 11, maximum: 10)
SQL> rollback;
Rollback complete
SQL>
SQL> update test_char set x_char='一二三',x_varchar2='一二三',x_nchar='一二三四五六七八九十',x_nvarchar2='一二三四五六七八九十';
1 row updated
SQL> commit;
Commit complete
SQL> SELECT length(x_char),
2 lengthb(x_char),
3 length(x_varchar2),
4 lengthb(x_varchar2),
5 length(x_nchar),
6 lengthb(x_nchar),
7 length(x_nvarchar2),
8 lengthb(x_nvarchar2)
9 FROM test_char;
LENGTH(X_CHAR) LENGTHB(X_CHAR) LENGTH(X_VARCHAR2) LENGTHB(X_VARCHAR2) LENGTH(X_NCHAR) LENGTHB(X_NCHAR) LENGTH(X_NVARCHAR2) LENGTHB(X_NVARCHAR2)
-------------- --------------- ------------------ ------------------- --------------- ---------------- ------------------- --------------------
4 10 3 9 10 20 10 20
==>char用空格补齐到10字节