第5章 數(shù)據(jù)庫(kù)的高級(jí)應(yīng)用

1、韓山師范學(xué)院任課人：任課人：cws第第5 5章章 數(shù)據(jù)庫(kù)的高級(jí)應(yīng)用數(shù)據(jù)庫(kù)的高級(jí)應(yīng)用數(shù)據(jù)庫(kù)原理與數(shù)據(jù)庫(kù)原理與SQL ServerSQL Server章節(jié)內(nèi)容章節(jié)內(nèi)容5.1架構(gòu)架構(gòu)5.2 索引索引5.3 視圖視圖5.4 Transact-SQL編程基礎(chǔ)編程基礎(chǔ)5.5 存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程5.6 觸發(fā)器觸發(fā)器5.7 事務(wù)事務(wù)5.8 鎖鎖數(shù)據(jù)庫(kù)原理與數(shù)據(jù)庫(kù)原理與SQL ServerSQL Server數(shù)據(jù)庫(kù)的高級(jí)應(yīng)用數(shù)據(jù)庫(kù)的高級(jí)應(yīng)用學(xué)習(xí)目標(biāo)：學(xué)習(xí)目標(biāo)： 了解索引、視圖、游標(biāo)、存儲(chǔ)過(guò)程、觸發(fā)器了解索引、視圖、游標(biāo)、存儲(chǔ)過(guò)程、觸發(fā)器及事務(wù)的作用及事務(wù)的作用 掌握索引、視圖、游標(biāo)、存儲(chǔ)過(guò)程、

2、觸發(fā)器掌握索引、視圖、游標(biāo)、存儲(chǔ)過(guò)程、觸發(fā)器及事務(wù)的創(chuàng)建方法及事務(wù)的創(chuàng)建方法 掌握索引、視圖、游標(biāo)、存儲(chǔ)過(guò)程、觸發(fā)器掌握索引、視圖、游標(biāo)、存儲(chǔ)過(guò)程、觸發(fā)器及事務(wù)的修改及刪除方法及事務(wù)的修改及刪除方法數(shù)據(jù)庫(kù)原理與數(shù)據(jù)庫(kù)原理與SQL ServerSQL Server5.3 5.3 視視 圖圖【課堂任務(wù)課堂任務(wù)】 本節(jié)要理解視圖的作用及使用。本節(jié)要理解視圖的作用及使用。 視圖的概念及作用視圖的概念及作用 視圖的創(chuàng)建、修改和刪除視圖的創(chuàng)建、修改和刪除數(shù)據(jù)庫(kù)原理與數(shù)據(jù)庫(kù)原理與SQL ServerSQL Server5.3.1 5.3.1 視圖概述視圖概述視圖（虛擬表）

3、視圖（虛擬表） 是從一個(gè)或者幾個(gè)基本表或者視圖中導(dǎo)出的是從一個(gè)或者幾個(gè)基本表或者視圖中導(dǎo)出的虛擬表虛擬表，是從現(xiàn)有基表中抽取若干子集組成，是從現(xiàn)有基表中抽取若干子集組成用戶的用戶的“專用表專用表”，這種構(gòu)造方式必須使用，這種構(gòu)造方式必須使用SQL中的中的SELECT語(yǔ)句來(lái)實(shí)現(xiàn)語(yǔ)句來(lái)實(shí)現(xiàn)。 在定義一個(gè)視圖時(shí)，只是把在定義一個(gè)視圖時(shí)，只是把其定義其定義存放在數(shù)存放在數(shù)據(jù)庫(kù)中，并不直接存儲(chǔ)視圖對(duì)應(yīng)的數(shù)據(jù)，直據(jù)庫(kù)中，并不直接存儲(chǔ)視圖對(duì)應(yīng)的數(shù)據(jù)，直到用戶使用視圖時(shí)才去查找對(duì)應(yīng)的數(shù)據(jù)。到用戶使用視圖時(shí)才去查找對(duì)應(yīng)的數(shù)據(jù)。數(shù)據(jù)庫(kù)原理與數(shù)據(jù)庫(kù)原理與SQL ServerSQL Server5.

4、3.1 5.3.1 視圖概述視圖概述使用視圖具有如下優(yōu)點(diǎn)：使用視圖具有如下優(yōu)點(diǎn)： (1) 簡(jiǎn)化對(duì)數(shù)據(jù)的操作簡(jiǎn)化對(duì)數(shù)據(jù)的操作 (2) 自定義數(shù)據(jù)自定義數(shù)據(jù) (3) 數(shù)據(jù)集中顯示數(shù)據(jù)集中顯示 (4) 導(dǎo)入和導(dǎo)出數(shù)據(jù)導(dǎo)入和導(dǎo)出數(shù)據(jù) (5) 合并分割數(shù)據(jù)合并分割數(shù)據(jù) (6) 安全機(jī)制安全機(jī)制數(shù)據(jù)庫(kù)原理與數(shù)據(jù)庫(kù)原理與SQL ServerSQL Server5.3.2 5.3.2 視圖的創(chuàng)建視圖的創(chuàng)建視圖創(chuàng)建方法視圖創(chuàng)建方法 1. 使用使用SSMS創(chuàng)建視圖（演示）創(chuàng)建視圖（演示） 2. 使用使用Transact-SQL語(yǔ)句創(chuàng)建視圖語(yǔ)句創(chuàng)建視圖CREATE VIEW view_name (

5、Column ,n)WITH ENCRYPTIONAS select_statementWITH CHECK OPTION數(shù)據(jù)庫(kù)原理與數(shù)據(jù)庫(kù)原理與SQL ServerSQL Server5.3.2 5.3.2 視圖的創(chuàng)建視圖的創(chuàng)建命令中的參數(shù)含義如下命令中的參數(shù)含義如下: Column：聲明視圖中使用的列名。如果省略，就使聲明視圖中使用的列名。如果省略，就使用基表的列名作為視圖的列名，也可在用基表的列名作為視圖的列名，也可在Select_statement語(yǔ)句中指定列名。語(yǔ)句中指定列名。 WITH ENCRYPTION：給系統(tǒng)表給系統(tǒng)表syscomments中視中視圖定義的圖

6、定義的SELECT命令加密。這個(gè)選項(xiàng)可用于提高命令加密。這個(gè)選項(xiàng)可用于提高SQL Server系統(tǒng)的安全性。系統(tǒng)的安全性。 AS：說(shuō)明視圖要完成的操作。說(shuō)明視圖要完成的操作。數(shù)據(jù)庫(kù)原理與數(shù)據(jù)庫(kù)原理與SQL ServerSQL Server5.3.2 5.3.2 視圖的創(chuàng)建視圖的創(chuàng)建命令中的參數(shù)含義如下命令中的參數(shù)含義如下: Select_statement：定義視圖的定義視圖的SELECT命令。命令。 注意：注意：視圖中的視圖中的SELECT命令不能包括命令不能包括INTO、ORDER BY等子句。臨時(shí)表也不能在查詢中引用等子句。臨時(shí)表也不能在查詢中引用。 WITH CHEC

