項目8 CJGL系統編程

項目8CJGL系統編程

SQLServer2008項目實訓教程技能目標

學會使用Transact-SQL語言對CJGL數據庫進行應用編程，以提高數據庫應用系統的開發(fā)能力

SQLServer2008項目實訓教程知識目標正確理解和掌握使用SQLServer變量；掌握SQLServer函數的使用；掌握流程控制語句；掌握編寫順序結構、選擇結構和循環(huán)結構的程序學會創(chuàng)建、刪除、修改存儲過程；學會根據實際需要設計CJGL數據庫中的存儲過程；掌握創(chuàng)建、執(zhí)行、修改和刪除觸發(fā)器的方法；學會根據實際開發(fā)CJGL數據庫中的觸發(fā)器；熟悉游標的工作原理、定義和使用

SQLServer2008項目實訓教程Transact-SQL語言T-SQL是Microsoft公司在關系型數據庫管理系統SQLServer中的SQL-3標準的實現,是微軟對SQL（結構化查詢語言）的擴展,具有SQL的主要特點,同時增加了變量,運算符,函數,流程控制和注釋等語言元素,使得其功能更加強大。T-SQL對SQLServer十分重要,SQLServer中使用圖形界面能夠完成的所有功能,都可以利用T-SQL來實現.

SQLServer2008項目實訓教程Transact-SQL語言的分類（1）數據定義語言DDL：用來創(chuàng)建數據庫和數據庫對象的命令，絕大部分以CREATE開頭，如CREATETABLE等。（2）數據操作語言DML：用來操作數據庫中各種對象，對數據進行修改和檢索。DML語言主要有四種：SELECT（查詢）、INSERT（插入）、UPDATE（更新）和DELETE（刪除）。（3）數據控制語言DCL：用來控制數據庫組件的存取許可、權限等命令，如GRANT、REVOKE等。（4）事務管理語言TML：用于管理數據庫中的事務的命令，如COMMIT、ROLLBACK等。（5）其他語言元素：如標識符、數據類型、流程控制和函數等。

SQLServer2008項目實訓教程Transact-SQL語法約定Transact-SQL語法格式約定標識符續(xù)行注釋批處理

SQLServer2008項目實訓教程Transact-SQL語法格式約定語法約定說明大寫Transact-SQL關鍵字。

斜體用戶提供的Transact-SQL語法的參數。粗體數據庫名、表名、列名、索引名、存儲過程、實用工具、數據類型名以及必須按所顯示的原樣鍵入的文本。下劃線當語句中省略了包含帶下劃線的值的子句時應用的默認值。|（豎線）分隔括號或大括號中的語法項。只能選擇其中一項。[]（方括號）可選語法項。不要鍵入方括號。{}（大括號）必選語法項。不要鍵入大括號。[,...n]指示前面的項可以重復n次。每一項由逗號分隔。[...n]指示前面的項可以重復n次。每一項由空格分隔。[;]可選的

Transact-SQL語句終止符。不要鍵入方括號。<標簽>::=語法塊的名稱。此約定用于對可在語句中的多個位置使用的過長語法段或語法單元進行分組和標記。

SQLServer2008項目實訓教程語法格式約定標識符：用來定義服務器、數據庫、數據庫對象和變量等的名稱?？煞譃槌Ｒ?guī)標識符和分隔標識符。要求：第一個字符必須是字母、下劃線、at符號（@）和數字標記（#）；標識符不能是T-SQL的保留字；包含的字符數必須在1-128之間。

續(xù)行：直接將一條語句分幾行編寫。

SQLServer2008項目實訓教程語法格式約定注釋：使用--（雙減號）或/*…*/兩種表示方法例1：單行文本注釋--選擇學生信息Select*fromstudent例2：塊注釋語句Selectsno,sNameFromstudent/*wheresnoin(selectsnofromscore)*/其中，where子句被注釋，不再起作用。

SQLServer2008項目實訓教程語法格式約定批處理：由一條或多條SQL語句構成，可從批中讀取所有語句，編譯成可執(zhí)行的單元（執(zhí)行計劃），然后一次執(zhí)行計劃中的所有語句，用GO結束批處理。例：打印教師姓名Declare@namevarchar(10)Select@name=‘范新剛’Print‘教師姓名:’+@nameGo

SQLServer2008項目實訓教程數據庫對象命名方法

所有數據庫對象名可以是由四部分組成的名稱，格式如下。[server_name．[database_name]．[schema_name]．

|database_name．[schema_name]．|schema_name．]object_name

各部分說明如下。server_name：連接的服務器名稱或遠程服務器名稱。database_name：SQLServer數據庫的名稱schema_name：指定包含對象的架構的名稱。object_name：對象的名稱。

SQLServer2008項目實訓教程常量

根據常量值的不同類型，常量分為字符串常量、二進制常量、整型常量、實數常量、日期時間常量、貨幣常量和惟一標識常量。

SQLServer2008項目實訓教程變量

變量是指在程序運行過程中，其值會改變的量。主要用來存儲臨時性的數據。

SQLServer2005提供兩種變量：用戶自己定義的局部變量和系統提供的全局變量。

SQLServer2008項目實訓教程局部變量

用戶自己定義的變量稱為局部變量。局部變量用于保存特定類型的單個數據值的對象，必須先定義，再使用。變量名必須以@開頭。定義格式：

