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

创建带输入参数和一个输出参数的存储过程
• 统计指定课程的平均成绩，并将统计的结果 用输出参数返回。
CREATE PROCEDURE AvgGrade @cn char(20), @avg_grade int output AS SELECT @avg_grade = AVG(Grade) FROM SC JOIN Course C ON C.Cno = SC.Cno WHERE Cname = @cn
参数的传递方式
• 按参数位置传递值 EXEC student_grade2 '刘晨', 'VB' • 按参数名传递值 EXEC Student_grade2 @student_name = '刘晨', @course_name='VB'
创建带多个输入参数并有默认值的存储过程
• 查询某个学生某门课程的考试成绩，若没有指定课 程，则默认课程为“数据库基础”。
• 存储过程与其它程序设计语言中的过程很类似 。
使用存储过程的好处
• • • • • 允许模块化程序设计 改善性能 减少网络流量 提供了安全机制 简化管理和操作
5.2.1 创建和执行存储过程
• 创建存储过程： CREATE PROC [ EDURE ] 存储过程名 [ { @参数名 数据类型 } [ = default ] [OUTPUT] ] AS SQL语句 • 执行存储过程： [ EXEC [ UTE ] ] 存储过程名 [实参 [, OUTPUT] [, … n] ]
创建不带参数的存储过程
• 查询计算机系学生的考试成绩，列出学生的姓名、 课程名和成绩。 CREATE PROCEDURE student_grade1 AS SELECT Sname, Cname,Grade FROM Student s INNER JOIN sc ON s.sno = sc.sno INNER JOIN course c ON c.cno = sc.cno WHERE Sdept = '计算机系'
调用标量函数
• 调用例1所定义的函数，计算长、宽、高分别为4、 6、8的立方体的体积。 SELECT dbo.CubicVolume(4,6,8) • 调用例2所定义的函数，查询“VB”课程的选课人数。
创建带输入参数和多个输出参数的存储过程
• 统计指定课程的平均成绩和选课人数，将统 计的结果用输出参数返回。
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.Cno WHERE Cname = @cn
5.3.2 创建和调用标量函数
• 定义标量函数 CREATE FUNCTION [ 拥有者名.] 函数名 ( [ { @参数名 [AS] 标量数据类型 [ = def ault ] } [ ,...n ] ] ) RETURNS 返回值类型 [ AS ] BEGIN 函数体 RETURN 标量表达式 END
5.1.4 视图的作用
• • • • 简化数据查询语句 使用户能从多角度看到同一数据 提高了数据的安全性 提供了一定程度的逻辑独立性
5.2 存储过程
• 存储过程是 SQL 语句和控制流语句的预编 译集合，它以一个名称存储并作为一个单元 处理，应用程序可以通过调用的方法执行存 储过程。 • 它使得对数据库的管理和操作更加容易、效 率更高。
执行
• 执行1：不提供任何参数值。 EXEC P_Student • 执行2：提供全部参数值。 EXEC P_Student '信息系', '女', 19 • 执行3：只提供第二个参数的值。
EXEC P_Student @sex = '女‘
• 执行4：只提供第一个和第三个参数的值。 EXEC P_Student @sex='女' , @age = 19
基本表1 基本表 基本表2 基本表
视图
5.1.2 定义视图
• 语法：
CREATE VIEW <视图名> [（视图列名表）] AS 查询语句
定义单源表视图
• 建立信息系学生的视图。 CREATE VIEW IS_Student AS SELECT Sno, Sname, Sage FROM Student WHERE Sdept = '信息系'
调用参数有默认值的存储过程
EXEC student_grade3 '吴宾'
• 等价于执行： EXEC student_grade3 '吴宾', '数据库基础'
创建带有多个输入参数并均指定默认值的 存储过程
• 查询指定系、指定性别的学生中年龄大于等于指定年 龄的学生的情况。系的默认值为“计算机系”，性别 的默认值为“男生”， 年龄的默认值为20。 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
5.3 用户自定义函数
• • • • • 5.3.1 5.3.2 5.3.3 5.3.4 5.3.5 函数概念 创建和调用标量函数 创建和定义内嵌表值函数 创建和调用多语句表值函数 更改和删除函数
5.3.1 函数概念
• 函数是由一个或多个SQL 语句组成的子程序，它 可用于封装代码以提供代码共享的功能。 • 在概念上类似于一般的程序设计语言中定义的函 数。 • SQL Server 2000 支持三种用户自定义函数： • 标量函数 • 内嵌表值函数 • 多语句表值函数
定义多源表视图
• 建立信息系选修了‘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'
5.2.1 存储过程概念
• SQL 语言是应用程序和 SQL Server 数据库之间 的主要编程接口。使用SQL语言编写代码时，可用 两种方法存储和执行代码。
• 一种是在客户端存储代码，并创建向数据库管理系统 发送SQL命令（或SQL语句）并处理返回结果的应用程 序； • 第二种是将这些发送的SQL语句存储在数据库管理系 统中，这些存储在数据库管理系统中的SQL语句就是 存储过程。
创建示例
• 例1．创建计算立方体的体积的函数。三个输入参数， 分别为立方体的长、宽和高，类型均为整型。 CREATE FUNCTION dbo.CubicVolume (@CubeLength int, @CubeWidth int, @CubeHeight int) RETURNS int AS BEGIN RETURN ( @CubeLength * @CubeWidth * @CubeHe ight ) END
CREATE PROCEDURE student_grade3 @student_name char(10), @course_name char(20) = '数据库基础' AS SELECT Sname, Cname, Grade FROM Student s JOIN sc ON s.sno = sc.sno JOIN course c ON c.cno = sc.cno WHERE sname = @student_name AND cname = @course_name
创建示例
• 例2．创建统计指定课程的选课人数的函数。
CREATE FUNCTION dbo.f_count(@cname varchar(20)) RETURNS int AS BEGIN DECLARE @x int SELECT @x=count(*) from course c join sc on sc.cno = c.cno WHERE cname = @cname RETURN @x END
含分组统计信息的视图
• 定义一个存放每个学生的学号及平均成绩的 视图。 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_Student
在已有视图上定义新视图
• 建立信息系选修了‘c01’号课程且成绩在90 分以上的学生的视图。 CREATE VIEW V_IS_S2 AS SELECT Sno, 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 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
创建带输入参数的存储过程
• 查询某个指定系学生的考试情况，列出学生的姓 名、所在系、课程名和考试成绩。 CREATE PROCEDURE student_grade2 @dept char(20) AS SELECT Sname, Sdept, Cname, Grade FROM Student s INNER JOIN sc ON s.sno = sc.sno INNER JOIN course c ON c.cno = sc.cno WHERE Sdept = @dept
创建带有输出参数的存储过程
• 计算两个数的和。
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
高等院校计算机教材系列
数据库技术 应用及实验指导
第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 语句的结果集构成了视图的内容。
创建带多个输入参数的存储过程
• 查询某个学生某门课程的考试成绩，列出学生的姓名、 课程名和成绩。 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 c.cno = sc.cno WHERE sname = @student_name AND cname = @course_name