7、K OPTION：強(qiáng)制所有通過(guò)視圖修改的強(qiáng)制所有通過(guò)視圖修改的數(shù)據(jù)滿足數(shù)據(jù)滿足Select_statement語(yǔ)句中指定的選擇條件。語(yǔ)句中指定的選擇條件。 視圖創(chuàng)建成功后，可以在視圖創(chuàng)建成功后，可以在SSMS視圖窗口中看到新定視圖窗口中看到新定義的視圖名稱。視圖可以由一個(gè)或多個(gè)表或視圖來(lái)定義的視圖名稱。視圖可以由一個(gè)或多個(gè)表或視圖來(lái)定義。義。數(shù)據(jù)庫(kù)原理與數(shù)據(jù)庫(kù)原理與SQL ServerSQL Server5.3.2 5.3.2 視圖的創(chuàng)建視圖的創(chuàng)建【例例5-2】 有條件的視圖定義。定義視圖有條件的視圖定義。定義視圖v_student，查，查詢所有選修數(shù)據(jù)庫(kù)課程的學(xué)生的學(xué)號(hào)詢所

8、有選修數(shù)據(jù)庫(kù)課程的學(xué)生的學(xué)號(hào)(sno)、姓名、姓名(sname)、課程名稱、課程名稱(cname)和成績(jī)和成績(jī)(degree)。 該視圖的定義涉及了該視圖的定義涉及了student表、表、course表和表和sc表。表。 視圖定義后，可以像基本表一樣進(jìn)行查詢視圖定義后，可以像基本表一樣進(jìn)行查詢。 例：若要查詢以上定義的視圖例：若要查詢以上定義的視圖v_student，可以使用命令：，可以使用命令：SELECT * FROM v_studentCREATE VIEW v_student ASSELECT A.sno,sname,cname,degreeFROM student A,course

9、B,sc CWHERE A.sno=C.sno AND B.cno=C.cno AND cname=數(shù)據(jù)庫(kù)數(shù)據(jù)庫(kù)數(shù)據(jù)庫(kù)原理與數(shù)據(jù)庫(kù)原理與SQL ServerSQL Server5.3.3 5.3.3 視圖的使用視圖的使用視圖的使用視圖的使用 主要包括視圖的檢索、通過(guò)視圖對(duì)基表進(jìn)行主要包括視圖的檢索、通過(guò)視圖對(duì)基表進(jìn)行插入、修改、刪除操作插入、修改、刪除操作。 視圖的檢索幾乎沒(méi)有什么限制：同基本表視圖的檢索幾乎沒(méi)有什么限制：同基本表 對(duì)通過(guò)視圖實(shí)現(xiàn)表的對(duì)通過(guò)視圖實(shí)現(xiàn)表的插入、修改、刪除操作則有插入、修改、刪除操作則有一定的限制條件一定的限制條件。數(shù)據(jù)庫(kù)原理與數(shù)

10、據(jù)庫(kù)原理與SQL ServerSQL Server5.3.3 5.3.3 視圖的使用視圖的使用【例例5-3】 利用視圖向表利用視圖向表student中插入一條數(shù)據(jù)中插入一條數(shù)據(jù) 創(chuàng)建視圖創(chuàng)建視圖 CREATE VIEW V1_student AS SELECT sno,sname, speciality FROM student 向表中插入一條數(shù)據(jù)，腳本如下：向表中插入一條數(shù)據(jù)，腳本如下： INSERT INTO V1_student VALUES(20050203,王小龍王小龍,計(jì)算機(jī)應(yīng)用計(jì)算機(jī)應(yīng)用) 通過(guò)基本表，查詢是否插入了該條記錄：通過(guò)基本表，查詢是否插入了該條記錄： SELECT *

11、 FROM student WHERE sname=王小龍王小龍數(shù)據(jù)庫(kù)原理與數(shù)據(jù)庫(kù)原理與SQL ServerSQL Server5.3.3 5.3.3 視圖的使用視圖的使用如果視圖以下屬性，則插入、更新或刪除基表如果視圖以下屬性，則插入、更新或刪除基表將失?。簩⑹。?(1) 視圖定義中的視圖定義中的FROM子句包含兩個(gè)或多個(gè)表子句包含兩個(gè)或多個(gè)表，且，且SELECT選擇列表達(dá)式中的列包含來(lái)自多個(gè)表的列。選擇列表達(dá)式中的列包含來(lái)自多個(gè)表的列。 (2) 視圖的列是從視圖的列是從集合函數(shù)派生集合函數(shù)派生的。的。 (3) 視圖中的視圖中的SELECT語(yǔ)句包含語(yǔ)句包含GROUP BY

12、子句或子句或DISTINCT選項(xiàng)選項(xiàng)。 (4) 視圖的列是視圖的列是從常量或表達(dá)式派生從常量或表達(dá)式派生的。的。數(shù)據(jù)庫(kù)原理與數(shù)據(jù)庫(kù)原理與SQL ServerSQL Server5.3.4 5.3.4 視圖的修改視圖的修改1、使用、使用SSMS修改視圖（鼠標(biāo)右鍵選擇修改）修改視圖（鼠標(biāo)右鍵選擇修改）2、使用、使用T-SQL語(yǔ)句修改視圖語(yǔ)句修改視圖 ALTER VIEW view_name (Column,n) WITH ENCRYPTION AS select_statement WITH CHECK OPTION 提示：如果在創(chuàng)建視圖時(shí)使用了提示：如果在創(chuàng)建視圖時(shí)使用了WIT

13、H ENCRYPTION選項(xiàng)和選項(xiàng)和WITH CHECK OPTION選項(xiàng)選項(xiàng)，則在使用，則在使用ALTER VIEW命令時(shí)，也必須包括這些命令時(shí)，也必須包括這些選項(xiàng)。選項(xiàng)。數(shù)據(jù)庫(kù)原理與數(shù)據(jù)庫(kù)原理與SQL ServerSQL Server5.3.4 5.3.4 視圖的修改視圖的修改【例例5-5】 修改例修改例5-3中的視圖中的視圖V1_student。ALTER VIEW V1_student AS SELECT sno,sname FROM student數(shù)據(jù)庫(kù)原理與數(shù)據(jù)庫(kù)原理與SQL ServerSQL Server5.3.5 5.3.5 視圖的刪除視圖

14、的刪除1.使用使用SSMS刪除視圖刪除視圖2.使用使用Transact-SQL語(yǔ)句刪除視圖語(yǔ)句刪除視圖 DROP VIEW view ,n 提示：提示：DROP VIEW命令命令可以刪除多個(gè)視圖可以刪除多個(gè)視圖，各視圖名之間用逗號(hào)分隔。各視圖名之間用逗號(hào)分隔。數(shù)據(jù)庫(kù)原理與數(shù)據(jù)庫(kù)原理與SQL ServerSQL Server5.3.5 5.3.5 視圖的刪除視圖的刪除【例例5-6】 刪除視圖刪除視圖V1_student。 DROP VIEW V1_student 提示：提示： 刪除視圖時(shí)，將從系統(tǒng)目錄中刪除視圖的定義刪除視圖時(shí)，將從系統(tǒng)目錄中刪除視圖的定義和有關(guān)視圖的其他信息，

15、還將刪除視圖的所有權(quán)和有關(guān)視圖的其他信息，還將刪除視圖的所有權(quán)限。限。 使用使用DROP TABLE刪除的表上的任何視圖都必刪除的表上的任何視圖都必須用須用DROP VIEW命令刪除。命令刪除。數(shù)據(jù)庫(kù)原理與數(shù)據(jù)庫(kù)原理與SQL ServerSQL Server5.4 Transact-SQL5.4 Transact-SQL編程基礎(chǔ)編程基礎(chǔ)【課堂任務(wù)課堂任務(wù)】 本節(jié)要熟悉本節(jié)要熟悉Transact-SQL語(yǔ)言。語(yǔ)言。 Transact-SQL的語(yǔ)法規(guī)范的語(yǔ)法規(guī)范 Transact-SQL的語(yǔ)言基礎(chǔ)的語(yǔ)言基礎(chǔ) 常用函數(shù)常用函數(shù) 游標(biāo)的基本操作游標(biāo)的基本操作數(shù)據(jù)庫(kù)原