DECLARE局部變量名數據類型[,...n]【例】定義一個整型變量。Declare@numberint【例】定義三個varchar類型變量和一個整型變量Declare@namevarchar(8),@sexvarchar(2),@ageint,@addressvarchar(50)局部變量的賦值一次為一個變量賦值：SET@局部變量名=表達式[,…n]一次為多個變量賦值：SELECT@局部變量名=表達式[,…n][FROM子句][WHERE子句]

SQLServer2008項目實訓教程局部變量子任務1：聲明2個變量，分別為字符型和整型，為其賦值為學生表中學號為001的學生姓名和年齡，并以消息方式輸出以下結果：Declare@namevarchar(20),@ageintSelect@name=姓名,@age=datediff(yy,出生日期,getdate())From學生表Where學號='001'Print@name+’的年齡是：’+cast(@ageasvarchar(10))

SQLServer2008項目實訓教程全局變量

全局變量由系統定義和維護的變量，用于記錄服務器活動狀態(tài)的一組數據。全局變量名由@@符號開始。用戶不能建立全局變量，也不能使用SET語句去修改全局變量的值。在SQLServer中，全局變量以系統函數的形式在使用。

SQLServer2008項目實訓教程表達式和運算符

表達式是標識符、值和運算符的組合?？蓪⒈磉_式用在查詢中作為檢索數據的一部分，也可用作查找滿足一組條件的數據時的搜索條件。

SQLServer2008項目實訓教程算術運算符運算符含義+（加）加-（減）減*（乘）乘/（除）除%（取模）返回一個除法運算的整數余數。例如，12%5=2，這是因為12除以5，余數為2。

SQLServer2008項目實訓教程位運算符運算符含義&（位與）邏輯與運算（兩個操作數）|（位或）位或（兩個操作數）^（位異或）位異或（兩個操作數）

SQLServer2008項目實訓教程比較運算符運算符含義=（等于）等于>（大于）大于<（小于）小于>=（大于等于）大于或等于<=（小于等于）小于或等于<>（不等于）不等于!=（不等于）不等于（非SQL-92標準）!<（不小于）不小于（非SQL-92標準）!>（不大于）不大于（非SQL-92標準）

SQLServer2008項目實訓教程邏輯運算符運算符含義ALL如果一組的比較都為TRUE，那么就為TRUE。AND如果兩個布爾表達式都為TRUE，那么就為TRUE。ANY如果一組的比較中任何一個為TRUE，那么就為TRUE。BETWEEN如果操作數在某個范圍之內，那么就為TRUE。EXISTS如果子查詢包含一些行，那么就為TRUE。IN如果操作數等于表達式列表中的一個，那么就為TRUE。LIKE如果操作數與一種模式相匹配，那么就為TRUE。NOT對任何其他布爾運算符的值取反。OR如果兩個布爾表達式中的一個為TRUE，那么就為TRUE。SOME如果在一組比較中，有些為TRUE，那么就為TRUE。

SQLServer2008項目實訓教程字符串串聯運算符

加號(+)是字符串串聯運算符，將字符串串聯起來。例如，‘教師姓名:’+‘范新剛'的結果就是'教師姓名:范新剛'。

SQLServer2008項目實訓教程一元運算符運算符含義+（正）數值為正。-（負）數值為負。~（位非）返回數字的非。

SQLServer2008項目實訓教程運算符優(yōu)先級級別運算符1~（位非）2*（乘）、/（除）、%（取模）3+（正）、-（負）、+（加）、（+連接）、-（減）、&（位與）4=,

>、<、>=、<=、<>、!=、!>、!<（比較運算符）5^（位異或）、|（位或）6NOT7AND8ALL、ANY、BETWEEN、IN、LIKE、OR、SOME9=（賦值）

SQLServer2008項目實訓教程函數

在Transact-SQL編程語言中提供豐富的函數。函數可分系統定義函數和用戶定義函數。本節(jié)介紹的是系統定義函數中最常用的數學函數、字符串函數、日期時間函數、聚合函數、類型轉換函數的最常用的部分。

SQLServer2008項目實訓教程聚合函數常用的聚合函數（只用在select、compute、computeby或having子句中）

函數名功能AVG返回一組值的平均值。COUNT返回一組值中項目的數量。（返回值為int類型）。COUNT_BIG返回一組值中項目的數量。（返回值為bigint類型）。MAX返回表達式或者項目中的最大值。MIN返回表達式或者項目中的最小值。SUM返回表達式中所有項的和，或者只返回DISTINCT值。SUM只能用于數字列。

SQLServer2008項目實訓教程數學函數

函數名功能ABS()返回該數的絕對值。Pi()返回圓周率。POWER(N,M)返回N的M次冪。RAND([N])返回0~1之間的隨機數。ROUND(X,N)返回四舍五入值，精度為保留小數點N位。SQRT()返回平方根。

