实验11 存储过程和用户自定义函数_图
75-用Managed Code创建存储过程和用户自定义函数

导言:数据库,比如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版本数据库的实例。
第十章_存储过程触发器及自定义函数(1)

使用存储过程模板创建存储过程:
⑵ 修改存储过程的编程模板,插入需要的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五级)。
第8章 存储过程触发器和用户定义函数PPT课件

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,
存储过程、触发器和用户定义函数汇总

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值。
第11章--MYSQL存储过程与函数--2019-02-07

存储过程与函数操作
创建和使用存储过程或函数 变量 定义条件和处理 游标的使用 流程的控制 查看存储过程或函数 删除存储过程或函数
创建和使用存储过程或函数
存储过程 创建存储函数 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)。
- 1、下载文档前请自行甄别文档内容的完整性,平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益,请联系客服反馈,我们会尽快为您处理(人工客服工作时间:9:00-18:30)。
实验11 存储过程和用户自定义函数
实验目的
1.掌握通过企业管理器创建、修改、删除存储过程和用户自定义函数的方法
2.学会编写存储过程和用户自定义函数
3.掌握存储过程的执行方法
4.学会编写、调用三类用户自定义函数
实验准备
1.学习存储过程和用户自定义函数相关知识。
2.已掌握常程序控制流语句。
3.熟练使用T-SQL完成数据查询和程序设计。
4.还原studentdb数据库
实验内容和步骤
1.打开企业管理器,展开studentdb子目录,选中“存储过程”,单击鼠标右键,弹出
快捷菜单,选择【新建存储过程(S)…】,打开新建存储过程窗口,如图11- 1。
图11- 1 新建存储过程
2.新建并执行存储过程“字母打印”。
(1)在新建存储过程窗口输入以下代码。
CREATE PROCEDURE 字母打印AS
注解:该存储过程是将26个小写英文字母按a~z的顺序输出,其中ascii()函
数——返回字符对应ASCII码,char()函数——把ASCII码转换成对应字符。
(2)输入完成后,单击【检查语法】按钮,确认输入内容正确后,单击【确认】按
钮完成存储过程的创建。
(3)打开查询分析器,输入:
exec 字母打印
(4)执行,查看运行结果。
3.修改存储过程“字母打印”并执行。
(1)在企业管理器存储过程列表窗格中,选中存储过程“字母打印”,弹出快捷菜
单,选择【属性(R)】,或直接双击该存储过程,打开属性窗口,如图11- 2。
图11- 2 存储过程“字母打印”属性窗口
(2)修改代码内容,将“print char(ascii('a')+@count)”改为“print
char(ascii('A')+@count)”。
(3)单击【确认】按钮,完成存储过程的修改。
(4)重新在查询分析器执行该存储过程,查看运行结果。
4.新建并执行带输入参数的存储过程。
(1)在企业管理器中新建存储过程“成绩查询”,代码如下:
(2)在查询分析器窗口中,选择studentdb数据库。
要求:通过存储过程“成绩查
询”查看学号为“2007224117”的成绩。
●方法一:输入exec 成绩查询‘2007224117’,并执行。
●方法二:输入
执行,查看该同学的成绩。
注:以上是执行含输入参数存储过程的常用方法,参数可以直接通过值传递,
也可以通过变量传递。
5.练习:请新建存储过程“学生信息”,输入参数仍为学号,返回学号对应的“学生”
表信息,并通过该存储过程查看学号为“2007224117”的个人信息。
6.新建带返回参数的存储过程并执行。
(1)在企业管理器中新建存储过程“学生平均成绩”,代码如下:
(2)在查询分析器窗口中,选择studentdb数据库,输入代码:
执行,查看运行结果。
注:执行带有返回参数的存储过程时,必须先定义变量(存返回值),exec语
句须加output关键字。
7.练习:请新建存储过程“班级平均成绩”,输入参数为专业、年级、班序号,输出
对应的平均总评成绩。
并使用该存储过程查询专业为1009,年级为2008,班序号为1的平均总评成绩。
8.新建存储过程“学生成绩分析”,输入参数“学号”,判断该学号对应平均考试成绩
若在90分及以上,认为优秀,60分以下为差,其他情况为一般,并把结果存在“学生考试评价”表中。
(1)在查询分析器中输入以下代码,生成【学生考试评价】表。
CREATE TABLE [dbo].[学生考试评价] (
[学号] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[平均成绩] [int] NULL ,
[考试评价] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY]
GO
ALTER TABLE [dbo].[学生考试评价] ADD
CONSTRAINT [PK_学生考试评价] PRIMARY KEY CLUSTERED
(
[学号]
) ON [PRIMARY]
GO
(2)录入存储过程代码
注:IF的嵌套也可以通过CASE语句完成。
参考:
set @评价=(case when @平均分>=85 then '优秀'
when @平均分>=60 and @平均分<85 then '一般'
when @平均分<60 then '差' end)
insert into 学生考试评价(学号,平均成绩,考试评价)
values(@学号,@平均分,@评价)
或者:
insert into 学生考试评价(学号,平均成绩,考试评价)
values(@学号,@平均分,(case when @平均分>=85 then '优秀'
when @平均分>=60 and @平均分<85 then '一般'
when @平均分<60 then '差' end))
(3)运行存储过程。
9.新建用户自定义函数“DateToQuarter”并调用该函数。
(1)在企业管理器中展开studentdb子目录,选中“用户定义的函数”,单击鼠标右
键,弹出快捷菜单,选择【新建用户定义的函数(U)…】,打开新建用户自
定义函数窗口,如图11- 3。
图11- 3 新建用户自定义函数窗口
(2)在新建用户自定义函数窗口文本区域输入代码:
注:该函数的功能是将输入的日期数据转换为该日期对应的季度值。
如输入
‘2006-8-5’,返回‘3Q2006’,表示2006年3季度。
(3)调用该函数,返回当前日期对应的季度值。
在查询分析器中输入:
select dbo. DateToQuarter(getdate())
执行并查看运行结果,学会标量函数的调用方法。
10.新建并应用数字转换中文大写函数“NumToStr”
(1)在企业管理器打开新建用户自定义函数窗口,输入代码:
(2)在查询分析器中,调用该函数,测试是否正确。
(3)使用该函数,编写程序,完成0~99的数字大写转换,代码如下:
(4)设置@num值为30,查看结果,修改程序,完善该程序;能否编写程序完成
任意数字的中文大写转换。
11.新建并执行表值函数“stuInfo”,输入学号,返回对应学生信息
(1)仍在企业管理器打开新建用户自定义函数窗口,代码如下:
(2)调用该函数,查看学号为“2007224117”的个人信息,在查询分析器中输入:
select * from dbo.stuInfo('2007224117'),执行并查看运行结果,学会表值函数的
调用方法。
注:当调用标量值函数时,必须加上‚所有者‛,通常是dbo(但不是绝对,可以在企业管理器中的‚用户定义函数‛中查看所有者),调用表值函数时,可以只使用函数名。
12.新建并执行多语句表值函数“stuScore”,输入学号、课程号,返回对应学生姓名、
课程名和成绩。
(1)仍在企业管理器打开新建用户自定义函数窗口,代码如下:
(2)调用该函数,查看学号’2007122310’课程代码1239的成绩。
select * from stuScore('2007122310',1239)
13.删除自定义函数“xDelay”。
(1)在企业管理器用户定义的函数窗格中,选中“xDelay”。
(2)按DEL键或单击鼠标右键,弹出快捷菜单,选择【删除(D)】,打开“除去
对象”对话框。
(3)单击【全部除去】按钮,完成删除。
实验思考
1.请修改存储过程“字母打印”,要求按Z~A的顺序输出26个大写英文字母。
2.调用存储过程和用户自定义函数的方法是否全部掌握?
3.在执行带输出参数存储过程时,须先定义变量来传递输出参数,请问如何确定变量
的数据类型?
4.对比存储过程与用户自定义函数在使用上的不同。