項目7學(xué)生成績管理數(shù)據(jù)庫存儲過程及觸發(fā)器的應(yīng)用

1、項目項目7 學(xué)生成績管理數(shù)據(jù)庫存儲過學(xué)生成績管理數(shù)據(jù)庫存儲過程及觸發(fā)器的應(yīng)用程及觸發(fā)器的應(yīng)用1. 能利用能利用SSMS進行存儲過程的創(chuàng)建、管理。進行存儲過程的創(chuàng)建、管理。2. 能利用能利用T-SQL語句進行存儲過程的創(chuàng)建、管理。語句進行存儲過程的創(chuàng)建、管理。3. 能利用能利用SSMS進行觸發(fā)器的創(chuàng)建、管理。進行觸發(fā)器的創(chuàng)建、管理。4. 能利用能利用T-SQL語句進行觸發(fā)器的創(chuàng)建、管理。語句進行觸發(fā)器的創(chuàng)建、管理。5能結(jié)合實際需求靈活地運用存儲過程、觸發(fā)器的能力。能結(jié)合實際需求靈活地運用存儲過程、觸發(fā)器的能力。能力目標能力目標項項目目描描述述在學(xué)生成績管理系統(tǒng)中，用戶經(jīng)常要進行各種操作，為了方便

2、操作，同時確保數(shù)據(jù)的完整性和唯一性，要求數(shù)據(jù)庫管理員能將一個復(fù)雜的操作過程打包，由數(shù)據(jù)庫服務(wù)器處理；同時能建立數(shù)據(jù)表之間的一種強制業(yè)務(wù)規(guī)則。為了達到上述目的，需要在數(shù)據(jù)庫中建立存儲過程和觸發(fā)器。本項目學(xué)習(xí)的內(nèi)容就是：首先學(xué)習(xí)SQL語言，包括SQL概念、優(yōu)點、SQL語言的基礎(chǔ)知識、常見的程序控制語句等；然后學(xué)習(xí)存儲過程的概念，學(xué)習(xí)如何在SSMS環(huán)境及T-SQL命令創(chuàng)建、管理學(xué)生成績管理數(shù)據(jù)庫中的存儲過程，接下來介紹觸發(fā)器的概念、作用，學(xué)習(xí)如何在SSMS環(huán)境及T-SQL命令創(chuàng)建、管理學(xué)生成績管理數(shù)據(jù)庫中的觸發(fā)器；最后通過課堂實訓(xùn)、課外實訓(xùn)來加強對數(shù)據(jù)庫存儲過程和觸發(fā)器的靈活應(yīng)用能力。學(xué)學(xué)習(xí)習(xí)任任務(wù)

3、務(wù)任務(wù)7.1：SQL語言概述任務(wù)7.2：學(xué)生成績管理數(shù)據(jù)庫中存儲過程的創(chuàng)建和管理任務(wù)7.3：學(xué)生成績管理數(shù)據(jù)庫中觸發(fā)器的創(chuàng)建和管理項目描述項目描述及及學(xué)習(xí)任務(wù)學(xué)習(xí)任務(wù)7.1 SQL 7.1 SQL 語言概述語言概述我們已經(jīng)學(xué)習(xí)了用我們已經(jīng)學(xué)習(xí)了用T-SQL命令創(chuàng)建數(shù)據(jù)庫、數(shù)據(jù)表、視圖、索引等，還有用命令創(chuàng)建數(shù)據(jù)庫、數(shù)據(jù)表、視圖、索引等，還有用T-SQL語言進行數(shù)據(jù)表數(shù)據(jù)的查詢，顯然，語言進行數(shù)據(jù)表數(shù)據(jù)的查詢，顯然，T-SQL語言非常重要，本學(xué)習(xí)任務(wù)主要語言非常重要，本學(xué)習(xí)任務(wù)主要介紹介紹T-SQL語言的基本知識。語言的基本知識。任務(wù)描述任務(wù)描述 7.1.1 T-SQL語言的概述語言的概述 SQ

4、L全稱是全稱是“結(jié)構(gòu)化查詢語言（結(jié)構(gòu)化查詢語言（Structured Query Language）”，最早的，最早的是是IBM的圣約瑟研究實驗室為其關(guān)系數(shù)據(jù)庫管理系統(tǒng)的圣約瑟研究實驗室為其關(guān)系數(shù)據(jù)庫管理系統(tǒng)SYSTEM R開發(fā)的一種查詢開發(fā)的一種查詢語言，它的前身是語言，它的前身是SQUARE語言。語言。SQL語言結(jié)構(gòu)簡潔，功能強大，簡單易學(xué)，語言結(jié)構(gòu)簡潔，功能強大，簡單易學(xué)，所以自從所以自從IBM公司公司1981年推出以來，年推出以來，SQL語言，得到了廣泛的應(yīng)用。如今無論語言，得到了廣泛的應(yīng)用。如今無論是像是像Oracle，Sybase，Informix，SQL server這些大型的數(shù)

5、據(jù)庫管理系統(tǒng)，還這些大型的數(shù)據(jù)庫管理系統(tǒng)，還是像是像Visual FoxPro，PowerBuilder這些微機上常用的數(shù)據(jù)庫開發(fā)系統(tǒng)，都支持這些微機上常用的數(shù)據(jù)庫開發(fā)系統(tǒng)，都支持SQL語言作為查詢語言。語言作為查詢語言。SQL是利用一些簡單的句子構(gòu)成基本的語法來存取數(shù)據(jù)庫內(nèi)容的語言，由于是利用一些簡單的句子構(gòu)成基本的語法來存取數(shù)據(jù)庫內(nèi)容的語言，由于SQL簡單易學(xué)，它已經(jīng)成為目前關(guān)系數(shù)據(jù)庫系統(tǒng)中使用最廣泛的語言。簡單易學(xué)，它已經(jīng)成為目前關(guān)系數(shù)據(jù)庫系統(tǒng)中使用最廣泛的語言。T-SQL是是Microsoft SQL Server提供的一種結(jié)構(gòu)化查詢語言。下面，主要介紹提供的一種結(jié)構(gòu)化查詢語言。下面，

6、主要介紹T-SQL語語言基礎(chǔ)知識。言基礎(chǔ)知識。7.1.1 T-SQL語言的概述語言的概述 1SQL的優(yōu)點的優(yōu)點（1）是非過程化的語言）是非過程化的語言SQL語言是應(yīng)用于數(shù)據(jù)庫的語言，是非過程化的語言，與一般的高級語言（例語言是應(yīng)用于數(shù)據(jù)庫的語言，是非過程化的語言，與一般的高級語言（例C、Java）是大不相同的。一般的高級語言在存取數(shù)據(jù)庫時，需要依照每一行程序）是大不相同的。一般的高級語言在存取數(shù)據(jù)庫時，需要依照每一行程序的順序處理許多動作，但是使用的順序處理許多動作，但是使用SQL時，只需要告訴數(shù)據(jù)庫需要什么數(shù)據(jù)，怎時，只需要告訴數(shù)據(jù)庫需要什么數(shù)據(jù)，怎么顯示就可以了，具體的內(nèi)部操作則由數(shù)據(jù)庫系