SQLServer2008項目實訓教程字符串函數函數功能ASCII(字符表達式)返回最左側的字符的ASCII碼值CHAR（整型表達式）將intASCII代碼轉換為字符LEFT(字符表達式,整數)返回從左邊開始指定個數的字符串RIGHT(字符表達式,整數)截取從右邊開始指定個數字符串SUBSTRING(字符表達式,起始點,n)截取從起始點開始n個字符串LEN(字符串表達式)返回字符串的長度LTRIM(字符表達式)剪去左空格RTRIM(字符表達式)剪去右空格REPLICATE(字符表達式,n)重復字串CHARINDEX(字符C，字符表達式,[P])返回指定字符C在字符串中第一次出現的位置LOWER(字符常量)/UPPER(字符常量)/返回字符的小寫/大寫

SQLServer2008項目實訓教程日期和時間函數函數功能DATEADD(datepart,數值,日期)返回增加一個時間間隔后的日期結果DATEDIFF(datepart,日期1,日期2)返回兩個日期之間的時間間隔，格式為datepart參數指定的格式DATENAME(datepart,日期)返回日期的文本表示，格式為datepart指定格式DATEPART(datepart,日期)返回某日期的datepart代表的整數值GETDATE（）返回當前系統日期和時間DAY（日期）返回某日期的日datepart所代表的整數值MONTH（日期）返回某日期的月datepart所代表的整數值YEAR（日期）返回某日期的年datepart所代表的整數值

SQLServer2008項目實訓教程類型轉換函數函數功能CAST(@varASDatatype)將變量或常量數據類型轉換為指定類型。CONVERT(Datatype,@var,[日期格式])將變量或常量數據類型轉換為指定類型。如轉換為日期類型，可指定日期格式。

SQLServer2008項目實訓教程輸出顯示1.以網格方式輸出使用select語句輸出，如select@var1,@var2……2.以消息方式輸出使用print語句輸出，如print@var1+@var2+……注意：使用print輸出的只能是字符類型，如其中包含非字符類型數據，則要進行類型轉換。

SQLServer2008項目實訓教程任務實施子任務2：一個電子郵箱地址為student_www@126.com，請使用函數得到其賬戶名，并以消息方式輸出。Declare@emailvarchar(30),@usernamevarchar(20)Set@email='student_www@126.com'Set@username=substring(@email,1,charindex('@',@email)-1)Print@username子任務3：聲明一個字符變量，并為其賦值（任意單詞），然后將首字符變?yōu)榇髮懞筝敵?。Declare@wordvarchar(10),@new_wordvarchar(10)Set@word='student'Set@new_word=upper(left(@word,1))set@new_word=@new_word+right(@word,len(@word)-1)Print@new_word

SQLServer2008項目實訓教程任務實施子任務4：查詢80年代出生的學生信息，包括姓名、性別和出生日期。Select姓名,性別,出生日期From學生表Whereconvert(char(4),year(出生日期),102)like'198[0-9]‘其中，102表示日期采用ANSI標準的yy.mm.dd格式。

SQLServer2008項目實訓教程流程控制語句1、BEGIN…END語句

BEGIN…END語句用于將多個Transact-SQL語句組合為一個邏輯塊，相當于一個單一語句，達到一起執(zhí)行的目的。它的語法格式如下。

BEGIN{

語句1

語句2…}ENDSQLServer允許BEGIN…END語句嵌套使用。

SQLServer2008項目實訓教程流程控制語句（續(xù)）2、IF…ELSE語句

IF…ELSE語句實現程序選擇結構。它的語法格式如下。

IF邏輯表達式

{語句塊1}[ELSE{語句塊2}]

SQLServer2008項目實訓教程流程控制語句（續(xù)）【例】在CJGL數據庫中，查詢學生的平均成績是否超過60分，并顯示相關信息。

declare@avg_sintselect@avg_s=avg(成績)from成績表

if@avg_s>60print'學生的平均成績超過60分'elseprint'學生的平均成績不超過60分‘

SQLServer2008項目實訓教程流程控制語句（續(xù)）【例】在CJGL數據庫中，查詢課程編號為“X001”的平均分是否超過80，如果是，列出所有該課程分數超過平均分的學生信息：學號、姓名、成績；否則列出所有該課程分數低于平均分的學生信息。declare@avg_sfloatselect@avg_s=avg(成績)from成績表where課程編號=‘X001’if@avg_s>80selectS1.學號,姓名,成績from學生表S1,成績表S2whereS1.學號=S2.學號and成績>@avg_sand課程編號=‘X001’elseselects1.學號,姓名,成績from學生表S1,成績表S2whereS1.學號=S2.學號and成績<=@avg_sand課程編號=‘X001’

SQLServer2008項目實訓教程流程控制語句（續(xù)）3、CASE語句：多分支結構。格式有如下兩種：（1）簡單case表達式計算條件列表并返回多個可能結果表達式之一。Case輸入表達式

when表達式1then結果表達式1when表達式2then結果表達式2[,…n][else其它結果表達式]end

SQLServer2008項目實訓教程流程控制語句（續(xù)）【例】查詢所有學生的姓名、性別、出生日期和所在班級信息。select姓名,性別,出生日期,班級名稱=case班級編號when0900401001then'11網絡技術'when0900402002then'11軟件技術'when0900403003then’12軟件技術‘else'其它班級'endfrom學生表

SQLServer2008項目實訓教程流程控制語句（續(xù)）

（2）搜索類型case表達式

Casewhen邏輯表達式1then結果表達式1when邏輯表達式2then結果表達式2[,…n][else其它結果表達式]End