16、理與數(shù)據(jù)庫(kù)原理與SQL ServerSQL Server5.4 Transact-SQL5.4 Transact-SQL編程基礎(chǔ)編程基礎(chǔ)1、常量與變量、常量與變量 常量常量，也稱為文字值或標(biāo)量值，是指程序運(yùn)，也稱為文字值或標(biāo)量值，是指程序運(yùn)行中值始終不改的量。在行中值始終不改的量。在Transact-SQL程序程序設(shè)計(jì)過(guò)程中，定義常量的格式取決于它所表設(shè)計(jì)過(guò)程中，定義常量的格式取決于它所表示的值的數(shù)據(jù)類型。示的值的數(shù)據(jù)類型。數(shù)據(jù)庫(kù)原理與數(shù)據(jù)庫(kù)原理與SQL ServerSQL Server5.4 Transact-SQL5.4 Transact-SQL編程基礎(chǔ)編程基礎(chǔ)1、常量與

17、變量、常量與變量 變量，就是在程序執(zhí)行過(guò)程中，其值是可以改變的量變量，就是在程序執(zhí)行過(guò)程中，其值是可以改變的量?？梢岳米兞看鎯?chǔ)程序執(zhí)行過(guò)程中涉及的數(shù)據(jù)?？梢岳米兞看鎯?chǔ)程序執(zhí)行過(guò)程中涉及的數(shù)據(jù)。 變量由變量名和變量值構(gòu)成，其類型與常量一樣。變變量由變量名和變量值構(gòu)成，其類型與常量一樣。變量名不能與命令和函數(shù)名相同量名不能與命令和函數(shù)名相同 在在SQL Server 2008系統(tǒng)中，存在兩種類型的變量：系統(tǒng)中，存在兩種類型的變量：一種是系統(tǒng)定義和維護(hù)的一種是系統(tǒng)定義和維護(hù)的全局變量全局變量；另一種是用戶定；另一種是用戶定義用來(lái)保存中間結(jié)果的義用來(lái)保存中間結(jié)果的局部變量局部變量。5.35.45.

18、5數(shù)據(jù)庫(kù)原理與數(shù)據(jù)庫(kù)原理與SQL ServerSQL Server1 1、常量與變量、常量與變量1) 系統(tǒng)全局變量系統(tǒng)全局變量 通常將系統(tǒng)全局變量的值賦給局部變量以便通常將系統(tǒng)全局變量的值賦給局部變量以便保存和處理。保存和處理。全局變量以兩個(gè)全局變量以兩個(gè)符號(hào)開(kāi)頭符號(hào)開(kāi)頭。 例如：例如：error,rowcount等等。等等。 用戶可以通過(guò)全局變量來(lái)檢測(cè)系統(tǒng)的參數(shù)值用戶可以通過(guò)全局變量來(lái)檢測(cè)系統(tǒng)的參數(shù)值。 例：例：SELECT version：記錄當(dāng)前版本信：記錄當(dāng)前版本信息。息。數(shù)據(jù)庫(kù)原理與數(shù)據(jù)庫(kù)原理與SQL ServerSQL Server1 1、常量與變量、常量與變量2

19、) 局部變量局部變量 局部變量是作用域局限在一定范圍內(nèi)的局部變量是作用域局限在一定范圍內(nèi)的Transact-SQL對(duì)象。對(duì)象。 局部變量被引用時(shí)要在其名稱前加上局部變量被引用時(shí)要在其名稱前加上標(biāo)志標(biāo)志，而且必須先用，而且必須先用DECLARE命令定義命令定義后才可后才可以使用。以使用。數(shù)據(jù)庫(kù)原理與數(shù)據(jù)庫(kù)原理與SQL ServerSQL Server1 1、常量與變量、常量與變量2) 局部變量局部變量 聲明局部變量聲明局部變量 ，其語(yǔ)法如下，其語(yǔ)法如下: DECLARE 變量名變量名 變量類型變量類型 ,變量名變量名 變量類型變量類型 給變量賦值，其語(yǔ)法如下給變量賦值，其語(yǔ)法如

20、下: Set 變量名稱變量名稱=表達(dá)式表達(dá)式 Select 變量名稱變量名稱=表達(dá)式表達(dá)式,n 其中，其中，變量名稱是除變量名稱是除cursor、text、ntext、image外的任何類型外的任何類型變量名。變量名。 例：例： DECLARE hello char(20) Set hello=hello,China!數(shù)據(jù)庫(kù)原理與數(shù)據(jù)庫(kù)原理與SQL ServerSQL Server1 1、常量與變量、常量與變量2) 局部變量局部變量 提示：提示： SELECT語(yǔ)句給局部變量賦值，可以一次給多語(yǔ)句給局部變量賦值，可以一次給多個(gè)變量賦值；而個(gè)變量賦值；而SET 一次只能給一個(gè)變量

21、賦值。一次只能給一個(gè)變量賦值。 局部變量的輸出可以使用局部變量的輸出可以使用PRINT。 聲明一個(gè)變量后，該變量將被初始化為聲明一個(gè)變量后，該變量將被初始化為NULL。數(shù)據(jù)庫(kù)原理與數(shù)據(jù)庫(kù)原理與SQL ServerSQL Server1 1、常量與變量、常量與變量【例例】 USE grademanager GO DECLARE sex char(2) SET sex=女女 SELECT sno,sname,sdept FROM student WHERE ssex=sex數(shù)據(jù)庫(kù)原理與數(shù)據(jù)庫(kù)原理與SQL ServerSQL Server1 1、常量與變量、常量與

22、變量Go命令命令 GO不是不是sql語(yǔ)句，是語(yǔ)句，是sqlcmd和和osql實(shí)用工具及實(shí)用工具及SSMS編譯器可識(shí)別的命令；編譯器可識(shí)別的命令； 作用：作用：是將當(dāng)前批的是將當(dāng)前批的sql語(yǔ)句信號(hào)；當(dāng)前批處語(yǔ)句信號(hào)；當(dāng)前批處理由上一個(gè)理由上一個(gè)GO命令后所有語(yǔ)句組成；命令后所有語(yǔ)句組成； 批處理：批處理：指多條語(yǔ)句放在一起依次執(zhí)行，批指多條語(yǔ)句放在一起依次執(zhí)行，批處理語(yǔ)句之間用處理語(yǔ)句之間用GO隔開(kāi)；隔開(kāi)； GO命令不能和命令不能和SQL語(yǔ)句同一行中，但可包含語(yǔ)句同一行中，但可包含注釋；注釋；數(shù)據(jù)庫(kù)原理與數(shù)據(jù)庫(kù)原理與SQL ServerSQL Server5.4 Transa

23、ct-SQL5.4 Transact-SQL編程基礎(chǔ)編程基礎(chǔ)2. 表達(dá)式表達(dá)式 在在Transact-SQL語(yǔ)言中，表達(dá)式由變量、常語(yǔ)言中，表達(dá)式由變量、常量、運(yùn)算符、函數(shù)等元素組成。表達(dá)式可以量、運(yùn)算符、函數(shù)等元素組成。表達(dá)式可以在查詢語(yǔ)句中的任何位置使用。在查詢語(yǔ)句中的任何位置使用。 例如，檢索數(shù)據(jù)的條件，指定數(shù)據(jù)的值等。例如，檢索數(shù)據(jù)的條件，指定數(shù)據(jù)的值等。 數(shù)據(jù)庫(kù)原理與數(shù)據(jù)庫(kù)原理與SQL ServerSQL Server5.4 Transact-SQL5.4 Transact-SQL編程基礎(chǔ)編程基礎(chǔ)【例】【例】數(shù)據(jù)庫(kù)原理與數(shù)據(jù)庫(kù)原理與SQL Serv

