第七章-SQL-Server數(shù)據(jù)庫編程-《數(shù)據(jù)庫系統(tǒng)原理及應用》課件

7.1

T-SQL語言變量局部變量：變量名前加1個@符號全局變量：變量名前加2個@@符號。如:@@ERROR：當事務成功時為0，否則為最近一次的錯誤號@@ROWCOUNT：返回受上一語句影響的行數(shù)@@FETCH_STATUS：返回最近的FETCH語句執(zhí)行后的游標狀態(tài)變量的聲明與賦值聲明變量的語法：

DECLARE<@variableName><datatype>[,<@variableName><datatype>…]單個變量賦值的語法：SET<@variableName>=<expr>變量列表賦值(或顯示表達式的值)的語法：

SELECT<@variableName>[=<expr

|

columnName>][,<@variableName>[=<expr

|

columnName>]…]7.1

T-SQL語言運算符算術運算符：+，-，*，/，%(取余)比較運算符：>，>=，<，<=，=，<>，!=邏輯運算符：AND，OR，NOT位運算符：&(按位與)，|(按位或)，~(按位非)，^(按位異或)字符串連接運算符：+賦值語句：SELECT：一次可賦值多個變量，或顯示多個表達式的值SET：一次僅能給一個變量賦值函數(shù)：數(shù)學函數(shù)、字符串函數(shù)、日期和時間函數(shù)、聚合函數(shù)和系統(tǒng)函數(shù)等數(shù)學函數(shù)：絕對值函數(shù)abs、隨機數(shù)函數(shù)rand、四舍五入函數(shù)round、上取整函數(shù)ceiling、下取整函數(shù)floor、指數(shù)函數(shù)exp、平方根函數(shù)sqrt等7.1

T-SQL語言函數(shù)：數(shù)學函數(shù)、字符串函數(shù)、日期和時間函數(shù)、聚合函數(shù)和系統(tǒng)函數(shù)等字符串函數(shù)：表7-27.1

T-SQL語言函數(shù)：數(shù)學函數(shù)、字符串函數(shù)、日期和時間函數(shù)、聚合函數(shù)和系統(tǒng)函數(shù)等日期和時間函數(shù)：表7-37.1

T-SQL語言函數(shù)：數(shù)學函數(shù)、字符串函數(shù)、日期和時間函數(shù)、聚合函數(shù)和系統(tǒng)函數(shù)等系統(tǒng)函數(shù)：表7-47.1

T-SQL語言函數(shù)：數(shù)學函數(shù)、字符串函數(shù)、日期和時間函數(shù)、聚合函數(shù)和系統(tǒng)函數(shù)等系統(tǒng)函數(shù)：convert(data_type

[(length)],expr[,style])data_type：系統(tǒng)所提供的數(shù)據(jù)類型length：字符數(shù)據(jù)類型的可選參數(shù)，用于控制字符串的長度expr：任何有效的SQLServe表達式style：日期格式樣式(詳見表7-5)。例如，經(jīng)常將datetime數(shù)據(jù)或數(shù)值數(shù)據(jù)表達式expr轉換為字符數(shù)據(jù)類型data_type，然后可用于字符串的連接輸出7.1

T-SQL語言流程控制語句：表7-87.1

T-SQL語言程序實例：[例]在ScoreDB數(shù)據(jù)庫中，查詢Score表中的最高成績，如果最高成績大于95分，則顯示“verygood!”。7.1

T-SQL語言程序實例：[例]在ScoreDB數(shù)據(jù)庫中，查詢Score表中的最高成績，如果最高成績大于95分，則顯示“verygood!”。[例]聲明兩個局部變量@sno和@score，用于接受SELECT語句查詢返回的結果，并顯示其結果。7.1

T-SQL語言程序實例：[例7.12]在學生表Student中，如果有蒙古族學生，則顯示：存在蒙古族的學生。7.1

T-SQL語言程序實例：[例7.12]在學生表Student中，如果有蒙古族學生，則顯示：存在蒙古族的學生。[例]

列示成績表Score中的所有選課記錄，要求根據(jù)學期號termNo的不同取值分別顯示開課時間為xx年下半年、xx年上半年、xx年暑期小學期，根據(jù)成績score的不同取值分別顯示等級為優(yōu)良(80分及以上)、合格和不及格(小于60分)。如'152'顯示為“16年上半年”。目錄T-SQL語言游

標存儲過程觸

發(fā)

器7.17.37.27.47.2

游標對SELECT語句的結果集進行逐行處理，需使用游標。游標(cursor)是系統(tǒng)為用戶開設的一個數(shù)據(jù)緩沖區(qū)，用于存放SQL語句的執(zhí)行結果(元組集合)。每個游標都有一個名字，用戶可以用SQL提供的語句從游標中逐一獲取元組(記錄)，并賦給主變量，交由主語言進一步處理?？蓪τ螛说漠斍拔恢眠M行更新、查詢和刪除，使用游標需要經(jīng)歷5個步驟：定義游標：DECLARE打開游標：OPEN逐行提取游標集中的行：FETCH關閉游標：CLOSE釋放游標：DEALLOCATE定義游標語法為：DECLARE

