第5章視圖、存儲(chǔ)過程與函數(shù)

1、數(shù)據(jù)庫技術(shù) 應(yīng)用及實(shí)驗(yàn)指導(dǎo)高等院校計(jì)算機(jī)教材系列第5章 視圖、存儲(chǔ)過程和用戶自定義函數(shù) 5.1 視圖 5.2 存儲(chǔ)過程 5.3 用戶自定義函數(shù)5.1 視圖 5.1.1 視圖概念 5.1.2 定義視圖 5.1.3 刪除視圖 5.1.4 視圖的作用 5.1.1 視圖概念 視圖可以被看成是虛擬表。 視圖中的數(shù)據(jù)不物理地存儲(chǔ)在數(shù)據(jù)庫內(nèi)。 SELECT 語句的結(jié)果集構(gòu)成了視圖的內(nèi)容?；颈砘颈?基本表基本表2視圖視圖5.1.2 定義視圖 語法： CREATE VIEW （視圖列名表）AS 查詢語句 定義單源表視圖 建立信息系學(xué)生的視圖。CREATE VIEW IS_StudentAS SELECT S

2、no, Sname, SageFROM Student WHERE Sdept = 信息系 定義多源表視圖 建立信息系選修了c01號課程的學(xué)生的視圖。CREATE VIEW V_IS_S1(Sno, Sname, Grade)ASSELECT Student.Sno, Sname, Sage FROM Student JOIN SC ON Student.Sno = SC.Sno WHERE Sdept = 信息系 AND SC.Cno = c01 在已有視圖上定義新視圖 建立信息系選修了c01號課程且成績在90分以上的學(xué)生的視圖。CREATE VIEW V_IS_S2ASSELECT Sno

3、, Sname, Grade FROM V_IS_S1 WHERE Grade = 90 定義帶表達(dá)式的視圖 定義一個(gè)反映學(xué)生出生年份的視圖。CREATE VIEW BT_S(Sno, Sname, Sbirth)AS SELECT Sno, Sname, 2005-Sage FROM Student 含分組統(tǒng)計(jì)信息的視圖 定義一個(gè)存放每個(gè)學(xué)生的學(xué)號及平均成績的視圖。CREATE VIEW S_G(Sno, AverageGrade)AS SELECT Sno, AVG(Grade) FROM SCGROUP BY Sno 5.1.3 刪除視圖 格式：DROP VIEW 例刪除前邊定義的IS_

4、Student視圖。 DROP VIEW IS_Student 5.1.4 視圖的作用 簡化數(shù)據(jù)查詢語句 使用戶能從多角度看到同一數(shù)據(jù) 提高了數(shù)據(jù)的安全性 提供了一定程度的邏輯獨(dú)立性5.2 存儲(chǔ)過程 存儲(chǔ)過程是 SQL 語句和控制流語句的預(yù)編譯集合，它以一個(gè)名稱存儲(chǔ)并作為一個(gè)單元處理，應(yīng)用程序可以通過調(diào)用的方法執(zhí)行存儲(chǔ)過程。 它使得對數(shù)據(jù)庫的管理和操作更加容易、效率更高。 5.2.1 存儲(chǔ)過程概念 SQL 語言是應(yīng)用程序和 SQL Server 數(shù)據(jù)庫之間的主要編程接口。使用SQL語言編寫代碼時(shí)，可用兩種方法存儲(chǔ)和執(zhí)行代碼。 一種是在客戶端存儲(chǔ)代碼，并創(chuàng)建向數(shù)據(jù)庫管理系統(tǒng)發(fā)送SQL命令（或SQ

5、L語句）并處理返回結(jié)果的應(yīng)用程序； 第二種是將這些發(fā)送的SQL語句存儲(chǔ)在數(shù)據(jù)庫管理系統(tǒng)中，這些存儲(chǔ)在數(shù)據(jù)庫管理系統(tǒng)中的SQL語句就是存儲(chǔ)過程。 存儲(chǔ)過程與其它程序設(shè)計(jì)語言中的過程很類似 。使用存儲(chǔ)過程的好處 允許模塊化程序設(shè)計(jì) 改善性能 減少網(wǎng)絡(luò)流量 提供了安全機(jī)制 簡化管理和操作 5.2.1 創(chuàng)建和執(zhí)行存儲(chǔ)過程 創(chuàng)建存儲(chǔ)過程：CREATE PROC EDURE 存儲(chǔ)過程名 參數(shù)名 數(shù)據(jù)類型 = default OUTPUT AS SQL語句 執(zhí)行存儲(chǔ)過程： EXEC UTE 存儲(chǔ)過程名 實(shí)參 , OUTPUT , n 創(chuàng)建不帶參數(shù)的存儲(chǔ)過程 查詢計(jì)算機(jī)系學(xué)生的考試成績，列出學(xué)生的姓名、課程名

