應用中避免使用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,如上問題會經常出現。