24、erSQL Server5.4 Transact-SQL5.4 Transact-SQL編程基礎(chǔ)編程基礎(chǔ)3. Transact-SQL流程控制流程控制 結(jié)構(gòu)化程序設(shè)計(jì)語(yǔ)言的基本結(jié)構(gòu)是順序結(jié)構(gòu)結(jié)構(gòu)化程序設(shè)計(jì)語(yǔ)言的基本結(jié)構(gòu)是順序結(jié)構(gòu)、條件分支結(jié)構(gòu)和循環(huán)結(jié)構(gòu)。、條件分支結(jié)構(gòu)和循環(huán)結(jié)構(gòu)。 在在Transact-SQL語(yǔ)言中，流程控制語(yǔ)句就是語(yǔ)言中，流程控制語(yǔ)句就是用來(lái)控制程序執(zhí)行流程的語(yǔ)句，也稱流控制用來(lái)控制程序執(zhí)行流程的語(yǔ)句，也稱流控制語(yǔ)句或控制流語(yǔ)句。語(yǔ)句或控制流語(yǔ)句。數(shù)據(jù)庫(kù)原理與數(shù)據(jù)庫(kù)原理與SQL ServerSQL Server3. Transact-SQL3. Transa

25、ct-SQL流程控制流程控制1) BEGINEND語(yǔ)句塊語(yǔ)句塊 BEGINEND可以定義可以定義Transact-SQL語(yǔ)句塊，這些語(yǔ)句塊，這些語(yǔ)句塊作為一組語(yǔ)句執(zhí)行，允許語(yǔ)句嵌套。關(guān)鍵字語(yǔ)句塊作為一組語(yǔ)句執(zhí)行，允許語(yǔ)句嵌套。關(guān)鍵字BEGIN定義定義Transact-SQL語(yǔ)句的起始位置，語(yǔ)句的起始位置，END定定義同一塊義同一塊Transact-SQL語(yǔ)句的結(jié)尾。語(yǔ)句的結(jié)尾。 它的語(yǔ)法格式如下。它的語(yǔ)法格式如下。 BEGIN sql_statement|statement_block END數(shù)據(jù)庫(kù)原理與數(shù)據(jù)庫(kù)原理與SQL ServerSQL Server3. Transac

26、t-SQL3. Transact-SQL流程控制流程控制2) IFELSE條件語(yǔ)句條件語(yǔ)句 用于指定用于指定Transact-SQL語(yǔ)句的執(zhí)行條件。如果條件為語(yǔ)句的執(zhí)行條件。如果條件為真，則執(zhí)行條件表達(dá)式后面的真，則執(zhí)行條件表達(dá)式后面的Transact-SQL語(yǔ)句。當(dāng)語(yǔ)句。當(dāng)條件為假時(shí)，可以用條件為假時(shí)，可以用ELSE關(guān)鍵字指定要執(zhí)行的關(guān)鍵字指定要執(zhí)行的Transact-SQL語(yǔ)句。語(yǔ)句。 它的語(yǔ)法格式如下。它的語(yǔ)法格式如下。 IF Boolean_expression sql_statement|statement_block ELSE sql_statement|statement_blo

27、ck 數(shù)據(jù)庫(kù)原理與數(shù)據(jù)庫(kù)原理與SQL ServerSQL Server3. Transact-SQL3. Transact-SQL流程控制流程控制例如，使用例如，使用IFELSE條件語(yǔ)句查詢李勇同學(xué)的條件語(yǔ)句查詢李勇同學(xué)的家庭住址，如果空，則顯示家庭住址，如果空，則顯示“家庭地址不詳家庭地址不詳”，否則顯示其家庭地址。，否則顯示其家庭地址。 USE grademanager GO IF (SELECT saddress FROM student WHERE sname=李勇李勇) IS NULL BEGIN PRINT 家庭地址不詳家庭地址不詳 END ELSE SELECT

28、 saddress FROM student WHERE sname=李勇李勇數(shù)據(jù)庫(kù)原理與數(shù)據(jù)庫(kù)原理與SQL ServerSQL Server3. Transact-SQL3. Transact-SQL流程控制流程控制3) CASE分支語(yǔ)句分支語(yǔ)句 CASE關(guān)鍵字可根據(jù)表達(dá)式的真假來(lái)確定是否關(guān)鍵字可根據(jù)表達(dá)式的真假來(lái)確定是否返回某個(gè)值，可以允許使用表達(dá)式的任何位返回某個(gè)值，可以允許使用表達(dá)式的任何位置使用這一關(guān)鍵字。使用置使用這一關(guān)鍵字。使用CASE語(yǔ)句可以進(jìn)行語(yǔ)句可以進(jìn)行多個(gè)分支的選擇。多個(gè)分支的選擇。 CASE語(yǔ)句具有如下兩種格式。語(yǔ)句具有如下兩種格式。 簡(jiǎn)單格式：將某個(gè)

29、表達(dá)式與一組簡(jiǎn)單表達(dá)式進(jìn)簡(jiǎn)單格式：將某個(gè)表達(dá)式與一組簡(jiǎn)單表達(dá)式進(jìn)行比較以確定結(jié)果。行比較以確定結(jié)果。 搜索格式：計(jì)算一組布爾表達(dá)式以確定結(jié)果。搜索格式：計(jì)算一組布爾表達(dá)式以確定結(jié)果。數(shù)據(jù)庫(kù)原理與數(shù)據(jù)庫(kù)原理與SQL ServerSQL Server3. Transact-SQL3. Transact-SQL流程控制流程控制3) CASE分支語(yǔ)句分支語(yǔ)句 (1) 簡(jiǎn)單簡(jiǎn)單CASE格式格式的語(yǔ)法如下。的語(yǔ)法如下。 CASE WHEN THEN WHEN THEN ELSE END 簡(jiǎn)單簡(jiǎn)單CASE格式格式將某個(gè)表達(dá)式與一組簡(jiǎn)單表達(dá)式進(jìn)行將某個(gè)表達(dá)式與一組簡(jiǎn)單表達(dá)式進(jìn)行比較以確定結(jié)果

30、比較以確定結(jié)果數(shù)據(jù)庫(kù)原理與數(shù)據(jù)庫(kù)原理與SQL ServerSQL Server3. Transact-SQL3. Transact-SQL流程控制流程控制3) CASE分支語(yǔ)句分支語(yǔ)句 (2) 搜索搜索CASE格式的語(yǔ)法如下。格式的語(yǔ)法如下。 CASE WHEN THEN WHEN THEN 結(jié)果結(jié)果 ELSE END CASE 搜索格式計(jì)算一組布爾表達(dá)式以確定搜索格式計(jì)算一組布爾表達(dá)式以確定結(jié)果。結(jié)果。數(shù)據(jù)庫(kù)原理與數(shù)據(jù)庫(kù)原理與SQL ServerSQL Server3. Transact-SQL3. Transact-SQL流程控制流程控制簡(jiǎn)單簡(jiǎn)單CASE

