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

实验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.对比存储过程与用户自定义函数在使用上的不同。