6实验六--存储过程

实验六实验六 存储过程存储过程 一 实验目的一 实验目的 1 掌握T SQL流控制语句 2 掌握创建存储过程的方法 3 掌握存储过程的执行方法 4 掌握存储过程的管理和维护 二 实验内容二 实验内容 1 1 创建简单存储过程 创建简单存储过程 1 创建一个名为stu pr的存储过程 该存储过程能查询出051班学生的所有资料 包 括学生的基本信息 学生的选课信息 含未选课同学的信息 要求在创建存储过程前请判 断该存储过程是否已创建 若已创建则先删除 并给出 已删除 信息 否则就给出 不存 在 可创建 的信息 if exists select from sysobjects where name stu pr and type P begin drop procedure stu pr print 已删除 end else print 不存在 可创建 create procedure stu pr as select distinct from Student s left join SC on s Sno SC Sno left join Course c on c Cno sc Cno where Classno 051 执行 执行 exec stu pr 2 2 创建带参数的存储过程 创建带参数的存储过程 1 创建一个名为stu proc1的存储过程 查询某系 某姓名的学生的学号 姓名 年 龄 选修课程名 成绩 系名和姓名在调用该存储过程时输入 其默认值分别为 与 林 执行该存储过程 用多种参数加以测试 if exists select from sysobjects where name stu proc1 and type P begin drop procedure stu proc1 print 已删除 end else print 不存在 可创建 create procedure stu proc1 sdept varchar 10 sname varchar 10 林 as select Sname s Sno YEAR getdate YEAR Birth Age Cname Grade from Student s Course c SC where s Sno sc Sno and c Cno sc Cno and s Sname like sname and s Sdept like sdept 执行 执行 exec stu proc1 exec stu proc1 sdept sname 林 2 创建一个名为Student sc的存储过程 可查询出某段学号的同学的学号 姓名 总 成绩 学号起始号与终止号在调用时输入 可设默认值 执行该存储过程 if exists select name from sysobjects where name Student sc and type P drop procedure Student sc go create procedure Student sc sno begin varchar 10 20110001 sno end varchar 10 20110103 as select s Sno Sname SUM grade total grade from Student s SC where s Sno sc Sno and s Sno between sno begin and sno end group by s Sno Sname 执行 执行 exec Student sc 3 3 创建带输出参数的存储过程 创建带输出参数的存储过程 1 创建一个名为Course sum的存储过程 可查询某门课程考试的总成绩 总成绩可以输出 以便进一步调用 if exists select name from sysobjects where name Course sum and type P drop procedure Course sum create procedure Course sum cname varchar 10 as select SUM grade total grade COUNT sno sno from Course c SC where c Cno sc Cno and Cname like cname 执行 执行 exec Course sum 高数 2 创建一执行该存储过程的批处理 要求当总成绩小于100时 显示信息为 XX课程 的总成绩为 XX 其总分未达100分 超过100时 显示信息为 XX课程的总成绩为 XX create procedure sum grade2 cname varchar 10 sum smallint output as select sum sum grade from Course c SC where c Cno sc Cno and Cname like cname declare sumgrade smallint exec sum grade2 高数 sumgrade output if sumgrade 100 begin print 高数的总成绩为 CAST sumgrade AS varchar 20 其总分未达到 分 end else print 高数的总成绩为 CAST sumgrade AS varchar 20 4 4 创建带重编译及加密选项的存储过程 创建带重编译及加密选项的存储过程 创建一个名为update sc 并带重编译及加密选项的存储过程 可更新指定学号 指定 课程号的学生的课程成绩 学号 课程号由调用时输入 if exists select name from sysobjects where name update sc and type P drop procedure update sc create procedure update sc cno varchar 10 sno varchar 10 grade int with recompile encryption 重编译 加密 as update SC set grade grade where SC Cno cno and SC Sno sno exec update sc 001 20110001 80 5 5 使用 使用T SQLT SQL语句管理和维护存储过程语句管理和维护存储过程 1 使用sp helptext查看存储过程Student sc的定义脚本 sp helptext Student sc 2 使用select语句查看Student sc存储过程的定义脚本 提示 通过查询表sysobjects 和表syscomments select o id c text from sysobjects o inner join syscomments c on o id c id where o type p and o name Student sc 3 将存储过程stu pr改为查询学号为2011001的学生的详细资料 alter procedure stu pr sno varchar 10 as select distinct from Student where Sno 20110001 exec stu pr sno 2011001 4 删除存储过程stu pr drop procedure stu pr 6 6 使用 使用SQL Server Management Studio管理存储过程管理存储过程 1 在SQL Server Management Studio中重新创建刚删除的存储过程stu pr 选择数据库选择数据库Student info Student info 可编程性可编程性 存储过程 右击存储过程 右击 存储过程存储过程 新建存储过程新建存储过程 出现如下画面 出现如下画面 修改存储过程的名称为修改存储过程的名称为stu prstu pr 修改修改SQLSQL语句 使之成为完整的存储过程语句 使之成为完整的存储过程 CREATE PROCEDURE stu pr Add the parameters for the stored procedure here AS BEGIN SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements SET NOCOUNT ON Insert statements for procedure here SELECT distinct from Student s left join SC on s Sno SC Sno left join Course c on c Cno sc Cno where Classno 051 END 2 查看存储过程stu pr 并将该过程修改为查询051班女生的所有资料 可编程性可编程性 存储过程存储过程 stu pr stu pr 右击 右击stu pr stu pr 修改修改 出现如下画面 出现如下画面 修改修改SQLSQL语句 使之能查询语句 使之能查询051051班所有女生的资料的存储过程班所有女生的资料的存储过程 ALTER PROCEDURE dbo stu pr Add the parameters for the stored procedure here AS BEGIN SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements SET NOCOUNT ON Inse