实验五：T-SQL及存储过程.docVIP

实验五：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语句序列 说明： RECOMPILE表明每次运行该过程时，将其重新编译。 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_HELPTE