PL/SQL數(shù)據(jù)類型|字符串

官方文檔

什么是字符串

string/character data：a sequence of selected symbols from a particular set of characters.（從特定字符集中選中的一系列字符）
在PL/SQL中有三種類型：

  • Fixed-length strings（定長字符串）：在右邊用空格填充，直到達(dá)到在聲明時指定的長度的字符。
  • Variable-length strings（變長字符串）：有指定的最大長度（必須小于21767），但不會被填充。
  • Character large objects (CLOBs)（字符大對象）：是不定長度的字符串，可以達(dá)到128TB。
    字符串可以是文字或變量。字符串文字被包在單引號中：
    'This is a string literal'
    要在字符串中嵌入單引號，使用兩個挨著的單引號''
    'This isn''t a date'
    還可以用“q”來轉(zhuǎn)義，比''更易讀。
    q'[This isn't a date]'

聲明字符變量

ORACLE數(shù)據(jù)庫提供的字符數(shù)據(jù)類型有：CHAR， NCHAR,，VARCHAR2，NVARCHAR2， CLOB和NCLOB。以N開頭的是“national character set”，可以存儲Unicode字符數(shù)據(jù)。
變長字符串聲明：在聲明時必須指定字符串的最大長度，否則會拋出編譯異常。

DECLARE
   l_company_name VARCHAR2(100);

定長字符串聲明：使用CHAR數(shù)據(jù)類型，可以不用指定最大長度。如果沒有指定的話，ORACLE數(shù)據(jù)庫自動將此變量的最大長度設(shè)為1。如果聲明時指定的最大長度大于1，數(shù)據(jù)庫就會自動在你為這個變量賦的值的右邊填充空格，直到達(dá)到指定的最大長度。

DECLARE
   l_yes_or_no1 CHAR(1) := 'Y';
   l_yes_or_no2 CHAR := 'Y';

※如果不指定最大長度，值的長度又大于1的話，可以通過編譯，運(yùn)行時會報錯。

P_TEST CHAR := 'YY';

ORA-06502: PL/SQL: 數(shù)値または値のエラー: 文字列バッファが小さすぎます。が発生しました
ORA-06512: "XXX", 行3
ORA-06512: 行8

字符大對象聲明：使用CLOB數(shù)據(jù)類型，不需要指定最大長度。它的最大長度基于數(shù)據(jù)庫塊大小，由ORACLE數(shù)據(jù)庫自動決定。

DECLARE
   l_lots_of_text CLOB;

在程序中使用哪種數(shù)據(jù)類型

  • 如果大于32767個字符，用CLON(NCLOB)。
  • 如果這個字符總是長度固定的（性別、郵編），用CHAR(NCHAR)。
  • 其他的，用VARCHAR2(NVARCHAR2)

注意，l_variable VARCHAR2 (10) := 'Logic';和l_fixed CHAR (10) := 'Logic';是不相等的。

字符內(nèi)建方法

連接多個字符

  • CONCAT方法：連接兩個參數(shù)，不常用。
  • || 操作符：可連接多個參數(shù)，常用。
DECLARE
    l_first  VARCHAR2 ( 10 ) := 'Steven';
    l_middle VARCHAR2 ( 5 ) := 'Eric';
    l_last   VARCHAR2 ( 20 ) := 'Feuerstein';
BEGIN
    /* Use the CONCAT function */
    DBMS_OUTPUT.put_line ( CONCAT ( 'Steven' ,'Feuerstein' ) ) ;
    /* Use the || operator */
    DBMS_OUTPUT.put_line ( l_first || ' ' || l_middle || ' ' || l_last ) ;
END;
/

-- output
StevenFeuerstein
Steven Eric Feuerstein

字符大小寫切換

  • UPPER：所有字符轉(zhuǎn)換成大寫
  • LOWER：所有字符轉(zhuǎn)換成小寫
  • INITCAP：所有單詞首字母大寫（字符由空格或非字母數(shù)字間隔）