7、統(tǒng)來完成。例如，前面我們學(xué)過么顯示就可以了，具體的內(nèi)部操作則由數(shù)據(jù)庫系統(tǒng)來完成。例如，前面我們學(xué)過要在要在“學(xué)生表學(xué)生表”中查找姓名為中查找姓名為“李小民李小民”的學(xué)生記錄，使用簡單的一行命令即可：的學(xué)生記錄，使用簡單的一行命令即可：Select * From 學(xué)生表學(xué)生表 Where 姓名姓名=李小民李小民。（2）統(tǒng)一的語言）統(tǒng)一的語言SQL可用于所有用戶的可用于所有用戶的DB活動模型，包括系統(tǒng)管理員、數(shù)據(jù)庫管理員、活動模型，包括系統(tǒng)管理員、數(shù)據(jù)庫管理員、 應(yīng)用程應(yīng)用程序員、決策支持系統(tǒng)人員及許多其它類型的終端用戶?；镜男騿T、決策支持系統(tǒng)人員及許多其它類型的終端用戶?；镜腟QL 命令只需

8、很命令只需很少時間就能學(xué)會，最高級的命令在幾天內(nèi)便可掌握。少時間就能學(xué)會，最高級的命令在幾天內(nèi)便可掌握。 （3）是所有關(guān)系數(shù)據(jù)庫的公共語言）是所有關(guān)系數(shù)據(jù)庫的公共語言由于所有主要的關(guān)系數(shù)據(jù)庫管理系統(tǒng)都支持由于所有主要的關(guān)系數(shù)據(jù)庫管理系統(tǒng)都支持SQL語言，用戶可將使用語言，用戶可將使用SQL的技的技能從一個關(guān)系型數(shù)據(jù)庫管理系統(tǒng)轉(zhuǎn)到另一個。所有用能從一個關(guān)系型數(shù)據(jù)庫管理系統(tǒng)轉(zhuǎn)到另一個。所有用SQL編寫的程序都是可以編寫的程序都是可以移植的。移植的。7.1.1 T-SQL語言的概述語言的概述 2SQL語言的分類語言的分類SQL語言按照用途可以分為如下語言按照用途可以分為如下4個部分：個部分： （1）

9、數(shù)據(jù)查詢語言）數(shù)據(jù)查詢語言DQL（Data Query Language）查詢數(shù)據(jù)庫的基本功能，查詢操作通過查詢數(shù)據(jù)庫的基本功能，查詢操作通過SQL數(shù)據(jù)查詢語言來實現(xiàn)，例如，用數(shù)據(jù)查詢語言來實現(xiàn)，例如，用SELECT查詢表中的數(shù)據(jù)。查詢表中的數(shù)據(jù)。 （2）數(shù)據(jù)定義語言）數(shù)據(jù)定義語言DDL（Data Manipulation Language）可以用可以用SQL語言建立一個對象，例用語言建立一個對象，例用SQL語言創(chuàng)建及管理數(shù)據(jù)庫、數(shù)據(jù)表、視圖、語言創(chuàng)建及管理數(shù)據(jù)庫、數(shù)據(jù)表、視圖、索引等，主要通過對每個對象的索引等，主要通過對每個對象的CREATE、ALTER、DROP語句來實現(xiàn)。語句來實現(xiàn)。

10、（3）數(shù)據(jù)操縱語言）數(shù)據(jù)操縱語言DML（Data Definition Language ）用來操縱數(shù)據(jù)庫中數(shù)據(jù)的命令。例如，使用插入（用來操縱數(shù)據(jù)庫中數(shù)據(jù)的命令。例如，使用插入（INSERT）、修改（）、修改（UPDATE）、）、刪除（刪除（DELETE）記錄的操作屬于數(shù)據(jù)操縱語言。）記錄的操作屬于數(shù)據(jù)操縱語言。（4）數(shù)據(jù)控制語言）數(shù)據(jù)控制語言DCL（Data Control Language ）是一種對數(shù)據(jù)訪問權(quán)進行控制的指令。是一種對數(shù)據(jù)訪問權(quán)進行控制的指令。SQL通過對數(shù)據(jù)庫用戶的授權(quán)和收權(quán)命令來通過對數(shù)據(jù)庫用戶的授權(quán)和收權(quán)命令來實現(xiàn)數(shù)據(jù)的存取控制，以保證數(shù)據(jù)庫的完整性。實現(xiàn)數(shù)據(jù)的存取

11、控制，以保證數(shù)據(jù)庫的完整性。7.1.2 T-SQL語言基礎(chǔ)知識語言基礎(chǔ)知識（1）字符串常量）字符串常量用單引號括起來，例如：用單引號括起來，例如：China、How are you、OBear（2）Bit常量常量Bit常量使用數(shù)字常量使用數(shù)字0或或1表示。如果使用一個大于表示。如果使用一個大于1的數(shù)字，則該數(shù)字轉(zhuǎn)換為的數(shù)字，則該數(shù)字轉(zhuǎn)換為1。（3）整型常量）整型常量Integer常量必須全部為數(shù)字，沒有小數(shù)，例如：常量必須全部為數(shù)字，沒有小數(shù)，例如：123、5、-56（4）實型常量）實型常量實型常量有定點表示和浮點表示兩種方式。實型常量有定點表示和浮點表示兩種方式。定點表示：定點表示：123.

12、45、2.0、+12.45、-89.7浮點表示：浮點表示：101.3e2、0.5e-2、+12e3、-12e21常量常量7.1.2 T-SQL語言基礎(chǔ)知識語言基礎(chǔ)知識 （5）日期時間常量）日期時間常量由用單引號將表示日期時間的字符串括起來構(gòu)成。例如：由用單引號將表示日期時間的字符串括起來構(gòu)成。例如：字母日期格式：字母日期格式：April 15.2013數(shù)字日期格式：數(shù)字日期格式：04/15/2013、2013-04-15未分隔的字符串格式：未分隔的字符串格式：20130415時間格式：時間格式：15:30:20、04:15 PM日期時間格式：日期時間格式：2013-04-15 15:30:20

13、（6）money常量常量以以“$”作為前綴的整型或?qū)嵭统Ａ繑?shù)據(jù)，例如：作為前綴的整型或?qū)嵭统Ａ繑?shù)據(jù)，例如：$123、$89.5、-$23.57.1.2 T-SQL語言基礎(chǔ)知識語言基礎(chǔ)知識 （1）全局變量）全局變量由系統(tǒng)提供，通過在名稱前加由系統(tǒng)提供，通過在名稱前加“”符號區(qū)別于局部變量。符號區(qū)別于局部變量。用戶可以使用全局變量，但不能建立全局變量。用戶可以使用全局變量，但不能建立全局變量。例如：例如：ERROR2變量變量標識符就是用來定義服務(wù)器、數(shù)據(jù)庫、數(shù)據(jù)表對象和變量等的名稱。一般以字母標識符就是用來定義服務(wù)器、數(shù)據(jù)庫、數(shù)據(jù)表對象和變量等的名稱。一般以字母或下劃線開頭，后面跟字母、數(shù)字或下劃

