Oracle中的字符型及

1、第6章 Oracle中的字符型及處理方法與其他編程語言一樣，Oracle中同樣存在著數(shù)據(jù)類型。Oracle中的數(shù)據(jù)類型主要有兩個(gè)應(yīng)用場景：一是用于指定數(shù)據(jù)表中列的類型；二是用于PL/SQL編程中聲明變量。Oracle的數(shù)據(jù)類型主要包括：字符型（character）、數(shù)值型（number）、日期型（date）和大對象型（LOB）。同時(shí)，Oracle提供了針對數(shù)據(jù)類型的內(nèi)置函數(shù)。本章著重講述Oracle中的字符型及其相關(guān)的函數(shù)。本章的主要內(nèi)容包括：字符型簡介字符型函數(shù)6.1 字符型簡介Oracle中的字符型有三種，分別是char(n)、varchar(n)、varchar2(n)。本節(jié)將分別針對這

2、三種類型進(jìn)行介紹。6.1.1 固定長度字符串char(n)char(n)指定變量或列的數(shù)據(jù)類型為固定長度的字符串。其中，n代表字符串的長度。當(dāng)實(shí)際字符串的長度不足n時(shí)，Oracle利用空格在右端補(bǔ)齊。當(dāng)然，Oracle不允許實(shí)際字符串的長度大于n。數(shù)據(jù)庫中列指定為char(n)類型時(shí)，n的最大值不能大于2000。否則，Oracle將拋出錯誤，如示例6-1所示?！臼纠?-1】當(dāng)char(n)作為列的數(shù)據(jù)類型時(shí)，測試其最大長度。SQL create table test_char(f_char char(2001); create table test_char(f_char char(2001)

3、 ORA-00910: specified length too long for its datatype6.1.2 varchar(n)Oracle中提供了varchar(n)的數(shù)據(jù)類型。該類型是Oracle迎合工業(yè)標(biāo)準(zhǔn)中的varchar而制定的。該數(shù)據(jù)類型實(shí)際是一個(gè)可變長度字符串類型。也就是說，當(dāng)實(shí)際字符串的長度不足時(shí)，不會使用空格進(jìn)行填充。同樣，實(shí)際字符串的長度也不允許超出n?！臼纠?-2】當(dāng)作為列的數(shù)據(jù)類型出現(xiàn)時(shí)，varchar的最大長度不能大于4000，如下所示。SQL alter table test_char add f_varchar varchar(4001); alter

4、 table test_char add f_varchar varchar(4001) ORA-00910: specified length too long for its datatype SQL alter table test_char add f_varchar varchar(4000); Table altered6.1.3 varchar2(n)與varchar(n)類型，varchar2(n)同樣是可變長度的字符串類型。Oracle在工業(yè)標(biāo)準(zhǔn)之外，自定義了該數(shù)據(jù)類型。同時(shí)Oracle也提醒用戶，盡量使用varchar2(n)，而非varchar(n)。因?yàn)槭褂胿archa

5、r2(n)可以獲得Oracle向后兼容性的保證。【示例6-3】當(dāng)作為列的數(shù)據(jù)類型出現(xiàn)時(shí)，varchar2的長度同樣不能大于4000，如下所示。SQL alter table test_char add f_varchar2 varchar2(4001); alter table test_char add f_varchar2 varchar2(4001) ORA-00910: specified length too long for its datatypeSQL alter table test_char add f_varchar2 varchar2(4000); Table alte

6、red6.2 字符型分析對于一般用途的數(shù)據(jù)表來說，最常用的字符串類型為varchar2(n)。本節(jié)將著重分析char(n)與varchar2(n)的區(qū)別。6.2.1 varchar2(n)與char(n)的區(qū)別varchar2(n)為可變字符串類型，而char(n)為固定字符串類型。這二者的區(qū)別在于是否使用空格來補(bǔ)齊不足的部分?！臼纠?-4】在表test_char中，列f_char和f_varchar2的長度分別為2000和4000。先向其中插入新的記錄，并為兩列賦予相同的值。SQL insert into test_char(f_char, f_varchar2) 2 values(000,

7、 000); 1 row inserted6.2.2 varchar2(n)與char(n)的選擇通過示例6-5可以看出，char(n)類型的列通常占用較大的存儲空間；而varchar2(n)類型的列占用的空間較小。所以，varchar2(n)類型是我們在進(jìn)行數(shù)據(jù)庫設(shè)計(jì)時(shí)的一般選擇。但這并不意味著char(n)類型應(yīng)該被摒棄。相反，char(n)在效率方面要高于varchar2(n)。這是因?yàn)榭勺冮L度的字符串類型在實(shí)際數(shù)據(jù)長度發(fā)生改變時(shí)，總需要不斷調(diào)整存儲空間。尤其是頻繁修改數(shù)據(jù)，而數(shù)據(jù)長度也不斷改變的情況下，這種效率的損耗尤其明顯。大多數(shù)的應(yīng)用程序，并不將數(shù)據(jù)庫端的效率作為首要考慮的需求。而

