Oracle預存程序實戰

1.基本結構
CREATE OR REPLACE PROCEDURE 預存程序名字
(
參數1 IN NUMBER,
參數2 IN NUMBER,
參數3 OUT NUMBER
) IS
變數1 INTEGER := 預設值;
變數2 DATE;
BEGIN
…
EXCEPTION
WHEN 異常類別 THEN
…
WHEN OTHERS THEN
…
END 預存程序名字;

2.基本操作
變數賦值 V_TEST := 1;
動態賦值 SELECT COL1,COL2,… INTO VAR1,VAR2,… FROM … WHERE …;
字元相加  ‘STRING1′ || ‘STRING2′;
相等判斷 =而不是==;
邏輯判斷 AND,OR,>,<,<>;
列印輸出 dbms_output.put_line();
是否為空白 V_TEST IS (NOT) NULL;
提交復原 COMMIT/ROLLBACK;
異常捕捉 BEGIN … EXCEPTION WHEN … THEN … END;

3.IF 判斷
IF (判斷條件) THEN
BEGIN
…
END;
ELSIF THEN
…
ELSE
…
END IF;

4.WHILE 迴圈
WHILE (判斷條件) LOOP
BEGIN
…
END;
END LOOP;

5.FOR 迴圈
FOR X IN (SELECT col1,col2 …) LOOP
BEGIN
引用X.col1、X.col2
…
END;
END LOOP;

6.常見FUNCTION
以下列出的是一些常用函數的普通用法，並不是按照ORACLE函數標準進行說明的，所以有些用法並不全面，只是列舉了最常用的情況；
SYSDATE：用來得到系統的當前日期；
TO_NUMBER(STR)：將字串轉換成數字；若轉換不成功，則拋錯：ORA-01722: invalid number；
TO_CHAR(?)：?可支援很多的類型，比如NUM，DATE……；DATE用得比較多，一般來說有如下用法：
TO_CHAR(SYSDATE, ‘YYYY-MM-DD HH24:MI:SS’)：將當前日期按照格式輸出完整字串；其中的日期格式可以自行定義；
TO_DATE(STR,FORMAT)：對字串按照一定格式解析成Date型；
NVL(col,defaultValue)：對col判斷是否為NULL,若為NULL，則返回defaultValue；
CONCAT(STR1,STR2)：串連兩個字串，功能與||相同；
LENGTH(STR)：返回字串的長度，其中中文字元的長度計算要視DB的編碼而定，在GBK編碼下，中文也算1個字元；
若需要中文字元長度算2個字元，則可以用LENGTHB(STR)代替；
簡單來說，LENGTH返回字元長度；LENGTHB返回位元組長度；
SUBSTR(str,start,count)：截取子字串,從start開始,取count個；
start為0和1的效果是一樣的，都是從str的第一個字元開始截取；
若start>=LENGTH(str)，直接返回空，不會拋錯；
INSTR(STR1,STR2,I,J)：在一個字串中搜尋指定的字元,返回傳現指定的字元的位置;
STR1:被搜尋的字串
STR2:希望搜尋的字串
I:搜尋的開始位置,預設為1
J:第幾次出現的位置,預設為1
LOWER/UPPER(STR)：將字串全部轉成小寫或者大寫；在GBK編碼下，該函數對中文無效；
LTRIM/RTRIM(STR)：刪除左邊貨右邊出現的空白字元串；
FLOOR(NUMBER)：對給定的數字取整數；
MOD(num1,num2)：返回num1除以num2的餘數；num1和num2可以帶有小數位；返回的值也可能有小數位；
ROUND(num)：對num四捨五入取值；
TRUNC(num)：對num截取整數，去掉小數位，不進行四捨五入；功能與FLOOR類似；當然也可以在參數中指定精度；
TRUNC(Date)：對date截取到天，去掉小時分鐘之類的，其傳回型別仍然為Date；類似的，也可以在參數中指定Date截取層級，比如’hh’；
ADD_MONTHS(Date,num)：對Date加上num個月，num可以為負數，表示減去幾個月；
num可以有小數位，但是並不會起效，效果相當於ADD_MONTHS(Date,TRUNC(num))；
CHARTOROWID/ROWIDTOCHAR：將字元資料類型轉換為ROWID類型或者相反；
AVG/MIN/MAX/SUM/COUNT(DISTINCT|ALL col)：對某欄位取平均、最小、最大、求和、計數；預設為ALL，若需要對不重複值運算，則用DISTINCT參數；
CASE WHEN … THEN …
WHEN … THEN …
ELSE …
END：根據不同條件取不同的值，與JAVA中的CASE也很類似；

6.CURSOR 申明
無參數申明：CURSOR CUR_NAME IS SELECT COL1,COL2,… FROM … WHERE …;
帶參數申明：CURSOR CUR_NAME(PARAM1 DATA_TYPE,PARAM2 DATA_TYPE,…) IS SELECT COL1,COL2,… FROM … WHERE …;

7.用FOR IN使用CURSOR
FOR CUR_RS IN CUR_NAME LOOP
BEGIN
引用CUR_RS.COL1,CUR_RS.COL2,…
…
END;
END LOOP;

8.用FETCH INTO使用CURSOR
LOOP
FETCH CUR_NAME INTO V_COL1,V_COL2,…;
…
EXIT WHEN CUR_NAME%NOTFOUND;
END LOOP;