14、線。或下劃線開頭，后面跟字母、數(shù)字或下劃線。變量用于臨時存放數(shù)據(jù)。變量可以分為用戶自定義的局部變量和系統(tǒng)提供的全局變量用于臨時存放數(shù)據(jù)。變量可以分為用戶自定義的局部變量和系統(tǒng)提供的全局變量。變量。表表7-1 7-1 常見的全局變量表常見的全局變量表變變 量量含含 義義ERROR最后一個T-SQL錯誤的錯誤號IDENTITY最后一次插入的標識值LANGUAGE當(dāng)前使用的語言的名稱MAX_CONNECTIONS可以創(chuàng)建的同時連接的最大數(shù)目ROWCOUNT受上一個SQL語句影響的行數(shù)SERVERNAME本地服務(wù)器的名稱TRANSCOUNT當(dāng)前連接打開的事務(wù)數(shù)VERSIONSQL Server的版本信

15、息7.1.2 T-SQL語言基礎(chǔ)知識語言基礎(chǔ)知識【例【例7.1】輸出當(dāng)前】輸出當(dāng)前SQL Server的版本號，插入一條記錄到課程表中，并顯示是的版本號，插入一條記錄到課程表中，并顯示是否出錯。否出錯。在查詢編輯器中輸入：在查詢編輯器中輸入：print SQL Server的版本的版本+VERSION print 服務(wù)器的名稱服務(wù)器的名稱: +SERVERNAME INSERT INTO 課程表課程表(課程號課程號,課名課名,學(xué)分學(xué)分,學(xué)時學(xué)時) VALUES(3005, 網(wǎng)絡(luò)信息檢索網(wǎng)絡(luò)信息檢索, 2, 34) -如果大于如果大于0表示上一條語句執(zhí)行有錯誤表示上一條語句執(zhí)行有錯誤print

16、當(dāng)前錯誤號當(dāng)前錯誤號+convert(varchar(5),ERROR)GO說明：說明： VERSION是顯示當(dāng)前數(shù)據(jù)庫系是顯示當(dāng)前數(shù)據(jù)庫系統(tǒng)的版本號。統(tǒng)的版本號。 SERVERNAME是顯示服務(wù)器是顯示服務(wù)器的名稱。的名稱。 convert(varchar(5),ERROR)表示將當(dāng)前出錯號轉(zhuǎn)換成字符型。表示將當(dāng)前出錯號轉(zhuǎn)換成字符型。7.1.2 T-SQL語言基礎(chǔ)知識語言基礎(chǔ)知識（2）局部變量）局部變量用戶自己定義的變量，稱為局部變量。用戶自己定義的變量，稱為局部變量。局部變量用來保存特定類型的單個數(shù)據(jù)值的對象。局部變量在引用時必須以局部變量用來保存特定類型的單個數(shù)據(jù)值的對象。局部變量在引用

17、時必須以開開頭，變量名要符合頭，變量名要符合SQL Server的命名規(guī)則。在的命名規(guī)則。在T-SQL中，局部變量必須先定義，中，局部變量必須先定義，才能使用。才能使用。定義：定義：DECLARE 局部變量名局部變量名 數(shù)據(jù)類型數(shù)據(jù)類型 ,n【例例7.2】定義一個整型變量。定義一個整型變量。DECLARE x Int【例例7.3】定義二個整型變量，一個定義二個整型變量，一個char(10)類型變量。類型變量。DECLARE x Int, y IntDECLARE c1 char(10) 局部變量賦值局部變量賦值用用SET或或SELECT語句為局部變量賦值，格式如下：語句為局部變量賦值，格式如下

18、：SET 局部變量名局部變量名=表達式表達式 ,nSELECT 局部變量名局部變量名=表達式表達式 ,n From 子句子句 Where 子句子句【例例7.4】輸出局部變量輸出局部變量c1的值為的值為“張大成張大成”DECLARE c1 char(10)SET c1=張大成張大成PRINT c17.1.2 T-SQL語言基礎(chǔ)知識語言基礎(chǔ)知識【例例7.5】輸出局部變量輸出局部變量X+Y的值。的值。DECLARE X INT,Y INT SELECT X=1,y=2PRINT X+Y執(zhí)行結(jié)果如左圖所示。執(zhí)行結(jié)果如左圖所示?！纠?.6】以消息的形式輸出以消息的形式輸出“學(xué)生表學(xué)生表”的總?cè)藬?shù)。的總

19、人數(shù)。DECLARE X INTSELECT X=COUNT(*) from 學(xué)生表學(xué)生表PRINT X執(zhí)行結(jié)果如右圖所示。執(zhí)行結(jié)果如右圖所示。7.1.2 T-SQL語言基礎(chǔ)知識語言基礎(chǔ)知識3注釋語句注釋語句在在T-SQL中，注釋語句有行注釋中，注釋語句有行注釋“-”及塊注釋及塊注釋“/* */”?！纠纠?.7】使用行注釋的語句?！渴褂眯凶⑨尩恼Z句。DECLARE X INT -定義一個整型變量定義一個整型變量X-下面是給變量下面是給變量X賦值。賦值。SELECT X=1【例例7.8】使用塊注釋的語句。使用塊注釋的語句。/*先定義一個整型變量先定義一個整型變量X再給變量再給變量X賦值為賦值為

20、1最后輸出變量最后輸出變量X的值。的值。*/DECLARE X INT SELECT X=1PRINT X4續(xù)行續(xù)行如果，如果，T-SQL語句很長，則可以將一條語句在多行中進行編寫，而不需要使用特語句很長，則可以將一條語句在多行中進行編寫，而不需要使用特殊的符號。殊的符號。7.1.2 T-SQL語言基礎(chǔ)知識語言基礎(chǔ)知識5批處理語句批處理語句批處理語句實際上是若干語句行或命令的集合，執(zhí)行批處理就是要求批處理語句實際上是若干語句行或命令的集合，執(zhí)行批處理就是要求SQL Server對多條語句命令進行分析并執(zhí)行。使用批處理可以節(jié)省系統(tǒng)開銷，但是如對多條語句命令進行分析并執(zhí)行。使用批處理可以節(jié)省系統(tǒng)開

21、銷，但是如果在一個批處理中包含任何語法錯誤，則整個批處理就不能被成功地編譯和執(zhí)行。果在一個批處理中包含任何語法錯誤，則整個批處理就不能被成功地編譯和執(zhí)行。建立批處理時，使用建立批處理時，使用GO語句作為批處理的結(jié)束標記。語句作為批處理的結(jié)束標記。 【例例7.9】利用查詢編輯器執(zhí)行兩個批處理，用來顯示利用查詢編輯器執(zhí)行兩個批處理，用來顯示“學(xué)生表學(xué)生表”的信息及人數(shù)。的信息及人數(shù)。USE 學(xué)生成績管理學(xué)生成績管理goprint 顯示學(xué)生表的信息顯示學(xué)生表的信息select * from 學(xué)生表學(xué)生表print 學(xué)生表的人數(shù)學(xué)生表的人數(shù)select COUNT(*) from 學(xué)生表學(xué)生表go上面

22、這個例子包含兩個批處理，前者僅包含一條語句，后者包含四條語句上面這個例子包含兩個批處理，前者僅包含一條語句，后者包含四條語句 7.1.2 T-SQL語言基礎(chǔ)知識語言基礎(chǔ)知識6輸出語句輸出語句（1）PRINTPRINT語句的作用是向客戶端返回用戶定義的消息。其語法格式為：語句的作用是向客戶端返回用戶定義的消息。其語法格式為：PRINT 字符串字符串|局部變量局部變量|全局變量全局變量【例例7.10】用用PRINT語句輸出變量語句輸出變量X的值。的值。DECLARE x intSET x=1PRINT x（2）SELECT格式：格式：SELECT 字符串字符串|局部變量局部變量|全局變量全局變量【