31、格式舉例格式舉例USE pubs GO SELECT Category = CASE type WHEN popular_comp THEN Popular Computing WHEN mod_cook THEN Modern Cooking WHEN business THEN Business WHEN psychology THEN Psychology WHEN trad_cook THEN Traditional Cooking ELSE Not yet categorized END, CAST(title AS varchar(25) AS Shortened Title, p

32、rice AS Price FROM titles WHERE price IS NOT NULL ORDER BY type, price COMPUTE AVG(price) BY type GO 數(shù)據(jù)庫(kù)原理與數(shù)據(jù)庫(kù)原理與SQL ServerSQL Server3. Transact-SQL3. Transact-SQL流程控制流程控制搜索搜索CASE格式格式 USE grademanager GO select sname as 姓名姓名,degree= case when degree=90 then A when degree=70 then B when degr

33、ee=60 then C when degree60 then D end from student a, sc b where a.sno=b.sno數(shù)據(jù)庫(kù)原理與數(shù)據(jù)庫(kù)原理與SQL ServerSQL Server3. Transact-SQL3. Transact-SQL流程控制流程控制4) WHILE循環(huán)語(yǔ)句循環(huán)語(yǔ)句 WHILE語(yǔ)句是設(shè)置重復(fù)執(zhí)行語(yǔ)句是設(shè)置重復(fù)執(zhí)行Transact-SQL語(yǔ)句或語(yǔ)語(yǔ)句或語(yǔ)句塊的條件。當(dāng)指定的條件為真時(shí)，重復(fù)執(zhí)行循環(huán)語(yǔ)句塊的條件。當(dāng)指定的條件為真時(shí)，重復(fù)執(zhí)行循環(huán)語(yǔ)句?？梢栽谘h(huán)體內(nèi)設(shè)置句?？梢栽谘h(huán)體內(nèi)設(shè)置BREAK和和CONTINUE關(guān)鍵

34、關(guān)鍵字，以便控制循環(huán)語(yǔ)句的執(zhí)行過(guò)程。字，以便控制循環(huán)語(yǔ)句的執(zhí)行過(guò)程。 其語(yǔ)法格式如下：其語(yǔ)法格式如下： WHILE BEGIN IF BREAK END數(shù)據(jù)庫(kù)原理與數(shù)據(jù)庫(kù)原理與SQL ServerSQL Server3. Transact-SQL3. Transact-SQL流程控制流程控制例如，使用例如，使用WHILE語(yǔ)句求語(yǔ)句求1100之和。之和。 DECLARE i int,sum int SELECT i=1,sum=0 /*或分兩行用或分兩行用set賦值賦值*/ WHILE i=100 BEGIN SET sum=sum+i SET i=i+1 END SELECT

35、 sum數(shù)據(jù)庫(kù)原理與數(shù)據(jù)庫(kù)原理與SQL ServerSQL Server3. Transact-SQL3. Transact-SQL流程控制流程控制4注釋注釋 注釋是程序代碼中不被執(zhí)行的文本字符串，用于對(duì)代注釋是程序代碼中不被執(zhí)行的文本字符串，用于對(duì)代碼進(jìn)行說(shuō)明或進(jìn)行診斷的部分語(yǔ)句。碼進(jìn)行說(shuō)明或進(jìn)行診斷的部分語(yǔ)句。 在在SQL Server 2008系統(tǒng)中，支持兩種注釋方式系統(tǒng)中，支持兩種注釋方式: 雙連字符雙連字符(-)：從雙連字符到行尾都是注釋內(nèi)容。從雙連字符到行尾都是注釋內(nèi)容。 正斜杠星號(hào)字符正斜杠星號(hào)字符(/*/)：開(kāi)始注釋對(duì)開(kāi)始注釋對(duì)(/*)和結(jié)束注釋對(duì)和結(jié)束注釋對(duì)

36、(*/)之間的所有內(nèi)容均視為注釋。之間的所有內(nèi)容均視為注釋。數(shù)據(jù)庫(kù)原理與數(shù)據(jù)庫(kù)原理與SQL ServerSQL Server5.4.2 5.4.2 常用函數(shù)常用函數(shù)數(shù)據(jù)庫(kù)原理與數(shù)據(jù)庫(kù)原理與SQL ServerSQL Server5.5 5.5 存存 儲(chǔ)儲(chǔ) 過(guò)過(guò) 程程【課堂任務(wù)課堂任務(wù)】 本節(jié)要求掌握存儲(chǔ)過(guò)程的概念及應(yīng)用。本節(jié)要求掌握存儲(chǔ)過(guò)程的概念及應(yīng)用。 存儲(chǔ)過(guò)程的概念存儲(chǔ)過(guò)程的概念 存儲(chǔ)過(guò)程的創(chuàng)建及管理存儲(chǔ)過(guò)程的創(chuàng)建及管理 存儲(chǔ)過(guò)程中參數(shù)的使用存儲(chǔ)過(guò)程中參數(shù)的使用數(shù)據(jù)庫(kù)原理與數(shù)據(jù)庫(kù)原理與SQL ServerSQL Server5.5.1

37、5.5.1 存儲(chǔ)過(guò)程概述存儲(chǔ)過(guò)程概述 1. 什么是存儲(chǔ)過(guò)程什么是存儲(chǔ)過(guò)程 存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程(Stored Procedure)是一組完成特定是一組完成特定功能的功能的SQL語(yǔ)句集（語(yǔ)句集（SQL語(yǔ)句語(yǔ)句+流程控制語(yǔ)句流程控制語(yǔ)句 ），經(jīng)編譯后存儲(chǔ)在數(shù)據(jù)庫(kù)中。），經(jīng)編譯后存儲(chǔ)在數(shù)據(jù)庫(kù)中。 存儲(chǔ)過(guò)程可包含程序流、編輯及對(duì)數(shù)據(jù)庫(kù)的存儲(chǔ)過(guò)程可包含程序流、編輯及對(duì)數(shù)據(jù)庫(kù)的查詢。它們可以接受參數(shù)、輸出參數(shù)、返回查詢。它們可以接受參數(shù)、輸出參數(shù)、返回單個(gè)或者多個(gè)結(jié)果集及返回值。單個(gè)或者多個(gè)結(jié)果集及返回值。數(shù)據(jù)庫(kù)原理與數(shù)據(jù)庫(kù)原理與SQL ServerSQL Server5.5.1 5.5.1

38、 存儲(chǔ)過(guò)程概述存儲(chǔ)過(guò)程概述 2. 存儲(chǔ)過(guò)程的分類存儲(chǔ)過(guò)程的分類 1) 用戶定義的存儲(chǔ)過(guò)程用戶定義的存儲(chǔ)過(guò)程 該類存儲(chǔ)過(guò)程是指封裝了可重用代碼的模塊或者該類存儲(chǔ)過(guò)程是指封裝了可重用代碼的模塊或者例程。存儲(chǔ)過(guò)程可以接受輸入函數(shù)、向客戶端返例程。存儲(chǔ)過(guò)程可以接受輸入函數(shù)、向客戶端返回表格或者標(biāo)量結(jié)果和消息、調(diào)用數(shù)據(jù)定義語(yǔ)言回表格或者標(biāo)量結(jié)果和消息、調(diào)用數(shù)據(jù)定義語(yǔ)言(DDL)和數(shù)據(jù)操作語(yǔ)言和數(shù)據(jù)操作語(yǔ)言(DML)語(yǔ)句，然后返回輸語(yǔ)句，然后返回輸入?yún)?shù)。在入?yún)?shù)。在SQL Server 2005中，用戶定義的存儲(chǔ)中，用戶定義的存儲(chǔ)過(guò)程有兩種：過(guò)程有兩種：Transact-SQL和和CLR。5.35.45

