其实LENGTH与VSIZE这两个函数联系不大，区别很大。虽然都是“取长度”，但是LENGTH函数结果是“有多少个字符”，VSIZE结果是“需要多少bytes”。

简单看一下这两个函数。

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官方文档参考
参考链接：

LENGTH

Syntax

length::=

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.

参考链接：

VSIZE

Syntax

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.

7.小结
Oracle提供了很多便捷的函数为我们所用，不过在使用之前需要认真的了解他们的功能，以免误用。
关于更多Oracle提供的函数说明可以参考Oracle的官方文档：

Good luck.

secooler
09.12.24

-- The End --

ITPub博客

【差异】LENGTH与VSIZE的区别演示

LENGTH

VSIZE

最新文章