23、例例7.11】用用SELECT語句輸出變量語句輸出變量X的值。的值。DECLARE x intSET x=1SELECT x7.1.2 T-SQL語言基礎(chǔ)知識語言基礎(chǔ)知識7數(shù)據(jù)庫對象的引用規(guī)則數(shù)據(jù)庫對象的引用規(guī)則一般情況下，數(shù)據(jù)庫對象的引用由以下四部分所組成：一般情況下，數(shù)據(jù)庫對象的引用由以下四部分所組成：server_name.database_name.owner_name.object_name說明：說明：server_name 用于指定所連結(jié)的本地服務(wù)器或遠程服務(wù)器的名稱。用于指定所連結(jié)的本地服務(wù)器或遠程服務(wù)器的名稱。database_name 用于確定在服務(wù)器中當(dāng)前狀態(tài)下所操作的數(shù)據(jù)

24、庫名稱。用于確定在服務(wù)器中當(dāng)前狀態(tài)下所操作的數(shù)據(jù)庫名稱。owner_name 表示數(shù)據(jù)庫對象的所有者。表示數(shù)據(jù)庫對象的所有者。object_name 在數(shù)據(jù)庫中被引用的數(shù)據(jù)庫對象名稱。在數(shù)據(jù)庫中被引用的數(shù)據(jù)庫對象名稱。例如：例如： 學(xué)生成績管理學(xué)生成績管理.dbo.學(xué)生表學(xué)生表 dbo.課程表課程表7.1.3 程序中的流程控制程序中的流程控制流程控制語句是用來控制程序執(zhí)行和流程分支的命令。在流程控制語句是用來控制程序執(zhí)行和流程分支的命令。在SQL Server 2008可以可以使用的流程控制語句有使用的流程控制語句有BEGINEND、IFELSE、WAITFOR、WHILE、RETURN等。等

25、。1BEGINEND在條件和循環(huán)等流程語句中，如果要執(zhí)行兩個或兩個以上的在條件和循環(huán)等流程語句中，如果要執(zhí)行兩個或兩個以上的T-SQL語句時，就需語句時，就需要使用要使用BEGINEND語句將這些語句組合在一起，形成一個整體。語句將這些語句組合在一起，形成一個整體。BEGIN語句語句1語句語句nEND2IFELSE語法格式為：語法格式為：IF （條件）（條件） 語句語句1 ELSE 語句語句2 其中，其中，ELSE是可選部分。是可選部分。7.1.3 程序中的流程控制程序中的流程控制【例【例7.12】統(tǒng)計并顯示選課表中課程號為統(tǒng)計并顯示選課表中課程號為1001的課程的平均分，如果平均分的課程的平

26、均分，如果平均分在在70以上，顯示以上，顯示“成績優(yōu)秀成績優(yōu)秀”，并顯示前三個最高的成績；如果在，并顯示前三個最高的成績；如果在70以下，顯示以下，顯示“成績較差成績較差”，并顯示后三個最低的成績。，并顯示后三個最低的成績。DECLARE myavg floatSELECT myavg=AVG (成績成績) FROM 選課表選課表 WHERE 課程號課程號=1001select 本課程的平均成績本課程的平均成績+convert(varchar,myavg)IF (myavg70) BEGIN select 本課程成績優(yōu)秀，前三個最高的成績?yōu)椋罕菊n程成績優(yōu)秀，前三個最高的成績?yōu)椋?SELECT

27、TOP 3 * FROM 選課表選課表 WHERE 課程號課程號=1001 ORDER BY 成績成績 DESC ENDELSE BEGIN select 本課程成績較差，后三個最低的成績?yōu)椋罕菊n程成績較差，后三個最低的成績?yōu)椋?SELECT TOP 3 * FROM 選課表選課表 WHERE 課程號課程號=1001 ORDER BY 成成績績 ENDGO7.1.3 程序中的流程控制程序中的流程控制【例【例7.13】在在“學(xué)生表學(xué)生表”中查找姓名為中查找姓名為“張大成張大成”同學(xué)的信息，若查找到，則顯同學(xué)的信息，若查找到，則顯示相應(yīng)信息，若沒有查到，則顯示示相應(yīng)信息，若沒有查到，則顯示“對不起

28、，查無此人對不起，查無此人”的信息。的信息。IF exists(select * from 學(xué)生表學(xué)生表 where 姓名姓名=張大成張大成) select * from 學(xué)生表學(xué)生表 where 姓名姓名=張大成張大成 ELSE PRINT 對不起，查無此人對不起，查無此人7.1.3 程序中的流程控制程序中的流程控制3WHILE循環(huán)語句循環(huán)語句在程序設(shè)計中，使用在程序設(shè)計中，使用WHILE語句重復(fù)執(zhí)行一組語句重復(fù)執(zhí)行一組SQL語句，完成重復(fù)處理的某項語句，完成重復(fù)處理的某項工作。其語法格式為：工作。其語法格式為：WHILE （條件）（條件） BEGIN 語句塊語句塊1 BREAK END其中

29、：其中：BREAK表示退出循環(huán)。表示退出循環(huán)。7.1.3 程序中的流程控制程序中的流程控制【例例7.14】假定要給考試成績提分。提分規(guī)則很簡單，給沒達到假定要給考試成績提分。提分規(guī)則很簡單，給沒達到65分的學(xué)生每人都分的學(xué)生每人都加加2分，看是否都達到分，看是否都達到65分以上，如果沒有全部達到分以上，如果沒有全部達到65分以上，每人再加分以上，每人再加2分，再分，再看是否都達到看是否都達到65分以上，如此反復(fù)提分，直到所有人都達到分以上，如此反復(fù)提分，直到所有人都達到65分以上為止分以上為止 。分析：分析：第一步，統(tǒng)計沒達到第一步，統(tǒng)計沒達到65分的人數(shù)分的人數(shù) ；第二步，如果有人沒達到，加

30、分；第二步，如果有人沒達到，加分；第三步，循環(huán)判斷。第三步，循環(huán)判斷。在查詢編輯器中輸入并執(zhí)行：在查詢編輯器中輸入并執(zhí)行：DECLARE n intWHILE(1=1) -條件永遠成立條件永遠成立 BEGIN SELECT n=COUNT(*) FROM 選課表選課表 WHERE 成績成績0) UPDATE 選課表選課表 SET 成績成績=成績成績+2 WHERE 成績成績85 then 優(yōu)秀優(yōu)秀when 平均成績平均成績75 then 良好良好ELSE一般一般end from 學(xué)生平均成績視圖學(xué)生平均成績視圖執(zhí)行命令，則結(jié)果如圖執(zhí)行命令，則結(jié)果如圖7-7所示。所示。7.1.3 程序中的流程控

31、制程序中的流程控制5RETURN 語句語句用于從過程、批處理或語句塊中無條件退出，不執(zhí)行位于用于從過程、批處理或語句塊中無條件退出，不執(zhí)行位于RETURN之后的語句。之后的語句。其語法格式為：其語法格式為：RETURN 整型表達式整型表達式【例例7.17】輸出的值。輸出的值。DECLARE x floatset x=1begin print xreturn print x+1end說明：到執(zhí)行到說明：到執(zhí)行到RETURN語句時，程序退出，不再執(zhí)行語句時，程序退出，不再執(zhí)行print x+1語句。語句。7.1.3 程序中的流程控制程序中的流程控制6WAITFOR 語句語句WAITFOR DELA

