OCP 複習筆記之PL/SQL (3)

最近打算把9i 的OCP教程看一遍，作一些簡單的筆記，作為備忘。

PL/SQL看似簡單，但實際使用起來還是有很多技巧和誤區的。以下記錄的是複習過程中想到的、和容易出現錯誤的地方。

這些東西在教程大部分是沒有記載的。

第三章：單行函式

第三章：單行函式

3.1 單行函式的概念

接受一個或多個引數，為每一行記錄返回一個值。

3.2 字元函式

1） 一般的字元函式都是以字元作為計算單位的，如果要用位元組作為單位，則在對應的字元函式後加上”B”就可以，如LENGTHB。字元函式的入參是字串，返回值可以是字元型、數值型別等。（對含有漢字的字串有意義）

2） LOWER(column|expression)

把字串換成小寫形式。

3） UPPER(column|expression)

將字串換成大寫形式。

4） INITCAP(column|expression)

將字串的所有單詞的第一個字元轉換成大寫，其餘小寫。

5） CONCAT(column1|expression1,column2|expression2)

連線兩個字串，作用相當於||

6） SUBSTR(column|expression,m[,n])

Ø 返回字串的第m到n個字元（包含m、n邊界），其中：m不可省略，n可以省略。如果省略n，則擷取從第m個開始的所有字元。

Ø 如果m是正數，則從頭開始數第m個開始擷取；如果m為負數，則從尾往前|m|個字元開始，擷取的方向還是從頭至尾

Ø N不能為負數；n是負數時返回空串

7） LENGTH(column|expression)

返回字串的長度，單位是字元，如果需要返回單位為位元組的話用LENGTHB。如可以用length(‘string’)=lengthb(‘string’)判斷字串是否含有中文。

8） INSTR(column|expression,’string’, [,m], [n] )

Ø 返回從m開始，第n次出現’string’的位置

Ø M、n預設都是1，表示從頭開始，第一次出現’string’的位置。

Ø 如果m為負數，則從字串尾部開始反方向的第m個字元開始，找第n次出現’string’的位置（找的方向也是反方向），得到的位置仍是從頭開始數的。

Ø N不能為負數

9） LPAD(column|expression, n,'string')

Ø 如果n大於column|expression的長度，則用’string’左補齊，直到返回長度達到n；

Ø 如果n小於column|expression的長度，則返回column|expression的前n個字元

10） RPAD(column|expression, n,'string')

Ø 如果n大於column|expression的長度，則用’string’右補齊，直到返回長度達到n；

Ø 如果n小於column|expression的長度，則返回column|expression的前n個字元

11） TRIM(leading|trailing|both trim_character FROM trim_source)

Ø 去掉trim_source開頭|末尾|兩邊的’trim_character’

Ø 如果trim_source或trim_character任意一個為NULL，則返回結果為NULL

Ø 如果不指定leading/trailing/both，則預設為both

Ø 如果省略trim_character，則trim_character預設為空格

12） TRIM(trim_source)

Trim函式的簡化，作用是取出trim_source左右兩邊的空格

13） REPLACE(text,search_string,replacement_string)

將text中所有的search_string替換成replacement_string

3.3 數值函式

1) ROUND(column|expression, n)

對數值執行四捨五入操作，n預設為0，也就是四捨五入到個位；如果n為負數，則對整數部分四捨五入到10的n次方。

2) TRUNC(column|expression,n)

對數值執行截斷操作，保留n位小數（不執行四捨五入）；如果n位負數，則對截斷整數部分到10的n次方。

3) MOD(m,n)

對m用n進行求模。

3.4 日期函式

1) 日期格式的儲存

Ø 在oracle內部，日期都是以數值形式儲存的，但它的顯示格式取決於nls_date_format引數。

Ø 預設顯示格式為：DD-Mon-RR

Ø 顯示為07-Jun-94在庫中實際可能是這樣儲存的：

Century year month day hour minute second

19 94 06 07 5 10 43

2) Sysdate

Sysdate返回伺服器端的時間，如果是分散式事務，它返回的是遠端資料庫的時間，它包含日期和時間兩個部分。

3) 日期運算

Ø 日期格式的資料加上或者減去一個數值後，返回資料的仍然是日期型

Ø 兩個日期型相減得到兩個日期相差的天數（可能包含小數）

4) 日期函式

Ø MONTHS_BETWEEN(date1, date2)

得到兩個日期相差的月份，返回值可以為整數、負數和小數。

Ø ADD_MONTHS(date, n)

在date的基礎上加上n個月。N可以為正整數、負整數。如果n帶小數，則小數部分會被忽略，只取整數部分（類似floor操作）

Ø NEXT_DAY(date,n| 'char')

