[DB] Stored Procedure On Oracle

簡單整理一下Oracle的Stored Procedure(SP)的基本寫法~
P.S：C#如何呼叫SP可以參考此篇。

下面是SP的主要架構(SP大小寫沒有差別)：
此SP命名為SP1。
sYEAR和sNAME是呼叫SP時要輸入的參數(sNAME有預設值TIM)，sRETURN_VALUE是SP執行結束後會輸出的參數。

create or replace 
PROCEDURE SP1 (sYEAR NUMBER, sNAME VARCHAR2 DEFAULT 'TIM', sRETURN_VALUE OUT VARCHAR2) 
AS
  -- 定義變數區
BEGIN
  -- 主要執行區
END SP1;

定義變數區的範例：

--NUMBER
nYEAR NUMBER;

--INTEGER
iINTEGER PLS_INTEGER := 1;

--VARCHAR要指定好長度
sADDRESS VARCHAR2(20); 

--變數為 Not Null時，須指定預設值
sNOT_NULL VARCHAR2(4) NOT NULL := 'test';

主要執行區的範例：

--IF的用法
IF nYEAR IS NULL THEN
 BEGIN
  nYEAR := '10';
  sADDRESS := '台灣省台中市';
 END;
ELSE
 BEGIN
  nYEAR := '99';
  sADDRESS := '台灣省台北市';
 END;
END IF;

--FOR迴圈的用法：用SELECT指令找出資料集，再用LOOP來跑迴圈
FOR row_TEST IN (SELECT YEAR,ADDRESS from tblTEST)
LOOP
  -- row_TEST代表每一個RECORD，用下面的方法很方便的就能取到讓RECORD的某個欄位值
  sRETURN_VALUE := row_TEST.YEAR;
END LOOP;

--單純用SELECT取一個值：存放值的變數要先定義於定義變數區
SELECT ADDRESS INTO sADDRESS FROM tblTEST WHERE YEAR='99';

--呼叫另一個SP，並傳入一個參數過去.
--P.S：如果此SP_OTHER不想包含於SP1同一個Transaction，那可以在SP_OTHER的定義變數區最後加上PRAGMA AUTONOMOUS_TRANSACTION;
SP_OTHER(sYEAR);

--印一個資訊出來，方便用於測試時
dbms_output.put_line(sYEAR);

Transaction的範例(寫在主要執行區最下面)：

--commit
COMMIT;

--發生Exception時就RollBack
EXCEPTION
WHEN OTHERS
THEN 
 sRETURN_VALUE := '執行失敗，請洽系統管理員';

 --也可在此再做些產生Exception時的動作，例如寫Log檔

 --RollBack
 ROLLBACK;

FUNCTION的寫法跟SP很像：
此function命名為Fun1。
sNAME是呼叫function時要輸入的參數，執行結束後會回傳一個VARCHAR。

create or replace 
FUNCTION Fun1 (sNAME VARCHAR2) RETURN VARCHAR2
AS
  -- 定義變數區
BEGIN
  -- 主要執行區
  ...
  ...
  RETURN '執行成功';
END;

由於早期Oracle沒有內建可以分割字串的功能，因此必須自己動手來做分割字串。在此分享一個在StackOverflow找到的SPLIT字串的function，只要餵入",a,b,c,d"，就會分別回傳abcd，使用範例如下：

create or replace 
PROCEDURE TEST
AS
    i PLS_INTEGER := 1;
BEGIN
 LOOP
  -- 注意！輸入的字串開頭要有一個逗號
  str := SPLIT(',A,B,C,D', i, ',');
  EXIT WHEN str IS NULL;
  dbms_output.put_line(str);
  i := i + 1;
 END LOOP;
END;

--執行結果：
--A
--B
--C
--D

參考來源：StackOverflow