应用中避免使用char/nchar类型

定宽的存储空间可能导致表和相关索引比平常大许多，还会伴随着绑定变量问题，所以无论什么场合都避免使用char类型

JEL@JEL >create table t (x char(20),y varchar2(20));

Table created.

JEL@JEL >insert into t values ('hello','hello');

1 row created.

JEL@JEL >commit;

Commit complete.

JEL@JEL >select * from t;

X                    Y
-------------------- --------------------
hello                hello

JEL@JEL >select * from t where x='hello';

X                    Y
-------------------- --------------------
hello                hello

JEL@JEL >select * from t where y='hello';

X                    Y
-------------------- --------------------
hello                hello

如上查询结果相同是因为发生了隐式转换

x与y字段是截然不同的
JEL@JEL >select * from t where x=y;

no rows selected

JEL@JEL >select * from t where trim(x)=y;

X                    Y
-------------------- --------------------
hello                hello


关于绑定变量

JEL@JEL >variable v_x varchar2(20)
JEL@JEL >exec :v_x := 'hello';

PL/SQL procedure successfully completed.

JEL@JEL >select * from t where x= :v_x;

no rows selected

JEL@JEL >select * from t where y= :v_x;

X                    Y
-------------------- --------------------
hello                hello

varchar2绑定变量不会像字符串直接量那样隐式转换成char(20)

JEL@JEL >variable v_x char(20)
JEL@JEL >exec :v_x := 'hello';

PL/SQL procedure successfully completed.

JEL@JEL >select * from t where x= :v_x;

X                    Y
-------------------- --------------------
hello                hello

JEL@JEL >select * from t where y= :v_x;

no rows selected

如果混合使用并匹配varchar2和char,如上问题会经常出现。

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

转载于:http://blog.itpub.net/29337971/viewspace-1063845/