SQL-server上机练习题-何.docVIP

含触发器、存储过程、游标 一、触发器练习 1========== 示例说明inserted，deleted表的作用。 程序清单如下： create table sc (sno char(10), cno char(2), score real) Go CREATE TRIGGER tr1 ON sc FOR INSERT, UPDATE, DELETE AS PRINT ‘inserted表：’ Select * from inserted PRINT ‘deleted表：’ Select * from deleted Go 2======== 创建一个触发器，在 s 表上创建一个插入、更新类型的触发器。 程序清单如下： CREATE TRIGGER tr_s ON s FOR INSERT, UPDATE AS Begin DECLARE @bh varchar(6) SELECT @bh =inserted.sno FROM inserted /*获取插入或更新操作时的新值（学号）*/ End 3=============== 使用INSERT触发器 INSERT触发器通常被用来更新时间标记字段，或者验证被触发器监控的字段中数据满足要求的标准，以确保数据的完整性。 例9-3建立一个触发器，当向sc表中添加数据时，如果添加的数据与s表中的数据不匹配（没有对应的学号），则将此数据删除。 程序清单如下： CREATE TRIGGER sc_ins ON sc FOR INSERT AS BEGIN DECLARE @bh char(5) Select @bh=Inserted.sno from Inserted If not exists(select sno from s where s.sno=@bh) Delete sc where sno=@bh END 二、存储过程练习 1========= 创建一个带有SELECT语句的简单过程，该存储过程返回所有员工姓名，Email地址，电话等。该存储过程不使用任何参数 程序清单如下。 USE adventureworks GO CREATE PROCEDURE au_infor_all AS SELECT lastname, firstname, emailaddress, phone FROM person.contact GO EXECUTE（EXEC） au_infor_all 2========== 创建一个存储过程，以简化对sc表的数据添加工作，使得在执行该存储过程时，其参数值作为数据添加到表中。 程序清单如下： CREATE PROCEDURE [dbo].[ pr1_sc_ins] @Param1 char(10),@Param2 char(2),@Param3 real AS BEGIN insert into sc(sno,cno,score) values(@Param1,@Param2,@Param3) END EXEC pr1_sc_ins ‘3130040101’,’c1’,85 当然，在执行过程中变量可以显式命名： EXEC sc_ins @Param1=’ 3130040101’,@Param2=’c1’, @Param3=85 3========== 以下示例显示有一个输入参数和一个输出参数的存储过程。存储过程中的第一个参数@sname将接收由调用程序指定的输入值(学生姓名)，第二个参数@sscore（成绩）将用于将该值返回调用程序。SELECT 语句使用@sname参数获取正确的@sscore值，并将该值分配给输出参数。 程序清单如下： CREATE PROCEDURE s_score @sname char(8),@sscore real output AS SELECT @sscore =score from sc join s on s.sno=sc.sno where sn=@sname GO 4========= CREATE PROCEDURE p2 @sno nchar(8)=NULL, @average numeric(6,2) OUTPUT AS SELECT @average =SUM(G) FROM sc WHERE sno= @sno DECLARE @ave numeric(6,2) EXEC p2 @sno=001,@average =@ave OUTPUT SELECT @ave as gg DECLARE @ave numeric(6,2) EXEC p2 001,@ave OUTPUT SELECT @ave as ggg 三、游标