SQL Server教學 » SQL Server儲存過程

SQL Server儲存過程

在SQL Server中，儲存過程是比檢視更強大。檢視讓我們簡單地做一個SELECT查詢也在可檢視本身，它的工作方式是用於執行複雜的查詢。 但檢視並不姝真正為我們提供程式碼業務邏輯的能力。例如，檢視不會能讓我們增加條件程式碼(如IF語句)。幸運的是儲存過程可能使用。

什麼是儲存過程？

儲存過程是一組SQL語句編譯成一個SQL。類似於在說的SQL指令碼頁面，在這裡可以執行許多SQL指令碼合為一個整體。

然而，儲存過程並不僅僅是一個長指令碼。這是已儲存在SQL Server中明確在儲存過程節點的指令碼。儲存過程通常包含一些業務邏輯。 例如，一個儲存過程可以接受被傳遞給它的並針對這些引數測試使用IF語句的引數。例如，如果該引數是一個值，這樣做，如果它是另一個值。

它們包含業務邏輯的能力使儲存過程SQL Server的強大的一部分。儲存過程可以提高效能的應用程式，因為儲存過程進行解析，並儘快，因為它是建立優化，然後儲存在記憶體中。執行通過儲存過程條件的查詢可以是非常快 - 相比，傳送查詢通過網路，到SQL Server的應用程式，遂將全部返還給它在網路上，因此它可以過濾通過它，並挑選出只是它感興趣的記錄資料。

儲存過程的好處

下面是一些在使用儲存過程的主要優點：

好處 說明
模組化程式設計 可以寫一個儲存過程一次，然後一次又一次地呼叫它，從應用程式的不同部分（甚至多個應用程式）。
效能 儲存過程提供更快的程式碼執行和減少網路流量。
  • 更快的執行：儲存過程進行解析，並為他們創造盡快優化和儲存過程是儲存在記憶體中。這意味著它會執行比從應用程式到SQL Server傳送SQL多行程式碼快了很多。這樣做需要SQL Server來編譯和每次執行時優化改進你的SQL程式碼。
  • 減少網路流量：如果通過網路傳送的SQL多行程式碼到你的SQL Server中，這會影響網路效能。如果你有數以百計的SQL程式碼行和/或你有很多活動在應用程式，這是尤其如此。執行SQL Server上的程式碼（作為儲存過程中）消除了需要傳送此程式碼在網路上。唯一的網路通訊將提供的引數和任何查詢的結果。
安全 使用者無需執行任何直接的語句可以執行儲存過程。因此，儲存過程可以誰也不能正常存取這些任務的使用者提供先進的資料庫功能，但這種功能是在嚴格控制的方式提供。

建立一個儲存過程

要建立一個儲存過程，需要使用CREATE PROCEDURE語句，其次是彌補了儲存過程的程式碼。如果儲存過程將要接受引數，它們需要被包括在名稱後。

? 
CREATE PROCEDURE myStoredProcedure AS
...

OR

CREATE PROCEDURE myStoredProcedure @{Parameter Name} {data type} AS
...

範例

下面的程式碼建立一個儲存過程被稱為“LatestTasks”。它接受一個引數名為 @Count. 當呼叫這個儲存過程，通過@count引數，它決定你想要多少行返回。下面的程式碼：

? 
CREATE PROCEDURE LatestTasks @Count int AS
SET ROWCOUNT @Count
SELECT TaskName AS LatestTasks, DateCreated
FROM Tasks
ORDER BY DateCreated DESC

執行這段程式碼在SQL Server管理套件，會看到它被在儲存過程節點建立為“LatestTasks”。

Screenshot of stored procedure in the Object Explorer

在SQL Server2014，可以在儲存過程節點/檔案夾中建立通過右鍵單擊一個儲存過程，選擇儲存過程....這將開啟一個模板，這是隨時可以填入自己的具體程式。

執行儲存過程

現在，已經建立了儲存過程，要執行它的任何時間，需要使用呼叫它要麼執行或EXEC。如果儲存過程的引數要求提供這些程式名在後面。像這樣：

? 
EXECUTE LatestTasks 
EXEC LatestTasks

EXEC LatestTasks @Count = 5

範例

在下面的例子中，我們在同一時間執行儲存過程兩次。我們第一次呼叫它，我們通過@Count傳遞為3，第二次傳遞的值為5。

該截圖顯示，通過傳遞引數（和值），儲存過程返回結果的基礎上提供值。頂端結果集返回3行，因為我們通過值3。第二個結果集返回5行，因為我們提供一個值5：

Screenshot of stored procedure being executed

使用GUI

還可以使用圖形化使用者介面來執行儲存過程。

具體方法如下：

  1. 使用物件資源管理器，瀏覽到儲存過程
  2. 右鍵單擊該儲存過程並選擇 Execute Stored Procedure...:
    Initiating the execution of a stored procedure
  3. 會出現一個對話方塊。輸入您所選擇的引數值：
    Initiating the execution of a stored procedure
  4. 點選 OK
  5. SQL Server現在會生成SQL程式碼並執行儲存過程。

修改儲存過程

如果需要修改現有的儲存過程，只需更換CREATE使用ALTER。 讓我們新增一個空格之間“Latest”和“Tasks”（即“Latest Tasks”），並新增描述欄位：

? 
ALTER PROCEDURE LatestTasks @Count int AS
SET ROWCOUNT @Count
SELECT TaskName AS "Latest Tasks", Description, DateCreated
FROM Tasks
ORDER BY DateCreated DESC

系統儲存過程

SQL Server包含了大量的系統儲存過程，以幫助資料庫管理任務。可以通過GUI執行的任務可以通過系統儲存過程來完成。 例如，有些東西可以用系統儲存過程的包括：

  • 組態安全帳戶
  • 建立連結伺服器
  • 建立一個資料庫維護計劃
  • 建立全文檢索目錄
  • 新增遠端登入
  • 組態複製
  • 設定排程作業
  • 以及更多...

命名約定

擴充套件系統儲存過程節點，一起來看看。會發現，他們的名字都開始以sp_，表明它是一個儲存過程。該系統儲存過程顯然遵循的命名約定。

這是一個好主意，制定一個一致的命名約定儲存過程（並為資料庫中的所有其他物件）。

有些人字首的儲存過程usp_，另外其他人使用SQL關鍵字，如SELECT，INSERT，UPDATE，DELETE。其他人使用的縮寫應用。

一些使用下劃線的儲存過程（例如，latest_tasks）在單獨的單詞，而其他人將使用標題的情況下（例如，LatestTasks）。

因此，這是可能的，我們的儲存過程可以被命名為以下任意一種，這取決於命名約定的使用。

  • LatestTasks
  • latest_tasks
  • uspLatestTasks
  • usp_latest_tasks
  • selectLatestTasks
  • select_LatestTasks
  • select_latest_tasks
  • getLatestTasks
  • get_latest_tasks

最重要的是一致性。選擇一個，並堅持下去。它會更容易，當你需要使用儲存過程。 想象一下，有幾十，甚至上百的儲存程式，每一次執行一次，需要找到它在物件資源管理器，是否把它稱為usp_LatestTasks或uspLatestTasks。

所以這是儲存過程覆蓋。接下來，我們了解使用者登入。