oracle列字符可以增加长度,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字节