实验11 存储过程和用户自定义函数_图

导言：数据库，比如Microsoft‟s SQL Server 2005使用Transact-Structured Query Language (T-SQL)来插入、修改、检索数据.绝大多数数据库系统都包含constructs来对一系列的SQL statements进行分组，这些statements可以作为单独的单元来执行.存储过程就是一个例子，另一个例子是用户自定义函数(UDFs), 我们将在第9步进行详细的探讨.SQL是设计来处理一系列数据的. SELECT,UPDATE,和DELETE statements适用于相应表的所有记录，且通过WHERE字句来进行筛选.也有很多的特性被设计来一次处理一条记录，或操作标量数据(scalar data).比如CURSORs允许一次遍历所有的记录.字符串操作功能，比如LEFT, CHARINDEX, 以及PATINDEX用来处理标量数据.SQL也包含了控制流声明，比如IF和WHILE.在Microsoft SQL Server 2005之前，存储过程和用户自定义函数UDFs只能当做一个T-SQL statements集来创建，而SQL Server 2005设计时包含Common Language Runtime (CLR)。

因此，对一个SQL Server 2005数据库里的存储过程和用户定义方法，我们可以用managed code来进行创建。

那就也说你可以在一个C#类里创建一个存储过程或用户定义函数.这样一来我们就可以在.NET Framework或你自己定义的类里面使用这些存储过程或方法.在本文我们将考察如何创建存储过程和用户定义函数，以及如何将它们整合进数据库Northwind.让我们开始吧。

注意：管理数据库对象(Managed database objects)与SQL数据库里包含的相对应的数据库对象比较起来有一些优势，主要体现在：使用的语言更丰富、熟悉；可以使用现有的代码和逻辑.但是在处理那些并不包含很多逻辑的一系列数据时，其效率可能要低一些.关与managed code相较T-SQL而言的优势，请参阅文章《Advantages of Using Managed Code to Create Database Objects》(/en-us/library/k2e1fb36(VS.80).aspx)第一步：将Northwind数据库移出App_Data文件夹本教程到目前为止使用的是放在App_Data文件夹里的Microsoft SQL Server 2005 Express版本的数据库.然而在本教程，我们将Northwind数据库移出App_Data文件夹，再使用一个被注册为SQL Server 2005 Express版本数据库的实例。

使用存储过程模板创建存储过程：
⑵ 修改存储过程的编程模板，插入需要的T-SQL代码。
• ① 存储过程编程模板。
……
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
-- Add the parameters for the stored procedure here
⑵ 修改存储过程的编程模板，插入需要的T-SQL代码。
• ② 在存储过程中编写代码。
• 在上述模板代码中：
− 在”CREATE PROCEDURE…”行命令中，用户必须自己定义 一个存储过程名称，来替代参数部分，即“< >”部分。
− “<@Param1,…”、“<@Param2,…”行命令用来指定参数 项，如果用户需要为该存储过程指定参数，则按照提示指定参 数，例如：@Cust_name varchar(20)；如果不需要参数，则 删除这两条命令。
− “SELECT <@Param1,…”行命令是为参数赋值。如果没有 参数，则删除此条命令。
− 用户从模板的第33行之后（即“-- Insert statements for procedure here”之后），插入所要编写的存储过程代码。
SQL Server 2008
数据库实用技术
存储过程
使用SSMS创建与管理存储过程
− data_type：模板中参数的数据类型。此字段是只读的。若要 更改数据类型，请更改模板中的参数。
− default_value：为所选参数指定值。默认值。
SQL Server 2008
数据库实用技术
存储过程
使用SSMS创建与管理存储过程

实验报告课程名称：数据库系统概论实验时间：2012.5.10学号：姓名：班级：一、实验题目：存储过程与用户自定义函数二、实验目的：1）掌握SQLServer中存储过程的使用方法。

2）掌握SQLServer中用户自定义函数的使用方法。