<cursorName>

CURSOR

FOR

<SQL-Statements>[FOR{READONLY|UPDATE[OF<columnName_list>]}]

在使用游標之前，必須先定義游標。其中：<cursorName>：所定義游標的名稱；<SQL-Statements>：游標要實現(xiàn)的功能程序，即SQL子查詢；<columnName_list>：屬性列名列表；[FOR{READONLY|UPDATE[OF

<columnName_list>]}]：READONLY表示當前游標集中的元組僅可查詢，不能修改;UPDATE

[OF

<columnName_list>]表示可對當前游標集中的元組進行更新操作。如果有OF<columnName_list>，表示僅可以對游標集中指定的屬性列進行修改操作；缺省為UPDATE7.2.2游標的定義與使用

打開游標語法為：

OPEN

<curserName>游標定義后，如果要使用游標，必須要先打開游標。打開游標操作表示：系統(tǒng)按照游標的定義從數(shù)據(jù)庫中將數(shù)據(jù)檢索出來，放在內存的游標集中(如果內存不夠，會放在臨時數(shù)據(jù)庫中)為游標集指定一個游標(相當于一個指針)，該游標指向游標集中的第1個元組7.2.2游標的定義與使用

獲取當前游標值：即獲取當前游標所指向元組的值，語法是FETCH

<curserName>

INTO

<@variableName_list>執(zhí)行一次該SQL語句，系統(tǒng)將當前游標所指向的元組屬性值放到變量中，然后游標自動下移一個元組。當前游標所指向元組的每個屬性值必須分別用一個變量來接收，即變量個數(shù)、數(shù)據(jù)類型必須與定義游標中的SELECT子句所定義的屬性(或表達式)個數(shù)、數(shù)據(jù)類型相一致。當游標移至尾部，不可再讀取游標，必須關閉游標，然后重新打開游標。通過檢查全局變量@@FETCH_STATUS來判斷是否已讀完游標集中所有行(元組)。

@@FETCH_STATUS的值有：0：FETCH語句成功，表示已經(jīng)從游標集中獲取了元組值1：FETCH語句失敗或此行不在結果集中2：被提取的行不存在7.2.2游標的定義與使用

關閉游標：游標不使用了，必須關閉，其語法為：

CLOSE<curserName>釋放游標(集)所占用的空間：關閉游標并沒有釋放游標所占用的內存和外存空間，必須釋放游標，其語法為：

DEALLOCATE

<curserName>7.2.2游標的定義與使用

[例7.18]創(chuàng)建一個游標，逐行顯示選修了《計算機原理》課程的學生姓名、相應成績和該課程的平均分。分析：①選修《計算機原理》課程的同學可能不止一個，需要使用游標查詢選修該門課程的學生姓名和相應的選課成績。

定義游標為：DECLARE

myCur

CURSOR

FOR

SELECTstudentName,score,termNoFROMStudenta,Courseb,ScorecWHEREa.studentNo=c.studentNoANDb.courseNo=c.courseNoAND

courseName='計算機原理‘

ORDERBYstudentName7.2.2游標的定義與使用

②要獲得該課程的平均分，必須首先計算選課人數(shù)和總分聲明計數(shù)器和累加器變量@countScore、@sumScore，賦初值為0DECLARE@countScore

smallint,@sumScore

int

SET

@countScore=0

SET@sumScore=0③聲明3個變量@sName、@score和@termNo，用于接收游標集中當前游標中的學生姓名、選課成績和選課學期

DECLARE@sName

varchar(20),@score

tinyint,@termNo

char(3)④由于FETCH命令每次僅從游標集中提取一條記錄，必須通過一個循環(huán)來重復提取，直到游標集中的全部記錄被提取全局變量@@FETCH_STATUS用于判斷是否正確地從游標集中提取到了記錄；@@FETCH_STATUS=0表示已經(jīng)正確提取到了游標記錄；循環(huán)語句為：

WHILE(@@FETCH_STATUS=0

)7.2.2游標的定義與使用

⑤在循環(huán)體內：首先，顯示提取到的學生姓名、選課成績和選課學期，使用語句：PRINT

convert(char(10),@sName)

+convert(char(10),@score)+convert(char(10),@termNo)其次，計數(shù)器@countScore進行計數(shù)，并將提取到的成績累加到變量@sumScore中。語句為：SET@sumScore=@sumScore+@score

--計算總分SET@countScore=@countScore+1--計算選課人數(shù)提取當前游標所指向元組，并下移(即使其指向游標集中下一元組)：

FETCH

myCur

INTO

@sName,@score,@termNo重復⑤，直到全部游標記錄處理完畢，退出循環(huán)。⑥處理完全部游標記錄后：關閉和釋放游標對計數(shù)器@countScore進行判斷：如果為0，表示沒有同學選修，其平均分為0；否則，平均分等于總分除以選課人數(shù)。7.2.2游標的定義與使用

