数据库原理（存储过程和触发器设计）

1. 掌握存储过程的操作方法；
2. 掌握触发器的创建和使用方法。
   、

实验内容：

1. 创建一个名为stu_pr的存储过程。该存储过程能查询出计算机系学生的所有资料，包括学生的基本信息、学生的选课信息（含未选课同学的信息）。要求在创建存储过程前请判断该存储过程是否已创建，若已创建则先删除，并给出“已删除！”信息，否则就给出“不存在，可创建！”的信息。

 if exists (select name from sysobjects
     where type='P' and name='stu_pr')
	  begin drop proc stu_pr
	   print '已删除'
end
	else print '不存在,可创建！'
create procedure stu_pr
as
  select * from 学生 left join 选课
        on 学生.学号=选课.学号
	      where 所在系='计算机'
 exec stu_pr

2. 创建一查询存储过程，过程名称为P_CX。参数是学号，输出信息是指定学号所应的姓名、课程名称、成绩。运行时若没有指定学号，则提示请输入学号；若学号不存在则提示学号不存在。

create proc P_CK
 @sno char(5)=null
 as
   if @sno is null
   begin
     print'请输入学号'
     return
   end
   else if not exists(select * from 学生
      where 学号=@sno)
	print '学号不存在'
   else  select 姓名,课程名,成绩 from 学生,选课,课程
  where 学生.学号=选课.学号
		  and 选课.课程号=课程.课程号
		  and 学生.学号=@sno
	exec P_CK '002'

3. 创建一个名为stu_proc1的存储过程，查询某系、某姓名的学生的学号、姓名、年龄，选修课程名、成绩。系名和姓名在调用该存储过程时输入，其默认值分别为“%”与“李%”。执行该存储过程，用多种参数加以测试。

create proc stu_proc2
@dept char(20)='%',@name char(10)='李%'
as
select 学生.学号,姓名,年龄,课程名,成绩 from 学生  join 选课
   on 学生.学号=选课.学号  join 课程
	 on 选课.课程号=课程.课程号
where 所在系 = @dept
	and  姓名 = @name
	exec stu_proc2 '会计','李桃'

4. 创建一个名为Course_sum的存储过程，可查询某门课程考试的总成绩。总成绩可以输出，以便进一步调用。

create procedure Course_SUM1 @c_num varchar(20),@total_grade decimal(4,1) output
	as 
	 set @total_grade=(select  SUM(成绩) from 课程,选课 where 课程.课程号=选课 .课程号
						and 课程.课程号=@c_num)
     declare @total decimal(4,1)
	 execute Course_sum1 'c01',@total output
	 print 'c01的总成绩为：'+str(@total)

5. 创建一统计存储过程，过程名称为P_CJTJ。参数是学号，输出信息是指定学号的姓名、选课门数、平均分、总分、最高分、最低分。运行时若没有指定学号，则提示输入学号；若学号不存在则提示学号不存在。

create proc P_CJTJ
@sno char(5)=null
as
 if @sno is null
   print '学号不存在'
 else
   if(not exists(select @sno from 学生))
    print'请输入学号'
  else
    select 姓名,avg(成绩) as 平均分,
       count(*) as 选课门数,
		sum(成绩) as 总分,
		max(成绩) as 最高分,
		min(成绩) as 最低分 
    from 学生,选课
      where 学生.学号=选课.学号
       and 学生.学号=@sno
       group by 学生.学号,姓名
    exec P_CJTJ '001'

6. 创建一个为选课表添加选课记录的存储过程Addsc

  create proc Addsc
@sno char(5),@cno char(3),@sgrade int
as
  insert into 选课 
    values(@sno,@cno,@sgrade)

7. 创建一个存储过程Delscr删除选课表中指定学号的记录

create proc Delscr
  @sno char(5)
as
  delete from 选课
     where 学号=@sno

8. 显示存储过程Delscr的定义信息。
   

9. 为学生表建立一个名TRI_INSERT的INSERT触发器。功能是当用户向学生表添加数据时，同时也把这些数据添加到stu表中。其中：学生表和表stu的结构相同。

USE STUDENT
create table stu
(
sno char(5) primary key,
sname char(8) not null,
age smallint null,
sex char(2) null,
dept char (20) null
)
create trigger TRI_INSERT的INSERT on 学生
after insert
  as 
  
  if exists(select 学号  from inserted 
       where 学号 not in (
	       select 学号 from 学生) )
	    print'可插入该记录'
    insert into 学生 values('021','常玉',19,'男','计算机系')

10. 创建触发器tr_s3，在删除一个毕业生的信息的同时删除这个学生的成绩记录。
    –如果没有此学生则给出提示信息。

create trigger tr_s3 on  学生
after delete
 as
   declare @sno char(5)
    set @sno=(select 学号 from deleted)
     if not exists(select @sno from 学生)
         print '没有此学生'
     else
         delete  from 选课
            where 学号=@sno
			
 delete  from 学生
            where 学号='021'

11. 在数据库中创建一个触发器，向选课表添加一条记录时，检查该记录的学号在学生表中是否存在，检查该记录的课程号在课程表中是否存在，若其中有一项为否，则拒绝添加操作，并显示“违反数据一致性”提示信息。

create trigger sc_insert on 选课
after insert
as
begin 
     if exists(select 学号 from inserted 
      where 学号 not in (select 学号 from 学生))
       rollback
       print '违反数据一致性'
       if exists(select 课程号 
	     from inserted 
		     where 课程号 not in (select 课程号 from 选课))
       rollback
       print '违反数据一致性'
       end
       insert into 课程 values('002','c02',95)

12.在没有外键约束的情况下，在sc表中插入student表中不存在的学号及在course表中不存在的课程，会报错，并不让插入

alter table sc 
drop constraint FK_sc
create trigger trig_insert
on 选课 after insert
as
if not exists(select * from 课程,inserted where 课程.课程号=inserted.课程号)
begin
print'插入的课程号不在课程表中'
rollback
end
insert into 选课 values('002','c02',80)