三、实验内容：（记录每个实验步骤内容、命令、截屏结果）(一存储过程1、对学生课程数据库，编写2个存储过程，分别完成下面功能：1）统计某一门课的成绩分布情况，即按照各分数段统计人数，要求使用游标。

create proc TotalByCnoNum(@cno varchar(6asbegindeclare @num1 int,@num2 int, @num3 int,@num4 int,@num5 int,@grade int,@cname char(20select @num1=0,@num2=0,@num3=0,@num4=0,@num5=0declare cur_cno cursor for select grade from sc where cno=@cnoopen cur_cnofetch next from cur_cno into @gradewhile@@fetch_status=0beginif @grade between 90 and 100set @num1=@num1+1else if @grade between 80 and 89set @num2=@num2+1else if @grade between 70 and 79set @num3=@num3+1else if @grade between 60 and 69set @num4=@num4+1elseset @num5=@num5+1fetch next from cur_cno into @gradeendclose cur_cnodeallocate cur_cnoselect @cname=cname from course where cno=@cno print'课程：'+@cnameprint'分数段人数统计'print'=========================='print' 90-100 : '+convert(varchar(3,@num1print' 80-89 : '+convert(varchar(3,@num2print' 70-79 : '+convert(varchar(3,@num3print' 60-69 : '+convert(varchar(3,@num4print' 不及格: '+convert(varchar(3,@num5print'=========================='end执行以下语句，显示课程号为3的成绩情况：exec TotalByCnoNum '3'运行结果如下：2）将学生选课成绩从百分制改为等级制（即A、B、C、D、E五级）。

2020/8/18
8
存储过程概述(6)
例 在教学管理数据库中，显示表S的相关性信息。 EXEC sp_depends @objname = 'S'
(2) 扩展存储过程 扩展存储过程以在SQL Server 环境外执行的动态链接库(Dynamic-Link Libraries，DLL)来实现。
扩展存储过程 xp_availablemedia xp_dirtree xp_enumdsn xp_enumgroups xp_fixeddrives
2020/8/18
3
存储过程概述(1)
存储过程是T-SQL语句和流程控制语句的预编译集合，以一个
名称存储并作为一个单元处理。存储过程存储在数据库内，可由 应用程序通过一个调用执行，而且允许用户声明变量、有条件执 行以及强大的编程功能。
使用存储过程的优势：
提高了处理复杂任务的能力。主要用于数据库中执行操作
的业务逻辑框架。
代码可读性差，因此一般比较难维护。
2020/8/18
5
存储过程概述(3)
❖ 常见的存储过程
(1) 系统存储过程 系统存储过程是由SQL Server 系统提供的存储过程，可以作为命令执行各种 操作。
存储过程 sp_addlogin sp_addrole sp_cursorclose sp_dbremove sp_droplogin sp_helpindex sp_helprolemember sp_helptrigger sp_lock sp_primarykeys sp_statistics
其返回的数据，则需要更新程序集中的代码以添加参数、更新
调用等，一般比较繁琐。
可移植性差。由于存储过程将应用程序绑定到SQL Server，

WHILE(@@fetch_status=0) --游标读取下一条数据成功。
BEGIN PRINT @info FETCH NEXT FROM @curs INTO @info
END
2020/10/4
8/73
流程控制语句(7)
RETURN语句
使用RETURN语句，可以从查询或过程中无条件地退出，而不去执 行位于RETURN之后的语句。语句格式为：
（2）用户存储过程。本地存储过程是指在用户数据库中创建 的存储过程，这种存储过程完成用户指定的数据库操作，其名称 不能以sp_为前缀。SQL Server 2008中，本地存储过程可以使 用T-SQL语言编写。
2020/10/4
11/73
存储过程的分类(2)
① 存储过程：存储过程保存T-SQL语句集合，可以接受和返回 用户提供的参数。存储过程中可以包含根据客户端应用程序提供 的信息，在一个或多个表中插入新行所需的语句。
IF (SELECT AVG(GRADE) FROM SC WHERE CNO='C4')>80 PRINT 'C4号课程的平均成绩还不错'
ELSE PRINT 'C4号课程的平均成绩一般'
2020/10/4
4/73
流程控制语句(3)
❖ CASE语句
(1) 简单CASE语句:
CASE <输入条件表达式>
RETURN [<整形表达式>] 其中，<整形表达式>为一个整型数值，是RETURN语句要返回的值。 该语句的含义是：向执行调用的过程或应用程序返回一个整数值。
注意：当用于存储过程时，不能返回空值。如果试图返回空值，将 生成警告信息，并返回0值。

存储过程与函数简介 存储过程与函数操作 系统函数 知识点小结 本章实验
存储过程与函数操作
创建和使用存储过程或函数 变量 定义条件和处理 游标的使用 流程的控制 查看存储过程或函数 删除存储过程或函数
创建和使用存储过程或函数
存储过程 创建存储函数 delimiter命令
说明：var_name是存放数据的变量名。fetch…into语句与 select...into语句具有相同的意义，fetch语句是将游标指向的 一行数据赋给一些变量，子句中变量的数目必须等于声明游 标时select子句中列的数目。
游标的使用
关闭游标 游标使用完以后，要及时关闭。关闭游标使用close语句 语法格式： close cursorname
其中，case_value参数表示条件判断的变量；when_value参数表
示变量的取值；statement_list参数表示不同条件的执行语句。
流程的控制
loop语句
loop语句可以使用某些特定的语句重复执行，实现简单的循环。 loop没有停止循环的语句。要结合leave离开退出循环或iterate 继续迭代。基本形式如下：
[begin_label:] loop statement_list
end loop [end_label]
statement_list参数表示不同条件的执行语句
流程的控制
leave语句
leave语句主要用于跳出循环。语法形式如下：
level label
其中label参数表示循环标志
流程的控制
游标的使用
打开游标 语法格式： open cursor_ name
说明：在程序中，一个游标可以打开多次，由于其他的用 户或程序本身已经更新了表，所以每次打开结果可能不同。

存储过程、触发器和用户自定义函数实验实验内容一练习教材中存储过程、触发器和用户自定义函数的例子。

教材中的BookSales数据库，在群共享中，文件名为BookSales.bak。

实验内容二针对附件1中的教学活动数据库，完成下面的实验内容。

1、存储过程（1）创建一个存储过程，该存储过程统计“高等数学”的成绩分布情况，即按照各分数段统计人数。

CREATE Proc MATH_NUM @MATH CHAR(20)='高等数学'ASSELECT @MATH as canme,count(case when score>=90 then 1 end)as[90以上],count(case when score>=80 and score<90 then 1 end)as[80-90],count(case when score>=70 and score<80 then 1 end)as[70-80],count(case when score>=60 and score<70 then 1 end)as[60-70],count(case when score<60 then 1 end)as[60以下] FROM study,course WHERE o=o and ame=@MATHGROUP BY ame（2）创建一个存储过程，该存储过程有一个参数用来接收课程号，该存储过程统计给定课程的平均成绩。

CREATE Proc AVG_SCORE @cno CHAR(20)ASSELECT @cno as 课程号,ame as 课程名,STR(AVG(score),5,2) as 平均成绩FROM study,courseWHERE o=o and o=@cno GROUP BY ame（3）创建一个存储过程，该存储过程将学生选课成绩从百分制改为等级制（即A、B、C、D、E）。

存储过程和用户自定义函数一:存储过程的简单创建,修改与删除1.创建简单的存储过程use AdventureWorksgocreate proc spEmployeeasselect*from HumanResources.Employee执行上面的代码就创建了一个存储过程如果想运行这个存储过程可以直接执行exec spEmployee这个语句2.更改存储过程ALTER proc[dbo].[spEmployee]asselect top13*from HumanResources.Employee3.删除存储过程drop proc dbo.spEmployee二:存储过程的输入参数和输出参数1.有输入参数的存储过程use AdventureWorksgocreate proc spEmployee@LastName nvarchar(50) =nullasif@LastName is nullselect top13*from HumanResources.Employee elseselect top10*from HumanResources.Employee查看该存储过程的结果可以用exec spEmployee '123'或直接exec spEmployee存储过程的重载...2.有输出参数的存储过程use AdventureWorksgoalter proc spEmployee@LastName nvarchar(50) =null outputasif@LastName is nullbeginprint'null'return'123'endelsebeginprint@LastNamereturn'456'end看第一个测试该存储过程的语句declare@myval nvarchar(50)exec@myval= spEmployee @myval outputprint@myval输出null 123第二个测试该存储过程的语句declare@myval nvarchar(50)set@myval='xland'exec@myval= spEmployee @myval outputprint@myval输出xland 456三:用户定义函数1.返回标量值的用户定义函数先做一个简单的日期处理函数把长日期缩短成短日期Create function dbo.DayOnly(@date datetime)returns varchar(12)asbeginreturn convert(varchar(12),@date,101)end为了测试上面的函数先做个脚本use Accountingdeclare@counter intset@counter=1while@counter<=10begininsert into Orders values(1,dateadd(mi,@counter,getdate()),1)set@counter=@counter+1end然后检索这个脚本新插入的数据记录use Accountingselect*from orders where dbo.DayOnly(date1) = dbo.DayOnly(getdate()) 2.返回表的用户定义函数先看例子use AdventureWorksgocreate function dbo.fnContactSearch(@LastName nvarchar(50))returns tableasreturn (select*from Person.Contact where LastName like@LastName+'%') 执行这个例子use AdventureWorksselect*from fnContactSearch('Ad')3.综合例子:返回表,有输入参数use xlandgocreate function dbo.funGetMytable(@id as int)returns@allrows table(id int not null,title nvarchar(max) null)asbegininsert into@allrows select id,title from mytable where id =@id returnendgo执行这个例子select*from funGetMytable(1)。

数据库系统原理实验报告实验名称：__用户定义数据类型与自定义函数_ 指导教师：_叶晓鸣刘国芳_____ 专业：_计算机科学与技术_ 班级：__2010级计科班_ 姓名：_文科_____学号： 100510107 完成日期：_2012年11月10日_成绩： ___ ___一、实验目的：（1）学习和掌握用户定义数据类型的概念、创建及使用方法。

（2）学习和掌握用户定义函数的概念、创建及使用方法。

二、实验内容及要求：实验 11.1 创建和使用用户自定义数据类型内容：（1）用SQL语句创建一个用户定义的数据类型Idnum。

（2）交互式创建一个用户定义的数据类型Nameperson。

要求：（1）掌握创建用户定义数据类型的方法。

（2）掌握用户定义数据类型的使用。

实验 11.2 删除用户定义数据类型内容：（1）使用系统存储过程删除用户定义的数据类型Namperson。

（2）交互式删除用户定义的数据类型Idnum。

要求：（1）掌握使用系统存储过程删除用户定义的数据类型。

（2）掌握交互式删除用户定义的数据类型。

实验 11.3 创建和使用用户自定义的函数内容：（1）创建一个标量函数Score_FUN，根据学生姓名和课程名查询成绩。

（2）创建一个内嵌表值函数S_Score_FUN，根据学生姓名查询该生所有选课的成绩。

（3）创建一个多语句表值函数ALL_Score_FUN，根据课程名查询所有选择该课程学生的成绩信息。

要求：（1）掌握创建标量值函数的方法。

（2）掌握创建内嵌表值函数的方法。

（3）掌握创建多语句表值函数的方法。

实验 11.4 修改用户定义的函数内容：（1）交互式修改函数Score_FUN，将成绩转换为等级输出。

（2）用SQL修改函数S_Score_FUN，要求增加一输出列定义的成绩的等级。

要求：（1）掌握交互式修改用户定义函数的方法。

（2）掌握使用SQL修改用户定义函数的方法。

实验 11.5 输出用户定义的函数内容：（1）交互式删除函数Score_FUN。

labExam FROM stuInfo
INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo WHERE writtenExam<writtenPass
查询没有通过 考试的学员
OR labExam<labPass
GO
演示：示例4-1带参数的存储过程1
SQL2019 存储过程与 自定义函数
课程目标
1 存储过程
1.1 了解存储过程的概念和优点 1.2 掌握常用的系统存储过程 1.3 掌握创建、调用存储过程 1.4 查看、修改、删除存储过程
2 掌握临时表的使用 3 掌握自定义函数的使用
1.1 什么是存储过程
用来执行管理任务或应用复杂的业务规则 存储过程可以带参数，也可以返回结果
SQL语句 GO
和Java语言的方法一样，参数可选 参数分为输入参数、输出参数 输入参数允许有默认值
1.3 创建存储过程
使用模板创建存储过程
（1）在SQL Server 管理平台中，选择“视图（View）”菜单中的“模 板资源资源管理器(Template Explorer)”，出现“模板资源管理器(Template Explorer)”窗口，选择“存储过程”中的“创建存储过程”选项，如下图所 示。
EXEC sp_columns stuInfo EXEC sp_help stuInfo
查看表stuInfo的信息
EXEC sp_helpconstraint stuInfo EXEC sp_helpindex stuMarks
查看表stuInfo的约束
EXEC sp_helptext 'view_stuInfo_stuMarks' 查看表stuMarks的索引

第7 章存储过程、触发器和用户自定义函数（6课时）主要内容：1 存储过程（概述、创建与执行、修改与删除）2 触发器（概述、DML触发器、DDL触发器）3 用户自定义函数（概述、标量函数的建立与调用、内嵌表值函数的建立与调用、多语名表值函数的建立与调用）存储过程是一个可重用的代码模块，可以高效率地完成指定的操作。

触发器是一种特殊类型的存储过程，可以实现自动化的操作。

用户定义函数是由用户根据应用程序的需要而定义的可以完成特定操作的函数。

这三种数据库对象都可以通过两种方法来定义：●SQL Server Management Studio工具●命令这里只讨论通过命令的方式定义相应对象。

7.1 存储过程7.1.1 存储过程概述当使用SQL Server创建应用时，TRANSACT-SQL语言是应用程序与SQL Server数据库之间的主要编程接口。

使用TRANSACT-SQL语言进行程序设计时，有两种方式：一种方式是在应用程序中直接使用T-SQL语句向SQL Server发送命令；另一种方式就是使用存储过程。

存储过程是一种数据库对象，由一组预编译的T-SQL语句组成，这些语句在一个名称下存储，并作为一个单元进行处理。

存储过程类似于其他编程语言中的函数或过程：能够使用传递给它的参数，能够调用其它存储过程甚至本身，能够返回一个状态码来表示是否成功执行。

在SQL Server 2008系统中，除了可以使用Transact-SQL语言编写存储过程外，也可以使用CLR方式编写存储过程。

【CLR，公用语言运行时（Commen Language Runtime），.NET 提供了一个运行时环境，它负责资源管理（内存分配和垃圾收集），并保证应用和底层操作之间必要的分离。

是一种多语言执行环境，支持众多的数据类型和语言特性。

他管理着代码的执行，并使开发过程变得更加简单。

】SQL Server中有三类存储过程：系统存储过程（sp_为前缀）、用户自定义存储过程和扩展存储过程（xp_为前缀，扩展了SQL Server的功能，使得用户能调用外部例程(自已编写的程序或系统提供的命令)，从SQL Server2005版本开始，将逐步删除扩展存储过程类型，因为使用CLR存储过程可以可靠和安全地替代扩展存储过程的功能）。

RETURNS nvarchar(20)
BEGIN DECLARE @returnstring nvarchar(20)
38
SET
@returnstring='今天是' +CONVERT(nvarchar(5), datepart(year,@date)) +‘年’+@separator +CONVERT(nvarchar(5), datepart(month,@date)) +‘月’ +@separator +CONVERT(nvarchar(5), datepart(day,@date)) +'日‘ RETURN @returnstring END
ELSE SET @returnstring='热销商品' RETURN @returnstring END
返回的变量
22
执行命令
23
函数调用
视图
在Northwind库上有一个记录各种产品在 1997年销售额的视图，通过它来引用新建 的my_function1，查看哪些商品属于热销 商品
24
商品名称
17
例：创建一个自定义函数，计算某 人到现在为止的工龄工资
思路： 工龄工资=已工作年数 * 工作一年的工龄工资 已工作年数=当前时间 – 开始参加工作时间 工龄工资=(当前时间-开始参加工作时间) *年工龄工资
18
19
例：在Northwind库上创建 自定义函数my_function1， 该函数实现输入代表商品年 销售额的money类型参数的 值后返回字符串，如果年销 售额大于10000，返回“热 销商品”，否则返回“非热 销商品”
SQL Server 2005 支持用户定义 函数和内置函数。

视图简介
视图是从一个或多个表或视图中导出的表。其结构和
数据是建立在对表的查询基础上的，和表一样视图也 是包括几个被定义的数据列和多个数据行。但就本质 而言这些数据列和数据行来源于其所引用的表，所以 视图不是真实存在的基础表，而是一张虚表。视图所 对应的数据并不实际地以视图结构存储在数据库中， 而是存储在视图所引用的表中。 视图一经定义便存储在数据库中，与其相对应的数据 并没有像表那样又在数据库中再存储一份。通过视图 看到的数据只是存放在基本表中的数据。 对视图的操作与对表的操作一样，可以对其进行查询、 修改、一定限制的删除，同时若基本表的数据发生变 化则这种变化也可以自动地反映到视图中。
DROP INDEX 'tablename.indexname' [,...n]
DROP INDEX 命令不能删除由CREATE
TABLE 或ALTER TABLE 命令创建的 PRIMARY KEY 或UNIQUE 约束索引也不能 删除系统表中的索引
索引的规划
索引与系统性能
– 索引可以加快数据检索的速度，但它会使数据的 插入删除和更新变慢。尤其是簇索引，数据是按 照逻辑顺序存放在一定的物理位置，当变更数据 时，根据新的数据顺序需要将许多数据进行物理 位置的移动，这将增加系统的负担。对非簇索引 数据更新时，也需要更新索引页，这也需要占用 系统时间。 – 因此在一个表中使用太多的索引会影响数据库的 性能。对于一个经常会改变的表应该尽量限制， 表只使用一个簇索引和不超过3~4个非簇索引；对 事务处理特别繁重的表，其索引应尽量不超过3个
SQL Server 索引类型
非簇索引
非簇索引具有与表的数据完全分离的结构。使用非簇索引不用将 物理数据页中的数据按列排序。非簇索引的叶节点中存储了组 成非簇索引的关键字的值和行定位器。行定位器的结构和存储 内容取决于数据的存储方式： 如果数据是以簇索引方式存储的，则行定位器中存储 的是簇索引的索引键。 如果数据不是以簇索引方式存储的，这种方式又称为堆存储 方式。行定位器存储的是指向数据行的指针。 非簇索引将行定位器按关键字的值用一定的方式排序，这个顺 序与表的行在数据页中的排序是不匹配的。由于非簇索引使用 索引页存储，因此它比簇索引需要更多的存储空间，且检索效 率较低。但一个表只能建一个簇索引，当用户需要建立多个索 引时，就需要使用非簇索引了。 从理论上讲一个表最多可以建249个非簇索引

存储过程和存储函数实验报告实验目的通过本次实验，掌握存储过程和存储函数的基本概念、使用方法以及相应的应用场景。

实验环境- 操作系统：Windows 10- 数据库管理系统：MySQL 8.0- 开发工具：MySQL Workbench 8.0实验内容1. 创建存储过程和存储函数存储过程和存储函数可以使用MySQL Workbench 8.0自带的编辑器进行创建和编辑，也可以在MySQL命令行中使用SQL 语句进行创建。

创建存储过程的SQL语句如下：```mysqlCREATE PROCEDURE 存储过程名([参数列表])BEGIN存储过程的主体（SQL语句）END;```创建存储函数的SQL语句如下：```mysqlCREATE FUNCTION 存储函数名([参数列表]) RETURNS 返回值类型BEGIN存储函数的主体（SQL语句）RETURN 返回值;END;```其中，参数列表和返回值类型可以不设置，存储过程和存储函数的主体可以包含任意的SQL语句。

以下是一个简单的存储过程和存储函数的创建示例：```mysqlCREATE PROCEDURE add(a INT, b INT)BEGININSERT INTO table1 VALUES (a+b);END;CREATE FUNCTION multiply(a INT, b INT) RETURNS INT BEGINDECLARE c INT;SET c = a*b;RETURN c;END;```2. 调用存储过程和存储函数存储过程和存储函数可以在MySQL命令行中使用CALL语句进行调用，也可以在MySQL Workbench的SQL编辑窗口中编写调用语句执行。

调用存储过程的CALL语句如下：```mysqlCALL 存储过程名(参数列表);```调用存储函数的SELECT语句如下：```mysqlSELECT 存储函数名(参数列表);```例如，使用上述示例中的存储过程和存储函数进行调用的示例如下：```mysqlCALL add(3, 5);SELECT multiply(3, 5);```3. 存储过程和存储函数的应用场景存储过程和存储函数在数据库开发中有着重要的应用场景，主要体现在以下几个方面：3.1 封装复杂的业务逻辑存储过程和存储函数可以将复杂的业务逻辑封装起来，提高系统的稳定性和安全性。