⑦程序如下：/*聲明變量及賦初值

*/DECLARE@sName

varchar(20),@score

tinyint,@termNo

char(3)DECLARE@countScore

smallint,@sumScore

intSET

@countScore=0SET@sumScore=0--定義游標DECLARE

myCur

CURSOR

FOR

SELECTstudentName,score,termNoFROMStudenta,Courseb,ScorecWHEREa.studentNo=c.studentNoANDb.courseNo=c.courseNoAND

courseName='計算機原理‘

ORDERBYstudentNameOPENmyCur

--打開游標，游標指向游標集(查詢結果集)的第1個元組PRINT

convert(char(10),'學生姓名')+convert(char(10),'課程成績')+convert(char(10),'選課學期')PRINT

replicate(‘-’,30)--輸出表頭信息7.2.2游標的定義與使用

--獲取當前游標的值(即第1個元組值)放到變量@sName、@score和@termNo中FETCH

myCur

INTO

@sName,@score,@termNo--獲取第1個元組值,游標下移WHILE(@@FETCH_STATUS=0

)--循環(huán)處理游標集中的每一個元組BEGIN--

顯示變量@sName、@score和@termNo中的值PRINT

convert(char(10),@sName)

+convert(char(10),@score)+convert(char(10),@termNo)SET@sumScore=@sumScore+@score

--計算總分SET@countScore=@countScore+1--計算選課人數(shù)FETCH

myCur

INTO

@sName,@score,@termNo

--獲取當前游標所指向元組值,游標下移ENDPRINT

replicate(‘-’,30)--輸出表格底線PRINT‘課程平均分’

--輸出選修《計算機原理》課程的所有學生的平均分IF

@countScore>0

PRINT

@sumScore/@countScoreELSE

--選修人數(shù)為0，即沒有學生選修《計算機原理》課程

PRINT

0.00CLOSEmyCur

--關閉游標DEALLOCATEmyCur

--釋放游標7.2.2游標的定義與使用

游標可以放在觸發(fā)器和存儲過程中使用可以對當前游標集中的元組執(zhí)行刪除和更新操作刪除游標集中的當前元組(即游標所指向的元組)

DELETEFROM

<tableName>

WHERE

CURRENTOF<curserName>從游標集中刪除當前元組后，游標定位于被刪除元組的下一行，但還需要用FETCH語句提取該行的值。更新游標集中的當前元組(即游標所指向的元組)

UPDATE

<tableName>SET<columnName>=<expr>[,<columnName>=<expr>...]WHERE

CURRENT

OF

<curserName>7.2.3當前游標集的修改與刪除

[例7.19]將選修了《高等數(shù)學》課程且成績不及格的學生選課記錄顯示出來，并從數(shù)據(jù)庫中刪除該選課記錄。/*聲明變量及賦初值*/DECLARE

@sName

varchar(20),@score

tinyint--

定義游標DECLARE

myCur

CURSOR

FORSELECTstudentName,scoreFROMStudenta,Courseb,ScorecWHEREa.studentNo=c.studentNoANDb.courseNo=c.courseNoANDcourseName='高等數(shù)學'ANDscore<60OPEN

myCur--打開游標7.2.3當前游標集的修改與刪除

--獲取當前游標(此時指向游標集中第1個元組)的值放入變量@sName和@scoreFETCH

myCur

INTO

@sName,@score

--獲取第1個元組值，游標下移WHILE(

@@FETCH_STATUS

=0

)

--循環(huán)處理BEGIN--顯示變量@sName和@score中的值

SELECT@sName

學生姓名,@score

課程成績

--刪除當前游標獲取值的元組，并且游標指向下一元組

DELETE

FROM

Score

WHERE

CURRENT

OF

myCur

FETCH

myCur

INTO

@sName,@score

--獲取當前游標所指向元組值,游標下移ENDCLOSE

myCur

--關閉游標DEALLOCATEmyCur

--釋放游標注意：對游標當前位置的記錄進行修改和刪除，最終都將

轉化為對基本表的更新。7.2.3當前游標集的修改與刪除

目錄T-SQL語言游標存儲過程觸

發(fā)

器7.17.37.27.47.3存儲過程存儲過程是為了完成特定功能匯集而成的一組命名了的SQL語句集合該集合編譯后存放在數(shù)據(jù)庫中，可根據(jù)實際情況重新編譯；存儲過程可直接在服務器端運行，也可在客戶端遠程調用運行，遠程調用時存儲過程還是在服務器端運行。使用存儲過程具有如下優(yōu)點：將業(yè)務操作封裝可為復雜的業(yè)務操作編寫存儲過程，放在數(shù)據(jù)庫中；用戶可調用存儲過程執(zhí)行，而業(yè)務操作對用戶是不可見的；若存儲過程僅修改了執(zhí)行體，沒有修改接口(即調用參數(shù))，則用戶程序不需要修改，達到業(yè)務封裝的效果。便于事務管理事務控制可以用在存儲過程中；用戶可依據(jù)業(yè)務的性質定義事務，并對事務進行相應級別的操作。7.3存儲過程