8、更傾向于較小的空間代價(jià)，因此大多使用varchar2(n)來定義列。而char(n)則是典型的“以空間換時(shí)間”，讀者可以在實(shí)際開發(fā)中酌情選擇。6.2.3 變量聲明中的字符串類型三種字符串類型char(n)、varchar(n)、varchar2(n)，都可用于聲明變量。但是，利用三者聲明時(shí)，最大長度均為32767?！臼纠?-5】測試char(n)、varchar(n)及varchar2(n)用于變量聲明時(shí)的最大長度。SQL declare s char(32768); 2 begin 3 null; 4 end; 5 / 6.3 字符型處理Oracle提供了豐富的字符串函數(shù)來處理字符型數(shù)據(jù)。本

9、節(jié)將著重介紹這些函數(shù)。6.3.1向左補(bǔ)全字符串lpad ()函數(shù)lpad()函數(shù)用于向左補(bǔ)全字符串。該函數(shù)主要用于字符串的格式化。格式化的方式為，將字符串格式化為指定長度，如有不足部分，則在字符串的左端填充特定字符。其調(diào)用方式如下所示。lpad(string, padded_length, pad_string)其中，第一個(gè)參數(shù)指定原始字符串；第二個(gè)參數(shù)指定格式化之后的字符串長度；第三個(gè)參數(shù)指定使用哪個(gè)字符來填充不足位數(shù)。6.3.2 向右補(bǔ)全字符串rpad ()函數(shù)與lpad()函數(shù)相似，rpad()函數(shù)返回字符串格式化為特定位數(shù)的操作。只是該函數(shù)自右端補(bǔ)全不足位數(shù)?！臼纠?-7】利用rpad

10、()函數(shù)實(shí)現(xiàn)右端補(bǔ)全字符串。SQL select rpad(1, 4, *) empplyee_no from dual; EMPPLYEE_NO-1*6.3.3 返回字符串的小寫形式lower()函數(shù)lower()函數(shù)用于返回字符串的小寫形式。該函數(shù)僅有一個(gè)參數(shù)，即原始字符串。【示例6-8】在數(shù)據(jù)庫中，當(dāng)查詢條件可以忽略大小寫形式時(shí)，我們通常利用lower()函數(shù)進(jìn)行字符串大小寫形式的統(tǒng)一。SQL select username, password 2 from dba_users 3 where lower(username) = system; 6.3.4 返回字符串的大寫形式upper

11、()函數(shù)與lower()函數(shù)相反，upper()函數(shù)用于返回字符串的大寫形式。該函數(shù)僅有一個(gè)參數(shù)，即原始字符串?！臼纠?-9】我們同樣可以upper()函數(shù)來改寫示例6-8的查詢語句。SQL select username, password 2 from dba_users 3 where upper(username) = SYSTEM; 6.3.5 單詞首字符大寫initcap()函數(shù)initcap()函數(shù)用于將單詞轉(zhuǎn)換為首字符大寫、其它字符小寫的形式。【示例6-10】利用initcap()函數(shù)格式化單詞。SQL select initcap(like) new_word from du

