萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 存儲過程完整指南:創建、參數、輸出、加密、游標、分頁、重編譯、權限

存儲過程優勢

  • **預編譯計劃緩存**:首次優化後重用,減少編譯開銷。
  • **減少網絡流量**:調用只傳過程名+參數,而非大段 SQL。
  • **模塊化復用**:邏輯封裝,應用層解耦。
  • **安全邊界**:授權 EXECUTE 權限而非表直接權限,防注入、最小權限。

系統存儲過程速查

EXEC sp_databases;          -- 列庫
EXEC sp_tables;             -- 列表
EXEC sp_columns 'Student';  -- 列表結構
EXEC sp_helpindex 'Student';-- 索引
EXEC sp_helpconstraint 'Student'; -- 約束
EXEC sp_stored_procedures;  -- 所有存儲過程
EXEC sp_helptext 'sp_who';  -- 查看定義
EXEC sp_rename 'Student', 'StuInfo';          -- 改表名
EXEC sp_rename 'StuInfo.Name', 'StuName', 'COLUMN'; -- 改列名
EXEC sp_renamedb 'OldDB', 'NewDB';            -- 改库名

用戶自定義存儲過程模式

1. 無參數

CREATE PROC usp_GetAllStudents AS SELECT * FROM Student;

2. 輸入參數 + 默認值

CREATE PROC usp_GetById @Id INT = 0 AS SELECT * FROM Student WHERE Id = @Id;

3. 輸出參數 (OUTPUT)

CREATE PROC usp_GetNameAge @Id INT, @Name VARCHAR(20) OUTPUT, @Age INT OUTPUT
AS SELECT @Name = Name, @Age = Age FROM Student WHERE Id = @Id;

-- 調用
DECLARE @N VARCHAR(20), @A INT;
EXEC usp_GetNameAge 1, @N OUT, @A OUT;
SELECT @N, @A;

4. 通配符參數

CREATE PROC usp_Search @Name VARCHAR(20) = '%' AS
SELECT * FROM Student WHERE Name LIKE @Name;

5. WITH RECOMPILE(不緩存計劃)

CREATE PROC usp_VolatileData WITH RECOMPILE AS SELECT * FROM BigTable WHERE DateCol > DATEADD(day,-1,GETDATE());

適用:參數嗅探導致計劃不穩、臨時表統計變化大、極低頻執行。

6. WITH ENCRYPTION(隱藏定義)

CREATE PROC usp_Secret WITH ENCRYPTION AS SELECT * FROM Payroll;

**不可逆**,部署前必備份腳本。sp_helptext 返回加密提示。

7. 游標輸出參數

CREATE PROC usp_CursorOut @Cur CURSOR VARYING OUTPUT AS
SET @Cur = CURSOR FORWARD_ONLY STATIC FOR SELECT Id, Name FROM Student;
OPEN @Cur;

-- 調用
DECLARE @C CURSOR, @Id INT, @Name VARCHAR(20);
EXEC usp_CursorOut @Cur = @C OUTPUT;
FETCH NEXT FROM @C INTO @Id, @Name;
WHILE @@FETCH_STATUS = 0 BEGIN PRINT @Id; FETCH NEXT FROM @C INTO @Id, @Name; END
CLOSE @C; DEALLOCATE @C;

8. 分頁存儲過程 (ROW_NUMBER)

CREATE PROC usp_Page @PageSize INT, @PageNum INT AS
SELECT * FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY Id) AS RN, * FROM Product
) T WHERE RN BETWEEN (@PageNum-1)*@PageSize+1 AND @PageNum*@PageSize;

修改與刪除

ALTER PROC usp_GetById @Id INT AS SELECT * FROM Student WHERE Id = @Id;
DROP PROC usp_GetById;

查詢所有用戶存儲過程

SELECT name FROM sys.objects WHERE type = 'P' AND is_ms_shipped = 0;
copyright © 萬盛學電腦網 all rights reserved