DECLARE
test_string VARCHAR(20) := 'juSt tEst';
BEGIN
DBMS_OUTPUT.put_line (UPPER (test_string));
DBMS_OUTPUT.put_line (LOWER (test_string));
DBMS_OUTPUT.put_line (INITCAP (test_string));
END;
-- output
JUST TEST
just test
Just Test

截取字符串

SUBSTR(str, start [, num])有三個參數(shù)，目標(biāo)字符串，要截取的字符串的開始位置，以及截取的字符串?dāng)?shù)（可選）。

  • start要小于目標(biāo)字符串的長度，可以為負(fù)數(shù)。
  • start為-1，則表示從目標(biāo)字符串的末尾開始，逆向截取字符。
  • num必須大于0，可大于目標(biāo)字符串的長度。
DECLARE
    l_company_name VARCHAR2 ( 6 ) := 'Oracle';
BEGIN
    /* start可為負(fù)數(shù) */
    DBMS_OUTPUT.put_line ( SUBSTR ( l_company_name , - 2 ,1 ) ) ;
    /* num可大于字符串長度 */
    DBMS_OUTPUT.put_line ( SUBSTR ( l_company_name ,2 ,11 ) ) ;
    /* num可省略 */
    DBMS_OUTPUT.put_line ( SUBSTR ( l_company_name ,3 ) ) ;
    /* num要大于0 */
    DBMS_OUTPUT.put_line ( SUBSTR ( l_company_name ,2 ,0 ) ) ;
END;
/
-- output
l
racle
acle

查找特定字符串

INSTR(strA, strB [, start, Nth])strA是目標(biāo)字符串，strB是要在strA中查找的字符串，start是開始查找的位置，Nth是查找到的第幾個。函數(shù)返回strB第Nth次出現(xiàn)時，在strA中的位置。如果沒有查找到，就返回0。

  • start，Nth可選
  • 查找時大小寫區(qū)分
  • start為0的話，不能查找，返回0
  • 若查詢不到，返回0
  • start為負(fù)數(shù)時，表示從strA的右邊第start位開始，從右到左查詢
  • Nth小于0的話，會拋出異常
BEGIN
    /* 尋找'E' */
    DBMS_OUTPUT.put_line ( INSTR ( 'oracle oraclE' ,'E' ) ) ;
    /* 從第7位開始，檢索到的第一個'e'的位置 */
    DBMS_OUTPUT.put_line ( INSTR ( 'oracle oraclE oracle' ,'e' ,7 ) ) ;
    /* 從右邊第1位開始，從左往右檢索到的第一個e的位置 */
    DBMS_OUTPUT.put_line ( INSTR ( 'oracle oraclE oracle' ,'e' , - 1 ) ) ;
    /* 從第2位開始，檢索到的第二個'l'的位置 */
    DBMS_OUTPUT.put_line ( INSTR ( 'oracle oraclE' ,'l' ,2 ,2 ) ) ;
END;
/
-- output
13
20
20
12

用空格（或其他字符）填充字符串

LPAD/RPAD(str, length, cha)在字符串str的左側(cè)/右側(cè)填充cha，使字符串的位數(shù)達(dá)到length。

DECLARE
    l_first VARCHAR2 ( 10 ) := 'Steven';
    l_last  VARCHAR2 ( 20 ) := 'Feuerstein';
    l_phone VARCHAR2 ( 20 ) := '773-426-9093';
BEGIN
    DBMS_OUTPUT.put_line ( 'Header' ) ;
    /* 如果length小于str的length，就會截去多余的字符 */
    DBMS_OUTPUT.put_line ( LPAD ( 'Sub-header' ,5 ,'.' ) ) ;
    /* 在str右邊填充'123'，直到達(dá)到20位 */
    DBMS_OUTPUT.put_line ( RPAD ( 'abc' ,20 ,'123' ) ) ;
    /* Display headers and then values to fit within the columns. */
    DBMS_OUTPUT.put_line (
    /*1234567890x12345678901234567890x*/
    'First Name Last Name            Phone' ) ;
    DBMS_OUTPUT.put_line ( RPAD ( l_first ,10 ) || ' ' || RPAD ( l_last ,20 ) || ' ' || l_phone ) ;
