由04年下半年数工下午第二题引发的系列实验1--字符问题

一个汉字在库里为一个char，对应几个byte取决于数据库的字符集，如果是ZHS16GBK字符集，则对应2个byte，如果是WE8ISO8859P1,则对应一个byte。 --客户端的字符集 --在windows平台下，就是注册表里面相应OracleHome的NLS_LANG --查看server端字符集 SQL> select userenv('language') from dual 2 / USERENV('LANGUAGE') ---------------------------------------------------- SIMPLIFIED CHINESE_CHINA.ZHS16GBK SQL> ed Wrote file afiedt.buf 1 CREATE TABLE testChar( 2 v1 CHARACTER(5), 3 v2 CHARACTER(5 CHARACTER) 4* ) SQL> / Table created. SQL> ed Wrote file afiedt.buf 1 INSERT 2 INTO testChar(v1) 3* VALUES('12345') SQL> / 1 row created. SQL> edit Wrote file afiedt.buf 1 INSERT 2 INTO testChar(v1) 3* VALUES('123456') SQL> / VALUES('123456') * ERROR at line 3: ORA-12899: value too large for column "YMC"."TESTCHAR"."V1" (actual: 6, maximum: 5) SQL> ed Wrote file afiedt.buf 1 INSERT 2 INTO testChar(v1) 3* VALUES('听听笨笨') SQL> / VALUES('听听笨笨') * ERROR at line 3: ORA-12899: value too large for column "YMC"."TESTCHAR"."V1" (actual: 8, maximum: 5) SQL> ed Wrote file afiedt.buf 1 INSERT 2 INTO testChar(v1) 3* VALUES('听听') SQL> / 1 row created. SQL> ed Wrote file afiedt.buf 1 INSERT 2 INTO testChar(v1) 3* VALUES('小听听') SQL> / VALUES('小听听') * ERROR at line 3: ORA-12899: value too large for column "YMC"."TESTCHAR"."V1" (actual: 6, maximum: 5) SQL> ed Wrote file afiedt.buf 1 INSERT 2 INTO testChar(v2) 3* VALUES('小听听') SQL> / 1 row created. SQL> ed Wrote file afiedt.buf 1 INSERT 2 INTO testChar(v2) 3* VALUES('听听和笨笨') SQL> / 1 row created. SQL> ed Wrote file afiedt.buf 1 INSERT 2 INTO testChar(v2) 3* VALUES('小听听和笨笨') SQL> / VALUES('小听听和笨笨') * ERROR at line 3: ORA-12899: value too large for column "YMC"."TESTCHAR"."V2" (actual: 6, maximum: 5) SQL> desc testChar Name Null? Type ----------------------------------------- -------- ---------------------------- V1 CHAR(5) V2 CHAR(5 CHAR) SQL> alter session set nls_length_semantics = CHAR 2 / Session altered. --修改了默认为char SQL> desc testChar Name Null? Type ----------------------------------------- -------- ---------------------------- V1 CHAR(5 BYTE) V2 CHAR(5) SQL> ed Wrote file afiedt.buf 1* DROP TABLE testChar SQL> / Table dropped. SQL> ed Wrote file afiedt.buf 1 CREATE TABLE testChar( 2 v1 CHARACTER(5), 3 v2 CHARACTER(5 byte) 4* ) 5 / Table created. SQL> ed Wrote file afiedt.buf 1 INSERT 2 INTO testChar(v1) 3* VALUES('听听和笨笨') SQL> / 1 row created. SQL> ed Wrote file afiedt.buf 1 INSERT 2 INTO testChar(v2) 3* VALUES('听听和笨笨') SQL> / VALUES('听听和笨笨') * ERROR at line 3: ORA-12899: value too large for column "YMC"."TESTCHAR"."V2" (actual: 10, maximum: 5) SQL> spool off