简单看一下这两个函数。
1.创建表T并初始化三条数据
sec@ora10g> create table t (x varchar2(10));
sec@ora10g> insert into t values ('H');
sec@ora10g> insert into t values ('侯');
sec@ora10g> insert into t values ('H侯');
sec@ora10g> commit;
2.查看表T中的数据
sec@ora10g> select * from t;
X
----------
H
侯
H侯
3.比较LENGTH和VSIZE两个函数的结果
sec@ora10g> col x for a10
sec@ora10g> col DUMP(X) for a30
sec@ora10g> select x, length(x), vsize(x), dump (x) from t;
X LENGTH(X) VSIZE(X) DUMP(X)
---------- ---------- ---------- ------------------------------
H 1 1 Typ=1 Len=1: 72
侯 1 3 Typ=1 Len=3: 228,190,175
H侯 2 4 Typ=1 Len=4: 72,228,190,175
4.结论
“H”包含1个字符，长度是1 byte；
“侯”包含1个字符，长度是3 bytes；
“H侯”包含2个字符，长度是4 bytes。
5.扩展
LENGTH的一个扩展函数LENGTHB也可以达到VSIZE函数的效果。
sec@ora10g> select x, length(x), lengthb(x), vsize(x), dump (x) from t;
X LENGTH(X) LENGTHB(X) VSIZE(X) DUMP(X)
---------- ---------- ---------- ---------- ------------------------------
H 1 1 1 Typ=1 Len=1: 72
侯 1 3 3 Typ=1 Len=3: 228,190,175
H侯 2 4 4 Typ=1 Len=4: 72,228,190,175
6.Oracle官方文档参考
参考链接：http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions076.htm#SQLRF00658
LENGTH
Syntax
length::=
Description of the illustration length.gif
Purpose
The LENGTH functions return the length of char. LENGTH calculates length using characters as defined by the input character set. LENGTHB uses bytes instead of characters. LENGTHCLENGTH2 uses UCS2 code points. LENGTH4 uses UCS4 code points. uses Unicode complete characters.
char can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The return value is of datatype NUMBER. If char has datatype CHAR, then the length includes all trailing blanks. If char is null, then this function returns null.
Restriction on LENGTHB The LENGTHB function is supported for single-byte LOBs only. It cannot be used with CLOB and NCLOB data in a multibyte character set.
参考链接：http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions213.htm#SQLRF06162
VSIZE
Syntax
Description of the illustration vsize.gif
Purpose
VSIZE returns the number of bytes in the internal representation of expr. If expr is null, then this function returns null.
This function does not support CLOB data directly. However, CLOBs can be passed in as arguments through implicit data conversion.
Oracle提供了很多便捷的函数为我们所用，不过在使用之前需要认真的了解他们的功能，以免误用。
关于更多Oracle提供的函数说明可以参考Oracle的官方文档：
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions001.htm#i88893
Good luck.
secooler
09.12.24
-- The End --
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/519536/viewspace-623537/ ，如需转载，请注明出处，否则将追究法律责任。
