SQL SERVER 從入門到精通 第5版 第三篇 高階應用 第10章 儲存過程 讀書筆記

一曲轻扬發表於2024-04-12
SQLServer儲存過程筆記

第10章 儲存過程

　　>.儲存過程概述

　　儲存過程(stored procedure)是預編譯SQL語句的集合,這些語句儲存在 一個名稱下並作為一個單元來處理.儲存過程取代了傳統的逐條執行SQL語句的方式.一個儲存過程中可以包含增刪改查等一系列SQL語句,當這個儲存過程被呼叫時,這些操作也會同時被執行.

　　儲存過程與其他程式語言中的過程類似,它可以接受輸入引數並以輸出引數的格式,它可以向呼叫過程或者批處理返回:

　　　　1.多個值;包含用於在資料庫中執行操作(包括呼叫 其他過程)的程式設計語句;

　　　　2.狀態值,以指明成功或者失敗(以及失敗的原因)

　　>.儲存過程的分類:

　　　　>.系統儲存過程:用來管理SQL SERVER和顯示有關資料庫與使用者資訊的儲存過程;

　　　　>.自定義儲存過程: 使用者在SQL SERVER中透過採用SQL語句建立的儲存過程;

　　　　>.擴充套件儲存過程: 透過程式語言(例如C)建立外部全程,並將這個例程在SQL SERVER 中作為儲存過程使用.

　　>.儲存過程的優點:

　　　　>.儲存過程可以巢狀使用,支援程式碼重用.

　　　　>.儲存過程可以接受與使用引數,動態執行其中的SQL語句.

　　　　>.儲存過程比一般的SQL語句執行速度愉.儲存過程在建立時已經被編譯,每次執行時不需要重新編譯,而SQL語句每次執行時都需要編譯.

　　　　>.儲存過程具有安全性和所有權連結,以及可以附加到它們的證書.使用者可以被授予許可權來執行儲存過程而不必直接對儲存過程中引用的物件具有許可權.

　　　　>.儲存過程允許模組化程式設計.儲存過程一旦建立,以後可以在程式中多次呼叫.

　　　　>.儲存過程可以減少網路通訊流量.

　　　　>.儲存過程可以保證應用程式的安全性.

　　>.建立儲存過程:

　　　　>.使用嚮導建立一個名為test的儲存過程.

　　　　

　　在"查詢"選單中,選擇 "指定模板引數的值",然後修改儲存過程的名稱(Procedure_name)與SQL語句

　　>.使用Create PROC語句來建立儲存過程　　　　

    -- 此儲存過程透過員工ID檢索員工資訊。
CREATE PROCEDURE [dbo].[GetEmployeeByID]     --GetEmployeeByID儲存過程名稱
    @EmployeeID INT    -- 引數(可以有多個),要檢索的員工的ID。
AS
BEGIN    
    SET NOCOUNT ON;   --  用於防止額外的結果集干擾SELECT語句。

    -- 在此處插入儲存過程的語句
    SELECT * 
    FROM Employees 
    WHERE EmployeeID = @EmployeeID;
END
GO

　　>.管理儲存過程

　　　　使用者可以在對應資料庫的 [儲存過程] 目錄下檢視所有儲存過程

　　　　　　>.執行儲存過程

　　　　　　　　儲存過程可以透過EXECUTE或者它的縮寫EXEC命令來執行,比如執行 test 儲存過程可以寫成:

EXEC test;
　　　　　　>.檢視儲存過程

　　　　　　有三種方法可以檢視儲存過程的定義:

　　　　　　　　1.使用SYS.SQL_MODULES

　　　　　　　　2.使用OBJECT_DEFINITION

　　　　　　　　3.使用SP_HELPTEXT

SELECT * FROM sys.sql_modules;
SELECT OBJECT_DEFINITION(661577395) AS sql;
EXEC sp_helptext 'test';

　　　　　　>.修改儲存過程

　　　　　　　　雖然刪除後再重新建立儲存過程可以達到修改儲存過程的目的,但是重建會丟失與該儲存過程相關聯的所有許可權.

　　　　　　　　有兩種方法可以實現對儲存過程的修改,一種是透過選單修改,一種是透過SQL語句修改.

　　　　　　　　1.透過選單修改.如下圖

　　　　　　　　2.使用ALTER PROCEDURE語句實現對儲存過程的修改.
ALTER PROCEDURE [dbo].[test]
    -- 新的儲存過程引數或註釋可在這裡新增
AS
BEGIN
    -- 儲存過程的新邏輯可在這裡新增
    -- 請注意，這裡不需要重新宣告已有的引數
END
GO
　　　　　　>.重新命名儲存過程

　　　　　　　　>.透過選單重新命名

　　　　　　　　>.透過sp_rename SQL語句重新命名

EXEC sp_rename 'dbo.GetEmployeeByID', 'GetEmployeeByEmployeeID';

　　　　　　>.刪除儲存過程

　　　　　　　　>.透過選單刪除.略

　　　　　　　　>.透過DROP PROCEDURE SQL語句刪除　　

DROP PROCEDURE [dbo].[GetEmployeeByEmployeeID];

小例:

CREATE PROCEDURE [dbo].[YourStoredProcedureName]
AS
BEGIN
    DECLARE @truc INT;
    SET @truc = 0; -- 設定預設值，可以根據需要更改

    BEGIN TRY
        BEGIN TRANSACTION;
        -- 這裡是儲存過程的邏輯
        -- 使用條件判斷語句處理事務
        IF @truc = 2
        BEGIN
            ROLLBACK TRANSACTION;    --回滾
            -- 返回值為25
            SELECT 25 AS ReturnValue;    --返回一個值為25
        END
        ELSE IF @truc = 0
        BEGIN
            COMMIT TRANSACTION;     --提交事務
            -- 返回值為0
            SELECT 0 AS ReturnValue;   --返回一個值為0
        END
        ELSE
        BEGIN
            -- 處理其他情況
        END
    END TRY
    BEGIN CATCH
        -- 處理錯誤
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;

        -- 丟擲或記錄錯誤資訊
        THROW;
    END CATCH;
END

相關文章