鎖的定義，游標的新建和應用，存儲過程的新建，查看，修改和刪除 ...
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
GO2.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
