yaya

Store procedure (預存程序) 

簡單的說就是把SQL的操作語句寫成類似程式的概念，當中可以有宣告變數、IF ELSE判斷式、TRY CATCH等等，

並且儲存在DB來執行，通常應用在batch(批次作業)上面，例如系統每日備份某資料表或製作報表的batch。

以下是一個使用MS SQL建立store procedure的例子

CREATE PROCEDURE SP001
AS

DECLARE @jobStatus varchar(1)

SET @jobStatus = 

(SELECT job_status
FROM job
WHERE job_id = 'SP001')

IF (@jobStatus <> 'S')
BEGIN

INSERT INTO job_log
(job_id
,job_status
,exec_des)
VALUES
('SP001'
,'F'
,'執行前job_status!=S，不執行'
,GETDATE())
RETURN 401

END

ELSE
BEGIN

INSERT INTO job_log
(job_id
,job_status
,exec_des)
VALUES
('SP001'
,'S'
,'預存程序001-開始執行')

--要執行的SQL

END

● 建立 procedure方法

CREATE PROCEDURE [程序名稱] AS

EX. CREATE PROCEDURE SP001 AS

也可建立須帶入參數的procedure

CREATE PROCEDURE [程序名稱] (@參數1 參數型態, @參數2 參數型態, ...)

● 變數宣告

DECLARE @變數名稱 變數型態

EX. DECLARE @jobStatus varchar(1)

● 變數賦值

1. 直接給予值

EX. @jobStatus = 'F'

2. 利用SQL查詢賦值

SET @jobStatus = 

(SELECT job_status 
FROM job 
WHERE job_id = 'SP001')

● IF ELSE 判斷式

SQL procedure中也有類似程式語言中的 if...else判斷式，注意需用BEGIN  END將執行語句包覆，類似於程式語言中的{}作用

IF (判斷條件)

BEGIN

(判斷條件成立要執行的SQL)

END

ELSE

BEGIN

(IF之判斷條件階不成立，則要執行的SQL)

END

EX. 若@jobStatus參數不為S，則新增job_log一筆紀錄

IF (@jobStatus <> 'S')
BEGIN

INSERT INTO job_log
(job_id
,job_status
,exec_des)
VALUES
('SP001'
,'F'
,'執行前job_status!=S，不執行'
,GETDATE())
RETURN 401

END

● 迴圈

可使用WHILE來達到迴圈的作用

WHILE (判斷條件)

BEGIN

(判斷條件成立要執行的SQL) 直到判斷條件不成立才會跳出迴圈

END

可搭配BREAK語句來跳出迴圈，另有CONTINUE的用法 (不再執行CONTINUE之後的語句，重新執行迴圈)

EX.

WHILE (SELECT AVG(ListPrice) FROM Production.Product) < $300
BEGIN
   UPDATE Production.Product
      SET ListPrice = ListPrice * 2
   SELECT MAX(ListPrice) FROM Production.Product
   IF (SELECT MAX(ListPrice) FROM Production.Product) > $500
      BREAK
   ELSE
      CONTINUE
END

● procedure回傳值

SQL SERVER中 procedure預設執行成功之回傳值為INT 0，我們也可使用RETURN語句，另procedure執行結束並回傳該值

EX.

IF (@jobStatus <> 'S')
BEGIN

RETURN 401    (procedure執行結束並回傳401)

END

● TRY...CATCH

procedure中也有程式語言中的try catch機制， 不同於迴圈控制使用BEGIN END包覆執行語句，而是使用BEGIN TRY  END TRY來包覆，當TRY包覆語句執行時發稱錯誤，則會執行CATCH包覆之語句，CATCH中可呼叫系統函數來取得錯誤訊息。

在 CATCH 區塊的範圍內，下列系統函數可用來取得造成執行 CATCH 區塊之錯誤的相關資訊：
ERROR_NUMBER() 會傳回錯誤碼。
ERROR_SEVERITY() 會傳回嚴重性。
ERROR_STATE() 會傳回錯誤狀態碼。
ERROR_PROCEDURE() 會傳回發生錯誤的預存程序或觸發程序的名稱。
ERROR_LINE() 會傳回常式內造成錯誤的行號。
ERROR_MESSAGE() 會傳回錯誤訊息的完整文字。 文字包括提供給任何可替代參數的值，例如，長度、物件名稱或次數。

EX.

BEGIN TRY
    -- Generate divide-by-zero error.
    SELECT 1/0;
END TRY
BEGIN CATCH
    -- Execute error retrieval routine.
    EXECUTE usp_GetErrorInfo;
END CATCH; 

參考資料：https://technet.microsoft.com/zh-tw/library