實現(xiàn)一定程度的安全性保護存儲過程存放在數(shù)據(jù)庫中，且在服務器端運行；對于不允許用戶直接操作的基本表或視圖，可通過調用存儲過程來間接地訪問這些基本表或視圖，達到一定程度的安全性；這種安全性緣于用戶對存儲過程只有執(zhí)行權限，沒有查看權限；擁有存儲過程的執(zhí)行權限，自動獲取了存儲過程中對相應基本表或視圖的操作權限；這些操作權限僅能通過執(zhí)行存儲過程來實現(xiàn)，一旦脫離存儲過程，也就失去了相應操作權限。注意：對存儲過程只需授予執(zhí)行權限，不需授予基本表或視圖的操作權限。特別適合統(tǒng)計和查詢操作一般統(tǒng)計和查詢，尤其是期末統(tǒng)計，往往涉及數(shù)據(jù)量大、表多，若在客戶端實現(xiàn)，數(shù)據(jù)流量和網(wǎng)絡通信量較大；很多情況下，管理信息系統(tǒng)的設計者，將復雜的查詢和統(tǒng)計用存儲過程來實現(xiàn)，免去客戶端的大量編程。7.3存儲過程

減少網(wǎng)絡通信量存儲過程僅在服務器端執(zhí)行，客戶端只接收結果；由于存儲過程與數(shù)據(jù)一般在同一個服務器中，可減少大量的網(wǎng)絡通信量。使用存儲過程前，首先要創(chuàng)建存儲過程。可對存儲過程進行修改和刪除。創(chuàng)建存儲過程后，必須對存儲過程授予執(zhí)行EXECUTE的權限，否則該存儲過程僅可以供創(chuàng)建者執(zhí)行。7.3.2創(chuàng)建存儲過程語法：CREATE

PROCEDURE

<procedureName>

[(<@parameterName>

<datatype>[=<defaultValue>][OUTPUT][,<@parameterName>

<datatype>[=<defaultValue>][OUTPUT]])]AS

<SQL-Statements>其中：<procedureName>：存儲過程的名稱，必須符合標識符規(guī)則，且在同一個數(shù)據(jù)庫中唯一；<@parameterName>：參數(shù)名，存儲過程可不帶參數(shù)，形式參數(shù)是變量，但實際參數(shù)可以是變量、常量和表達式；OUTPUT：說明該參數(shù)是輸出參數(shù)，被調用者獲取使用。缺省時表示是輸入?yún)?shù)。7.3.2創(chuàng)建存儲過程如果存儲過程的輸出參數(shù)取集合值，則該輸出參數(shù)不在存儲過程的參數(shù)中定義，而是在存儲過程中定義一個臨時表來存儲該集合值。臨時表的表名前加一個#符號，如#myTemp在存儲過程尾部，使用語句：

SELECT*FROM#myTemp

將結果集合返回給調用者。存儲過程結束后，臨時表自動被刪除。注意：用戶定義的存儲過程只能在當前數(shù)據(jù)庫中創(chuàng)建；一個存儲過程最大不能超過128MB。若超過128MB，可將超出的部分編寫為另一個存儲過程，然后在存儲過程中調用。7.3.2創(chuàng)建存儲過程[例7.20]

輸入某個同學的學號，統(tǒng)計該同學的平均分。CREATE

PROCEDURE

proStudentByNo1(@sNo

char(7))ASSELECTa.studentNo,studentName,avg(score)FROMStudenta,ScorebWHEREa.studentNo=b.studentNoANDa.studentNo=@sNoGROUPBYa.studentNo,studentName7.3.2創(chuàng)建存儲過程[例7.22]輸入某同學的學號，使用游標統(tǒng)計該同學的平均分，并返回平均分，同時逐行顯示該同學的姓名、選課名稱和選課成績。CREATE

PROCEDURE

proStudentAvg(@sNo

char(7),

@avg

numeric(6,2)OUTPUT)ASBEGIN

DECLARE

@sName

varchar(20),@cName

varchar(20)DECLARE@grade

tinyint,@sum

int,@count

tinyintSELECT@sum=0,@count=0

--

定義、打開、獲取游標

DECLAREcurGrade

CURSOR

FOR

SELECTstudentName,courseName,scoreFROMScorea,Studentb,Coursec

WHEREb.studentNo=@sNo

ANDa.studentNo=b.studentNoANDa.courseNo=c.courseNo7.3.2創(chuàng)建存儲過程OPEN

curGrade

FETCH

curGrade

INTO

@sName,@cName,@grade

WHILE(@@FETCH_STATUS=0)

BEGIN--業(yè)務處理

SELECT

@sName,@cName,@grade--逐行顯示該同學的選課信息SET@sum=@sum+@gradeSET@count=@count+1

