Stored Procedure(預存程序)基礎(Oracle)

之前都沒有碰過Stored Procedure

但最近幾個專案寫了蠻多的SP,所以來做個紀錄

SP的主要架構為:

CREATE OR REPLACE procedure 程序名稱 IS
    宣告區
BEGIN
    執行主體區
END

 

程序名稱的部分,若不需要參數則不用加括號,例如:

CREATE OR REPLACE procedure MY_TEST_SP IS……

 

若要使用參數,則需要區分為帶入的參數,以及傳出的參數

帶入的參數為執行中會用到的參數,宣告時需要加IN

傳出的參數即為執行完畢後要回傳的參數,宣告時需要加OUT。例如:

CREATE OR REPLACE procedure MY_TEST_SP(STORE_CODE IN VARCHAR,TOTAL_AMOUNT OUT NUMBER) IS……

 

上述代表若要執行MY_TEST_SP,則需要用兩個參數

一個為帶入值,一個為接收回傳值,執行方法如下:

DECLARE
    CODE VARCHAR(5) := ‘252’;
    MONEY NUMBER;
BEGIN
    MY_TEST_SP(CODE, MONEY);
    DBMS_OUTPUT.PUT_LINE(MONEY);
END;

此時執行SP時會傳入252這個字串作為執行參數用

而MONEY則會接收SP執行完成後的回傳值,所以MONEY不需要給值

 

這邊先補充一些小細節:

1.DBMS_OUTPUT.PUT_LINE為輸出字串的功能

就像是JAVA的System.out.println或是C#的Console.WriteLine功能一樣

2.:=是oracle中賦值的語法

3.和許多程式語言一樣,Oracle結尾要加;

4.字串要用單引號(''),用雙引號會出錯

5.字串相加用 || ,用 + 會認為是數字相加而報錯

 

宣告區的部分,則是宣告該SP會使用到的變數

宣告時需要給予變數名稱以及資料型態,而值可給可不給。例如:

sellDate Date;
strTodayDate Varchar(8);
dataType Varhcar(2) := ‘15’;
maxAmount smallint;
Cursor storeData is
    select * from dual;

其中Cursor是一個指標,會指向一個查詢結果

簡單來說就是當作一個變數去承接SQL查詢後的結果

之後就把變數當作查詢結果來操作

 

接下來進入執行主體區,該區域就像是Main方法一樣,是實際執行程式碼的部份。以下介紹幾個常用的語法:

1.

SELECT TO_CHAR(sysdate,’YYYYMMDD’) into strTodayDate from dual;

上述語法中,TO_CHAR可以將數字或日期轉成字串型態

若要轉數字只需要一個參數即可,即TO_CHAR(256)

若要轉為日期型態則需要兩個參數,第一個參數為日期型態的變數(這裡使用sysdate,為系統目前時間)

第二個為轉為字串的日期型態,可以為’YYYY-MM-DD’或是’YYYY/MM/DD’

再來,dual是一個不存在、虛擬的table,方便SQL語法操作

然後,主體執行區可以將SQL語法查出來的值帶入宣告區的變數中

所以上述語法的意思為:將SELECT TO_CHAR(sysdate,’YYYYMMDD’) from dual查出來的值

帶給strTodayDate這個變數(into strTodayDate)

 

2.IF…ELSE…

使用IF時會搭配一個END IF做結尾,中間可以隨意塞數個ELSE IF和一個ELSE,所以使用起來會如下:

IF (判斷式1) THEN
    ……
ELSE IF (判斷式2) THEN
    ……
ELSE
    ……
END IF;

 

 

3.迴圈

迴圈主要有三種用法:

(1)FOR LOOP,語法為:

FOR i IN n…m LOOP
    ……
END LOOP;

 

跟大多數的程式語言一樣,用一個i為變數

其值為n到m去執行迴圈,共執行m-n+1次

 

(2)WHILE LOOP,語法為:

WHILE (判斷式) LOOP
    ……
END LOOP;

無窮迴圈,記得下條件式跳出迴圈,或者使用EXIT跳出迴圈

 

(3)FOR item IN Cursor,語法為:

CURSOR cur IS
    select …;

FOR ITEM IN cur LOOP
    ……
END LOOP

其功用就像是foreach,可以直接遍歷Cursor物件裡面的每一筆資料

在對每一筆資料做處理時非常好用

 

 

4.例外處理

執行時會發生一些例外,需要try…catch…去處理,而SP處理例外的語法為:

BEGIN
    執行主體
EXCEPTION
    WHEN (錯誤種類) THEN
        ……

    WHEN OTHERS THEN
        ……
END;

例外處理的範圍要再使用一組BEGIN + END包裹起來

裡面加一個EXCEPTION為例外處理的區塊

而錯誤種類可以用OTHERS代表所有種類的錯誤

若要抓仔細一點的錯種類,就上網查一下吧

 

以上為一些基礎的SP寫法,其實跟一般程式語言沒兩樣

就是熟悉一下語法就可以了,多多練習吧!!!