第5章 视图、存储过程与函数

1、数据库技术 应用及实验指导高等院校计算机教材系列第5章 视图、存储过程和用户自定义函数 5.1 视图 5.2 存储过程 5.3 用户自定义函数5.1 视图 5.1.1 视图概念 5.1.2 定义视图 5.1.3 删除视图 5.1.4 视图的作用 5.1.1 视图概念 视图可以被看成是虚拟表。 视图中的数据不物理地存储在数据库内。 SELECT 语句的结果集构成了视图的内容。基本表基本表1基本表基本表2视图视图5.1.2 定义视图 语法： CREATE VIEW （视图列名表）AS 查询语句 定义单源表视图 建立信息系学生的视图。CREATE VIEW IS_StudentAS SELECT S

2、no, Sname, SageFROM Student WHERE Sdept = 信息系 定义多源表视图 建立信息系选修了c01号课程的学生的视图。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分以上的学生的视图。CREATE VIEW V_IS_S2ASSELECT Sno

3、, Sname, Grade FROM V_IS_S1 WHERE Grade = 90 定义带表达式的视图 定义一个反映学生出生年份的视图。CREATE VIEW BT_S(Sno, Sname, Sbirth)AS SELECT Sno, Sname, 2005-Sage FROM Student 含分组统计信息的视图 定义一个存放每个学生的学号及平均成绩的视图。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 视图的作用 简化数据查询语句 使用户能从多角度看到同一数据 提高了数据的安全性 提供了一定程度的逻辑独立性5.2 存储过程 存储过程是 SQL 语句和控制流语句的预编译集合，它以一个名称存储并作为一个单元处理，应用程序可以通过调用的方法执行存储过程。 它使得对数据库的管理和操作更加容易、效率更高。 5.2.1 存储过程概念 SQL 语言是应用程序和 SQL Server 数据库之间的主要编程接口。使用SQL语言编写代码时，可用两种方法存储和执行代码。 一种是在客户端存储代码，并创建向数据库管理系统发送SQL命令（或SQ

5、L语句）并处理返回结果的应用程序； 第二种是将这些发送的SQL语句存储在数据库管理系统中，这些存储在数据库管理系统中的SQL语句就是存储过程。 存储过程与其它程序设计语言中的过程很类似 。使用存储过程的好处 允许模块化程序设计 改善性能 减少网络流量 提供了安全机制 简化管理和操作 5.2.1 创建和执行存储过程 创建存储过程：CREATE PROC EDURE 存储过程名 参数名 数据类型 = default OUTPUT AS SQL语句 执行存储过程： EXEC UTE 存储过程名 实参 , OUTPUT , n 创建不带参数的存储过程 查询计算机系学生的考试成绩，列出学生的姓名、课程名

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 = 计算机系 创建带输入参数的存储过程 查询某个指定系学生的考试情况，列出学生的姓名、所在系、课程名和考试成绩。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 创建带多个输入参数的存储过程 查询某个学生某门课程的考试成绩，列出学生的姓名、课程名和成绩。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 参数的传递方式 按参数位置传递值 EXEC student_grade2 刘晨, VB 按参数名传递值 EXEC Student_grade2 student_name = 刘晨, course_name=VB 创建带多个输入参数并有默认值的存储过程 查询某个学生某门课程的考试成绩，若没有指定课程，则默认课程为“数据库基础”。CREATE PROCEDURE student_grade3 student_name char(10), course_name char(20) =

9、数据库基础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 调用参数有默认值的存储过程EXEC student_grade3 吴宾 等价于执行：EXEC student_grade3 吴宾, 数据库基础 创建带有多个输入参数并均指定默认值的存储过程 查询指定系、指定性别的学生中年龄大于等于指定年龄的学生的情况。系的默认值为“计算机系”，性别的默认值为“男生”

10、， 年龄的默认值为20。CREATE PROC P_Student dept char(20) = 计算机系, sex char(2) = 男, age int = 20AS SELECT * FROM Student WHERE Sdept = dept AND Ssex = sex AND Sage = age 执行 执行1：不提供任何参数值。EXEC P_Student 执行2：提供全部参数值。EXEC P_Student 信息系, 女, 19 执行3：只提供第二个参数的值。EXEC P_Student sex = 女 执行4：只提供第一个和第三个参数的值。EXEC P_Student

11、sex=女 , age = 19创建带有输出参数的存储过程 计算两个数的和。CREATE PROCEDURE sumvar1 int, var2 int, var3 int outputAs Set var3 = var1 * var2 执行此存储过程：Declare res intExecute Proc1 5,7,res outputPrint res 创建带输入参数和一个输出参数的存储过程 统计指定课程的平均成绩，并将统计的结果用输出参数返回。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 创建带输入参数和多个输出参数的存储过程 统计指定课程的平均成绩和选课人数，将统计的结果用输出参数返回。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 创建删除数据的存储过程 删除考试成绩不及格学生的修课记录。CREATE PROCEDURE p_DeleteSCASDELETE FROM sc WHERE grade grade) 调用内嵌表值函数调用内嵌表值函数 利用例3定义的内嵌表值函数，查询“计算机系”学生的信息。SELECT * FROM dbo.f_sdept (计算机系) 利用例4所定义的内嵌表值函数，查询“VB”课程成绩大于70分的学生信息。SELECT * FROM dbo.f_grade (VB, 70) 5.3.4 创建和调用多语句表值函数 定义多语句表值函数：定义多语句表值函数：CREATE

14、FUNCTION 拥拥有者名. 函数数名 ( 参数参数名 AS 标标量数数据类类型 = default ,.n ) RETURNS 返回变变量 TABLE AS BEGIN 函数数体 RETURNEND ：= ( 列定义义 | 表约约束 , .n ) 创建示例 例5创建返回考试成绩为指定层次的学生的学号、姓名、课程名和考试成绩的多语句表值函数，如果指定的层次为“优秀”，则返回的表中的内容为成绩大于等于90的学生的信息；如果指定的层次为“非优秀”，则返回的表中的内容为成绩小于90的学生的信息。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 示例（续）BEGIN IF level = 优优秀 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 = 非优优秀 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 调用多语句表值函数调用多语句表值函数 利用例5定义的函数，查询成绩层次为“优秀”的学生的信息。SELECT * FROM dbo.f_GradeLevel(优秀) 5.3.5 更改和删除函数 1更改用户户自定义义函数数 更改函数的定义使用ALTER FUNCTION语句来实现，其语法格式为：ALTER FUNCTION 函数名 修改示例 例6修改例3定义的函数为查询指定系的学生的学号、姓名、性别和年龄的函数。ALTER FUNCTION dbo.f_sdept( sdept varc