Stored Procedure(預存程序)基礎 FOR 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寫法,其實跟一般程式語言沒兩樣,就是熟悉一下語法就可以了,多多練習吧!!!