END;
/

置換

  • REPLACE：用一組字符替換另一組字符
  • TRANSLATE： 翻譯或替換單個字符
    替換單個字符時，REPLACE和TRANSLATE的作用是一樣的。替換多字符時情況就不同了。
DECLARE
    l_name VARCHAR2 ( 50 ) := 'Steven Feuerstein';
BEGIN
    /* 將'abc'替換成'123' */
    DBMS_OUTPUT.put_line ( REPLACE ( 'abc-a-b-c-abc' ,'abc' ,'123' ) ) ;
    /* 'a'->'1' 'b'->'2' 'c'->'' */
    DBMS_OUTPUT.put_line ( TRANSLATE ( 'abc-a-b-c-abc' ,'abc' ,'12' ) ) ;
END;
/
--output
123-a-b-c-123
12-1-2--12

刪除字符

LTRIM/RTRIM(str [, char])從str左邊或右邊刪除char，直到遇到除char以外的字符。若沒有設(shè)定char參數(shù)，就刪去左側(cè)或右側(cè)的空格。

DECLARE
    a VARCHAR2 ( 40 ) := 'This sentence has too many periods....';
    b VARCHAR2 ( 40 ) := 'The number 1';
BEGIN
    DBMS_OUTPUT.put_line ( RTRIM ( a ,'.' ) ) ;
    DBMS_OUTPUT.put_line ( LTRIM ( b ,'ABCDEFGHIJKLMNOPQRSTUVWXYZ ' || 'abcdefghijklmnopqrstuvwxyz' ) ) ;
END;
-- output
This sentence has too many periods
1

TRIM：刪去兩邊的字符。

  • BOTH '.' FROM x，去掉兩邊的'.'，只能用于單字符
  • RTRIM(LTRIM(x,',.;'),',.;')，去掉兩邊的多字符
DECLARE
    x VARCHAR2 ( 30 ) := '.....Hi there!.....';
BEGIN
    DBMS_OUTPUT.put_line ( TRIM ( LEADING '.' FROM x ) ) ;
    DBMS_OUTPUT.put_line ( TRIM ( TRAILING '.' FROM x ) ) ;
    DBMS_OUTPUT.put_line ( TRIM ( BOTH '.' FROM x ) ) ;
    --The default is to trim
    --from both sides
    DBMS_OUTPUT.put_line ( TRIM ( '.' FROM x ) ) ;
    --The default trim character
    --is the space:
    DBMS_OUTPUT.put_line ( TRIM ( x ) ) ;
END;

-- output
Hi there!.....
.....Hi there!
Hi there!
Hi there!
.....Hi there!.....

其他

字符串長度溢出

一個VARCHAR2類型的變量，如果給它賦值的長度大于其聲明的最大長度，會拋出VALUE_ERROR異常(ORA-06502: PL/SQL: numeric or value)。
如果使用insert或update操作，值的長度大于字段的規(guī)定長度，會拋出不同的異常（ORA-12899: value too large for column）。

不同的最大長度

VARCHAR2在PL/SQL中的最大長度是32767bytes，在SQL中是4000。CHAR在PL/SQL中也是32767bytes，在SQL中是2000.CLOB在PL/SQL中的最大長度是128TB，在SQL中是(4 GB - 1) * DB_BLOCK_SIZE。
在存儲VACHAR2類型字段到表中時，為了避免ORA-12899錯誤，有兩種做法

  • 使用SUBSTR使值的長度在4000以內(nèi)，但是會失去部分字符。
  • 把字段的數(shù)據(jù)類型換成CLOB。
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容