目的:了解字符串要怎么应用
测试:
SQL> create table t
2 (char_column char(20),
3 varchar2_column varchar2(20)
4 )
5 /
Table created.
SQL> insert into t values ('hello world','hello world');
1 row created.
SQL> select * from t;
CHAR_COLUMN VARCHAR2_COLUMN
-------------------- --------------------
hello world hello world
这里的CHAR_COLUMN列长度为20,自动把长度定死为20
SQL> select * from t where char_column = 'hello world';
CHAR_COLUMN VARCHAR2_COLUMN
-------------------- --------------------
hello world hello world
SQL> select * from t where varchar2_column = 'hello world';
CHAR_COLUMN VARCHAR2_COLUMN
-------------------- --------------------
hello world hello world
SQL> select * from t where char_column=varchar2_column;
no rows selected
这里虽然两列值从显示来看一模一样,但是并不相等,因为长度不一样
SQL> select * from t where trim(char_column) = varchar2_column;
CHAR_COLUMN VARCHAR2_COLUMN
-------------------- --------------------
hello world hello world
SQL> select * from t where char_column = rpad( varchar2_column, 20 );
CHAR_COLUMN VARCHAR2_COLUMN
-------------------- --------------------
hello world hello world
这里去掉CHAR列中的空格或者让VARCHAR2增加空格到长度为20时,这两列值就相等
使用绑定变量时要使用CHAR类型而不是VARCHAR2,为什么?
测试:以一使用VARCHAR2类型来进行绑定变量操作
SQL> variable varchar2_bv varchar2(20)
SQL> exec :varchar2_bv := 'hello world';
PL/SQL procedure successfully completed.
SQL> select * from t where char_column = :varchar2_bv;
no rows selected
这个时候长度不等,绑定变量不会成功
SQL> select * from t where varchar2_column = :varchar2_bv;
CHAR_COLUMN VARCHAR2_COLUMN
-------------------- --------------------
hello world hello world
SQL>
测试:以下以CHAR类型来进行绑定变量操作
SQL> variable char_bv char(20)
SQL> exec :char_bv := 'hello world';
PL/SQL procedure successfully completed.
SQL> select * from t where char_column = :char_bv;
CHAR_COLUMN VARCHAR2_COLUMN
-------------------- --------------------
hello world hello world
这里列长度相等,绑定成功
SQL> select * from t where varchar2_column = :char_bv;
no rows selected
SQL>
总结:能不使用CHAR就不使用CHAR,都用VARCHAR2来操作字符数据!
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15720542/viewspace-731471/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/15720542/viewspace-731471/