实验五 存储过程和触发器的使用
- 格式：doc
- 大小：402.50 KB
- 文档页数：20
实验五 存储过程和触发器的使用
【目的要求】
1、了解存储过程的基本概念和类型。
2、了解创建存储过程的T-SQL语句的基本语法。
3、了解查看、执行、修改和删除存储过程的T-SQL命令的用法。
4、了解触发器的基本概念和类型。
5、了解创建触发器的T-SQL语句的基本语法。
6、了解查看、修改和删除存储过程的T-SQL命令的用法。
【实验内容】
内容一：存储过程的使用
一、数据需求分析
存储过程是一种数据库对象，为了实现某个特定任务，将一组预编译的SQL语句以一个存储单元的形式存储在服务器上，供用户调用，自动完成需要预先执行的任务。存储过程在第一次执行时进行编译，然后将编译好的代码保存在高速缓存中便于以后调用，提高了代码的执行效率。
二、内容要点分析
1、SQL SERVER支持五种类型的存储过程：系统存储过程、本地存储过程、临时存储过程、远程存储过程和扩展存储过程。其中，系统存储过程是由系统提供的存储过程，可以作为命令执行各种操作。系统存储过程定义在系统数据库master中，其前缀是sp_。本地存储过程是指在用户数据库中创建的存储过程，这种存储过程完成特定数据库操作任务，不能以sp_为前缀。
2、只能在当前数据库中创建存储过程。
3、创建存储过程时，应指定所有输入参数和向调用过程或批处理返回的输出参数、执行数据库操作的编程语句和返回至调用过程或批处理以表明成功或失败的状态值。
4、创建存储过程的T-SQL语句
CREATE PROC[EDURE] 存储过程名称
[{ @参数名称 数据类型 }] [,„n]
[WITH
{ RECOMPILE|ENCRYPTION }]
AS
SQL语句序列
说明：
（1） RECOMPILE表明每次运行该过程时，将其重新编译。
（2） ENCRYPTION表示 SQL SERVER 加密SYSCOMMENTS表中包含
CREATE PROCEDURE语句文本的条目。
注：必须将CREATE PROCEDURE语句放在单个批处理中。
成功执行CREATE PROCEDURE语句后，存储过程存储在sysobjects系统表中。
5、执行存储过程
EXEC[UTE] @返回值=存储过程名称 参数值
6、查看存储过程
查看存储过程的文本信息：SP_HELPTEXT 存储过程名 查看存储过程的一般信息：SP_HELP 存储过程名
查看存储过程的相关信息：SP_DEPENDS 存储过程名
7、编辑修改存储过程
ALTER PROCEDURE 存储过程名称
[{ @参数名称 数据类型 }][,„n]
[WITH
{ RECOMPILE|ENCRYPTION }]
AS
SQL语句序列
说明：各参数含义与CREATE PROCEDURE命令相同。
8、删除存储过程
DROP PROCEDURE 存储过程名称[,…N]
三、实验内容
1、使用“学生信息”数据库中的“学生”表、“课程注册”表和“学期成绩”表，创建一个存储过程xsxx_proc。查询学生的姓名、课程名称和成绩。
方法一：使用企业管理器
（1） 打开企业管理器。
（2） 依次展开控制台根目录。
（3） 双击“学生信息”数据库节点，在其右边明细窗格中右键单击“存储过程”图标。
（4） 在弹出的快捷菜单中选择“新建存储过程”命令。
图5—1 选择“新建存储过程”命令
（5）执行“新建存储过程”命令，打开“存储过程属性”对话框，如图5—2所示。
图5—2 “存储过程属性”对话框
（6）在其文本框中首先输入所有者和存储过程名称，例如用所有者DBO替换OWNER,用过程名XSXX_PROC替换PROCEDURE NAME。
（7）在文本框的第二行输入存储过程文本，根据题意输入如下语句：
IF EXISTS(SELECT NAME FROM SYSOBJECTS
WHERE NAME='xsxx_proc' AND TYPE='p')
DROP PROCEDURE XSXX_PROC
GO
SELECT 姓名,课程名称,成绩
FROM 学生,学期成绩,课程注册
WHERE 学生.学号=学期成绩.学号
AND 学期成绩.课程编号=课程注册.课程编号
GO
（8）输入完成后，如图5—3所示。
图5—3 “存储过程属性”
（9）进行语法检查。
（10）如果没有错误，单击“确定”按钮，将存储过程保存到数据库中。
注：自行完成以下各例使用企业管理器的方法。
方法二：使用查询分析器
步骤：
（1） 打开查询分析器。
（2） 在查询子窗口中输入如下程序代码。
USE 学生信息
GO
IF EXISTS(SELECT NAME FROM SYSOBJECTS
WHERE NAME='xsxx_proc' AND TYPE='p')
DROP PROCEDURE XSXX_PROC
GO
CREATE PROCEDURE xsxx_proc AS
SELECT 姓名,课程名称,成绩
FROM 学生,学期成绩,课程注册
WHERE 学生.学号=学期成绩.学号
AND 学期成绩.课程编号=课程注册.课程编号
GO
（3） 语法检查
（4） 运行代码
（5） 在查询结果显示窗口查看结果并分析结果。
2、使用“学生信息”数据库，创建带参数的存储过程xbrs_proc。返回各系的男、女生人数。
步骤：
（1） 打开查询分析器。
（2） 在查询子窗口中输入如下程序代码。
USE 学生信息
GO
CREATE PROCEDURE xbrs_proc
@xbmc char(8) /*局部变量必须先定义后使用*/
AS
SELECT 系别,性别,COUNT(*) AS 人数
FROM 学生
WHERE 系别=@xbmc
GROUP BY 系别,性别
GO
（3） 语法检查
（4） 运行代码
（5） 在查询结果显示窗口查看结果并分析结果。
3、使用“学生信息”数据库中的“学生”表，创建一个加密的存储过程jmxs_proc。返回计算机应用专业的所有学生的信息。
步骤：
（1） 打开查询分析器。
（2） 在查询子窗口中输入如下程序代码。
USE 学生信息
GO
CREATE PROCEDURE jmxs_proc
WITH ENCRYPTION
AS
SELECT *
FROM 学生
WHERE 专业='计算机应用'
GO
（3） 语法检查
（4） 运行代码
（5） 在查询结果显示窗口查看结果并分析结果。
4、执行已存在的存储过程xsxx_proc。
步骤：
（1） 打开查询分析器。
（2） 在查询子窗口中输入如下程序代码。
USE 学生信息
GO
exec xsxx_proc
GO
（3） 语法检查
（4） 运行代码
（5） 在查询结果显示窗口查看结果并分析结果。
5、执行已存在的带参数的存储过程xbrs_proc，返回“管理系”的学生人数以及男、女生人数。
步骤：
（1） 打开查询分析器。
（2） 在查询子窗口中输入如下程序代码。
USE 学生信息
GO
Exec xbrs_proc '管理系'
GO
（3） 语法检查
（4） 运行代码
（5） 在查询结果显示窗口查看结果并分析结果。
6、分别查看XSXX_PROC存储过程的一般信息、文本信息和依赖关系。
步骤：
（1） 打开查询分析器。
（2） 在查询子窗口中输入如下程序代码。
USE 学生信息
GO
EXEC SP_HELP xsxx_proc
EXEC SP_HELPTEXT xsxx_proc
EXEC SP_DEPENDS xsxx_proc
GO
（3） 语法检查
（4） 运行代码
（5） 在查询结果显示窗口查看结果并分析结果。
7、查看JMXS_PROC存储过程的一般信息。
步骤：
（1） 打开查询分析器。
（2） 在查询子窗口中输入如下程序代码。
USE 学生信息
GO
SP_HELP jmxs_proc
GO
（3） 语法检查
（4） 运行代码
（5） 在查询结果显示窗口查看结果并分析结果。
8、修改XSXX_PROC存储过程，返回计算机系学生的姓名、课程名称和成绩。
步骤：
（1） 打开查询分析器。
（2） 在查询子窗口中输入如下程序代码。
USE 学生信息
GO
ALTER PROCEDURE xsxx_proc
AS
SELECT 姓名,课程名称,成绩
FROM 学生,学期成绩,课程注册
WHERE 学生.学号=学期成绩.学号
AND 学期成绩.课程编号=课程注册.课程编号
AND 系别= '计算机系'
GO
（3） 语法检查
（4） 运行代码
