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

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 语句的结果集构成了视图的内容。,5.1.2 定义视图,语法： CREATE VIEW （视图列名表） AS 查询语句,定义单源表视图,建立信息系学生的视图。 CREATE VIEW IS_Student AS SELECT Sno, Sname, Sage FROM Student WHERE Sd

2、ept = 信息系,定义多源表视图,建立信息系选修了c01号课程的学生的视图。 CREATE VIEW V_IS_S1(Sno, Sname, Grade) AS SELECT 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_S2 AS SELECT Sno, Sname, Grade FROM V_IS_S1 WH

3、ERE 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 SC GROUP BY Sno,5.1.3 删除视图,格式： DROP VIEW 例删除前边定义的IS_Student视图。 DROP VIEW IS_

4、Student,5.1.4 视图的作用,简化数据查询语句 使用户能从多角度看到同一数据 提高了数据的安全性 提供了一定程度的逻辑独立性,5.2 存储过程,存储过程是 SQL 语句和控制流语句的预编译集合，它以一个名称存储并作为一个单元处理，应用程序可以通过调用的方法执行存储过程。 它使得对数据库的管理和操作更加容易、效率更高。,5.2.1 存储过程概念,SQL 语言是应用程序和 SQL Server 数据库之间的主要编程接口。使用SQL语言编写代码时，可用两种方法存储和执行代码。 一种是在客户端存储代码，并创建向数据库管理系统发送SQL命令（或SQL语句）并处理返回结果的应用程序； 第二种是将

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

6、E student_grade1 AS 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 Student s INNER JOIN

7、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 o = o WHERE sn

8、ame = 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) = 数据库基础 AS SELEC

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

10、。 CREATE PROC P_Student dept char(20) = 计算机系, sex char(2) = 男, age int = 20 AS 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 sex=女

11、 , age = 19,创建带有输出参数的存储过程,计算两个数的和。 CREATE PROCEDURE sum var1 int, var2 int, var3 int output As Set var3 = var1 * var2 执行此存储过程： Declare res int Execute Proc1 5,7,res output Print res,创建带输入参数和一个输出参数的存储过程,统计指定课程的平均成绩，并将统计的结果用输出参数返回。 CREATE PROCEDURE AvgGrade cn char(20), avg_grade int output AS SELECT

12、avg_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 output AS SELECT avg_grade = AVG(Grade), total = COUNT(*) FROM SC JOIN Course C ON C.Cno = SC.Cn

13、o WHERE Cname = cn,创建删除数据的存储过程,删除考试成绩不及格学生的修课记录。 CREATE PROCEDURE p_DeleteSC AS DELETE FROM sc WHERE grade 60,创建修改数据的存储过程,将指定课程的学分增加2分。 CREATE PROCEDURE p_UpdateCredit cn varchar(20) AS UPDATE course SET credit=credit+2 WHERE cname = cn,5.3 用户自定义函数,5.3.1 函数概念 5.3.2 创建和调用标量函数 5.3.3 创建和定义内嵌表值函数 5.3.4

14、创建和调用多语句表值函数 5.3.5 更改和删除函数,5.3.1 函数概念,函数是由一个或多个SQL 语句组成的子程序，它可用于封装代码以提供代码共享的功能。 在概念上类似于一般的程序设计语言中定义的函数。 SQL Server 2000 支持三种用户自定义函数： 标量函数 内嵌表值函数 多语句表值函数,5.3.2 创建和调用标量函数,定义标量函数 CREATE FUNCTION 拥有者名. 函数名 ( 参数名 AS 标量数据类型 = default ,.n ) RETURNS 返回值类型 AS BEGIN 函数体 RETURN 标量表达式 END,创建示例,例1创建计算立方体的体积的函数。三

15、个输入参数，分别为立方体的长、宽和高，类型均为整型。 CREATE FUNCTION dbo.CubicVolume (CubeLength int, CubeWidth int, CubeHeight int) RETURNS int AS BEGIN RETURN ( CubeLength * CubeWidth * CubeHeight ) END,创建示例,例2创建统计指定课程的选课人数的函数。 CREATE FUNCTION dbo.f_count(cname varchar(20) RETURNS int AS BEGIN DECLARE x int SELECT x=count(

16、*) from course c join sc on o = o WHERE cname = cname RETURN x END,调用标量函数,调用例1所定义的函数，计算长、宽、高分别为4、6、8的立方体的体积。 SELECT dbo.CubicVolume(4,6,8) 调用例2所定义的函数，查询“VB”课程的选课人数。 SELECT cname as 课程名, dbo.f_count(VB) as 选课人数 FROM course WHERE cname = VB,5.3.3 创建和定义内嵌表值函数,定义内嵌表值函数： CREATE FUNCTION 拥有者名. 函数名 ( 参数名 A

17、S 标量数据类型 = default ,.n ) RETURNS TABLE AS RETURN ( select语句 ) ,创建示例,例3创建查询指定系的学生的姓名、年龄和性别的函数。 CREATE FUNCTION dbo.f_sdept (dept varchar(20) RETURNS TABLE AS RETURN ( SELECT sname, sage, ssex from student WHERE sdept = dept),创建示例,例4创建查询指定课程中成绩大于指定分数的学生的姓名、所在系和这门课程的考试成绩。 CREATE FUNCTION dbo.f_grade (

18、cname varchar(20), grade int) RETURNS TABLE AS RETURN ( SELECT sname, sdept, grade from student s JOIN sc ON s.sno = sc.sno JOIN course c ON o = o WHERE cname = cname and grade grade),调用内嵌表值函数,利用例3定义的内嵌表值函数，查询“计算机系”学生的信息。 SELECT * FROM dbo.f_sdept (计算机系) 利用例4所定义的内嵌表值函数，查询“VB”课程成绩大于70分的学生信息。 SELECT *

19、 FROM dbo.f_grade (VB, 70),5.3.4 创建和调用多语句表值函数,定义多语句表值函数： CREATE FUNCTION 拥有者名. 函数名 ( 参数名 AS 标量数据类型 = default ,.n ) RETURNS 返回变量 TABLE AS BEGIN 函数体 RETURN END ：= ( 列定义 | 表约束 , .n ),创建示例,例5创建返回考试成绩为指定层次的学生的学号、姓名、课程名和考试成绩的多语句表值函数，如果指定的层次为“优秀”，则返回的表中的内容为成绩大于等于90的学生的信息；如果指定的层次为“非优秀”，则返回的表中的内容为成绩小于90的学生的信息。 CREATE FUNCTION dbo.f_GradeLevel(level char(6) RETURNS 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 WHE