39、.5數(shù)據(jù)庫(kù)原理與數(shù)據(jù)庫(kù)原理與SQL ServerSQL Server5.5.1 5.5.1 存儲(chǔ)過(guò)程概述存儲(chǔ)過(guò)程概述 (1) Transact-SQL存儲(chǔ)過(guò)程是指保存的存儲(chǔ)過(guò)程是指保存的Transact-SQL語(yǔ)句集合，可以接受和返回語(yǔ)句集合，可以接受和返回用戶提供的參數(shù)。用戶提供的參數(shù)。(2) CLR存儲(chǔ)過(guò)程是指對(duì)存儲(chǔ)過(guò)程是指對(duì)Microsoft .NET Framework公共語(yǔ)言運(yùn)行時(shí)公共語(yǔ)言運(yùn)行時(shí)(CLR)方法的方法的引用，可以接受和返回用戶提供的參數(shù)。引用，可以接受和返回用戶提供的參數(shù)。它們?cè)谒鼈冊(cè)?NET Framework程序集中作為類程序集中作為類的公共靜態(tài)方法實(shí)現(xiàn)。的公共靜態(tài)

40、方法實(shí)現(xiàn)。數(shù)據(jù)庫(kù)原理與數(shù)據(jù)庫(kù)原理與SQL ServerSQL Server5.5.1 5.5.1 存儲(chǔ)過(guò)程概述存儲(chǔ)過(guò)程概述 2) 擴(kuò)展存儲(chǔ)過(guò)程擴(kuò)展存儲(chǔ)過(guò)程 擴(kuò)展存儲(chǔ)過(guò)程允許使用編程語(yǔ)言擴(kuò)展存儲(chǔ)過(guò)程允許使用編程語(yǔ)言(例如例如C語(yǔ)言語(yǔ)言)建立自己的外部例程。擴(kuò)展存儲(chǔ)過(guò)程是指建立自己的外部例程。擴(kuò)展存儲(chǔ)過(guò)程是指Microsoft SQL Server 2005的實(shí)例可以動(dòng)態(tài)加的實(shí)例可以動(dòng)態(tài)加載和運(yùn)行載和運(yùn)行DLL。擴(kuò)展存儲(chǔ)過(guò)程直接在。擴(kuò)展存儲(chǔ)過(guò)程直接在SQL Server 2005的實(shí)例的地址空間中運(yùn)行，可以的實(shí)例的地址空間中運(yùn)行，可以使用使用SQL Server擴(kuò)展存儲(chǔ)過(guò)程擴(kuò)展存

41、儲(chǔ)過(guò)程API完成編程。完成編程。數(shù)據(jù)庫(kù)原理與數(shù)據(jù)庫(kù)原理與SQL ServerSQL Server5.5.1 5.5.1 存儲(chǔ)過(guò)程概述存儲(chǔ)過(guò)程概述 3) 系統(tǒng)存儲(chǔ)過(guò)程系統(tǒng)存儲(chǔ)過(guò)程 SQL Server 2005中的許多管理活動(dòng)都通過(guò)執(zhí)行一種中的許多管理活動(dòng)都通過(guò)執(zhí)行一種特殊的存儲(chǔ)過(guò)程實(shí)現(xiàn)，這種存儲(chǔ)過(guò)程被稱為系統(tǒng)存儲(chǔ)特殊的存儲(chǔ)過(guò)程實(shí)現(xiàn)，這種存儲(chǔ)過(guò)程被稱為系統(tǒng)存儲(chǔ)過(guò)程。例如，過(guò)程。例如，sys.sp_changedbowner就是一個(gè)系統(tǒng)存就是一個(gè)系統(tǒng)存儲(chǔ)過(guò)程。儲(chǔ)過(guò)程。 雖然雖然SQL Server 2005中的系統(tǒng)存儲(chǔ)過(guò)程被放在中的系統(tǒng)存儲(chǔ)過(guò)程被放在master數(shù)據(jù)庫(kù)中，但是仍可

42、以在其他數(shù)據(jù)庫(kù)中對(duì)其進(jìn)數(shù)據(jù)庫(kù)中，但是仍可以在其他數(shù)據(jù)庫(kù)中對(duì)其進(jìn)行調(diào)用，而且在調(diào)用時(shí)不必再在存儲(chǔ)過(guò)程名前加上數(shù)行調(diào)用，而且在調(diào)用時(shí)不必再在存儲(chǔ)過(guò)程名前加上數(shù)據(jù)庫(kù)名。據(jù)庫(kù)名。 例如查看該存儲(chǔ)過(guò)程：例如查看該存儲(chǔ)過(guò)程：EXEC sp_helptext sp_changedbowner。數(shù)據(jù)庫(kù)原理與數(shù)據(jù)庫(kù)原理與SQL ServerSQL Server5.5.1 5.5.1 存儲(chǔ)過(guò)程概述存儲(chǔ)過(guò)程概述 3. 存儲(chǔ)過(guò)程的特點(diǎn)存儲(chǔ)過(guò)程的特點(diǎn) (1) 接受輸入?yún)?shù)并以輸出參數(shù)的格式向調(diào)用過(guò)程或接受輸入?yún)?shù)并以輸出參數(shù)的格式向調(diào)用過(guò)程或者批處理返回多個(gè)值。者批處理返回多個(gè)值。 (2) 包含用于在

43、數(shù)據(jù)庫(kù)中執(zhí)行操作包含用于在數(shù)據(jù)庫(kù)中執(zhí)行操作(包括調(diào)用其他過(guò)程包括調(diào)用其他過(guò)程)的編程語(yǔ)句。的編程語(yǔ)句。 (3) 向調(diào)用過(guò)程或者批處理返回狀態(tài)值，以指明成功向調(diào)用過(guò)程或者批處理返回狀態(tài)值，以指明成功或者失敗或者失敗(以及失敗的原因以及失敗的原因)。 注意：存儲(chǔ)過(guò)程與函數(shù)不同，因?yàn)榇鎯?chǔ)過(guò)程不返回取注意：存儲(chǔ)過(guò)程與函數(shù)不同，因?yàn)榇鎯?chǔ)過(guò)程不返回取代其名稱的值，也不能直接在表達(dá)式中使用。代其名稱的值，也不能直接在表達(dá)式中使用。數(shù)據(jù)庫(kù)原理與數(shù)據(jù)庫(kù)原理與SQL ServerSQL Server5.5.1 5.5.1 存儲(chǔ)過(guò)程概述存儲(chǔ)過(guò)程概述 Transact-SQL程序有以下幾點(diǎn)優(yōu)點(diǎn)程序有