FETCH

curGrade

INTO

@sName,@cName,@gradeEND

CLOSEcurGradeDEALLOCATEcurGrade

IF

@count=0

SELECT@avg=0

ELSE

SELECT

@avg=@sum/@countEND7.3.2創(chuàng)建存儲過程本例使用了SELECT語句來顯示變量的值，即

SELECT

@sName,@cName,@grade由于存儲過程僅在服務器端執(zhí)行，其顯示的內容只在服務器端出現(xiàn)，并不返回給客戶端，這樣的輸出結果是沒有價值的。顯示內容在調試存儲過程時有作用，一旦存儲過程調試正確，使用存儲過程的修改命令將存儲過程中的顯示命令刪除。SQLServer數(shù)據(jù)庫還可以返回一個數(shù)據(jù)集合該數(shù)據(jù)集合在客戶端的程序中可以被網(wǎng)格類的對象接收；可以對其進行逐行處理；游標中可以嵌套游標。7.3.2創(chuàng)建存儲過程[例7.23]輸入某學院名稱，統(tǒng)計該學院每個班級同學的選課信息,返回班級編號、班級名稱、課程名稱、課程選課人數(shù)、課程平均分。本例使用嵌套游標，讀者通過該例掌握嵌套游標的使用方法。分析：本例涉及兩個參數(shù)一個是輸入?yún)?shù)：學院名稱，設為@institute；一個是輸出參數(shù)，它為一個集合值，包含了該學院所有班級的班級編號、班級名稱、課程名稱、課程選課人數(shù)、課程平均分；對于集合值輸出參數(shù)，在存儲過程中定義一個臨時表來存儲該集合，設臨時表為#myTemp在存儲過程尾部使用語句“SELECT*FROM#myTemp”將該集合返回給調用者。7.3.2創(chuàng)建存儲過程聲明5個臨時變量，分別保存查詢出來的班級編號@classNo、班級名稱@className、課程名稱@courseName、選課人數(shù)@count、選課平均分@avg。由于一個學院有多個班級，定義一個游標curClass，根據(jù)輸入的學院名稱，查詢該學院所有的班級編號和班級名稱。將查詢出的班級編號和班級名稱放入變量@classNo、@className中。定義游標語句為：

DECLARE

curClass

CURSOR

FORSELECTclassNo,classNameFROMClassWHEREinstitute=@institute7.3.2創(chuàng)建存儲過程由于一個班級選修了多門課程，需依據(jù)查詢出來的班級號@classNo，按選課的課程名進行分組計算，統(tǒng)計該班級每門課程的選課人數(shù)和選課平均分。需要使用第二個游標，將查詢出來的該班級的選課人數(shù)和平均分放入變量@count和@avg中。定義游標語句為：DECLARE

curCourse

CURSOR

FOR

SELECTcourseName,count(*),avg(score)FROMStudenta,Scoreb,CoursecWHEREa.studentNo=b.studentNoANDb.courseNo=c.courseNoANDclassNo=@classNoGROUPBYcourseName注意：@classNo變量的值是從外游標中獲取的班級編號。將查詢出來的班級編號、班級名稱、課程名稱、課程選課人數(shù)、課程平均分插入到臨時表#myTemp中。7.3.2創(chuàng)建存儲過程存儲過程為：CREATE

PROCEDURE

proInstitute(@institute

varchar(30))ASBEGIN

DECLARE

@className

varchar(30),@courseName

varchar(30)

DECLARE

@classNo

char(6),@count

tinyint,@avg

numeric(5,1)/*定義一個臨時表，存放每個班級的班級編號、班級名稱、

課程名稱、課程選課人數(shù)、課程平均分*/

CREATE

TABLE

#myTemp

(

classNochar(6),

classNamevarchar(30),

courseNamevarchar(30),classCounttinyint,classAvgnumeric(5,1))7.3.2創(chuàng)建存儲過程--

定義游標curClass，依據(jù)輸入?yún)?shù)@institute，查找班級編號和班級名稱

DECLARE

curClass

CURSOR

FORSELECTclassNo,classNameFROMClassWHEREinstitute=@instituteOPENcurClass

FETCH

curClassINTO@classNo,@classNameWHILE(@@FETCH_STATUS

=0)BEGIN

--定義游標curCourse，查找@classNo班選課的課程名稱、選課人數(shù)、平均分

DECLAREcurCourse

CURSOR

FOR

SELECTcourseName,count(*),avg(score)FROMStudenta,Scoreb,CoursecWHEREa.studentNo=b.studentNoANDb.courseNo=c.courseNoANDclassNo=@classNo

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

OPEN

curCourseFETCHcurCourse

INTO

@courseName,@count,@avg

WHILE(@@FETCH_STATUS

=0)BEGIN/*將班級編號、班級名稱、課程名稱、課程選課人數(shù)、課程平均分

插入到臨時表#myTemp中*/

INSERTINTO