32、T time|TIME time說明：說明：DELAY 指示指示SQL Server一直等到指定的時間過去，最長可達一直等到指定的時間過去，最長可達24小時；小時；TIME指示指示SQL Server等到指定時間等到指定時間 WAITFOR DELAY 0:0:10 -等待等待10秒秒WAITFOR TIME 12:30:00 -等到等到12點點30分分說明：說明：WAITFOR語句通常用在存儲過程或觸發(fā)器中，用來設(shè)定時間開關(guān)。語句通常用在存儲過程或觸發(fā)器中，用來設(shè)定時間開關(guān)。7.2 7.2 學(xué)生成績管理數(shù)據(jù)庫存儲過程的創(chuàng)建及管理學(xué)生成績管理數(shù)據(jù)庫存儲過程的創(chuàng)建及管理在學(xué)生成績管理系統(tǒng)中，用戶

33、經(jīng)常查詢學(xué)生考試信息，包括：學(xué)生姓名、所屬專在學(xué)生成績管理系統(tǒng)中，用戶經(jīng)常查詢學(xué)生考試信息，包括：學(xué)生姓名、所屬專業(yè)、授課老師、課程名、考試分數(shù)等信息。由于該查詢在程序中很多地方都要用業(yè)、授課老師、課程名、考試分數(shù)等信息。由于該查詢在程序中很多地方都要用到，而且使用頻率非常高，因此，開發(fā)人員想用一種可以重復(fù)使用而又高性能的到，而且使用頻率非常高，因此，開發(fā)人員想用一種可以重復(fù)使用而又高性能的方式來實現(xiàn)。這種方法就是下面將要介紹的存儲過程。本學(xué)習(xí)任務(wù)主要介紹存儲方式來實現(xiàn)。這種方法就是下面將要介紹的存儲過程。本學(xué)習(xí)任務(wù)主要介紹存儲過程的概念、存儲過程的創(chuàng)建及管理。過程的概念、存儲過程的創(chuàng)建及管理

34、。任務(wù)描述任務(wù)描述 7.2.1 存儲過程的概述存儲過程的概述在任務(wù)描述中提到的查詢信息是分布在學(xué)生表、選課表、課程表、教師表、授課在任務(wù)描述中提到的查詢信息是分布在學(xué)生表、選課表、課程表、教師表、授課表五張表中，需要用連接查詢，查詢代碼如下：表五張表中，需要用連接查詢，查詢代碼如下：SELECT 學(xué)生姓名學(xué)生姓名=a.姓名姓名,專業(yè)專業(yè),授課老師授課老師=e.姓名姓名,課名課名,成績成績 from 學(xué)生表學(xué)生表 a join 選課表選課表 b on a.學(xué)號學(xué)號=b.學(xué)號學(xué)號 join 課程表課程表 c on b.課程號課程號=c.課程號課程號join 授課表授課表 d on d.課程號課程號

35、=c.課程號課程號join 教師表教師表 e on e.教師號教師號=d.教師號教師號 如果查詢信息有所改變，則還要改變很多，這樣，給用戶帶來一定的麻煩。同時，如果查詢信息有所改變，則還要改變很多，這樣，給用戶帶來一定的麻煩。同時，用戶每次提交查詢，數(shù)據(jù)庫服務(wù)器都要對查詢語句進行編譯、解析和執(zhí)行，而且用戶每次提交查詢，數(shù)據(jù)庫服務(wù)器都要對查詢語句進行編譯、解析和執(zhí)行，而且是反復(fù)做同樣的事情，浪費服務(wù)器資源。是反復(fù)做同樣的事情，浪費服務(wù)器資源。因此因此SQL SERVER給出了一種可重用、易維護和高效的解決方案給出了一種可重用、易維護和高效的解決方案 存儲過程存儲過程（Stored Procedu

36、re ）7.2.1 存儲過程的概述存儲過程的概述1存儲過程的概念存儲過程的概念SQL Server的存儲過程類似于編程語言中的過程。將某些需要多次調(diào)用的實現(xiàn)的存儲過程類似于編程語言中的過程。將某些需要多次調(diào)用的實現(xiàn)某個特定任務(wù)的代碼段編寫成一個過程，將其保存在數(shù)據(jù)庫中，并由某個特定任務(wù)的代碼段編寫成一個過程，將其保存在數(shù)據(jù)庫中，并由SQL Server服務(wù)器通過過程名來調(diào)用它們，這個就叫存儲過程。即是一組被編譯在一服務(wù)器通過過程名來調(diào)用它們，這個就叫存儲過程。即是一組被編譯在一起的起的T-SQL語句的集合，它們被集合在一起以完成一個特定的任務(wù)。語句的集合，它們被集合在一起以完成一個特定的任務(wù)。

37、 存儲過程是一個存儲過程是一個SQL語句組合。在創(chuàng)建時進行預(yù)編譯，首次被調(diào)用時進行解析，語句組合。在創(chuàng)建時進行預(yù)編譯，首次被調(diào)用時進行解析，以后再被調(diào)用，則可直接執(zhí)行。以后再被調(diào)用，則可直接執(zhí)行。SQL Server的存儲過程與其他程序設(shè)計語言的過程類似，同樣能按下列方式運行：的存儲過程與其他程序設(shè)計語言的過程類似，同樣能按下列方式運行：（1）能夠包含執(zhí)行各項數(shù)據(jù)庫操作的語句，并且可以調(diào)用其他的存儲過程。）能夠包含執(zhí)行各項數(shù)據(jù)庫操作的語句，并且可以調(diào)用其他的存儲過程。（2）能夠接受輸入?yún)?shù)并以輸出參數(shù)的形式將多個數(shù)據(jù)值返回給調(diào)用程序）能夠接受輸入?yún)?shù)并以輸出參數(shù)的形式將多個數(shù)據(jù)值返回給調(diào)用程序

38、(Ca11ing Procedure)或批處理或批處理(Batch)。（3）向調(diào)用程序或批處理返回一個狀態(tài)值，以表明成功或失?。┫蛘{(diào)用程序或批處理返回一個狀態(tài)值，以表明成功或失敗(以及失敗的原因以及失敗的原因)。7.2.1 存儲過程的概述存儲過程的概述2存儲過程的優(yōu)點存儲過程的優(yōu)點 （1）存儲過程實現(xiàn)了模塊化編程）存儲過程實現(xiàn)了模塊化編程存儲過程一旦創(chuàng)建完成并存儲于數(shù)據(jù)庫中，即可在應(yīng)用程序中反復(fù)調(diào)用，實現(xiàn)共存儲過程一旦創(chuàng)建完成并存儲于數(shù)據(jù)庫中，即可在應(yīng)用程序中反復(fù)調(diào)用，實現(xiàn)共享，因此利用存儲過程完成某些例行操作是最恰當(dāng)不過了。享，因此利用存儲過程完成某些例行操作是最恰當(dāng)不過了。 （2）允許更快

39、執(zhí)行）允許更快執(zhí)行對存儲過程而言，在它們創(chuàng)建時就己經(jīng)檢查過語法的正確性、編譯并加以優(yōu)化，對存儲過程而言，在它們創(chuàng)建時就己經(jīng)檢查過語法的正確性、編譯并加以優(yōu)化，因此當(dāng)執(zhí)行存儲過程時，可以立即直接執(zhí)行，自然速度比較快。因此當(dāng)執(zhí)行存儲過程時，可以立即直接執(zhí)行，自然速度比較快。（3）使用存儲過程可以減少網(wǎng)絡(luò)流量）使用存儲過程可以減少網(wǎng)絡(luò)流量可將數(shù)百行的可將數(shù)百行的Transact-SQL程序代碼編寫成一個存儲過程，則只需從客戶端調(diào)程序代碼編寫成一個存儲過程，則只需從客戶端調(diào)用該存儲過程的名稱即可執(zhí)行它，此時在網(wǎng)絡(luò)上傳輸?shù)膬H僅是存儲過程名稱的幾用該存儲過程的名稱即可執(zhí)行它，此時在網(wǎng)絡(luò)上傳輸?shù)膬H僅是存儲過