44、以下幾點(diǎn)優(yōu)點(diǎn): (1) 存儲(chǔ)過(guò)程與其他應(yīng)用程序共享應(yīng)用程序邏輯，因而確保了數(shù)存儲(chǔ)過(guò)程與其他應(yīng)用程序共享應(yīng)用程序邏輯，因而確保了數(shù)據(jù)訪問(wèn)和修改的一致性。據(jù)訪問(wèn)和修改的一致性。 (2) 存儲(chǔ)過(guò)程提供了安全機(jī)制。即使是沒(méi)有訪問(wèn)存儲(chǔ)過(guò)程引用的存儲(chǔ)過(guò)程提供了安全機(jī)制。即使是沒(méi)有訪問(wèn)存儲(chǔ)過(guò)程引用的表或者視圖權(quán)限的用戶，也可以被授權(quán)執(zhí)行該存儲(chǔ)過(guò)程。表或者視圖權(quán)限的用戶，也可以被授權(quán)執(zhí)行該存儲(chǔ)過(guò)程。 (3) 存儲(chǔ)過(guò)程允許模塊化程序設(shè)計(jì)。存儲(chǔ)過(guò)程一旦創(chuàng)建，以后即存儲(chǔ)過(guò)程允許模塊化程序設(shè)計(jì)。存儲(chǔ)過(guò)程一旦創(chuàng)建，以后即可在程序中調(diào)用任意次。這可以改進(jìn)應(yīng)用程序的可維護(hù)性，并可在程序中調(diào)用任意次。這可以改進(jìn)應(yīng)用程序的可維

45、護(hù)性，并允許應(yīng)用程序統(tǒng)一訪問(wèn)數(shù)據(jù)庫(kù)。允許應(yīng)用程序統(tǒng)一訪問(wèn)數(shù)據(jù)庫(kù)。 (4) 存儲(chǔ)過(guò)程可以減少網(wǎng)絡(luò)通信流量。用戶可以通過(guò)發(fā)送一個(gè)單存儲(chǔ)過(guò)程可以減少網(wǎng)絡(luò)通信流量。用戶可以通過(guò)發(fā)送一個(gè)單獨(dú)的語(yǔ)句實(shí)現(xiàn)一個(gè)復(fù)雜的操作，而不需要在網(wǎng)絡(luò)上發(fā)送幾百個(gè)獨(dú)的語(yǔ)句實(shí)現(xiàn)一個(gè)復(fù)雜的操作，而不需要在網(wǎng)絡(luò)上發(fā)送幾百個(gè)Transact-SQL代碼，這樣減少了在服務(wù)器和客戶機(jī)之間傳遞請(qǐng)代碼，這樣減少了在服務(wù)器和客戶機(jī)之間傳遞請(qǐng)求的數(shù)量。求的數(shù)量。數(shù)據(jù)庫(kù)原理與數(shù)據(jù)庫(kù)原理與SQL ServerSQL Server5.5.2 5.5.2 使用存儲(chǔ)過(guò)程使用存儲(chǔ)過(guò)程 1、創(chuàng)建存儲(chǔ)過(guò)程、創(chuàng)建存儲(chǔ)過(guò)程 使用使用CREATE

46、PROCEDURE語(yǔ)句創(chuàng)建存儲(chǔ)過(guò)程的語(yǔ)語(yǔ)句創(chuàng)建存儲(chǔ)過(guò)程的語(yǔ)法如下法如下 CREATE PROCEDURE procedure_name parameter data_type =defaultOUTPUT,n AS sql_statementn數(shù)據(jù)庫(kù)原理與數(shù)據(jù)庫(kù)原理與SQL ServerSQL Server1 1、創(chuàng)建存儲(chǔ)過(guò)程、創(chuàng)建存儲(chǔ)過(guò)程其中各參數(shù)含義如下：其中各參數(shù)含義如下： (1) procedure_name：存儲(chǔ)過(guò)程的名稱。：存儲(chǔ)過(guò)程的名稱。 (2) parameter：過(guò)程中的參數(shù)。在：過(guò)程中的參數(shù)。在CREATE PROCEDURE語(yǔ)語(yǔ)句中可以聲明一個(gè)或者多個(gè)參

47、數(shù)。句中可以聲明一個(gè)或者多個(gè)參數(shù)。 (3) data_type：參數(shù)的數(shù)據(jù)類型。：參數(shù)的數(shù)據(jù)類型。 (4) default：參數(shù)的默認(rèn)值。如果定義：參數(shù)的默認(rèn)值。如果定義default值，則無(wú)需指定此值，則無(wú)需指定此參數(shù)的值即可執(zhí)行過(guò)程。默認(rèn)值必須是常量或者參數(shù)的值即可執(zhí)行過(guò)程。默認(rèn)值必須是常量或者NULL。如果。如果過(guò)程使用帶過(guò)程使用帶LIKE關(guān)鍵字的參數(shù)，則可包含下列通配符：關(guān)鍵字的參數(shù)，則可包含下列通配符：%、_。 (5) OUTPUT：指示參數(shù)的輸出參數(shù)。此選項(xiàng)的值可以返回給調(diào)：指示參數(shù)的輸出參數(shù)。此選項(xiàng)的值可以返回給調(diào)用的用的EXECUTE的語(yǔ)句。的語(yǔ)句。 (6) ：要包含在過(guò)程中的

48、一個(gè)或者多個(gè)：要包含在過(guò)程中的一個(gè)或者多個(gè)Transact-SQL語(yǔ)句。語(yǔ)句。 數(shù)據(jù)庫(kù)原理與數(shù)據(jù)庫(kù)原理與SQL ServerSQL Server1 1、創(chuàng)建存儲(chǔ)過(guò)程、創(chuàng)建存儲(chǔ)過(guò)程例題：下面創(chuàng)建一個(gè)基本存儲(chǔ)過(guò)程，從數(shù)據(jù)庫(kù)例題：下面創(chuàng)建一個(gè)基本存儲(chǔ)過(guò)程，從數(shù)據(jù)庫(kù)grademanager的的student表中檢索出姓表中檢索出姓“李李”的學(xué)生的的學(xué)生的學(xué)號(hào)、姓名、班級(jí)號(hào)及家庭地址等信息。學(xué)號(hào)、姓名、班級(jí)號(hào)及家庭地址等信息。 具體語(yǔ)句如下：具體語(yǔ)句如下： USE grademanager GO CREATE PROCEDURE pro_學(xué)生信息學(xué)生信息 AS SELECT sno,s

49、name,classno,address FROM student WHERE sname LIKE 李李% ORDER BY sno GO數(shù)據(jù)庫(kù)原理與數(shù)據(jù)庫(kù)原理與SQL ServerSQL Server1 1、創(chuàng)建存儲(chǔ)過(guò)程、創(chuàng)建存儲(chǔ)過(guò)程執(zhí)行存儲(chǔ)過(guò)程執(zhí)行存儲(chǔ)過(guò)程“pro_學(xué)生信息學(xué)生信息”，返回所，返回所有有“姓李姓李”的學(xué)生信息的學(xué)生信息 USE grademanager GO EXEC pro_學(xué)生信息學(xué)生信息 或或 pro_學(xué)生信息學(xué)生信息 數(shù)據(jù)庫(kù)原理與數(shù)據(jù)庫(kù)原理與SQL ServerSQL Server1 1、創(chuàng)建存儲(chǔ)過(guò)程、創(chuàng)建存儲(chǔ)過(guò)程建議使用下面的

50、方式建立存儲(chǔ)過(guò)程。建議使用下面的方式建立存儲(chǔ)過(guò)程。 if exists (select name from sysobjects where name = pro_學(xué)生信息學(xué)生信息 and type = p) drop procedure pro_學(xué)生信息學(xué)生信息 Go CREATE PROCEDURE pro_學(xué)生信息學(xué)生信息 AS SELECT sno,sname,classno,saddress FROM student WHERE sname LIKE 李李% ORDER BY sno GO數(shù)據(jù)庫(kù)原理與數(shù)據(jù)庫(kù)原理與SQL ServerSQL Server5.5.2 5