#myTemp

VALUES(@classNo,@className,@courseName,

@count,@avg

)--獲取當前游標值，取該班級下一門課程的課程名稱、選課人數(shù)和平均分

FETCHcurCourse

INTO

@courseName,@count,@avgEND

CLOSEcurCourseDEALLOCATEcurCourse--獲取游標curClass的當前游標值，即取下一個班級

FETCHcurClass

INTO

@classNo,@classNameENDCLOSEcurClassDEALLOCATEcurClass--顯示臨時表的內容，同時將臨時表的內容返回給調用者

SELECT*FROM#myTempEND7.3.2創(chuàng)建存儲過程在本例中，獲取班級編號、班級名稱不能寫成：SELECT@classNo=classNo,@className=classNameFROMClassWHEREinstitute=@institute因為：一個學院有多個班級，該查詢返回一個元組集合。變量@classNo和@className僅分別接收一個數(shù)據(jù)。必須使用游標，本例定義游標為curClass。7.3.3執(zhí)行存儲過程使用存儲過程時，必須執(zhí)行命令EXECUTE語法：

EXECUTE

<procedurName>[[<@parameterName>=]<expr>,[<@parameterName>=]<@variableName>[OUTPUT][,[<@parameterName>=]<expr>,[<@parameterName>=]<@variableName>[OUTPUT]]]注意：EXECUTE的參數(shù)必須與對應的PROCEDURE的參數(shù)相匹配。[例7.24]執(zhí)行存儲過程proStudentByNo1

EXECUTE

proStudentByNo1

'0800001'

7.3.3執(zhí)行存儲過程[例7.25]執(zhí)行存儲過程proStudentByNo2DECLARE

@sName

varchar(20),@avg

numeric(5,1)EXECUTE

proStudentByNo2

'0800001',@sName

OUTPUT,@avg

OUTPUTSELECT

@sName,@avg

[例7.26]執(zhí)行存儲過程proInstitute

EXECUTE

proInstitute

'信息管理學院'也可以使用命令：DECLARE

@institute

varchar(30)SET

@institute='信息管理學院'EXECUTE

proInstitute

@institute7.3.4修改和刪除存儲過程

修改存儲過程語法為：

ALTER

PROCEDURE

<procedureName>[<@parameterName>

<datatype>[=<defaultValue>][OUTPUT][,<@parameterName>

<datatype>[=<defaultValue>][OUTPUT]]]

AS

<SQL-Statements>注意：由于存儲過程是在服務器端執(zhí)行，程序中不需要有輸出命令SELECT，由SELECT引出的輸出不會在客戶端出現(xiàn)。7.3.4修改和刪除存儲過程

[例7.27]修改存儲過程proStudentAvg，將顯示結果的語句刪除。ALTER

PROCEDURE

proStudentAvg(@sNochar(7),@avg

numeric(6,2)OUTPUT)ASBEGIN

--定義、打開、獲取游標……

--省略的程序代碼見例7.22

FETCH

curGrade

INTO

@sName,@cName,@grade

WHILE(@@FETCH_STATUS=0)

BEGIN--業(yè)務處理

--SELECT

@sName,@cName,@grade--刪除或注釋掉該輸出語句SET@sum=@sum+@scoreSET@count=@count+1

FETCH

curGrade

INTO

@sName,@cName,@gradeEND

……

--省略的程序代碼見例7.22END7.3.4修改和刪除存儲過程

刪除存儲過程語法：

DROP

PROCEDURE

<procedureName>[例7.28]刪除存儲過程proStudentAvg

DROPPROCEDURE

proStudentAvg目錄T-SQL語言游標存儲過程觸

發(fā)

器7.17.37.27.47.4

觸發(fā)器觸發(fā)器(trigger)是用戶定義在關系表上的一類由事件驅動的存儲過程，由服務器自動激活。觸發(fā)器是一種特殊的存儲過程，不管什么原因造成的數(shù)據(jù)變化都能自動響應，對于每條SQL語句，觸發(fā)器僅執(zhí)行一次，事務可用于觸發(fā)器中。事務定義：

BEGIN

TRANSACTION[<transactionName>

]

COMMITTRANSACTION[<transactionName>

]ROLLBACK

TRANSACTION[<transactionName>

]有兩個特殊的表用在觸發(fā)器語句中，不同的數(shù)據(jù)庫管理系統(tǒng)其名稱不一樣：在SQLServer中使用deleted表和inserted表；Oracle數(shù)據(jù)庫使用old表和new表。7.4

觸發(fā)器下面以SQLServer為例介紹觸發(fā)器。注意：deleted表、inserted表的結構與觸發(fā)器作用的基本表結構完全一致;當針對觸發(fā)器作用的基本表(簡稱作用表)的SQL語句開始執(zhí)行時，自動產生deleted表、inserted表的結構與內容；當SQL語句執(zhí)行完畢，

