《SQL Server 2008從入門到精通》--20180716

1.鎖

當多個用戶同時對同一個數據進行修改時會產生并發問題，使用事務就可以解決這個問題。但是為了防止其他用戶修改另一個還沒完成的事務中的數據，就需要在事務中用到鎖。
SQL Server 2008提供了多種鎖模式：排他鎖，共享鎖，更新鎖，意向鎖，鍵范圍鎖，架構鎖和大容量更新鎖。
查詢sys.dm_tran_locks視圖可以快速了解SQL Server 2008內的加鎖情況。

SELECT * FROM sys.dm_tran_locks;

注：關于鎖的知識書中沒細講，將在以后的博客中補充。

2.游標

游標是類似于C語言指針一樣的結構，是一種數據訪問機制，允許用戶訪問單獨的數據行。游標主要由游標結果集和游標位置組成。游標結果集是定義游標的SELECT語句返回行的集合，游標位置是指向這個結果集中某一行的指針。
示例1：用游標檢索出student表中每行記錄
Student表記錄如圖所示

執行下列語句

USE test
DECLARE stu_cursor CURSOR FOR
SELECT * FROM student--聲明student表的游標stu
OPEN stu_cursor--打開游標
FETCH NEXT FROM stu_cursor--移動該記錄指針
WHILE @@FETCH_STATUS=0--@@FETCH_STATUS用于保存FETCH操作的結束信息，=0表示有記錄檢索成功
BEGIN
FETCH NEXT FROM stu_cursor--游標指針移動到下一條記錄
END
CLOSE stu_cursor--關閉游標
DEALLOCATE stu_cursor--釋放游標資源

結果如圖所示

2.1.游標定義的參數LOCAL和GLOBAL

游標定義參數LOCAL表示該游標只能作用于本次批處理或函數或存儲過程。游標定義參數GLOBAL表示該游標可以作用于全局。
執行下列語句

DECLARE stu_cursor CURSOR LOCAL
FOR SELECT * FROM student
GO
OPEN stu_cursor
GO

執行結果如下

語句中，聲明了一個student表的游標stu_cursor，在打開游標時提示游標不存在。因為該游標參數是LOCAL，只能作用于當前批處理語句中，而打開游標語句和聲明語句不在一個批處理中。如果去掉第一個GO，使兩個語句在同一個批處理中，就能順利執行不會報錯。
執行下列語句

DECLARE stu_cursor1 CURSOR GLOBAL
FOR SELECT * FROM student
GO
OPEN stu_cursor1
GO

執行結果：命令已成功完成
和LOCAL參數對比，GOLBAL參數設置游標作用于全局，因此OPEN和DECLARE語句不在同一個批處理中依然可以成功執行。

2.2.游標分為游標變量和游標類型

如下列語句

--語句1
DECLARE stu_cursor CURSOR
FOR SELECT * FROM student--聲明student表的游標名稱為stu_cursor并賦值
GO--語句2
DECLARE @stu_cursor CURSOR--聲明游標類型的變量@stu_cursor
SET @stu_cursor=CURSOR FOR--給該變量賦值
SELECT * FROM student

在語句1中直接聲明了一個游標并賦值，而語句2中聲明了游標類型的變量@stu_cursor，然后給該變量賦值。這兩者是不同的。

2.3.游標參數FORWARD_ONLY和SCROLL

FORWARD_ONLY參數設置游標只能從結果集的開始向結束方向讀取，使用FETCH語句時只能用NEXT，而SCROLL參數設置游標可以從結果集的任意方向，任意位置移動。如下列語句

--語句1，默認FORWARD_ONLY
DECLARE stu_cursor CURSOR LOCAL
FOR SELECT * FROM student
OPEN stu_cursor
FETCH NEXT FROM stu_cursor
GO
--語句2，FORWARD_ONLY參數，FETCH時只能從開始往結束方向
DECLARE stu_cursor CURSOR FORWARD_ONLY LOCAL
FOR SELECT * FROM student
OPEN stu_cursor 
FETCH NEXT FROM stu_cursor
GO
--語句3，SCROLL參數，FETCH時可以從任意位置往任意方向
DECLARE stu_cursor CURSOR SCROLL LOCAL
FOR SELECT * FROM student
OPEN stu_cursor
FETCH LAST FROM stu_cursor
GO

2.4.游標的簡單應用

示例2：將student表中stu_enter_score大于600分的學生都減去100分
Student表中的數據如圖所示

執行下列語句

--游標的簡單應用
DECLARE stu_cursor CURSOR FORWARD_ONLY LOCAL
FOR SELECT stu_no,stu_enter_score FROM student
OPEN stu_cursor
DECLARE @score INT
DECLARE @stu_no VARCHAR(8)
FETCH NEXT FROM stu_cursor INTO @stu_no,@score--把游標值賦值給變量@score
WHILE @@FETCH_STATUS=0 
BEGIN
IF @score>=600
BEGIN
UPDATE student
SET stu_enter_score=@score-100 WHERE stu_no=@stu_no 
END
FETCH NEXT FROM stu_cursor INTO @stu_no,@score--把游標值賦值給變量@score
END
CLOSE stu_cursor
DEALLOCATE stu_cursor
GO

結果如圖所示

3.存儲過程

存儲過程是一組用于完成特定功能的語句集，經過編譯后存儲在數據庫中。在SQL Server 2008中，既可以用T-SQL編寫存儲過程，也可以用CLR編寫存儲過程。