SQLServer2008項目實訓教程流程控制語句（續(xù)）【例】教師信息，并增加一列“等級”：如果職稱為“教授”、“副教授”和“高級工程師”則等級為“高級”，如果職稱為“講師”和“工程師”的則等級為“中級”，其它為“初級”。select教師編號,教師姓名,出生日期,等級=casewhen職稱in('教授','副教授','高級工程師')then'高級'when職稱in('講師','工程師')then'中級'else'初級'endfrom教師表

SQLServer2008項目實訓教程流程控制語句（續(xù)）4、WHILE語句

WHILE語句實現循環(huán)結構。如果指定的條件為真，就重復執(zhí)行語句塊，直到邏輯表達式為假。它的語法格式如下。WHILE邏輯表達式

BEGIN

語句塊1[CONTINUE][BREAK]

語句塊2END

SQLServer2008項目實訓教程流程控制語句（續(xù)）【例】編寫程序，求1-100之間的和。declare@iint,@sumbigintselect@i=1,@sum=0while@i<=100beginset@sum=@sum+@iset@i=@i+1endprint@sum【例】編寫程序，求1-200之間所有能被7整除的整數。declare@iintset@i=1while@i<=200beginif@i%7=0print@iSet@i=@i+1end

SQLServer2008項目實訓教程流程控制語句（續(xù)）5、RETURN語句：無條件退出。格式如下：RETURN[整數表達式]6、WAITFOR語句

WAITFOR語句實現語句延緩一段時間或延遲到某特定的時間執(zhí)行。它的語法格式如下。WAITFOR{DELAY'time'|TIME'time'}其中，Delay表示一直等到指定的時間過去，最長24小時。

Time則表示等待到指定時間?！纠康却?0秒后執(zhí)行select語句。

Waitfordelay'00:00:10’Select*from學生表【例】等到11點10分后才執(zhí)行select語句。

Waitfortime’11:10:00’Select*from學生表

SQLServer2008項目實訓教程用戶自定義函數函數是由一個或多個T-SQL語句組成的子程序，可用于封裝代碼以便重新使用。SQLserver中除了可以使用系統函數外，還允許用戶創(chuàng)建自己的函數。T-SQL中用戶自定義函數分為3類：標量函數、內嵌表值函數和多語句表值函數。

SQLServer2008項目實訓教程用戶自定義函數1.標量函數返回一個標量值。語法格式如下：Createfunction函數名([參數名1數據類型,…])returns數據類型–定義函數返回的數據類型[withencryption]asbeginSQL_statementreturn表達式語句–標量函數必須具有返回值end

SQLServer2008項目實訓教程用戶自定義函數【例】創(chuàng)建標量函數UF_Credits，該函數根據輸入的學號，返回該學生取得的總學分數，并利用該函數查詢“11網絡技術”班所有學生的學號、姓名、總學分數。--首先創(chuàng)建函數createfunctionUF_Credits(@snovarchar(10))returnsintasbegindeclare@creditintselect@credit=sum(學分)from成績表Sjoin課程表ConS.課程編號=C.課程編號where成績>=60and學號=@snoreturn@creditend

SQLServer2008項目實訓教程用戶自定義函數【例】創(chuàng)建標量函數UF_Credits，該函數根據輸入的學號，返回該學生取得的總學分數，并利用該函數查詢“11網絡技術”班所有學生的學號、姓名、總學分數。--應用函數select學號,姓名,dbo.UF_Credits(學號)總學分數from學生表Sjoin班級表ConS.班級編號=C.班級編號where班級名稱='11網絡技術'

SQLServer2008項目實訓教程用戶自定義函數2.內嵌表值函數函數體本身是一個查詢語句，結果返回一個表。語法格式如下：Createfunction函數名[(參數名1數據類型,…)]returnstable–返回的數據類型是表asreturnselect語句

SQLServer2008項目實訓教程用戶自定義函數【例】創(chuàng)建內嵌表值函數UF_Classlist，該函數根據輸入的班級名稱，給出該班所有學生的詳細信息，并利用該函數查詢“11網絡技術”班所有學生信息。--首先創(chuàng)建函數createfunctionUF_Classlist(@classnamevarchar(20))returnstableasreturnselectS.*from學生表Sjoin班級表ConS.班級編號=C.班級編號where班級名稱=@classname--應用函數select*fromdbo.UF_Classlist('11網絡技術')

SQLServer2008項目實訓教程用戶自定義函數1.多語句表值函數返回的也是一個表，但此表是在該函數中新定義的。語法格式如下：Createfunction函數名[(參數名1數據類型,…)]returns@table_nametable–返回的數據類型是表變量{table_define_statement}–定義表結構asbeginSQL_statement–一般是select…into…語句return–后面不需要任何表達式end

SQLServer2008項目實訓教程用戶自定義函數【例】創(chuàng)建多語句表值函數UF_Class_score，根據輸入的班級名稱和課程名稱返回該班所有學生的學號、姓名、性別、課程名稱和成績，并利用該函數查詢“11網絡技術”班“數據庫技術與應用”課程的成績。--首先創(chuàng)建函數createfunctionUF_Class_score(@classnamevarchar(20),@coursenamevarchar(20))returns@ttable(學號char(10),姓名varchar(20),性別char(2),課程名稱varchar(20),成績int)asbegininsertinto@tselectS1.學號,姓名,性別,課程名稱,成績from學生表S1,班級表C1,課程表C2,成績表S2WhereS1.班級編號=C1.班級編號andS1.學號=S2.學號andC2.課程編號=S2.課程編號and班級名稱=@classnameand課程名稱=@coursenamereturnend