40、程名稱的幾個字符。顯而易見，使用存儲過程，所造成的網(wǎng)絡(luò)流量比較小。個字符。顯而易見，使用存儲過程，所造成的網(wǎng)絡(luò)流量比較小。（4）使用存儲過程可以提高數(shù)據(jù)庫的安全性）使用存儲過程可以提高數(shù)據(jù)庫的安全性對于存儲過程，我們可以設(shè)置哪些用戶有權(quán)執(zhí)行它。這樣，就可達到較完善的安對于存儲過程，我們可以設(shè)置哪些用戶有權(quán)執(zhí)行它。這樣，就可達到較完善的安全控制和管理。全控制和管理。 7.2.1 存儲過程的概述存儲過程的概述3存儲過程的分類存儲過程的分類（1）系統(tǒng)存儲過程）系統(tǒng)存儲過程系統(tǒng)存儲過程就是系統(tǒng)創(chuàng)建的存儲過程，以系統(tǒng)存儲過程就是系統(tǒng)創(chuàng)建的存儲過程，以“sp_”前綴標識。在前綴標識。在master 數(shù)據(jù)庫

41、數(shù)據(jù)庫中創(chuàng)建并保存在該數(shù)據(jù)庫中中創(chuàng)建并保存在該數(shù)據(jù)庫中 。例如：。例如： 將數(shù)據(jù)庫改名。將數(shù)據(jù)庫改名。 Sp_renamedb 原數(shù)據(jù)庫名原數(shù)據(jù)庫名,新數(shù)據(jù)庫名新數(shù)據(jù)庫名 將表改名。將表改名。 Sp_rename 原表名原表名,新表名新表名 查看數(shù)據(jù)庫的信息。查看數(shù)據(jù)庫的信息。 Sp_helpdb 數(shù)據(jù)庫名數(shù)據(jù)庫名 查看數(shù)據(jù)庫中的對象表的信息。查看數(shù)據(jù)庫中的對象表的信息。 Sp_help 表名表名 查看存儲過程的信息：查看存儲過程的信息：sp_help 存儲過程名存儲過程名 查看存儲過程的文本：查看存儲過程的文本：sp_helptext 存儲過程名存儲過程名7.2.1 存儲過程的概述存儲過程的

42、概述3存儲過程的分類存儲過程的分類（2）用戶自定義存儲過程）用戶自定義存儲過程是由用戶根據(jù)需要，在自己的用戶數(shù)據(jù)庫中創(chuàng)建的完成某一特定功能的存儲過程。是由用戶根據(jù)需要，在自己的用戶數(shù)據(jù)庫中創(chuàng)建的完成某一特定功能的存儲過程。（3）臨時存儲過程）臨時存儲過程臨時存儲過程（臨時存儲過程（Temporary Stored Procedures）可分為下列兩種：）可分為下列兩種： 本地臨時存儲過程本地臨時存儲過程不論哪一個數(shù)據(jù)庫是當(dāng)前數(shù)據(jù)庫，如果在創(chuàng)建存儲過程時，以井字號不論哪一個數(shù)據(jù)庫是當(dāng)前數(shù)據(jù)庫，如果在創(chuàng)建存儲過程時，以井字號(#)作為其名作為其名稱的第一個字符，則該存儲過程將成為一個存放在稱的第一

43、個字符，則該存儲過程將成為一個存放在tempdb中的本地臨時存儲過中的本地臨時存儲過程。本地臨時存儲過程只有創(chuàng)建它并連接的用戶能夠執(zhí)行它，而且一旦這位用戶程。本地臨時存儲過程只有創(chuàng)建它并連接的用戶能夠執(zhí)行它，而且一旦這位用戶斷開與斷開與SQL Server的連接的連接(也就是注銷也就是注銷SQL Server)，本地臨時存儲過程會自動，本地臨時存儲過程會自動刪除。刪除。 全局臨時存儲過程全局臨時存儲過程不論哪一個數(shù)據(jù)庫是當(dāng)前數(shù)據(jù)庫，只要所創(chuàng)建的存儲過程名稱是以兩個井字號不論哪一個數(shù)據(jù)庫是當(dāng)前數(shù)據(jù)庫，只要所創(chuàng)建的存儲過程名稱是以兩個井字號(#)開頭，則該存儲過程將成為一個存放在開頭，則該存儲過程

44、將成為一個存放在tempdb中的全局臨時存儲過程。全中的全局臨時存儲過程。全局臨時存儲過程一旦創(chuàng)建，以后連接到局臨時存儲過程一旦創(chuàng)建，以后連接到SQL Server的任何用戶都能夠執(zhí)行它，的任何用戶都能夠執(zhí)行它，而且不需要特定的權(quán)限。而且不需要特定的權(quán)限。7.2.1 存儲過程的概述存儲過程的概述3存儲過程的分類存儲過程的分類（4）遠程存儲過程）遠程存儲過程在在SQL Server中，遠程存儲過程（中，遠程存儲過程（Remote Stored Procedures）是位于遠程服）是位于遠程服務(wù)器上的存儲過程，通常，我們可以使用分布式查詢和務(wù)器上的存儲過程，通常，我們可以使用分布式查詢和EXECU

45、TE命令執(zhí)行一個命令執(zhí)行一個遠程存儲過程。遠程存儲過程。（5）擴展存儲過程）擴展存儲過程擴展存儲過程（擴展存儲過程（Extended Stored Procedures）是用戶可以使用外部程序語言）是用戶可以使用外部程序語言編寫的存儲過程。擴展存儲過程在使用和執(zhí)行上與一般的存儲過程完全相同?？删帉懙拇鎯^程。擴展存儲過程在使用和執(zhí)行上與一般的存儲過程完全相同?？梢詫?shù)傳遞給擴展存儲過程，擴展存儲過程也能夠返回結(jié)果和狀態(tài)值。以將參數(shù)傳遞給擴展存儲過程，擴展存儲過程也能夠返回結(jié)果和狀態(tài)值。為了區(qū)別，擴展存儲過程的名稱通常以為了區(qū)別，擴展存儲過程的名稱通常以xp_開頭，擴展存儲過程一定要存放在系開

46、頭，擴展存儲過程一定要存放在系統(tǒng)數(shù)據(jù)庫統(tǒng)數(shù)據(jù)庫master中。中。7.2.2 存儲過程的創(chuàng)建和執(zhí)行存儲過程的創(chuàng)建和執(zhí)行在在SQL Server中通?？梢允褂脙煞N方法創(chuàng)建存儲過程：一種是在中通?？梢允褂脙煞N方法創(chuàng)建存儲過程：一種是在SSMS中創(chuàng)建存中創(chuàng)建存儲過程，另一種是使用查詢編輯器執(zhí)行儲過程，另一種是使用查詢編輯器執(zhí)行SQL語句創(chuàng)建存儲過程。創(chuàng)建存儲過程時，語句創(chuàng)建存儲過程。創(chuàng)建存儲過程時，需要注意下列事項。需要注意下列事項。（1）只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)建存儲過程。）只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)建存儲過程。（2）數(shù)據(jù)庫的所有者可以創(chuàng)建存儲過程，也可以授權(quán)其他用戶創(chuàng)建存儲過程。）數(shù)據(jù)庫的所有者可以創(chuàng)建存

