ORACLE字符列长度语义

<>关于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
CORE 10.2.0.1.0 Production
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字节

 

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

转载于:http://blog.itpub.net/18922393/viewspace-721020/