我的环境:
$ echo $NLS_LANG
AMERICAN_AMERICA.ZHS16GBK
SQL> select * from v$version ;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
2.建立测试:
SQL> create table t3 (a varchar2(10),b nvarchar2(10),c char(10),d nchar(10));
Table created.
SQL> insert into t3 values('文123','文123','文123','文123');
1 row created.
SQL> commit ;
Commit complete.
SQL> column a1 format a30
SQL> column b1 format a30
SQL> column c1 format a30
SQL> column d1 format a30
SQL> select dump(a) a1,dump(b) b1,dump(c) c1,dump(d) d1 from t3;
SQL> select dump(a) a1,dump(b) b1,dump(c) c1,dump(d) d1 from t3;
A1 B1 C1 D1
------------------------------ ------------------------------ ------------------------------ ------------------------------
Typ=1 Len=5: 206,196,49,50,51 Typ=1 Len=8: 101,135,0,49,0,50 Typ=96 Len=10: 206,196,49,50,5 Typ=96 Len=20: 101,135,0,49,0,
,0,51 1,32,32,32,32,32 50,0,51,0,32,0,32,0,32,0,32,0,
32,0,32
--使用length,lengthb看看存贮的长度。
SQL> select length(a),length(b),length(c),length(d) from t3;
LENGTH(A) LENGTH(B) LENGTH(C) LENGTH(D)
---------- ---------- ---------- ----------
4 4 9 10
SQL> select lengthb(a),lengthb(b),lengthb(c),lengthb(d) from t3;
LENGTHB(A) LENGTHB(B) LENGTHB(C) LENGTHB(D)
---------- ---------- ---------- ----------
5 8 10 20
--可以发现:
1.Nchar和char类型的最好不要采用。它不足的部分使用空格补充。会导致许多问题,除了存储消耗外。
2.nvarchar2也一样,除了占用空间外,每个"字符"(包括中文)后面都会加0,而且在一些程序的处理上会存在问题。例子:
SQL> select substr(b,1,2),substrb(b,1,2) from t3;
SUBS SU
---- --
文1 文
3.存在隐式转换的问题:
SQL> variable y nvarchar2(10);
SQL> exec :y := '文123';
SQL> select * from t3 where a =:y ;
A B C D
---------- -------------------- ---------- --------------------
文123 文123 文123 文123
SQL> @dpc
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 4an40344w91cg, child number 0
-------------------------------------
select * from t3 where a =:y
Plan hash value: 4161002650
--------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------
| 0 | SELECT STATEMENT | | | 3 (100)|
|* 1 | TABLE ACCESS FULL| T3 | 1 | 3 (0)|
--------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - (NVARCHAR2(30), CSID=2000): '文123'
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(SYS_OP_C2C("A")=:Y)
Note
-----
- dynamic sampling used for this statement (level=2)
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
30 rows selected.
--虽然可以查询到,但是存在隐式转换,我定义的y变量是nvarchar2型的,而表中a字段的数据类型是varchar2,
--存在这样会隐式转换,filter(SYS_OP_C2C("A")=:Y),我个人的建议最好仅仅使用varchar2类型的,其它最好
--不采用。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-716915/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/267265/viewspace-716915/