47、儲過程，也可以授權(quán)其他用戶創(chuàng)建存儲過程。（3）存儲過程是數(shù)據(jù)庫對象，其名稱必須遵守標識符命名規(guī)則。）存儲過程是數(shù)據(jù)庫對象，其名稱必須遵守標識符命名規(guī)則。（4）不能將）不能將CREATE PROCEDURE語句與其他語句與其他SQL語句組合到單個批處理中。語句組合到單個批處理中。7.2.2 存儲過程的創(chuàng)建和執(zhí)行存儲過程的創(chuàng)建和執(zhí)行1用用T-SQL命令創(chuàng)建及執(zhí)行無參數(shù)的存儲過程命令創(chuàng)建及執(zhí)行無參數(shù)的存儲過程（1）創(chuàng)建格式）創(chuàng)建格式CREATE PROCEDURE 過程名過程名 WITH ENCRYPTION -加密加密WITH RECOMPILE -重新編譯重新編譯 AST-SQL語句語句說明：說

48、明： WITH ENCRYPTION：不能察看和修改原腳本；：不能察看和修改原腳本；WITH RECOMPILE：創(chuàng)建存儲過程時在其定義中指定：創(chuàng)建存儲過程時在其定義中指定 WITH RECOMPILE 選項，選項，表明表明 SQL Server 將不對該存儲過程計劃進行高速緩存；該存儲過程將在每次執(zhí)行將不對該存儲過程計劃進行高速緩存；該存儲過程將在每次執(zhí)行時都重新編譯。當(dāng)存儲過程的參數(shù)值在各次執(zhí)行間都有較大差異，導(dǎo)致每次均需創(chuàng)時都重新編譯。當(dāng)存儲過程的參數(shù)值在各次執(zhí)行間都有較大差異，導(dǎo)致每次均需創(chuàng)建不同的執(zhí)行計劃時，可使用建不同的執(zhí)行計劃時，可使用 WITH RECOMPILE 選項。此選項

49、并不常用，因為選項。此選項并不常用，因為每次執(zhí)行存儲過程時都必須對其進行重新編譯，這樣會使存儲過程的執(zhí)行變慢。每次執(zhí)行存儲過程時都必須對其進行重新編譯，這樣會使存儲過程的執(zhí)行變慢。7.2.2 存儲過程的創(chuàng)建和執(zhí)行存儲過程的創(chuàng)建和執(zhí)行（2）調(diào)運格式）調(diào)運格式 EXECUTE|EXEC 過程名過程名 注意：注意：在執(zhí)行存儲過程時，如果語句是一個批處理中的第一個語句，則可以省略在執(zhí)行存儲過程時，如果語句是一個批處理中的第一個語句，則可以省略 EXECUTE 或或EXEC關(guān)鍵字。關(guān)鍵字?！纠?.18】創(chuàng)建一個名為創(chuàng)建一個名為p_Student的存儲過程，返回的存儲過程，返回“學(xué)生表學(xué)生表”中學(xué)號為中

50、學(xué)號為02000101的學(xué)生信息。的學(xué)生信息。分析：根據(jù)題意，只要返回分析：根據(jù)題意，只要返回“學(xué)生表學(xué)生表”中學(xué)號為中學(xué)號為02000101的學(xué)生信息，所以只要的學(xué)生信息，所以只要一條查詢語句即可。一條查詢語句即可。在查詢編輯器中輸入：在查詢編輯器中輸入：CREATE PROCEDURE p_StudentASSELECT * from 學(xué)生表學(xué)生表 where 學(xué)號學(xué)號=02000101執(zhí)行命令，顯示執(zhí)行命令，顯示“命令成功完成命令成功完成”，表示存儲過程已經(jīng)創(chuàng)建，可以在對象資源管理，表示存儲過程已經(jīng)創(chuàng)建，可以在對象資源管理器中展開存儲過程找到名為器中展開存儲過程找到名為p_Student

51、的存儲過程。的存儲過程。執(zhí)行剛剛創(chuàng)建的存儲過程：執(zhí)行剛剛創(chuàng)建的存儲過程：p_Student在查詢編輯器中輸入代碼：在查詢編輯器中輸入代碼：EXEC p_Student 或或EXECUTE p_Student7.2.2 存儲過程的創(chuàng)建和執(zhí)行存儲過程的創(chuàng)建和執(zhí)行若本題在創(chuàng)建存儲過程中插入：若本題在創(chuàng)建存儲過程中插入：WITH ENCRYPTION，即寫成：，即寫成：CREATE PROCEDURE p_Student_1WITH ENCRYPTIONASSELECT * from 學(xué)生表學(xué)生表 where 學(xué)號學(xué)號=02000101即存儲過程的文本加密。即存儲過程的文本加密。存儲過程存儲過程p_S

52、tudent在創(chuàng)建時沒有被加密，所以可以查看到文本：在創(chuàng)建時沒有被加密，所以可以查看到文本：執(zhí)行：執(zhí)行：exec sp_helptext p_Student，可看到，可看到p_Student建立的文本，如圖建立的文本，如圖7-9所示。所示。存儲過程存儲過程p_Student_1因為在創(chuàng)建時被加密，所以看不到文本。即執(zhí)行：因為在創(chuàng)建時被加密，所以看不到文本。即執(zhí)行：exec_helptext p_Student_1則顯示：則顯示：“對象對象 p_Student_1 的文本已加密的文本已加密”的信息。的信息。7.2.2 存儲過程的創(chuàng)建和執(zhí)行存儲過程的創(chuàng)建和執(zhí)行【例例7.19】創(chuàng)建一個名為創(chuàng)建一個名