SQLServer2008項目實訓教程用戶自定義函數【例】創(chuàng)建多語句表值函數UF_Class_score，根據輸入的班級名稱和課程名稱返回該班所有學生的學號、姓名、性別、課程名稱和成績，并利用該函數查詢“11網絡技術”班“數據庫技術與應用”課程的成績。--應用函數select*fromUF_Class_score('11網絡技術','數據庫技術與應用')

SQLServer2008項目實訓教程存儲過程

存儲過程（StoredProcedure）是一組完成特定功能的Transact-SQL語句集，經編譯后存儲在數據庫中，用戶調用過程名和給出參數來調用它們。

SQLServer2008項目實訓教程存儲過程的特點允許模塊化程序設計，提高代碼的重用性執(zhí)行速度快有效降低網絡流量提高數據庫的安全性

SQLServer2008項目實訓教程存儲過程的分類系統存儲過程用戶自定義存儲過程擴展存儲過程

SQLServer2008項目實訓教程存儲過程的分類(續(xù))系統存儲過程

SQLServer2008項目實訓教程存儲過程的分類（續(xù)）用戶自定義存儲過程用戶自定義存儲過程是指封裝的由用戶創(chuàng)建，能完成某一特定的功能的可重用代碼的模塊或例程。擴展存儲過程擴展存儲過程是指使用編程語言（例如C）創(chuàng)建自己的外部例程，是指MicrosoftSQLServer的實例可以動態(tài)加載和運行的DLL，以“XP_”為前綴。

SQLServer2008項目實訓教程

存儲過程語法格式語法格式：

CREATE[PROC|PROCEDURE]存儲過程名[;number][{@參數名稱參數數據類型}[VARYING][=參數的默認值][OUTPUT]][,...n][WITHENCRYPTION][WITHRECOMPILE]ASsql_statement

參數：Number:用來對同名的過程分組，以便用一條dropproc語句即可將同組的過程一起除去。@參數名稱：存儲過程可以沒有參數。也可以聲明一個或多個參數，參數名稱必須@作為第一個字符。VARYING表示指定作為輸出參數支持的結果集，僅適用于游標參數。參數可設置默認值，如后面帶OUTPUT，表示為輸出參數。WITHENCRYPTION：對存儲過程加密，其他用戶無法查看存儲過程的定義。WITHRECOMPILE：每次執(zhí)行該存儲過程都重新進行編譯。sql_statemen：該存儲過程中定義的編程語句。

SQLServer2008項目實訓教程存儲過程的組成存儲過程的定義中包含如下的兩個主要組成部分。（1）過程名稱及其參數的說明：包括所有的輸入參數以及傳給調用者的輸出參數。（2）過程的主體：也稱為過程體，針對數據庫的操作語句（T-SQL語句），包括調用其它存儲過程的語句。

SQLServer2008項目實訓教程不帶參數的存儲過程1、創(chuàng)建不帶參數的存儲過程語法格式：CREATE[PROC|PROCEDURE]存儲過程名

[WITHENCRYPTION][WITHRECOMPILE]ASsql_statement【例】創(chuàng)建一個名為s_inf的存儲過程，用于查詢學生的信息。createprocedures_infasselect*from學生表

SQLServer2008項目實訓教程不帶參數的存儲過程【例】創(chuàng)建一個名為s_lili_score存儲過程，用于查詢“李麗”的學號、姓名、性別、出生日期以及所有課程的成績。createprocedures_lili_scoreasselectS1.學號,姓名,性別,出生日期,成績from學生表S1,成績表S2whereS1.學號=S2.學號and姓名=‘李麗'

SQLServer2008項目實訓教程不帶參數的存儲過程（續(xù)）

執(zhí)行不帶參數的存儲過程語法結構如下。

EXEC[UTE]存儲過程名【例】執(zhí)行創(chuàng)建的s_inf和s_lili_score存儲過程。

EXECs_infEXECs_lili_score

SQLServer2008項目實訓教程帶輸入參數的存儲過程創(chuàng)建帶輸入參數的存儲過程CREATE[PROC|PROCEDURE]存儲過程名