deleted表、inserted表也隨即被刪除。deleted表存儲當DELETE和UPDATE語句執(zhí)行時所影響的行的拷貝，即在DELETE和UPDATE語句執(zhí)行前，先將該語句所作用的行轉移到deleted表中。即將被刪除的元組或修改前的元組值存入deleted表中inserted表存儲當INSERT和UPDATE語句執(zhí)行時所影響的行的拷貝，即在INSERT和UPDATE語句執(zhí)行期間，新行被同時加到inserted表和觸發(fā)器作用的表中。即將被插入的元組或修改后的元組值存入inserted表中，同時更新觸發(fā)器作用的基本表。7.4

觸發(fā)器實際上，UPDATE命令是刪除后緊跟著插入，舊行首先拷貝到deleted表中，新行同時拷貝到inserted表和基本表中。

觸發(fā)器僅在當前數(shù)據(jù)庫中生成觸發(fā)器有3種類型，即插入、刪除和修改；插入、刪除和修改可作為一種類型的觸發(fā)器；查詢操作不會產生觸發(fā)動作，沒有查詢觸發(fā)器類型。7.4.2創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)器的語法：

CREATETRIGGER<triggerName>

ON

<tableName>FOR{INSERT|UPDATE|DELETE}AS<SQL-Statement>--觸發(fā)動作的執(zhí)行體，即觸發(fā)器代碼其中：<triggerName>：觸發(fā)器的名稱，在1個數(shù)據(jù)庫中必須唯一；<tableName>：觸發(fā)器作用的基本表，該表也稱為觸發(fā)器的目標表；{INSERT|UPDATE|DELETE}：觸發(fā)器事件，觸發(fā)器的事件可以是插入INSERT、修改UPDATE和刪除DELETE事件，也可以是這幾個事件的組合。7.4.2創(chuàng)建觸發(fā)器INSERT類型的觸發(fā)器是指：當對指定基本表<tableName>執(zhí)行了插入操作時系統(tǒng)自動執(zhí)行觸發(fā)器代碼。UPDATE類型的觸發(fā)器是指：當對指定基本表<tableName>執(zhí)行了修改操作時系統(tǒng)自動執(zhí)行觸發(fā)器代碼。DELETE類型的觸發(fā)器是指：當對指定基本表<tableName>執(zhí)行了刪除操作時系統(tǒng)自動執(zhí)行觸發(fā)器代碼。<SQL-Statement>：觸發(fā)動作的執(zhí)行體，即觸發(fā)器代碼如果該觸發(fā)器代碼執(zhí)行失敗，則激活觸發(fā)器的事件就會終止，且觸發(fā)器的目標表<tableName>或觸發(fā)器可能影響的其它表不發(fā)生任何變化，即執(zhí)行事務的回滾操作。7.4.2創(chuàng)建觸發(fā)器[例7.29]創(chuàng)建觸發(fā)器，保證學生表中的性別僅能取男和女。分析：本例需要使用插入和修改兩個類型的觸發(fā)器，因為可能破壞約束“性別僅能取男和女”的操作是插入和修改操作。違約條件是：如果在inserted表中存在有性別取值不為“男”或“女”的記錄(由于inserted表保存了修改后的記錄，只要對inserted表進行判斷即可)，則取消本次操作——取消本次的所有操作。插入類型的觸發(fā)器CREATE

TRIGGER

sexIns--定義插入類型的觸發(fā)器ON

Student--觸發(fā)器作用的基本表FORINSERT--觸發(fā)器的類型，即觸發(fā)該觸發(fā)器被自動執(zhí)行的事件ASIFEXISTS(SELECT*FROMinsertedWHEREsexNOTIN('男','女'))

ROLLBACK--事務的回滾操作，即終止觸發(fā)該觸發(fā)器的插入操作7.4.2創(chuàng)建觸發(fā)器修改類型的觸發(fā)器CREATE

TRIGGER

sexUpt

--定義修改類型的觸發(fā)器ON

StudentFOR

UPDATEAS

IF

EXISTS(SELECT*FROMinsertedWHEREsexNOTIN('男','女'))ROLLBACK--事務的回滾操作，即終止觸發(fā)該觸發(fā)器的修改操作該例也可以合并為一個觸發(fā)器：CREATE

TRIGGER

sexUptInsON

StudentFOR

INSERT,UPDATEAS

IFEXISTS(SELECT*FROMinsertedWHEREsexNOTIN('男','女'))ROLLBACK本例的inserted表結構與Student表結構相同。7.4.2創(chuàng)建觸發(fā)器[例7.31]創(chuàng)建觸發(fā)器

，如果對學生表進行了更新(插入、刪除和修改)操作，則自動修改班級表中的班級人數(shù)。假設一次僅允許更新一個學生記錄，否則當作違反約束規(guī)則。分析：該觸發(fā)器的含義是：當對學生表Student刪除和插入記錄時必須修改班級人數(shù)；當修改學生表中某同學的所屬班級時，也要修改班級表中的相應班級的班級人數(shù)；分別為插入、刪除和修改操作設計觸發(fā)器。由于規(guī)定一次僅能修改一個學生記錄，因此在觸發(fā)器中必須進行判斷：如果執(zhí)行DML語句作用的對象超過一條記錄，則取消本次操作。由于規(guī)定一次僅能修改一個學生記錄，因此可直接在SELECT語句中使用變量接收查詢出來的屬性值，不需要使用游標：SELECT

