之前都沒有碰過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寫法,其實跟一般程式語言沒兩樣
就是熟悉一下語法就可以了,多多練習吧!!!