[{@參數名稱參數數據類型}[=參數的默認值][,...n][WITHENCRYPTION][WITHRECOMPILE]ASsql_statement【例】創(chuàng)建存儲過程stu_count，實現根據輸入的系部名稱輸出給定系部的學生人數。createprocstu_count@dnamevarchar(20)asselectcount(學號)學生人數from部門表D,班級表C,學生表SwhereD.系部編號=C.系部編號andC.班級編號=S.班級編號and系部名稱=@dname

SQLServer2008項目實訓教程帶輸入參數的存儲過程（續(xù)）執(zhí)行輸入參數的存儲過程兩種方法：（1）使用參數名傳遞參數值執(zhí)行的語法結構如下。EXEC存儲過程名[@參數名=參數值][DEFAULT][，…n]execstu_count@dname='信息工程系'（2）按位置傳遞參數值EXEC存儲過程名[參數值1，參數值2，…]execstu_count'信息工程系'

SQLServer2008項目實訓教程帶輸出參數的存儲過程1、創(chuàng)建帶輸出參數的存儲過程語法如下。@參數名數據類型[=默認值]OUTPUT【例】創(chuàng)建存儲過程stuname，實現根據輸入學生的學號，輸出該學生的姓名和政治面貌。并調用該存儲過程返回“001”號學生的姓名和政治面貌。createprocstuname@學號char(8),@姓名varchar(20)output,@政治面貌varchar(10)outputasselect@姓名=姓名,@政治面貌=政治面貌from學生表where學號=@學號

SQLServer2008項目實訓教程帶輸出參數的存儲過程2、執(zhí)行帶輸出參數的存儲過程語法如下。EXEC[UTE]存儲過程名[[@參數名=]{參數值|@變量[OUTPUT]|[默認值]}][,...n]declare@stunamevarchar(20),@stuzvarchar(10)execstuname‘001’,@stunameoutput,@stuzoutputselect@stuname姓名,@stuz政治面貌

SQLServer2008項目實訓教程查看存儲過程（1）sp_help用于顯示存儲過程的參數及其數據類型。語法格式如下。sp_help[[@objname=]存儲過程名]（2）sp_helptext用于顯示存儲過程的源代碼。語法格式如下。sp_helptext[[@objname=]存儲過程]

SQLServer2008項目實訓教程刪除用戶存儲過程

刪除用戶存儲過程可以使用DROP命令。語法格式如下。DROP{PROC|PROCEDURE}存儲過程名[,...n]

SQLServer2008項目實訓教程修改存儲過程ALTERPROCEDURE語句的語法格式如下。ALTER[PROC|PROCEDURE]存儲過程名

[{@參數名稱參數數據類型}[=參數的默認值][OUTPUT]][,...n][WITHENCRYPTION][WITHRECOMPILE]ASsql_statement

SQLServer2008項目實訓教程系統存儲過程常用系統存儲過程：sp_tables：返回可在當前環(huán)境中查詢的對象列表。這代表可在FROM子句中出現的任何對象。sp_stored_procedures：返回當前環(huán)境中的存儲過程列表。sp_rename：在當前數據庫中更改用戶創(chuàng)建對象的名稱。此對象可以是表、索引、列、別名數據類型。sp_renamedb：更改數據庫的名稱。sp_help：報告有關數據庫對象（sys.sysobjects兼容視圖中列出的所有對象）、用戶定義數據類型或SQLServer2005提供的數據類型的信息。sp_helptext：示用戶定義規(guī)則的定義、默認值、未加密的Transact-SQL存儲過程、用戶定義Transact-SQL函數、觸發(fā)器、計算列、CHECK約束、視圖或系統對象（如系統存儲過程）。sp_who：提供有關MicrosoftSQLServerDatabaseEngine實例中的當前用戶和進程的信息。sp_password：為MicrosoftSQLServer登錄名添加或更改密碼。

SQLServer2008項目實訓教程創(chuàng)建存儲過程需要考慮的因素：（1）一個存儲過程完成一個任務。（2）不要使用sp_來命名用戶存儲過程。（3）可以使用WITHENCRYPTION加密存儲過程，以免存儲過程的源代碼被人查閱。（4）在服務器上創(chuàng)建、測試存儲過程。

SQLServer2008項目實訓教程觸發(fā)器

觸發(fā)器通常可以完成一定的業(yè)務規(guī)則，用于SQLServer約束、默認值和規(guī)則的完整性檢查，還可以完成難以用普通約束實現的復雜功能的限制。

SQLServer2008項目實訓教程觸發(fā)器的分類DML觸發(fā)器當數據庫中發(fā)生數據操作語言(insert、update、delete)事件時將調用DML觸發(fā)器。主要用來實現表之間的級聯更新或刪除，以及防止惡意或錯誤的插入、更新和刪除操作。DDL觸發(fā)器在CREATE、ALTER、DROP和其他DDL語句上操作時發(fā)生的觸發(fā)器稱為DDL觸發(fā)器。主要用來記錄數據庫的修改過程，以及限制程序員對數據庫的修改。

SQLServer2008項目實訓教程DML觸發(fā)器的分類根據觸發(fā)器觸發(fā)方式的不同，可分為：AFTER觸發(fā)器：在數據修改完成后，觸發(fā)器被激活。執(zhí)行順序如下。數據表約束檢查→修改表中的數據→激活觸發(fā)器INSTEADOF觸發(fā)器：這類觸發(fā)器會取代原來要進行的操作，在數據更改之前發(fā)生，數據的如何更改完全取決于觸發(fā)器的內容，執(zhí)行順序如下。激活觸發(fā)器→若觸發(fā)器涉及數據更改，則檢查表約束CLR觸發(fā)器：CLR觸發(fā)器將執(zhí)行在托管代碼中編寫的方法，而不用執(zhí)行T-SQL存儲過程。

SQLServer2008項目實訓教程DML觸發(fā)器創(chuàng)建觸發(fā)器的語法格式如下。CREATETRIGGER觸發(fā)器名

ON表名或視圖名

[WITHENCRYPTION]{FOR|AFTER|INSTEADOF}//選擇觸發(fā)器的類型{INSERT[，]|UPDATE[，]|DELETE}//用來指明哪種數據操作將激活觸發(fā)器[NOTFORREPLICATION]AS[IFUPDATE(列名1)[{AND|OR}UPDATE(列名2)[…n]//用來確定對某一確定列進行插入操作還是更新操作，但不與刪除操作用在一起

sql_statements

SQLServer2008項目實訓教程INSERTED表和DELETED表操作類型INSERTED表DELETED表INSERT插入的記錄不創(chuàng)建DELETE不創(chuàng)建刪除的記錄UPDATE修改后的記錄修改前的記錄DML觸發(fā)器都會創(chuàng)建兩個臨時的用于記錄更改前后變化的表：INSERTED和DELETED，表的結構和創(chuàng)建觸發(fā)器的表的結構相同。在執(zhí)行DML操作時，兩個表的記錄如下：對具有觸發(fā)器的表進行insert、delete和update操作時，過程分別如下：1.Insert操作：插入到表中的新行被復制到inserted表中。2.Delete操作：從表中刪除的行轉移到了deleted表中。3.Update操作：先從表中刪除舊行，然后向表中插入新行。其中，刪除后的舊行被轉移到deleted表中，插入到表中的新行被復制到inserted表中。

SQLServer2008項目實訓教程創(chuàng)建DML觸發(fā)器1、INSERT觸發(fā)器INSERT觸發(fā)器通常被用來驗證被觸發(fā)器監(jiān)控的字段中的數據滿足要求的標準，以確保數據完整性?！纠縿?chuàng)建名為reminder的觸發(fā)器，當用戶向部門表中插入一條系部記錄時，向客戶端發(fā)送一條提示消息“插入一條記錄！”。