求下一個星期幾的日期。其中後一個引數可以為數字、字串；當是數字時，1代表星期天，7代表星期六；當是字串時，它可以是英文、漢字，取決於nls_date_language的設定。且當字串是英文時，它可以是縮寫，也可以是全拼。

該函式的返回值的時間部分與date一樣。

Ø LAST_DAY(date)

取date所在月份的最後一天。

Ø ROUND(date[,'fmt'])

Ø TRUNC(date[, 'fmt'])

【注意】除month_between是返回數值外，其他日期函式返回值都是日期格式

3.5 型別轉換函式

1） 隱式轉換

Ø 特定資料下如下資料型別之間可以進行隱式轉換

Varchar2/char -> number

Varchar2/char -> date

Number -> varchar2

Date -> varchar2

Varchar2 -> rowed

Ø 當date型別隱式轉換成varchar2時，得到的varchar2值與date的顯示格式一樣，它取決於nls_date_format和nls_languaage

Ø Nls_date_format和nls_language共同決定了日期的顯示格式，所以，如果想要字串能隱式轉換成日期格式，則該字串必須完全符合日期的顯示格式。

Ø Char/varchar2轉換為number時，字串必須是數字和小數點，且小數點個數不能大於1個。

2） TO_CHAR(DATE ,[fmt],[nlsparams])函式

Ø Fmt可以多個個數混用，甚至可以與常量混用，如：

SQL> select to_char(sysdate,'"今天是" yyyy-mm-dd " "day') date_info from dual;

DATE_INFO

----------------------------

今天是 2006-06-20 Tuesday

【注意】如果與常量混用，則常量要用雙引號括起來；如果多種格式混用，則格式之間應用空格分開，否則可能出錯。

Ø 在設定格式時可以加一些字尾，使得它以特定形式顯式數字，字尾包括：

TH 例如4表示為4th

SP 例如4表示為four

SPTH/THSP 例如4表示為fourth

例子：

SQL> select to_char(sysdate,'ddspth') from dual;

TO_CHAR(SYSDATE,'DDSPTH')

-------------------------

twentieth

Ø Nlsparams可以是多個nls引數中的一個或多個，如nls_date_format、nls_language等。如果省略該引數，則會用當前session的引數設定。

Ø TO_CHAR(DATE)函式得到結果的長度

TO_CHAR函式最終得到的結果是CHAR型別的，所以，同一類格式得到的長度一樣的（比如用TO_CHAR得到MONTH的長度都是9，MON的長度都是2），這個長度是由同一類格式的長度最大的值來決定的，如MONTH最大長度是September，長度是9，所以預設情況下用TO_CHAR得到英文月份的長度都是9。長度不足部分ORACLE會用空格填補。

Ø 如果需要返回沒有空格的字元（類似VARCHAR2），則在TO_CHAR函式的格式元素前加上“FM”；如果包含多個格式元素，則應在各個需要去掉空格的元素前都加上FM，如：

SQL> select to_char(sysdate,'month') month from dual;

MONTH

---------

may

SQL> select length(to_char(sysdate,'month')) month from dual;

MONTH

----------

9

SQL> select length(to_char(sysdate,'fmmonth')) month from dual;

MONTH

----------

3

SQL> select length(to_char(sysdate,'fmmonth fmday')) from dual;