6、和成績。CREATE PROCEDURE student_grade1AS SELECT Sname, Cname,Grade FROM Student s INNER JOIN sc ON s.sno = sc.sno INNER JOIN course c ON o = o WHERE Sdept = 計(jì)算機(jī)系 創(chuàng)建帶輸入?yún)?shù)的存儲(chǔ)過程 查詢某個(gè)指定系學(xué)生的考試情況，列出學(xué)生的姓名、所在系、課程名和考試成績。CREATE PROCEDURE student_grade2 dept char(20)AS SELECT Sname, Sdept, Cname, Grade FROM Stude

7、nt s INNER JOIN sc ON s.sno = sc.sno INNER JOIN course c ON o = o WHERE Sdept = dept 創(chuàng)建帶多個(gè)輸入?yún)?shù)的存儲(chǔ)過程 查詢某個(gè)學(xué)生某門課程的考試成績，列出學(xué)生的姓名、課程名和成績。CREATE PROCEDURE student_grade2 student_name char(10), course_name char(20) AS SELECT Sname, Cname, Grade FROM Student s JOIN sc ON s.sno = sc.sno INNER JOIN course c ON

8、 o = o WHERE sname = student_name AND cname = course_name 參數(shù)的傳遞方式 按參數(shù)位置傳遞值 EXEC student_grade2 劉晨, VB 按參數(shù)名傳遞值 EXEC Student_grade2 student_name = 劉晨, course_name=VB 創(chuàng)建帶多個(gè)輸入?yún)?shù)并有默認(rèn)值的存儲(chǔ)過程 查詢某個(gè)學(xué)生某門課程的考試成績，若沒有指定課程，則默認(rèn)課程為“數(shù)據(jù)庫基礎(chǔ)”。CREATE PROCEDURE student_grade3 student_name char(10), course_name char(20) =

9、數(shù)據(jù)庫基礎(chǔ)AS SELECT Sname, Cname, Grade FROM Student s JOIN sc ON s.sno = sc.sno JOIN course c ON o = o WHERE sname = student_name AND cname = course_name 調(diào)用參數(shù)有默認(rèn)值的存儲(chǔ)過程EXEC student_grade3 吳賓 等價(jià)于執(zhí)行：EXEC student_grade3 吳賓, 數(shù)據(jù)庫基礎(chǔ) 創(chuàng)建帶有多個(gè)輸入?yún)?shù)并均指定默認(rèn)值的存儲(chǔ)過程 查詢指定系、指定性別的學(xué)生中年齡大于等于指定年齡的學(xué)生的情況。系的默認(rèn)值為“計(jì)算機(jī)系”，性別的默認(rèn)值為“男生”

10、， 年齡的默認(rèn)值為20。CREATE PROC P_Student dept char(20) = 計(jì)算機(jī)系, sex char(2) = 男, age int = 20AS SELECT * FROM Student WHERE Sdept = dept AND Ssex = sex AND Sage = age 執(zhí)行 執(zhí)行1：不提供任何參數(shù)值。EXEC P_Student 執(zhí)行2：提供全部參數(shù)值。EXEC P_Student 信息系, 女, 19 執(zhí)行3：只提供第二個(gè)參數(shù)的值。EXEC P_Student sex = 女 執(zhí)行4：只提供第一個(gè)和第三個(gè)參數(shù)的值。EXEC P_Student

11、sex=女 , age = 19創(chuàng)建帶有輸出參數(shù)的存儲(chǔ)過程 計(jì)算兩個(gè)數(shù)的和。CREATE PROCEDURE sumvar1 int, var2 int, var3 int outputAs Set var3 = var1 * var2 執(zhí)行此存儲(chǔ)過程：Declare res intExecute Proc1 5,7,res outputPrint res 創(chuàng)建帶輸入?yún)?shù)和一個(gè)輸出參數(shù)的存儲(chǔ)過程 統(tǒng)計(jì)指定課程的平均成績，并將統(tǒng)計(jì)的結(jié)果用輸出參數(shù)返回。CREATE PROCEDURE AvgGrade cn char(20), avg_grade int outputAS SELECT avg_

