1.鎖

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

SELECT * FROM sys.dm_tran_locks;

注：關(guān)于鎖的知識書中沒細(xì)講，將在以后的博客中補(bǔ)充。

2.游標(biāo)

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

執(zhí)行下列語句

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

結(jié)果如圖所示

2.1.游標(biāo)定義的參數(shù)LOCAL和GLOBAL

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

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

執(zhí)行結(jié)果如下

語句中，聲明了一個(gè)student表的游標(biāo)stu_cursor，在打開游標(biāo)時(shí)提示游標(biāo)不存在。因?yàn)樵撚螛?biāo)參數(shù)是LOCAL，只能作用于當(dāng)前批處理語句中，而打開游標(biāo)語句和聲明語句不在一個(gè)批處理中。如果去掉第一個(gè)GO，使兩個(gè)語句在同一個(gè)批處理中，就能順利執(zhí)行不會報(bào)錯(cuò)。
執(zhí)行下列語句

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

執(zhí)行結(jié)果：命令已成功完成
和LOCAL參數(shù)對比，GOLBAL參數(shù)設(shè)置游標(biāo)作用于全局，因此OPEN和DECLARE語句不在同一個(gè)批處理中依然可以成功執(zhí)行。

2.2.游標(biāo)分為游標(biāo)變量和游標(biāo)類型

如下列語句

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

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

2.3.游標(biāo)參數(shù)FORWARD_ONLY和SCROLL

FORWARD_ONLY參數(shù)設(shè)置游標(biāo)只能從結(jié)果集的開始向結(jié)束方向讀取，使用FETCH語句時(shí)只能用NEXT，而SCROLL參數(shù)設(shè)置游標(biāo)可以從結(jié)果集的任意方向，任意位置移動(dòng)。如下列語句

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

2.4.游標(biāo)的簡單應(yīng)用

示例2：將student表中stu_enter_score大于600分的學(xué)生都減去100分
Student表中的數(shù)據(jù)如圖所示

執(zhí)行下列語句

--游標(biāo)的簡單應(yīng)用 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--把游標(biāo)值賦值給變量@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--把游標(biāo)值賦值給變量@score END CLOSE stu_cursor DEALLOCATE stu_cursor GO

結(jié)果如圖所示

3.存儲過程

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

3.1.用戶定義的存儲過程

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

3.2.擴(kuò)展存儲過程

擴(kuò)展存儲過程是指可以動(dòng)態(tài)加載和運(yùn)行的DLL，允許使用編程語言（如C語言）創(chuàng)建自己的外部例程。擴(kuò)展存儲過程直接在SQL Server 2008的實(shí)例的地址空間中運(yùn)行，可以使用SQL Server擴(kuò)展存儲過程API完成編程。

3.3.系統(tǒng)存儲過程

系統(tǒng)存儲過程是指存儲在源數(shù)據(jù)庫中，以sp開頭的存儲過程，出現(xiàn)在每個(gè)系統(tǒng)定義數(shù)據(jù)庫和用戶定義數(shù)據(jù)庫的sys架構(gòu)中。

3.3.1.創(chuàng)建存儲過程規(guī)則

在設(shè)計(jì)和創(chuàng)建存儲過程時(shí)，應(yīng)該滿足一定的約束和規(guī)則。

• CREATE PROCEDURE定義自身可以包括任意數(shù)量和類型的SQL語句，但下表中的語句除外。不能在存儲過程的任何位置使用這些語句。
• 可以引用在統(tǒng)一存儲過程中創(chuàng)建的對象，只要引用時(shí)已創(chuàng)建了該對象
• 可以在存儲過程內(nèi)引用臨時(shí)表
• 如果在存儲過程中創(chuàng)建了本地臨時(shí)表，該臨時(shí)表僅為該存儲過程而存在，退出該存儲過程后，該臨時(shí)表會消失
• 如果執(zhí)行的存儲過程調(diào)用了另一個(gè)存儲過程，被調(diào)用的存儲過程可以訪問第一個(gè)存儲過程的所有對象，包括臨時(shí)表
• 如果執(zhí)行對遠(yuǎn)程SQL Server 2008實(shí)例進(jìn)行更改的遠(yuǎn)程存儲過程，這些更改將不能被回滾。遠(yuǎn)程存儲過程不參與事務(wù)處理
• 存儲過程中的參數(shù)的最大數(shù)量為2100
• 存儲過程中的局部變量的最大數(shù)量僅受可用內(nèi)存的限制
• 根據(jù)可用內(nèi)存的不同，存儲過程最大可達(dá)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.限定存儲過程內(nèi)的名稱

在存儲過程內(nèi)，如果用于語句的對象沒有限定架構(gòu)，則架構(gòu)將默認(rèn)為該存儲過程的架構(gòu)。如果創(chuàng)建該存儲過程的用戶沒有限定INSERT,SELECT,UPDATE或DELETE語句中引用的表名或試圖名，則默認(rèn)情況下通過該存儲過程進(jìn)行的訪問將受到該過程創(chuàng)建者權(quán)限的限制。如果有其他用戶要使用存儲過程，則所有用于數(shù)據(jù)定義語言（DDL）的語句（如CREATE,ALTER,EXECUTE,DROP,DBCC或動(dòng)態(tài)SQL語句）的對象名應(yīng)該用該對象架構(gòu)的名稱來限定。

3.3.3.加密存儲過程的定義

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

3.3.4.SET語句選項(xiàng)

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

3.4.使用存儲過程

3.4.1.創(chuàng)建存儲過程

示例3：將示例2用存儲過程實(shí)現(xiàn)
Student表的數(shù)據(jù)如圖所示

執(zhí)行下列語句

CREATE PROCEDURE alter_data @a int--參數(shù) 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--把游標(biāo)值賦值給變量@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--把游標(biāo)值賦值給變量@score END CLOSE stu_cursor DEALLOCATE stu_cursor END GO EXEC dbo.alter_data '600'

結(jié)果如圖所示

3.4.2.查看存儲過程

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

3.4.2.1.圖形化界面

如下圖

3.4.2.2.系統(tǒng)存儲過程sp_helptext查看存儲過程定義

執(zhí)行下列語句

EXEC sp_helptext 'alter_data'

結(jié)果如圖所示

3.4.2.3.系統(tǒng)存儲過程sp_depends查看存儲過程相關(guān)信息

執(zhí)行下列語句

EXEC sp_depends 'alter_data'

結(jié)果如圖所示

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

執(zhí)行下列語句

SELECT * FROM sys.procedures

結(jié)果如圖所示

3.4.3.修改存儲過程

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

3.4.4.刪除存儲過程

執(zhí)行下列語句刪除存儲過程

DROP PROCEDURE alter_data

轉(zhuǎn)載于:https://www.cnblogs.com/kukubear0/p/9319361.html

創(chuàng)作挑戰(zhàn)賽新人創(chuàng)作獎(jiǎng)勵(lì)來咯，堅(jiān)持創(chuàng)作打卡瓜分現(xiàn)金大獎(jiǎng)

總結(jié)

以上是生活随笔為你收集整理的《SQL Server 2008从入门到精通》--20180716的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。