--創(chuàng)建觸發(fā)器

createtriggerreminderon部門表forinsertasprint'插入一條記錄！’

--測試觸發(fā)器insert部門表values(14,‘管理系’,‘王明陽')

SQLServer2008項目實訓教程創(chuàng)建DML觸發(fā)器2、DELETE觸發(fā)器當激發(fā)delete觸發(fā)器時，被刪除的記錄轉移到deleted表中，數據表中將不再存在該記錄。

【例】在學生表上創(chuàng)建一個觸發(fā)器，其功能為：當對學生表進行刪除操作時，首先檢查要刪除的學生是否為11網絡技術班的學生，如果不是的話，可以刪除該學生；否則撤銷此刪除，并顯示無法刪除的信息。--創(chuàng)建觸發(fā)器

createtriggerstu_delon學生表fordeleteasdeclare@cnamevarchar(20)select@cname=班級名稱from班級表joindeletedon班級表.班級編號=deleted.班級編號if(@cname='11網絡技術')beginprint'此為11網絡技術班學生，無法刪除記錄'rollbackend

--測試觸發(fā)器delete學生表where學號=’005’

SQLServer2008項目實訓教程創(chuàng)建DML觸發(fā)器（續(xù)）3、UPDATE觸發(fā)器

當在定義有觸發(fā)器的表上執(zhí)行UPDATE語句時，原始行被移入到DELETED表，更新行被移入到INSERTED表?！纠縿?chuàng)建了一個修改觸發(fā)器，禁止用戶更新學生表的學號字段值--創(chuàng)建觸發(fā)器

createtriggers_update

on學生表

afterupdate

as

ifupdate(學號)

begin

raiserror('不允許修改主鍵列！',16,1)

rollbacktran

end

--測試觸發(fā)器

update學生表set學號='012'where學號='004'

SQLServer2008項目實訓教程創(chuàng)建DML觸發(fā)器（續(xù)）【例】在成績表上創(chuàng)建觸發(fā)器trscore，檢查插入或更新的成績是否在0~100之間。--創(chuàng)建觸發(fā)器

ifexists(selectnamefromsysobjectswherename='trscore'andtype='tr')droptriggertrscoregocreatetriggertrscoreon成績表forinsert,updateasdeclare@scoreintselect@score=成績frominsertedif@score<0or@score>100beginprint'成績必須在0到100之間！'rollbackendgo--測試觸發(fā)器insert成績表values('004','x004',-50)goupdate成績表set成績=120where編號=3go

SQLServer2008項目實訓教程創(chuàng)建DML觸發(fā)器（續(xù)）【例】將上例改成使用insteadof觸發(fā)器來實現。--修改觸發(fā)器altertriggertrscoreon成績表insteadofinsert,updateassetnocountondeclare@scoredecimal(4,1),@snochar(8),@coursenochar(4)set@sno=(select學號frominserted)set@courseno=(select課程編號frominserted)select@score=成績frominsertedIf@score<0or@score>100beginprint'成績必須在0到100之間！'rollbackendelsebegininsert成績表values(@sno,@courseno,@score)endgo

SQLServer2008項目實訓教程創(chuàng)建DML觸發(fā)器（續(xù)）--測試觸發(fā)器insert成績表values('004','x004',-50)goupdate成績表set成績=120where編號=3go

SQLServer2008項目實訓教程修改觸發(fā)器使用ManagementStudio使用ALTERTRIGGER語句語法格式如下。ALTERTRIGGER要修改的觸發(fā)器名

