试验五存储过程和触发器的定义和使用分析解析

1、实验五 存储过程和触发器的定义和使用实验内容：tablestudent ( 学号 char ( 6) not null, 姓名 char ( 8) not null, 性别 bit not null, 出生日期 smalldatetime , 专业名 char ( 10), 所在系 char ( 10), 联系电话 char ( 11) null)createtablecreatetable1.在学生成绩数据库中建立表，并相应的导入数据 create database 学生成绩 use 学生成绩 createcourse ( 课程号 char ( 3) not null, 课程名 char (

2、 20) not null, 教师 char ( 10), 开课学期 tinyint , 学时 tinyint , 学分 tinyint not null)sc ( 学号 char ( 6) not null, 课程号 char ( 3) not null, 成绩 smallint )2.（1）全局变量（2）if 语句(3) 循环语句declare i int , sum int , count int set i=1set sum=0 set count =0 while ( i 100 ) beginif ( i%3=0)beginset sum=sum+i set count =coun

3、t +1 endset i=i+1end print 总和为 : + convert ( varchar ( 10), sum)print 个数为 : + convert ( varchar ( 10), count )declare i1 int , s1 int set i1=1 set s1=0 beg:if ( i1 = 5 )beginset s1=s1+i1set i1=i1 +1 goto beg end print s1（ 4 ） waitfor 语句waitfor delay 00:00:05 select from studentgo waitfortime 10:20ex

4、ec update_all_stats（ 5 ） case 语句 select 学号 , 性别 = case 性别when 1 then 男when 0 then 女 endfrom student go select 学号 , 成绩 =casewhen成绩IS NULLthen 未考 when成绩=60and成绩 =70and成绩 =90then 优秀 endfrom sc3存储过程proc proc1（1）create as select 学号 , 课程号 from scwhere 成绩 between 60 and 80 goexec proc1delete 成绩from scwhere

5、 学号 =学号gocreate proc proc22 ( 学号 char ( 6), 课程号 char ( 3), 成绩 smallint ) asinsert into scvalues ( 学号 , 课程号 , 成绩 )go( 3 ) createproc proc3( 学号 char ( 6), 课程号 char ( 3), 成绩 smallint output ) asselect成绩 = 成绩from scwhere 学号 =学号 and 课程号 =课程号 godeclare成绩 smallintexecproc3 020101, 101 , 成绩 output select 成绩

6、 =成绩（4）createproc proc_t1（ 专业名称char ( 10)asselect专业名 , student. 学号 , 姓名, course . 课程号 , course . 课程名 , 成绩 , 学分from student , sc , coursewhere 专业名 =专业名称 and student . 学号 =sc . 学号 and sc . 课程号 =course . 课程号 goexec proc_t1 信息管理 go(5)alter proc proc_t1 ( 专业名称 char ( 10)asselect 专业名 , student . 学号 , 姓名 ,

7、 course . 课程号 , course . 课程名 , 成绩 , 学分 from student , sc , coursewhere 专业名 =专业名称 and student . 学号 =sc . 学号 and sc . 课程号 =course . 课程号 and student . 性别 =1goexec proc_t1 信息管理 go6 ) drop proc proc_t1(7)create proc proc7 ( 课程名 char ( 10 ) asbeginselect 课程名 ,sum(casewhen成绩between0and 59then 1else 0end )

8、as 0-60 分 ,sum(casewhen成绩between60and79then 1else 0end )as 60-79 分 sum(casewhen成绩between80and89then 1else 0end )as 80-89 分 sum(casewhen成绩between90and100then1 else0 end )as 90-100fromsc ,coursewhere 课程名 =课程名 and sc . 课程号 =course . 课程号 group by 课程名end goexec proc7 英语 go4.on sc1 ) createtrigger tri_sc_

9、insert after insert asbegindeclare sno char ( 6) select sno=inserted . 学号from inserted if not exists （ select 学号 from student where 学号 =sno） delete scwhere sc . 学号 =snoendgo（2）create trigger tri_sc_upd on scafter updateasif UPDATE（ 成绩 ）beginprint 修改失败 !raiserror（ 不能修改 SC表的成绩 , 16, 10 ）rollback trans

10、actionendgo create trigger trigger_1 on Course for update asbeginupdate SCset 课程号 =( select 课程号from insertedfromSC, deletedwhereSC. 课程号 =deleted .课程号endif update （ 课程号 ）on Student createtrigger trigger_2for delete asbegindelete SC from SC, deletedwhere SC. 学号 =deleted . 学号 end go create trigger trig

11、ger_3 on Student for updateasprint 学生表被修改了！ go altertriggertrigger_3on Studentfor update asdeclare kch char ( 6) select kch =学号 from deletedprint 学生表中学号为 +kch+ 的记录被修改了go drop trigger trigger_3三、思考题1.存储过程的类型有哪些？分别有什么特征？ 类型：系统存储过程、扩展存储过程、用户定义存储过程特征：（ 1）系统存储过程：由系统创建、管理和使用；主要存储在master 数据库，以 sp_ 为前缀；用户只能

12、对其调用，不能修改或删除；通过系统存储过程能够得到系统信息或为数据库系统管理员管理 SQL Server 提供支持。（ 2）扩展存储过程：以 xp_为前缀；允许使用其它编程语言创建自己的外部存储过程，其内容 并不存储在 SQL2005 中，而是以 DLL 的形式单独存放，它允许用户 DLL 访问 SQL Serve。r（3）用户定义存储过程：由用户为完成某一特定功能而编写的存储过程。 2.如何建立一个存储过程？试述存储过程在程序设计的作用。建立存储过程：可以通过 T-SQL语句建立create proc =默认值 output ，=默认值 outputas也可以通过 ssms 创建存储过程作用： 第一：存储过程因为 SQL语句已经预编绎过了，因此运行的速度比较快。 第二：存储过程可以接受参数、输出参数、返回单个或多个结果集以及返回值。可以向程序返回错误原因。 第三：存储过程运行比较稳定，不会有太多的错误。只要一次成功，以后都会按这个程序运行。 第四：存储过程主要是在服务器上运行，减少对客户机的压力。 第五：存储过程可以包含程序流、逻辑以及对数据库的查询。同时可以实体封装和隐藏了数据逻辑。 第六：存储过程可以在单个存储过程中执行一系列SQL语句。第七：存储过程可以从自己的存储过程内引用其它存储过程，这可以简化一系列复杂语句。存储过程还可以控制权限，比如一个表