9.用BULK COLLECT使用CURSOR，用於大量操作，提高效率
–申明TABLE資料類別
TYPE TYPE_NAME IS TABLE OF DATA_TYPE;
比如TYPE T_ROWID IS TABLE OF UROWID;

–申明變數
VAR_NAME TYPE_NAME;
比如VAR_ROWID T_ROWID;

LOOP
–1000條一個批次
FETCH CUR_NAME BULK COLLECT INTO VAR_ROWID,V_COL2,… LIMIT 1000;
FORALL i IN VAR_ROWID.FIRST .. VAR_ROWID.LAST
…ONE SQL;
COMMIT;
EXIT WHEN CUR_NAME%NOTFOUND;
END LOOP;

10.EXIT WHEN CUR_NAME%NOTFOUND 位置
在配合FETCH INTO語句使用時，EXIT WHEN CUR_NAME%NOTFOUND可以放在迴圈的開始處，也可以放在最後面；視具體情境而定；
WHEN CUR_NAME%NOTFOUND也可以用其他的判斷條件替換；
比如在用BULK COLLECT時，就可以用EXIT WHEN VAR_ROWID.COUNT = 0;不過此時需要放在FETCH之後FORALL之前；

1.SELECT INTO語句要求SELECT出來的RESULT至少有一條記錄；
若RS為NULL,則會拋出NO_DATA_FOUND的EXCEPTION；所以需要進行異常捕獲；

2.進行UPDATE、DELETE操作時可以用ROWID替換PK可以提高執行效率；
因為ROWID是直接定位到物理磁碟地址，而無需先從PK Index中查詢，從而有效降低IO次數；
比如UPDATE MD_USER SET NAME = XX WHERE ROWID = ?;
不過用ROWID時需要小心，因為1)ROWID會被重用；2)在有GROUP BY語句的條件中無法使用；

3.EXCEPTION捕獲之後若不需要做任何事情，則可以如下處理：
BEGIN … EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END;

4.EXCEPTION能夠捕獲的常見類別有：
NO_DATA_FOUND：代碼塊中某條SELECT INTO語句返回的RS為NULL；
TOO_MANG_ROWS：SELECT INTO語句合格記錄有多條返回；
DUP_VAL_ON_INDEX：對於資料庫表中的某一列，該列已經被限制為唯一索引，程式試圖儲存兩個重複的值；
VALUE_ERROR：在轉換字元類型，截取或長度受限時，會發生該異常，如一個字元分配給一個變數，而該變數聲明的長度比該字元短，就會引發該異常；
STORAGE_ERROR：記憶體溢出；
ZERO_DIVIDE：除數為零；
CASE_NOT_FOUND：對於選擇case語句，沒有與之相匹配的條件，同時，也沒有else語句捕獲其他的條件；
CURSOR_ALREADY_OPEN：程式試圖開啟一個已經開啟的遊標；
TIMEOUT_ON_RESOURCE：系統在等待某一資源，時間逾時；
OTHERS：所有；
除此之外，還可以用RAISE來拋出一個自訂的EXCEPTION；比如
DECLARE MY_ERROR EXCEPTION;
BEGIN
IF (…) THEN
RAISE MY_ERROR;
END IF;
EXCEPTION WHEN MY_ERROR THEN
…
WHEN OTHERS THEN
…
END;

5.大量操作BULK COLLECT INTO情況下，FORALL下面只能有一條SQL操作，也不能是存過調用；若需要執行多條SQL，則需要用多個FORALL，比如
…
LOOP
–1000條一個批次
FETCH CUR_NAME BULK COLLECT INTO VAR_ROWID,V_COL2,… LIMIT 1000;
FORALL i IN VAR_ROWID.FIRST .. VAR_ROWID.LAST
INSERT INTO …;
FORALL i IN VAR_ROWID.FIRST .. VAR_ROWID.LAST
UPDATE TABLE_NAME SET …;
COMMIT;
EXIT WHEN CUR_NAME%NOTFOUND;
END LOOP;

6.重複資料高效清理SQL：
DELETE FROM TABLE_NAME tn
WHERE tn.ROWID >
(SELECT MIN(x.ROWID)
FROM TABLE_NAME x
WHERE x.col1 = tn.col1
AND x.col2 = tn.col2);
–根據col1、col2兩個欄位清理，清理後兩個欄位滿足unique約束；

7.一條SQL搞定如下情境：當資料存在進行更新，否則進行新增；
MERGE INTO TABLE_NAME tn
USING (SELECT v_pk AS pk FROM DUAL) tmps
ON (tn.pk = tmps.pk)
WHEN MATCHED THEN
UPDATE
SET col1 = ?, col2 = ? ,…
WHEN NOT MATCHED THEN
INSERT (col1, col2, …)
VALUES (v_col1, v_col2, …);
注意：ON子句中的欄位不能在UPDATE子句中進行SET操作；

8.CONNECT BY語句使用
SQL> SELECT rownum rn FROM dual CONNECT BY rownum <= 5;

RN
———-
1
2
3
4
5
該語句一般配合笛卡爾積使用，一條語句搞定很多條INSERT語句的事情，可以大大減少SQL代碼量；