ON表名或視圖名

[WITHENCRYPTION](FOR|AFTER|INSTEADOF){[DELETE][,][INSERT][,][UPDATE]}ASsql_statements

SQLServer2008項目實訓教程DDL觸發(fā)器DDL觸發(fā)器常用于如下的情況。防止對數據庫架構進行某些更改。響應數據庫架構中的更改。記錄數據庫架構中的更改或事件創(chuàng)建DDL觸發(fā)器的語法格式如下。CREATETRIGGER觸發(fā)器名ON{ALLSERVER|DATABASE}[WITHENCRYPTION]{FOR|AFTER}{DDL事件}[,...n]ASsql_statement

SQLServer2008項目實訓教程創(chuàng)建DDL觸發(fā)器【例】創(chuàng)建DDL觸發(fā)器trig_ddl，該觸發(fā)器禁止對所有表的結構修改及刪除。--創(chuàng)建觸發(fā)器

createtriggertrig_ddl

ondatabaseforcreate_table,alter_table,drop_table

asbeginraiserror(’該數據庫不允許進行架構更改！’,16,9)rollbackend--測試觸發(fā)器altertable學生表addAint

SQLServer2008項目實訓教程查看觸發(fā)器使用系統存儲過程sp_help、sp_helptext和sp_depents分別查看觸發(fā)器的不同信息。sp_help：顯示觸發(fā)器的所有者和創(chuàng)建時間sp_helptext：顯示觸發(fā)器的源代碼sp_depends：顯示該觸發(fā)器參考的對象清單

SQLServer2008項目實訓教程刪除觸發(fā)器使用ManagementStudio使用DROPTRIGGER命令。語法格式如下。DROPTRIGGERtrigger_name[,...n]ON{DATABASE|ALLSERVER}[;]

SQLServer2008項目實訓教程禁止或啟用觸發(fā)器禁用觸發(fā)器當不再需要某個觸發(fā)器時，可將其禁用或刪除。語法格式如下。DISABLETRIGGER{ALL|觸發(fā)器名[,...n]}ON{object_name|DATABASE|ALLSERVER}

啟用觸發(fā)器語法格式如下。ENABLETRIGGER{ALL|觸發(fā)器名[,...n]}ON{object_name|DATABASE|ALLSERVER}

SQLServer2008項目實訓教程游標

游標（cursor）是系統為用戶開設的一個數據緩沖區(qū)，存放SQL語句的執(zhí)行結果。每個游標區(qū)都有一個名字。用戶可以用SQL語句逐一從游標中獲取記錄，并賦給主變量，交由主語言進一步處理。即通過游標可對結果集逐行處理。

SQLServer2008項目實訓教程游標

游標總是與一條SQL選擇語句相關聯。游標由結果集（可以是零條、一條或由相關的選擇語句檢索出的多條記錄）和結果集中指向特定記錄的游標位置組成。當對結果集進行處理時，必須聲明一個指向該結果集的游標。

SQLServer2008項目實訓教程游標的特點允許程序對查詢語句返回的記錄集中的每一行執(zhí)行相應的操作，而不是對整個記錄集操作；提高對基于游標位置的表中記錄的刪除和更新；作為數據庫管理系統面向行的應用程序之間的橋梁。

SQLServer2008項目實訓教程游標的分類

游標分為：T-SQL游標，API服務器游標和客戶游標。（1）T-SQL游標由SQLServer服務器實現的游標，主要通過T-SQL腳本、存儲過程和觸發(fā)器將游標傳給服務器執(zhí)行。（2）API游標

API游標支持在OLEDB，ODBC以及DB_library中使用游標函數，主要用在服務器上。每一次客戶端應用程序調用API游標函數，MSSQLSEVER的OLEDB提供者、ODBC驅動器或DB_library的動態(tài)鏈接庫（DLL）都會將這些客戶請求傳送給服務器以對API游標進行處理。（3）客戶游標客戶游標主要是當在客戶機上緩存結果集時才使用，是前臺游標。在一般情況下，服務器游標能支持絕大多數的游標操作。而客戶游標常常被用作服務器游標的輔助。

SQLServer2008項目實訓教程游標創(chuàng)建游標的語法格式如下。DECLARE游標名[INSENSITIVE]|SCROLLCURSORFORselect_statement

其中，INSENSITIVE：表示不敏感游標，即游標不會隨基本表內容的改變而改變，也無法通過游標更新數據。SCROLL：表示滑動游標，可指定游標的移動方向，包括first、last、prior、next、relative、absolute

SQLServer2008項目實訓教程游標打開游標OPEN游標名游標定義后，如要讀取數據，則必須先打開游標。提取游標數據

FETCH[next|prior|first|last|absolute{n|@var}|relative{n|@var}]FROM游標名INTO@var注意：執(zhí)行FETCH語句后，可通過@@FETCH_STATUS全局變量返回游標當前的狀態(tài)。其有3個返回值：

0——FETCH語句執(zhí)行成功。

1——FETCH語句執(zhí)行失敗或行數據超過游標結果集范圍。

2——表示提取的數據不存在。關閉/釋放游標

CLOSE/DEALLOCATE游標名

SQLServer2008項目實訓教程游標【例】現需要為“11網絡技術”班學生打印所有課程的成績通知單，樣式如下：