CHAR与VARCHAR2
字符数据通常是以单字节存储在数据库字符集中:
CHAR
–固定长度的字符串最长可达2000个字节
–内部代码:96
VARCHAR@
–可变长的字符串最长可达4000个字节
–内部代码:1
CHAR
在一个类型为char的列中,所有空字节都会用空格来补充(字符32)
它的长度是固定的
SQL> select gr03,dump(gr03) from wb02 where gr03< '4307050000000097'; GR03 DUMP(GR03) ---------------- --------------------------------------------------------------- 4307050000000091 Typ=96 Len=16: 52,51,48,55,48,53,48,48,48,48,48,48,48,48,57,49 4307050000000092 Typ=96 Len=16: 52,51,48,55,48,53,48,48,48,48,48,48,48,48,57,50 430705000000093 Typ=96 Len=16: 52,51,48,55,48,53,48,48,48,48,48,48,48,48,57,51 4307050000000094 Typ=96 Len=16: 52,51,48,55,48,53,48,48,48,48,48,48,48,48,57,52 43070500000095 Typ=96 Len=16: 52,51,48,55,48,53,48,48,48,48,48,48,48,48,57,53
从上面的输出typ=96,len=16,可以看出char的内部代码是96,长度是16
varchar2
在一个类型为varchar2的列中,oracle不会使用空格来进行补位
它的长度是可变的
SQL> select dw02,dump(dw02) from wb01 where dw01< '0012'; DW02 DUMP(DW02) -------------------------- -------------------------------------------------------------------------------- 灵活人员 Typ=1 Len=8: 193,233,187,238,200,203,212,177 灵活就业人员 Typ=1 Len=12: 193,233,187,238,190,205,210,181,200,203,212,177 西湖区委、区管委 Typ=1 Len=16: 206,247,186,254,199,248,206,175,161,162,199,248,185,220,206,175 西湖区委、区管委办公室 Typ=1 Len=22: 206,247,186,254,199,248,206,175,161,162,199,248,185,220,206,175,17 西湖管理区政治部 Typ=1 Len=16: 206,247,186,254,185,220,192,237,199,248,213,254,214,206,178,191 西湖管理区监察局 Typ=1 Len=16: 206,247,186,254,185,220,192,237,199,248,188,224,178,236,190,214 西湖管理区财政局 Typ=1 Len=16: 206,247,186,254,185,220,192,237,199,248,178,198,213,254,190,214
从上面的输出typ=1可以看出varchar2的内部代码是1,它的长度不是固定的
nchar与nvarchar2
字符数据也可以存储在国际(多字节)字符集中
nchar
--固定长度的字符串最大可达2000个字节
--内部代码:96
nvarchar2
--可变长度的字符串最大可达4000个字节
--内部代码:1
nchar
在一个类型为nchar的列中,所有空字节都会用空格来补充(字符32)
它的长度是固定的
SQL> desc wb02_jy Name Type Nullable Default Comments ---- --------- -------- ------- -------- GR03 NCHAR(18) Y SQL> select gr03,dump(gr03) from wb02_jy; GR03 DUMP(GR03) ----------------------------- -------------------------------------------------------------------------------- 4307050000000091 Typ=96 Len=36: 0,52,0,51,0,48,0,55,0,48,0,53,0,48,0,48,0,48,0,48,0,48,0,48,0,48, 4307050000000092 Typ=96 Len=36: 0,52,0,51,0,48,0,55,0,48,0,53,0,48,0,48,0,48,0,48,0,48,0,48,0,48, 4307050000000093 Typ=96 Len=36: 0,52,0,51,0,48,0,55,0,48,0,53,0,48,0,48,0,48,0,48,0,48,0,48,0,48, 4307050000000094 Typ=96 Len=36: 0,52,0,51,0,48,0,55,0,48,0,53,0,48,0,48,0,48,0,48,0,48,0,48,0,48, 4307050000000095 Typ=96 Len=36: 0,52,0,51,0,48,0,55,0,48,0,53,0,48,0,48,0,48,0,48,0,48,0,48,0,48,
如上输出一样gr03定义为nchar(18),但是它是双字节所以长度len=36是18的2倍,内部代码还是与char一样96
nvarchar2
在一个类型为nvarchar2的列中,oracle不会使用空格来进行补位
它的长度是可变的
SQL> select dw02,dump(dw02) from wb01_jy; DW02 DUMP(DW02) ----------------------------- -------------------------------------------------------------------------------- 灵活人员 Typ=1 Len=8: 112,117,109,59,78,186,84,88 灵活就业人员 Typ=1 Len=12: 112,117,109,59,92,49,78,26,78,186,84,88 西湖区委、区管委 Typ=1 Len=16: 137,127,110,86,83,58,89,212,48,1,83,58,123,161,89,212 西湖区委、区管委办公室 Typ=1 Len=22: 137,127,110,86,83,58,89,212,48,1,83,58,123,161,89,212,82,158,81,10 西湖管理区政治部 Typ=1 Len=16: 137,127,110,86,123,161,116,6,83,58,101,63,108,187,144,232 西湖管理区监察局 Typ=1 Len=16: 137,127,110,86,123,161,116,6,83,58,118,209,91,223,92,64 西湖管理区财政局 Typ=1 Len=16: 137,127,110,86,123,161,116,6,83,58,141,34,101,63,92,64 西湖管理区劳动保障局 Typ=1 Len=20: 137,127,110,86,123,161,116,6,83,58,82,179,82,168,79,221,150,156,92 西湖管理区农村经济开发局 Typ=1 Len=24: 137,127,110,86,123,161,116,6,83,58,81,156,103,81,126,207,109,78,95 西湖管理区卫生局 Typ=1 Len=16: 137,127,110,86,123,161,116,6,83,58,83,107,117,31,92,64 西湖管理区国土局 Typ=1 Len=16: 137,127,110,86,123,161,116,6,83,58,86,253,87,31,92,64 西湖管理区移民开发局 Typ=1 Len=20: 137,127,110,86,123,161,116,6,83,58,121,251,108,17,95,0,83,209,92,6 西湖管理区经贸局 Typ=1 Len=16: 137,127,110,86,123,161,116,6,83,58,126,207,141,56,92,64
从上面的输出typ=1可以看出nvarchar2的内部代码是1,它的长度不是固定的