12、al; NEW_WORD-Like SQL select initcap(LIKE) new_word from dual; NEW_WORD-Like6.3.6 返回字符串長度length()函數(shù)length()函數(shù)可用于返回字符串長度?！臼纠?-11】利用length()函數(shù)返回字符長度。SQL select length(12345) len from dual; LEN- 56.3.7 截取字符串substr()函數(shù)substr()函數(shù)用于截取字符串。該函數(shù)的一般調(diào)用形式如下所示。substr(string, start_index, length)其中，第一個(gè)參數(shù)string指定原始

13、字符串；第二個(gè)參數(shù)start_index指定開始截取的位置；第三個(gè)參數(shù)指定截取的長度。需要注意的是，Oracle中字符串中第一個(gè)字符的位置為1。這與很多編程語言，如Java中自0開始的習(xí)慣不同。6.3.8 獲得字符串出現(xiàn)的位置instr()函數(shù)instr()函數(shù)用于返回子字符串在父字符串中出現(xiàn)的位置。如果子字符串未出現(xiàn)在父字符串中，該函數(shù)將返回0。其調(diào)用形式如下所示。instr(string, sub_string, start_index , times)其中，string為父字符串；sub_string為子字符串；start_index為可選參數(shù)，指定進(jìn)行搜尋的起始位置；times為可選參

14、數(shù)，表示第幾次獲得子字符串。【示例6-13】利用instr()函數(shù)獲得子字符串的出現(xiàn)位置。SQL select instr(123456789, 56) position from dual; POSITION- 56.3.9 刪除字符串左側(cè)空格ltrim()函數(shù)ltrim()函數(shù)用于刪除字符串的左側(cè)空格?！臼纠?-14】利用ltrim()函數(shù)刪除字符串的左側(cè)空格。SQL select ltrim( 000) new_str from dual; NEW_STR-000ltrim( 000)用于刪除字符串“ 000”左側(cè)的所有空格，結(jié)果返回“000”。6.3.10 刪除字符串右側(cè)空格rtrim

15、()函數(shù)rtrim()函數(shù)用于刪除字符串的右側(cè)空格?！臼纠?-15】利用rtrim()函數(shù)刪除字符串右側(cè)空格。SQL select rtrim( 000 ) new_str from dual; NEW_STR- 000select rtrim( 000 )用于刪除字符串“ 000 ”的右側(cè)空格。分析查詢結(jié)果可知，雖然原字符串左右兩側(cè)均含有空格，但是rtrim()函數(shù)僅刪除了右側(cè)空格。6.3.11 刪除字符串兩側(cè)空格trim()函數(shù)trim()函數(shù)可以返回字符串兩側(cè)的空格。它實(shí)際是ltrim()函數(shù)與rtrim()的綜合操作結(jié)果?！臼纠?-16】利用trim()函數(shù)刪除字符串兩側(cè)空格。SQL

16、select trim( 000 ) new_str from dual; NEW_STR-000trim( 000 )用于刪除字符串“ 000 ”左右兩側(cè)的空格，結(jié)果返回字符串“000”。6.3.12 串聯(lián)字符串concat()函數(shù)concat()函數(shù)可以將兩個(gè)字符串進(jìn)行連接。【示例6-17】利用concat()函數(shù)連接字符串。SQL select concat(Hello, World) new_str from dual; NEW_STR-Hello World6.3.13 翻譯字符串translate()函數(shù)translate()函數(shù)的執(zhí)行過程非常類似于翻譯的過程?！臼纠?-18】利用translate()函數(shù)翻譯字符串。SQL select translate(+-*, 1+23-4*, abcdefghijklmnopqistuvwxqz) trans from dual; TRANS-beg6.4 本章實(shí)例對于字符型函數(shù)來說，如果傳入的參數(shù)是一個(gè)空值，那