53、為p_StuBymajor的存儲過程，返回的存儲過程，返回“電子商務(wù)電子商務(wù)”專業(yè)的專業(yè)的所有學(xué)生的選課信息。所有學(xué)生的選課信息。分析：首先查找分析：首先查找“電子商務(wù)電子商務(wù)”專業(yè)的同學(xué)的學(xué)號，然后根據(jù)查到的學(xué)號，再查找專業(yè)的同學(xué)的學(xué)號，然后根據(jù)查到的學(xué)號，再查找相應(yīng)的選課表中的信息。所以可以用一個子查詢實現(xiàn)。當(dāng)然，用多表查詢也可以相應(yīng)的選課表中的信息。所以可以用一個子查詢實現(xiàn)。當(dāng)然，用多表查詢也可以實現(xiàn)。實現(xiàn)。在查詢編輯器中輸入：在查詢編輯器中輸入：CREATE PROCEDURE p_StuBymajorASSELECT * from 選課表選課表 where 學(xué)號學(xué)號 in (sele

54、ct 學(xué)號學(xué)號 from 學(xué)生表學(xué)生表 where 專業(yè)專業(yè)=電子商務(wù)電子商務(wù))點擊工具欄中的點擊工具欄中的“執(zhí)行執(zhí)行”按鈕，顯示按鈕，顯示“命令成功完成命令成功完成”，表示存儲過程已經(jīng)創(chuàng)建，表示存儲過程已經(jīng)創(chuàng)建，可以在可以在SSMS的對象資源管理器中展開存儲過程找到名為的對象資源管理器中展開存儲過程找到名為p_StuBymajor的存儲的存儲過程。過程。調(diào)用存儲過程調(diào)用存儲過程p_StuBymajor的命令：的命令：EXEC p_StuBymajor，顯示如圖結(jié)果。，顯示如圖結(jié)果。 7.2.2 存儲過程的創(chuàng)建和執(zhí)行存儲過程的創(chuàng)建和執(zhí)行2用用T-SQL命令創(chuàng)建及執(zhí)行帶輸入?yún)?shù)的存儲過程命令創(chuàng)建

55、及執(zhí)行帶輸入?yún)?shù)的存儲過程參數(shù)是存儲過程與外界進行交互的一種途徑，存儲過程通過輸入?yún)?shù)和輸出參數(shù)參數(shù)是存儲過程與外界進行交互的一種途徑，存儲過程通過輸入?yún)?shù)和輸出參數(shù)與外界進行交互。與外界進行交互。 （1）創(chuàng)建格式：）創(chuàng)建格式：CREATE PROCEDURE 過程名過程名輸入?yún)?shù)名輸入?yún)?shù)名 參數(shù)類型參數(shù)類型 =默認值默認值, WITH ENCRYPTION -加密加密WITH RECOMPILE -重新編譯重新編譯 AST-SQL語句語句7.2.2 存儲過程的創(chuàng)建和執(zhí)行存儲過程的創(chuàng)建和執(zhí)行（2）執(zhí)行格式）執(zhí)行格式 EXECUTE|EXEC 過程名過程名 參數(shù)名參數(shù)名=值值注意：在調(diào)用語句中

56、以注意：在調(diào)用語句中以“參數(shù)名參數(shù)名=值值”的格式指定參數(shù)。當(dāng)通過參數(shù)名傳遞值的格式指定參數(shù)。當(dāng)通過參數(shù)名傳遞值時，可以以任何順序指定參數(shù)值，并且可以省略允許空值或具有默認值的參數(shù)。時，可以以任何順序指定參數(shù)值，并且可以省略允許空值或具有默認值的參數(shù)。 EXECUTE|EXEC 過程名過程名 參數(shù)值參數(shù)值注意：通過位置傳遞參數(shù)，要按順序提供值，參數(shù)值必須以參數(shù)的定義順序列出，注意：通過位置傳遞參數(shù)，要按順序提供值，參數(shù)值必須以參數(shù)的定義順序列出，可以忽略有默認值的參數(shù)，但不能中斷次序?？梢院雎杂心J值的參數(shù)，但不能中斷次序。7.2.2 存儲過程的創(chuàng)建和執(zhí)行存儲過程的創(chuàng)建和執(zhí)行（3）應(yīng)用）應(yīng)用【

57、例例7.20】創(chuàng)建一個名為創(chuàng)建一個名為p_StudentPara的存儲過程，該存儲過程根據(jù)給定的的存儲過程，該存儲過程根據(jù)給定的學(xué)號顯示該學(xué)生的信息。學(xué)號顯示該學(xué)生的信息。分析：分析：【例例7.18】明確表示查找學(xué)號為明確表示查找學(xué)號為02000101的學(xué)生信息，是一個特例，而本的學(xué)生信息，是一個特例，而本題是根據(jù)給定的學(xué)號查找相應(yīng)的信息，所以應(yīng)用更為普遍，即要把題是根據(jù)給定的學(xué)號查找相應(yīng)的信息，所以應(yīng)用更為普遍，即要把【例例7.18】里里的學(xué)號：的學(xué)號：“02000101”改為一個變量，而變量在應(yīng)用時要先定義的，所以可以寫改為一個變量，而變量在應(yīng)用時要先定義的，所以可以寫成：成：CREATE

58、 PROCEDURE p_StudentParax char(9)ASSELECT * from 學(xué)生表學(xué)生表 where 學(xué)號學(xué)號=x點擊工具欄中的點擊工具欄中的“執(zhí)行執(zhí)行”按鈕，顯示按鈕，顯示“命令成功完成命令成功完成”，表示存儲過程已經(jīng)創(chuàng)建。，表示存儲過程已經(jīng)創(chuàng)建。調(diào)用帶能參數(shù)的存儲過程：調(diào)用帶能參數(shù)的存儲過程：p_StudentParaEXEC p_StudentPara 02000101或或 EXEC p_StudentPara x=020001017.2.2 存儲過程的創(chuàng)建和執(zhí)行存儲過程的創(chuàng)建和執(zhí)行總結(jié)建立一個帶參數(shù)的存儲過程，其步驟為：總結(jié)建立一個帶參數(shù)的存儲過程，其步驟為：步驟

59、步驟1：確定存儲過程所需輸入變量。：確定存儲過程所需輸入變量。步驟步驟2：創(chuàng)建帶參數(shù)的存儲過程。：創(chuàng)建帶參數(shù)的存儲過程。步驟步驟3：執(zhí)行存儲過程，驗證其是否能輸入?yún)?shù)。：執(zhí)行存儲過程，驗證其是否能輸入?yún)?shù)。7.2.2 存儲過程的創(chuàng)建和執(zhí)行存儲過程的創(chuàng)建和執(zhí)行（4）繼續(xù)操練）繼續(xù)操練【例例7.21】創(chuàng)建一個名為創(chuàng)建一個名為p_StuByPara的存儲過程，該存儲過程根據(jù)給定的專的存儲過程，該存儲過程根據(jù)給定的專業(yè)顯示該專業(yè)學(xué)生的選課信息。業(yè)顯示該專業(yè)學(xué)生的選課信息。分析：分析：【例例7.19】明確表示查找專業(yè)為明確表示查找專業(yè)為“電子商務(wù)電子商務(wù)”的同學(xué)的選課信息，而本題的同學(xué)的選課信息，而本題

60、是根據(jù)給定的專業(yè)查找相應(yīng)的信息，所以應(yīng)用更為廣泛，這里要把是根據(jù)給定的專業(yè)查找相應(yīng)的信息，所以應(yīng)用更為廣泛，這里要把【例例7.19】中中的專業(yè)：的專業(yè)：“電子商務(wù)電子商務(wù)”改為變量，而變量在應(yīng)用時要先定義的，所以可以寫成：改為變量，而變量在應(yīng)用時要先定義的，所以可以寫成：CREATE PROCEDURE p_StuByParac1 varchar(50)ASSELECT * from 選課表選課表 where 學(xué)號學(xué)號 in (select 學(xué)號學(xué)號 from 學(xué)生表學(xué)生表 where 專業(yè)專業(yè)=c1)點擊工具欄中的點擊工具欄中的“執(zhí)行執(zhí)行”按鈕，顯示按鈕，顯示“命令成功完成命令成功完成”，表