12、grade = AVG(Grade) FROM SC JOIN Course C ON C.Cno = SC.Cno WHERE Cname = cn 創(chuàng)建帶輸入?yún)?shù)和多個(gè)輸出參數(shù)的存儲(chǔ)過程 統(tǒng)計(jì)指定課程的平均成績和選課人數(shù)，將統(tǒng)計(jì)的結(jié)果用輸出參數(shù)返回。CREATE PROCEDURE Avg_Count cn char(20), avg_grade int output, total int outputAS SELECT avg_grade = AVG(Grade), total = COUNT(*) FROM SC JOIN Course C ON C.Cno = SC.Cno WHER

13、E Cname = cn 創(chuàng)建刪除數(shù)據(jù)的存儲(chǔ)過程 刪除考試成績不及格學(xué)生的修課記錄。CREATE PROCEDURE p_DeleteSCASDELETE FROM sc WHERE grade grade) 調(diào)用內(nèi)嵌表值函數(shù)調(diào)用內(nèi)嵌表值函數(shù) 利用例3定義的內(nèi)嵌表值函數(shù)，查詢“計(jì)算機(jī)系”學(xué)生的信息。SELECT * FROM dbo.f_sdept (計(jì)算機(jī)系) 利用例4所定義的內(nèi)嵌表值函數(shù)，查詢“VB”課程成績大于70分的學(xué)生信息。SELECT * FROM dbo.f_grade (VB, 70) 5.3.4 創(chuàng)建和調(diào)用多語句表值函數(shù) 定義多語句表值函數(shù)：定義多語句表值函數(shù)：CREATE

14、FUNCTION 擁擁有者名. 函數(shù)數(shù)名 ( 參數(shù)參數(shù)名 AS 標(biāo)標(biāo)量數(shù)數(shù)據(jù)類類型 = default ,.n ) RETURNS 返回變變量 TABLE AS BEGIN 函數(shù)數(shù)體 RETURNEND ：= ( 列定義義 | 表約約束 , .n ) 創(chuàng)建示例 例5創(chuàng)建返回考試成績?yōu)橹付▽哟蔚膶W(xué)生的學(xué)號、姓名、課程名和考試成績的多語句表值函數(shù)，如果指定的層次為“優(yōu)秀”，則返回的表中的內(nèi)容為成績大于等于90的學(xué)生的信息；如果指定的層次為“非優(yōu)秀”，則返回的表中的內(nèi)容為成績小于90的學(xué)生的信息。CREATE FUNCTION dbo.f_GradeLevel(level char(6) RETUR

15、NS GradeLevel TABLE ( sno char(7), sname char(8), cname varchar(20), grade int)AS 示例（續(xù)）BEGIN IF level = 優(yōu)優(yōu)秀 INSERT INTO GradeLevel SELECT s.sno,sname,cname,grade FROM student s JOIN sc ON s.sno = sc.sno JOIN course c ON o = o WHERE grade = 90 ELSE IF level = 非優(yōu)優(yōu)秀 INSERT INTO GradeLevel SELECT s.sno,

16、sname,cname,grade FROM student s JOIN sc ON s.sno = sc.sno JOIN course c ON o = o WHERE grade 90 RETURNEND 調(diào)用多語句表值函數(shù)調(diào)用多語句表值函數(shù) 利用例5定義的函數(shù)，查詢成績層次為“優(yōu)秀”的學(xué)生的信息。SELECT * FROM dbo.f_GradeLevel(優(yōu)秀) 5.3.5 更改和刪除函數(shù) 1更改用戶戶自定義義函數(shù)數(shù) 更改函數(shù)的定義使用ALTER FUNCTION語句來實(shí)現(xiàn)，其語法格式為：ALTER FUNCTION 函數(shù)名 修改示例 例6修改例3定義的函數(shù)為查詢指定系的學(xué)生的學(xué)號、姓名、性別和年齡的函數(shù)。ALTER FUNCTION dbo.f_sdept( sdept varch