实验五：T-SQL与存储过程

实验五：T-SQL与存储过程

一、实验目的

1、了解创建存储过程的T-SQL语句的基本语法

2、掌握使用界面操作方式和命令方式创建存储过程的方法和步骤 3、掌握存储过程的使用方法 二、实验平台和实验工具

Window 7、SQL Server 2008 三、实验相关知识

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、使用学生课程数据库S-T中的“student”表、“course”表和“sc”表，创建一个存储过程xsxx_proc。查询学生的姓名、课程名称和成绩。 代码： USE S-T 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 sname,cname,grade FROM student,sc,course WHERE student.sno=sc.sno

AND sno.cno=course.cno

GO

2、创建带参数的存储过程xbrs_proc。返回各系的男、女生人数。 代码： USE S-T GO

CREATE PROCEDURE xbrs_proc

@xbmc char(8) /*局部变量必须先定义后使用*/ AS

SELECT sdept,ssex,COUNT(*) AS 人数 FROM student WHERE sdept=@xbmc GROUP BYsdept,ssex GO

3、使用 “student”表，创建一个加密的存储过程jmxs_proc。返回信息系的所有学生的信息。 代码： USE S-T GO

CREATE PROCEDURE jmxs_proc WITH ENCRYPTION AS SELECT * FROM student WHERE sdept=‘is’ GO

4、执行已存在的存储过程xsxx_proc。 步骤： USE S-T GO

exec xsxx_proc GO

5、执行已存在的带参数的存储过程xbrs_proc，返回“cs”的学生人数以及男、女生人数。 步骤： USE S-T GO

Exec xbrs_proc ‘cs’ GO

6、分别查看XSXX_PROC存储过程的一般信息、文本信息和依赖关系。 步骤： USE S-T GO

EXEC SP_HELP xsxx_proc EXEC SP_HELPTEXT xsxx_proc

EXEC SP_DEPENDS xsxx_proc GO

7、查看JMXS_PROC存储过程的一般信息。 步骤： USE S-T

GO

SP_HELP jmxs_proc GO

8、修改XSXX_PROC存储过程，返回计算机系学生的姓名、课程名称和成绩。 步骤： USE S-T GO

ALTER PROCEDURE xsxx_proc AS

SELECT sname,cname,grade FROM student,sc,course WHERE student.sno=sc.sno

AND sc.cno=course.cno AND sdept= 'cs'

GO

9、删除JMXS_PROC存储过程。 步骤： USE S-T GO

DROP PROCEDURE jmxs_proc GO 四、实验任务

1、对学生课程数据库S-T，编写存储过程，完成下面功能：

1）统计离散数学的成绩分布情况，即按照各分数段统计人数； 2）将学生选课成绩从百分制改为等级制（即A、B、C、D、E）；

要求：提交源程序并标识必要的注释。保证程序能正确编译和运行，认真填写实验报告。

2、简述下列程序的运行结果并完成实验，并检验效果。 USE S-T

IF EXISTS(SELECT name from sysobjects

Where name=’根据性别显示学生综合信息’ and type=’p’) DROP PROCEDURE 根据性别显示学生综合信息 GO

CREATE PROC根据性别显示学生信息 @性别_1 char(2)

AS SELECT 学号，姓名 FROM student Where 性别=@性别_1

3、创建存储过程isexcellent，实现如下功能：根据学号检查数据库原理成绩是否优秀，并用select传回一个变量值，若优秀则值为1，并输入提示信息“数据库原理成绩优秀，继续！”；否则值为0，并输入提示信息“数据库原理成绩一般，努力！”。（假定>=90分为优秀）

本文来源：https://www.bwwdw.com/article/olrd.html