其實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官方文件參考
參考連結：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.

7.小結
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部落格 ” ，連結：http://blog.itpub.net/519536/viewspace-623537/，如需轉載，請註明出處，否則將追究法律責任。