LENGTH(TO_CHAR(SYSDATE,'FMMONT

------------------------------

13

SQL> select length(to_char(sysdate,'month day')) from dual;

LENGTH(TO_CHAR(SYSDATE,'MONTHD

------------------------------

19

注意：如果在格式元素字串中加上空格或者其他字元，則得到的字串的總長度也會加上額外的字元的長度。如：

SQL> select length(to_char(sysdate,' month')) month from dual;

MONTH

----------

10

3） TO_CHAR(NUMBER [.Fmt])函式

Ø 格式

元素	作用	舉例
9	以數字的一般格式顯示	SQL> select to_char(1234.12,'9999999999') from dual; TO_CHAR(1234.12,'9999999999') ----------------------------- 1234
0	對整數部分，如果原數字的整數部分大於設定格式的整數部分，則返回的字串會在在前面加’0’；如果是小數部分，則它相當於’9’的作用	SQL> select to_char(1234.1,'099999.999') new_char from dual; NEW_CHAR ---------- 001234.100 只要0出現的位置大於原數字的整數部分，返回值的整數部分長度就是0出現的位置；（不管0之外的是什麼數字） SQL> select to_char(1234.1,'9909999.999') new_char from dual; NEW_CHAR ------------ 01234.100
.	在返回的字串上加小數點
,	在返回的字串的整數部分加’,’；它不能在小數部分設定
L	在返回的字串前加上本地的貨幣符號	SQL> select to_char(1234,'l9999') new_char from dual; NEW_CHAR --------------- RMB1234
$	在返回字串前加上美圓符號

注意：

Ø 如果數字整數部分的長度大於設定的格式的整數的長度，則結果會不能正常顯示，而是用#####表示；如果數字的小數部分的長度大於設定格式的小數部分，則會對數字在設定的小數部分執行四捨五入，最後返回小數長度為設定格式的長度。

Ø 返回字串的長度為設定的格式的長度+1

SQL> select to_char(12345,'99.000') from dual;

TO_CHAR(12345,'99.000')

-----------------------

#######

SQL> select to_char(0.12345,'999.9999') from dual;

TO_CHAR(0.12345,'999.9999')

---------------------------

.1235

SQL> select length(to_char(1234.12,'9999999999')) from dual;

LENGTH(TO_CHAR(1234.12,'999999

------------------------------

11

Ø fm對TO_CHAR(NUMBER [,FMT])也有用，其作用可以參考TO_CHAR(DATE [,FMT])；如果不加fm，對數字長度小於FMT的情況會在返回的字串上前用空格補全。

Ø 對TO_CHAR(NUMBER [,FMT])函式，只要設定了格式，在返回結果中得到的字串就會自動在前面補上一個空格，返回的字串的長度就是FMT設定的長度+1。

4） TO_DATE和TO_NUMBER函式

Ø 如果在格式前加上FX，則表明字串和格式需要精確匹配；沒有FX則ORACLE會盡量匹配。

SQL> select to_date('2005-1-1','fxyyyy-mm-dd') from dual;

select to_date('2005-1-1','fxyyyy-mm-dd') from dual

ORA-01862: the numeric value does not match the length of the format item

SQL> select to_date('2005-01-01','fxyyyy-mm-dd') from dual;

TO_DATE('2005-01-01','FXYYYY-M

------------------------------

2005-01-01

SQL> select to_date('2005-1-1','yyyy-mm-dd') from dual;

TO_DATE('2005-1-1','YYYY-MM-DD

------------------------------

2005-01-01

3.6 幾個常用函式

1） NVL(EXP1,EXP2)

Ø 如果EXP1為空，則返回EXP2，否則返回EXP1。返回值的資料型別一定與EXP1相同。

Ø EXP1和EXP2的資料型別原則上要求一致，如果EXP2的資料型別可以隱式轉換成EXP1資料型別也可以。

Ø 第一個引數可以是任意表示式、或子查詢

舉例：如果A中存在b與變數值相等則按條件取值,否則取全部的值

select * from t where a=nvl((select a from t where t.a=2 and rownum<=1),a);

2） NVL2(EXP1,EXP2,EXP3)

Ø 如果EXP1非空，則返回EXP2；否則，返回EXP3。返回值的內容一定與EXP1相同。

Ø EXP1、EXP2、EXP3的資料型別原則上要求一致，但如果EXP2、EXP3的資料型別可以隱式轉換成EXP1的資料型別也可以。

3） NULLIF(EXP1,EXP2)

Ø 如果EXP1等於EXP2，則返回NULL，否則返回EXP1。

Ø EXP1、EXP2的資料型別嚴格要求一致，否則即使EXP2的資料型別可以隱式轉換為EXP1的資料型別也會出錯。

Ø EXP1不可以是NULL，但EXP2可以為null。

4） COALESCE(EXP1,EXP2...EXPN)

Ø 返回第一個非空的表示式的值。

Ø 要求所有的非空的EXP的資料型別一樣，否則即使其他非空的EXP的資料型別可以隱式轉換為EXP1的資料型別也會出錯。

3.7 CASE 子句

1） 語法

CASE expr WHEN comparison_expr1 THEN return_expr1

[WHEN comparison_expr2 THEN return_expr2

WHEN comparison_exprn THEN return_exprn

ELSE else_expr]

END

2） ELSE子句是可選的，不一定需要。

3） return_expr和else_expr可以部分為NULL，但不能全為NULL

4） 如果指定了EXPR，則comparison_expr只能是數值或者不包含比較運算子的表示式；如果EXPR為空，則comparison_expr必須都是包含比較運算子的表示式。

5） 所有的return_exprn、else_expr必須是相同的資料型別。

3.8 DECODE函式

1） 語法

DECODE(col|expression, search1, result1

[, search2, result2,...,]

[, default])

2） 函式的引數個數必須>=3個，三個以上的任意個引數都可以

3） result1和result2等可以是不同的資料型別

4） 如果不指定default值，且沒有符合條件的表示式，則返回NULL

5） 第一個引數可以是常量、列名、表示式，甚至可以是子查詢

SQL> select distinct id,decode((select t2.id from t2 where t2.id=t1.id and rownum=1),null,'null',t1.id) "if_t2_in_t1"from t1;

ID if_t2_in_t1

---------- ----------------------------------------

1 1

2 null

4 4

5 5

null