@classNo=classNo

FROM

inserted7.4.2創(chuàng)建觸發(fā)器/*插入類型的觸發(fā)器，inserted表結構與Student表結構相同*/CREATE

TRIGGER

ClassInsON

StudentFOR

INSERTASBEGIN/*定義一個變量@classNo，用于接受所插入的學生所屬的班級編號*/DECLARE@classNo

char(6)/*如果插入的記錄數(shù)大于1條,則回滾*/

IF(SELECTcount(*)FROMinserted)>1

ROLLBACKELSEBEGIN/*找出插入的學生所屬的班級編號放到變量@classNo中*/

SELECT

@classNo=classNoFROMinserted/*修改班級表中對應班級編號為@classNo的班級人數(shù)*/

UPDATE

Class

SET

classNum=classNum+1

WHERE

classNo=@classNoENDEND7.4.2創(chuàng)建觸發(fā)器/*刪除類型的觸發(fā)器，deleted表結構與Student表結構相同*/CREATE

TRIGGER

ClassDelON

StudentFOR

DELETEASBEGIN/*定義一個變量@classNo，用于接受所刪除的學生所屬的班級編號*/

DECLARE

@classNo

char(6)/*如果刪除的記錄數(shù)大于1條，則回滾*/

IF(SELECTcount(*)FROMdeleted)>1

ROLLBACKELSEBEGIN/*找出刪除的學生所屬的班級編號放到變量@classNo中*/

SELECT

@classNo=classNoFROMdeleted/*修改班級表中對應班級編號為@classNo的班級人數(shù)*/UPDATEClass

SET

classNum=classNum-1

WHERE

classNo=@classNoENDEND7.4.2創(chuàng)建觸發(fā)器/*修改類型的觸發(fā)器，deleted和inserted表結構與Student表結構相同

*/CREATE

TRIGGER

ClassUptON

StudentFOR

UPDATEASBEGIN/*定義一個變量@oldClassNo，用于接受所修改前的學生所屬的班級編號*//*定義一個變量@newClassNo，用于接受所修改后的學生所屬的班級編號*/

DECLARE

@oldClassNo

char(6),@newClassNo

char(6)/*如果修改的記錄數(shù)大于1條，則回滾*/

IF(SELECTcount(*)FROMdeleted)>1

ROLLBACK

ELSEBEGIN/*找出修改前的學生所屬的班級編號放到變量@oldClassNo中*/

SELECT

@oldClassNo=classNoFROMdeleted/*找出修改后的學生所屬的班級編號放到變量@newClassNo中*/

SELECT

@newClassNo=classNoFROMinserted

7.4.2創(chuàng)建觸發(fā)器/*修改班級表中對應班級編號的班級人數(shù)*/UPDATEClass

SET

classNum=classNum-1

WHERE

classNo=@oldClassNoUPDATEClassSET

classNum=classNum+1WHEREclassNo=@newClassNoENDEND本例在修改類型的觸發(fā)器中要同時使用deleted表和inserted表?？梢栽黾右粭lIF語句，判斷學生表中的classNo是否被修改了？如果修改了才需要去修改班級表中的classNum。如果一次允許插入多個學生記錄，則實現(xiàn)自動修改班級表中班級人數(shù)的插入類型的觸發(fā)器如下頁所示，請讀者寫出相應的刪除觸發(fā)器和修改觸發(fā)器。7.4.2創(chuàng)建觸發(fā)器/*插入類型的觸發(fā)器，inserted表結構與Student表結構相同*/CREATE

TRIGGER

ClassInsMany--一次插入多條學生記錄的插入類型的觸發(fā)器ON

StudentFOR

INSERTAS

--使用游標來實現(xiàn)BEGIN/*定義一個變量@classNo，用于接受所插入的學生所屬的班級編號*/DECLARE@classNo

char(6)DECLARE

curStudent

CURSOR

FOR--定義游標對多個插入的學生進行逐個處理

SELECTclassNo

FROM

insertedOPENcurStudentFETCH

curStudent

INTO

@classNoWHILE(@@FETCH_STATUS=0)BEGIN

UPDATE

Class

SET

classNum=classNum+1

WHERE

classNo=@classNo--修改班級表中班級編號為@classNo的班級人數(shù)

FETCH

curStudent

INTO

@classNoENDCLOSE

curStudentDEALLOCATE

curStudent

END7.4.2創(chuàng)建觸發(fā)器也可以不使用游標，直接通過一條SQL語句完成班級人數(shù)的修改，插入類型的觸發(fā)器如下：/*插入類型的觸發(fā)器