存儲過程

在資料庫中很多查詢都是大同小異，編寫他們費時費力，将他們儲存起來，以後執行就很友善了，把SQL語句“封裝”起來。

存儲過程的概念

存儲過程是一組SQL語句集，經過編譯存儲，可以”一次編譯，多次執行“。除了第一次調用需要編譯，後面都可以直接執行，執行速度更快，而不是普通SQL語句一樣，每一次執行都要編譯。

提供一種安全機制，如果某使用者滿意特定視圖的使用權限，但是有使用存儲過程的權限，通過執行存儲過程，依舊可以擷取存儲過程中的表。

存儲過程的優點

改善系統性能，一次編譯，多次執行，而普通SQL是每一次都編譯執行
安全機制
重用性，可以反複調用
共享性
減少網絡流量，存儲過程是伺服器上編譯好的T-sql代碼，對一個可能需要幾百行的T-sql操作，在用戶端隻需要通過一條執行語句完成，而不是發送幾百行代碼

存儲過程分類

分為3類：

系統存儲過程

通常以sp_開頭，有sp_helpdb檢視資料庫名稱和大小

所在位置

還有sp_helptext用于小時規則，預設值，觸發器

sp_renamedb重命名資料庫

sp_rename 重命名表，列，使用者定義的類型

sp_helplogins看使用者登入的資料

使用者存儲過程

使用者編寫的存儲過程（主要是的的就是這個部分）

擴充存儲過程

為擴充sqlserver提供的方法，可以動态的加載和執行動态連結庫的函數，以xp_開頭

存儲過程的建立語句

CREATE PROC[EDURE] 存儲過程名

[@參數名    參數類型  =預設值 ]  OUTPUT

WITH  RECOMPILE

FOR  REPLICATION

AS SQL語句組

說明：OUTPUT是輸出參數，

RECOMPILE表示sqlserver不對存儲過程計劃進行高速緩存，每一次重新編譯，

FOR REPLICATION表示存儲過程隻能在複制過程中使用，而且不和WITH RECOMPILE一起使用。

存儲過程執行文法格式

EXEC[UTE] [@狀态值=] 存儲過程名 [@參數名=] 參數值

存儲過程重新編譯

sqlserver強制重新編譯存儲過程3種方法：

系統存儲過程，EXEC sp_recompile 存儲過程名
可以建立的時候使用WITH RECOMPILE
指定WITH RECOMPILE選項，EXEC 存儲過程 WITH RECOMPILE

存儲過程修改

就是把建立語句的CREATE 換成ALTER ，僅此而已。

ALTER PROC[EDURE] 存儲過程名

[@參數名    參數類型  =預設值 ]  OUTPUT

WITH  RECOMPILE

FOR  REPLICATION

AS SQL語句組

存儲過程删除

可以一次删除多個存儲過程

DROP PROCEDURE PROC_3 , PROC_4

建立一個簡單的存儲過程

CREATE PROC 簡單的存儲過程
AS
BEGIN
	SELECT 商品編号 ,商品名稱,銷售價
	FROM 商品表
	WHERE 商品名稱='筆記本'
END
GO

存儲過程中輸入輸出參數在AS之前，申明，局部變量在AS之後，申明。

建立帶參數的存儲過程

CREATE PROC 帶參數的存儲過程
@SPM VARCHAR(20) ='筆記本'
AS
BEGIN
	DECLARE @JG SMALLMONEY
	SELECT @JG=銷售價
	FROM 商品表
	WHERE 商品名稱=@SPM

	RETURN @JG
END
GO

使用帶參數，有傳回值的存儲過程

DECLARE @MAXJG SMALLMONEY
EXEC @MAXJG=帶參數的存儲過程
SELECT @MAXJG AS 最高的實際銷售價格

資料庫的觸發器

觸發器的概念

觸發器是特殊的存儲過程，隻不過它是在操作資料表（增删改）之前或者之後，自動執行的，不能銅鼓名稱來調用。

優點

自動，操作表之後立即激活

實施更為複雜的資料限制

級聯修改資料庫中相關表，自動觸發其他與之相關操作

跟蹤變化，撤銷和復原

傳回自定義錯誤資訊（一般的限制是無法傳回資訊的）

可以調用更多的存儲過程

觸發器專用臨時表

有2個觸發器專用臨時表INSERTED 和DELETED

表存在INSERT觸發器（DDL觸發器），插入資料，系統自動建立一個與表一樣結構的INSERTED臨時表，新的記錄同時，添加到觸發器表和INSERTED中，INSERTED儲存的副本，友善使用者查找目前插入資料。

表存在DELETE觸發器，删除資料，系統自動建立一個DELETED臨時表，用來儲存被删除記錄。

修改表，就是删除一條記錄，然後插入一條記錄，删除記錄添加到DELETED表，新增記錄添加到INSERTED表。

觸發器的建立和觸發

CREATE TRIGGER 觸發器 ON 表名

FOR | AFTER | INSTEAD OF INSERT ,UPDATE , DELETE

AS SQL語句

CREATE TRIGGER 觸發器 ON ALL SERVER | DATABASE

FOR | AFTER 事件種類

AS SQL語句

觸發器的修改

和建立類似，就是把CREATE 改為ALTER

觸發器的删除

DROP TRIGGER 觸發器, 觸發器2

觸發器的啟用與禁用

禁用觸發器

ALTER TABLE 表名

DISABLE TRIGGER 觸發器名

DISABLE  TRIGGER  觸發器名

禁用資料庫級别觸發器

DISABLE TRIGGER 觸發器名  ON  DATABASE

啟用觸發器

ALTER TABLE 表名

ENABLE TRIGGER 觸發器名

ENABLE TRIGGER  觸發器  ON  表名

簡單的觸發器

USE 銷售管理
GO
CREATE TRIGGER tr_insert_mj
ON 買家表 FOR INSERT --指定觸發類型
AS
BEGIN
	PRINT '有新買家插入到買家表'
END
GO

驗證，插入一條資料

INSERT INTO 買家表
VALUES('M05','MARS',12232323,'J01')
GO

看消息，觸發器運作了

建立一個DDL觸發器，當删除資料庫中的表時，就撤銷該操作，并提示：禁止删除資料庫表！

USE 銷售管理
GO
CREATE TRIGGER tr_drop_dll
ON DATABASE
FOR DROP_TABLE
AS
BEGIN
	ROLLBACK TRANSACTION
	PRINT '禁止删除資料表！'
END
GO

驗證一下，删除表

DROP TABLE 買家表
GO

看資訊提示