实验七 存储过程创建与应用.doc

实验七 存储过程创建与应用 一、实验目的 使学生理解存储过程的概念，掌握创建存储过程的使用、执行存储过程和查看、修改、删除存储过程的方法。 二、实验内容 (1) 创建存储过程。 (2) 修改存储过程。 (3) 调用存储过程。 (4) 删除存储过程。 三、实验指导 1. 存储过程的创建 存储过程是一系列编辑好的、能实现特定数据操作功能的SQL代码集，它与特定的数据库相关联，存储在SQL Server服务器上。用户可以像使用自定义函数一样重复调用这些存储过程，实现它所定义的操作。 (1) 存储过程的类型 存储过程分为3类：系统存储过程、用户自定义存储过程和扩展存储过程。 ① 系统存储过程主要存储在master数据库中并以sp_为前缀。 ② 用户自定义存储过程是由用户创建并能完成某一特定功能（如查询用户所需数据信息）的存储过程，是封装了可重用代码的SQL语句模块。 ③ 扩展存储过程允许使用高级编程语言(例如C语言)创建应用程序的外部例程，从而使得SQL Server的实例可以动态地加载和运行DLL。 (2) 利用SQL Server Management Studio模板创建存储过程步骤如下： ① 打开SQL Server Management Studio窗口，连接到学生选课数据库。 ② 依次展开“服务器”→“数据库”→“JXGL”→“可编程性”节点。 ③ 在列表中右击“存储过程”节点出现快捷菜单，选择“新建存储过程”命令，然后出现如图1.6.1所示的“create procedure 语句的模板”，可以修改要创建的存储过程的名称，然后加入存储过程所包含的T-SQL语句。 ④ 修改完后，单击执行按钮即可创建一个存储过程。 (3) 利用T-SQL创建存储过程 一般来说，创建一个存储过程应按照以下步骤进行： T-SQL语句测试T-SQL语句是否正确，并能实现功能要求若得到的结果数据符合预期要求，则按照存储过程的语法，创建该存储过程执行该存储过程，验证其正确性。 使用T-SQL语句创建存储过程的语法格式： CREATE PROCEDURE procedure_name [;number] [@parameter data_type [=default], … n] AS sql_statement 说明： procedure name：给出存储过程名。 Number：为可选的整数，对同名的存储过程指定一个序号。 @parameter：为存储过程的形参，@符号作为第一个字符来指定参数名。 data_type：指出参数的数据类型。 =default：给出参数的默认值。 Sqlstatement：存储过程所要执行的SQL语句，它可以是一组SQL语句，可以包含流程控制语句等。 图1.9.1 创建存储过程界面 下面实验中都是在数据库 “JXGL”中，其表有“S”、“C”、“SC”表结构所示，通过T-SQL语句创建一个存储过程。 实验7.1 创建名为s_grade的存储过程，要求实现如下功能：查询“JXGL”数据库中每个学生各门功能的成绩，其中包括每个学生的sno，sname，cname，grade。在查询编辑器窗口输入创建该存储过程的语句如下： USE JXGL GO CREATE PROCEDURE s_grade AS SELECT S.sno,sname,cname,grade FROM S JOIN C ON S.sno=SC.sno JOIN C ON SC.cno=C.cno GO 调用存储过程s_grade，在查询编辑器窗口输入调用该存储过程的语句如下： USE JXGL GO EXEC s_grade GO 实验7.2创建名为proc_exp的存储过程，要求实现如下功能：输入某学生的姓名，从SC表中查询该学生的平均成绩。在查询编辑器窗口输入创建该存储过程的语句如下： USE JXGL GO CREATE PROCEDURE proc_exp @s_name char(20) AS SELECT avg(grade) AS 平均成绩 FROM S JOIN SC ON S.sno=SC.sno and sname=@s_name GO 调用存储过程proc_exp，求得“姜芸”同学的平均成绩。在查询编辑器窗口输入调用该存储过程的语句如下： USE JXGL GO EXEC proc_exp 姜芸 GO 实验7.3创建名为s_info的存储过程，要求实现如下功能：输入某学生的姓名，输出该学生所学课程门数以及他的平均成绩。在查询编辑器窗口输入创建该存储过程的语句如下： USE JXGL GO CREATE PROCEDURE s_info @s_name char(8) AS DECLARE @s_count in