3.1.用戶定義的存儲過程

該種存儲過程是指封裝了可重用代碼的模塊或者歷程，有2種類型：T-SQL存儲過程和CLR存儲過程。
T-SQL存儲過程是指保存的T-SQL語句集合
CLR存儲過程是指對Microsoft .NET Framework公共語言運行時（CLR）方法的引用

3.2.擴展存儲過程

擴展存儲過程是指可以動態加載和運行的DLL，允許使用編程語言（如C語言）創建自己的外部例程。擴展存儲過程直接在SQL Server 2008的實例的地址空間中運行，可以使用SQL Server擴展存儲過程API完成編程。

3.3.系統存儲過程

系統存儲過程是指存儲在源數據庫中，以sp開頭的存儲過程，出現在每個系統定義數據庫和用戶定義數據庫的sys架構中。

3.3.1.創建存儲過程規則

在設計和創建存儲過程時，應該滿足一定的約束和規則。

• CREATE PROCEDURE定義自身可以包括任意數量和類型的SQL語句，但下表中的語句除外。不能在存儲過程的任何位置使用這些語句。
• 可以引用在統一存儲過程中創建的對象，只要引用時已創建了該對象
• 可以在存儲過程內引用臨時表
• 如果在存儲過程中創建了本地臨時表，該臨時表僅為該存儲過程而存在，退出該存儲過程后，該臨時表會消失
• 如果執行的存儲過程調用了另一個存儲過程，被調用的存儲過程可以訪問第一個存儲過程的所有對象，包括臨時表
• 如果執行對遠程SQL Server 2008實例進行更改的遠程存儲過程，這些更改將不能被回滾。遠程存儲過程不參與事務處理
• 存儲過程中的參數的最大數量為2100
• 存儲過程中的局部變量的最大數量僅受可用內存的限制
• 根據可用內存的不同，存儲過程最大可達128MB

語句	語句	語句
CREATE AGGREGATE	CREATE RULE	CREATE DEFAULT
CREATE SCHEMA	CREATE(ALTER) FUNCTION	CREATE(ALTER) TRIGGER
CREATE(ALTER) PROCEDURE	CREATE(ALTER) VIEW	SET PARSEONLY
SET SHOWPLAN_ALL	SET SHOWPLAN_TEXT	SET SHOWPLAN_XML
USE database_name

3.3.2.限定存儲過程內的名稱

在存儲過程內，如果用于語句的對象沒有限定架構，則架構將默認為該存儲過程的架構。如果創建該存儲過程的用戶沒有限定INSERT,SELECT,UPDATE或DELETE語句中引用的表名或試圖名，則默認情況下通過該存儲過程進行的訪問將受到該過程創建者權限的限制。如果有其他用戶要使用存儲過程，則所有用于數據定義語言（DDL）的語句（如CREATE,ALTER,EXECUTE,DROP,DBCC或動態SQL語句）的對象名應該用該對象架構的名稱來限定。

3.3.3.加密存儲過程的定義

如果要創建存儲過程并確保其他用戶無法查看該存儲過程的定義，則可以使用WITH ENCRYPTION，這樣，過程定義將以不可讀的形式存儲。

3.3.4.SET語句選項

當創建或者更改T-SQL存儲過程后，數據庫引擎將保存SET QUOTED_IDENTIFIER和SET ANSI_NULLS的設置，執行存儲過程時將使用這些原始設置而忽略任何客戶端會話的ET QUOTED_IDENTIFIER和SET ANSI_NULLS設置。其他SET選項在創建或更改存儲過程后不保存。

3.4.使用存儲過程

3.4.1.創建存儲過程

示例3：將示例2用存儲過程實現
Student表的數據如圖所示

執行下列語句

CREATE PROCEDURE alter_data
@a int--參數
AS
BEGIN
DECLARE stu_cursor CURSOR FORWARD_ONLY LOCAL
FOR SELECT stu_no,stu_enter_score FROM student
OPEN stu_cursor
DECLARE @score INT
DECLARE @stu_no VARCHAR(8)
FETCH NEXT FROM stu_cursor INTO @stu_no,@score--把游標值賦值給變量@score
WHILE @@FETCH_STATUS=0 
BEGIN
IF @score>=@a
BEGIN
UPDATE student
SET stu_enter_score=@score-100 WHERE stu_no=@stu_no 
END
FETCH NEXT FROM stu_cursor INTO @stu_no,@score--把游標值賦值給變量@score
END
CLOSE stu_cursor
DEALLOCATE stu_cursor
END
GO
EXEC dbo.alter_data '600'

結果如圖所示

3.4.2.查看存儲過程

可以通過使用系統存儲過程或者目錄視圖查看存儲過程的定義

3.4.2.1.圖形化界面

如下圖

3.4.2.2.系統存儲過程sp_helptext查看存儲過程定義

執行下列語句

EXEC sp_helptext 'alter_data'

結果如圖所示

3.4.2.3.系統存儲過程sp_depends查看存儲過程相關信息

執行下列語句

EXEC sp_depends 'alter_data'

結果如圖所示

3.4.2.4.目錄視圖查看存儲過程

執行下列語句

SELECT * FROM sys.procedures

結果如圖所示

3.4.3.修改存儲過程

用ALTER PROCEDURE語句修改存儲過程，只需將上面示例中的CREATE修改成ALTER運行就行了。

3.4.4.刪除存儲過程

執行下列語句刪除存儲過程

DROP PROCEDURE alter_data