51、.5.2 使用存儲(chǔ)過(guò)程使用存儲(chǔ)過(guò)程 2. 使用存儲(chǔ)過(guò)程參數(shù)使用存儲(chǔ)過(guò)程參數(shù) 1) 參數(shù)的定義參數(shù)的定義 SQL Server 2005的存儲(chǔ)過(guò)程可以使用兩種類型的的存儲(chǔ)過(guò)程可以使用兩種類型的參數(shù)：輸入?yún)?shù)和輸出參數(shù)。參數(shù)用于在存儲(chǔ)過(guò)參數(shù)：輸入?yún)?shù)和輸出參數(shù)。參數(shù)用于在存儲(chǔ)過(guò)程以及應(yīng)用程序之間交換數(shù)據(jù)。程以及應(yīng)用程序之間交換數(shù)據(jù)。 (1) 輸入?yún)?shù)允許用戶將數(shù)據(jù)值傳遞到存儲(chǔ)過(guò)程或者函輸入?yún)?shù)允許用戶將數(shù)據(jù)值傳遞到存儲(chǔ)過(guò)程或者函數(shù)。數(shù)。 (2) 輸出參數(shù)允許存儲(chǔ)過(guò)程將數(shù)據(jù)值或者游標(biāo)變量傳遞輸出參數(shù)允許存儲(chǔ)過(guò)程將數(shù)據(jù)值或者游標(biāo)變量傳遞給用戶。給用戶。 (3) 每個(gè)存儲(chǔ)過(guò)程向用戶返回一個(gè)整數(shù)代碼，如果

52、存儲(chǔ)每個(gè)存儲(chǔ)過(guò)程向用戶返回一個(gè)整數(shù)代碼，如果存儲(chǔ)過(guò)程沒(méi)有明顯設(shè)置返回代碼的值，返回代碼為零。過(guò)程沒(méi)有明顯設(shè)置返回代碼的值，返回代碼為零。數(shù)據(jù)庫(kù)原理與數(shù)據(jù)庫(kù)原理與SQL ServerSQL Server2. 2. 使用存儲(chǔ)過(guò)程參數(shù)使用存儲(chǔ)過(guò)程參數(shù)2) 輸入?yún)?shù)：在執(zhí)行存儲(chǔ)過(guò)程時(shí)需指定值輸入?yún)?shù)：在執(zhí)行存儲(chǔ)過(guò)程時(shí)需指定值例：例： USE grademanager GO CREATE PROCEDURE pro_學(xué)生學(xué)生_性別性別_信息信息 性別性別 NVARCHAR(10) AS SELECT a.sno,sname,ssex,cname,degree FROM student

53、a,sc b,course c WHERE a.sno=b.sno and o=o and cname=數(shù)據(jù)庫(kù)數(shù)據(jù)庫(kù) and ssex=性別性別 GO數(shù)據(jù)庫(kù)原理與數(shù)據(jù)庫(kù)原理與SQL ServerSQL Server2. 2. 使用存儲(chǔ)過(guò)程參數(shù)使用存儲(chǔ)過(guò)程參數(shù)執(zhí)行帶有輸入?yún)?shù)的存儲(chǔ)過(guò)程時(shí)，執(zhí)行帶有輸入?yún)?shù)的存儲(chǔ)過(guò)程時(shí)，SQL Server 2005提供了兩種傳遞參數(shù)的方式。提供了兩種傳遞參數(shù)的方式。 (1) 按按位置位置傳遞。在執(zhí)行過(guò)程的語(yǔ)句中，直接給出參傳遞。在執(zhí)行過(guò)程的語(yǔ)句中，直接給出參數(shù)的值。當(dāng)有多個(gè)參數(shù)時(shí)，參數(shù)傳遞的順序就是參數(shù)數(shù)的值。當(dāng)有多個(gè)參數(shù)時(shí)，參數(shù)傳遞的順序就是

54、參數(shù)定義的順序。定義的順序。 EXEC pro_學(xué)生學(xué)生_性別性別_信息信息 女女 (2) 通過(guò)通過(guò)參數(shù)名參數(shù)名傳遞。在執(zhí)行存儲(chǔ)過(guò)程的語(yǔ)句中，使傳遞。在執(zhí)行存儲(chǔ)過(guò)程的語(yǔ)句中，使用用“參數(shù)名參數(shù)名=參數(shù)值參數(shù)值” 。好處，參數(shù)可以以。好處，參數(shù)可以以任意順序任意順序給出。給出。 EXEC pro_學(xué)生學(xué)生_性別性別_信息信息 性別性別=男男 數(shù)據(jù)庫(kù)原理與數(shù)據(jù)庫(kù)原理與SQL ServerSQL Server2. 2. 使用存儲(chǔ)過(guò)程參數(shù)使用存儲(chǔ)過(guò)程參數(shù)3) 使用默認(rèn)參數(shù)值使用默認(rèn)參數(shù)值 如果希望不給出參數(shù)時(shí)也能夠正確運(yùn)行，則如果希望不給出參數(shù)時(shí)也能夠正確運(yùn)行，則可以通過(guò)給參數(shù)設(shè)置默

55、認(rèn)值來(lái)實(shí)現(xiàn)?？梢酝ㄟ^(guò)給參數(shù)設(shè)置默認(rèn)值來(lái)實(shí)現(xiàn)。 USE grademanagerGOCREATE PROCEDURE pro_學(xué)生學(xué)生_性別性別_信息信息性別性別 VARCHAR(2) =男男AS SELECT a.sno,sname,ssex,sname,cname,degree FROM student a,sc b,course c WHERE a.sno=b.sno and o=o and cname=數(shù)據(jù)庫(kù)數(shù)據(jù)庫(kù) and ssex=性別性別GO數(shù)據(jù)庫(kù)原理與數(shù)據(jù)庫(kù)原理與SQL ServerSQL Server2. 2. 使用存儲(chǔ)過(guò)程參數(shù)使用存儲(chǔ)過(guò)程參數(shù)4) 輸出參數(shù)輸出

56、參數(shù) 通過(guò)定義輸出參數(shù)，可以從存儲(chǔ)過(guò)程中返回一個(gè)或者通過(guò)定義輸出參數(shù)，可以從存儲(chǔ)過(guò)程中返回一個(gè)或者多個(gè)值。多個(gè)值。 為了使用輸出參數(shù)，必須在為了使用輸出參數(shù)，必須在CREATE PROCEDURE語(yǔ)句和語(yǔ)句和EXECUTE語(yǔ)句中指定關(guān)鍵字語(yǔ)句中指定關(guān)鍵字OUTPUT。 在執(zhí)行存儲(chǔ)過(guò)程時(shí)，如果忽略在執(zhí)行存儲(chǔ)過(guò)程時(shí)，如果忽略O(shè)UTPUT關(guān)鍵字，存關(guān)鍵字，存儲(chǔ)過(guò)程仍然會(huì)執(zhí)行但不返回值。儲(chǔ)過(guò)程仍然會(huì)執(zhí)行但不返回值。數(shù)據(jù)庫(kù)原理與數(shù)據(jù)庫(kù)原理與SQL ServerSQL Server2. 2. 使用存儲(chǔ)過(guò)程參數(shù)使用存儲(chǔ)過(guò)程參數(shù)4) 輸出參數(shù)輸出參數(shù)use grademanagergocreate procedure avgscorec_no char(3),avgs numeric outputasselect avgs=avg(degree) from scwhere cno=c_nogroup by cnogodeclare avgs numericexec avgscore c01,avgs output數(shù)據(jù)庫(kù)原理與數(shù)據(jù)庫(kù)原理與SQL Se