【实验目的】

1、    了解数据库常用的对象及组成；

2、    熟悉掌握SQL的基本概念和特点；

3、    掌握T-SQL语句中数据的定义的使用方法

4、    了解数据库SQL SERVER高级设计存储过程和触发器的原理；

5、    可以简单的设计存储过程和触发器；

【实验原理】

**1、 ** 存储过程基本原理：

1.1系统存储过程

系统存储过程是 SQL Server系统自身提供的存储过程，可以作为命令执行各种操作。

系统存储过程主要用来从系统表中获取信息，使用系统存储过程完成数据库服务器的管理工作，为系统管理员提供帮助，为用户查看数据库对象提供方便，系统存储过程位于数据库服务器中，并且以sp_开头，系统存储过程定义在系统定义和用户定义的数据库中，在调用时不必在存储过程前加数据库限定名。

系统存储过程创建并存放在与系统数据库master中，一些系统存储过程只能由系统管理员使用，而有些系统存储过程通过授权可以被其它用户所使用。

1.2、用户存储过程(自定义存储过程)

自定义存储过程即用户使用T_SQL语句编写的、为了实现某一特定业务需求，在用户数据库中编写的T_SQL语句集合，自定义存储过程可以接受输入参数、向客户端返回结果和信息，返回输出参数等。创建自定义存储过程时，存储过程名前加上"##"表示创建了一个全局的临时存储过程;存储过程前面加上"#"时，表示创建的局部临时存储过程。局部临时存储过程只能在创建它的回话中使用，会话结束时，将被删除。这两种存储过程都存储在tempdb数据库中。

用户定义的存储过程分为两类：T_SQL 和CLR

1.3、扩展存储过程（本文档不考虑）

扩展存储过程是以在SQL SERVER环境外执行的动态连接(DLL文件)来实现的，可以加载到SQL SERVER实例运行的地址空间中执行，扩展存储过程可以用SQL SERVER扩展存储过程API编程，扩展存储过程以前缀"xp_"来标识，对于用户来说，扩展存储过程和普通话存储过程一样，可以用相同的方法来执行。

**2、 ** 触发器基本原理：

触发器（trigger）是SQL server 提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，当对一个表进行操作（ insert，delete，update）时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。 触发器可以从DBA_TRIGGERS ，USER_TRIGGERS 数据字典中查到。

2.1、触发器触发时：

系统自动在内存中创建deleted表或inserted表；

只读，不允许修改，触发器执行完成后，自动删除。

2.2、inserted表：

临时保存了插入或更新后的记录行；

可以从inserted表中检查插入的数据是否满足业务需求；

如果不满足，则向用户发送报告错误消息，并回滚插入操作。

2.3、deleted表：

临时保存了删除或更新前的记录行；

可以从deleted表中检查被删除的数据是否满足业务需求；

如果不满足，则向用户报告错误消息，并回滚插入操作

【主要仪器设备和材料】

WIN7+SQL SERVER 2008

【过程和步骤】

**1、 ** 存储过程

1.1、 存储过程的创建

1.1.1、 有参存储过程的创建（与课本相同）

create PROCSTU_PROC

@sno varchar(9)

as

if '数据库' not in (select course.cname from sc ,course where sc.cno = course.cno and sc.sno =@sno)

print '该学生没有选择数据库课程'

else if (90 < (select sc.grade from sc,student,course where sc.sno = student.sno and sc.cno = course.cno and cname = '数据库'))

begin

print '该大学生选修了该课程，且达到优秀'

selectstudent.sno ,student.sname ,course.cno ,course.cname ,'成绩等级'

fromstudent,course,sc where student.sno = sc.sno and course.cno = sc.cno

end

else

print '该大学生选修了该课程，但没有达到优秀'

1.1.2、 无参存储过程的创建

if (exists (select * from sys.objects where name = 'getAllStudent'))

drop procproc_get_student

go

create proceduregetAllStudent

as

select * from student;

--调用,执行存储过程

exec getAllStudent;

1.2、 存储过程的调用

1.2.1、有参存储过程的调用

execSTU_PROC '12006'

1.2.2、无参存储过程的调用

execgetAllStudent;

**2、 ** 触发器

2.1、触发器的创建

2.1.1、更新数据库的创建

create TRIGGERtrigger1 on sc

for Update

as

declare @old_grade REAL , @new_grade REAL

if Update(grade)

begin

select@old_grade = grade fromdeleted --触发器为数据库提供临时表deleted

select@new_grade = grade frominserted --触发器为数据库提供临时表inserted

if(@new_grade > (@old_grade * 1.1 ))

begin

Rollback Transaction

print '成绩修改幅度太大，更改失败'

end

end

2.1.2、插入触发器的创建

create triggertrig_insert

on student

after insert

as

begin

if object_id(N'student_sum',N'U') is null--判断student_sum表是否存在

create table student_sum(stuCount int default(0));--创建存储学生人数的student_sum表

declare @stuNumber int;

select @stuNumber = count(*)from student;

if not exists (select * fromstudent_sum)--判断表中是否有记录

insert into student_sum values(0);

update student_sum setstuCount =@stuNumber; --把更新后总的学生数插入到student_sum表中

end

2.2、触发器的调用

2.2.1、更新触发器的调用

update sc setgrade = grade +5  where sno = '12001' and cno = 1

select * from sc

2.2.2、插入数据库的调用

insert intostudent(sname,ssex,sage)values('吕布','男',18);

select stuCount 学生总人数 from student_sum;

insert intostudent(sname,ssex,sage)values('貂蝉','女',20);

select stuCount 学生总人数 from student_sum;

insert intostudent(sname,ssex,sage)values('曹阿瞒','男',21);

selectstuCount 学生总人数 from student_sum;

【原始数据记录和处理】

1、    数据库的原始数据：

Student表：

Sc表原始数据

Course表原始数据

2、    实验结果数据处理

2.1、有参存储过程的调用

 

2.2、无参存储过程的调用

2.3、更新触发器的调用

2.4、插入触发器的调用

由于考虑到：

既然定义了学生总数表student_sum表是向student表中插入数据后才计算学生总数的，所以学生总数表应该禁止用户向其中插入数据。

--创建insert_forbidden,禁止用户向student_sum表中插入数据

create triggerinsert_forbidden

on student_sum

after insert

as

begin

RAISERROR('禁止直接向该表中插入记录，操作被禁止',1,1)--raiserror 是用于抛出一个错误

rollback transaction

end

开始调用该触发器

--触发触发器insert_forbidden

insert student_sum (stuCount) values(5);

【心得体会】

**1、 ** 遇到的问题：

1）    在创建存储过程时，本想使用系统的自定义的方法来创建，但是在其操作页面中，看不懂其代码的组成，因此在这里纠结好好久，后来就直接采用了课本上的方法，简单易行。

2）    在创建无参的存储过程时，出现了一下错误：

经过排查，发现存储过程的参数传递与java的按引用传递类似。在创建时，已经对此进行了调用。

3）    Go的问题。一开始遇到这个问题，不理解Go的含义，后来经过多方证实，发现go时分成了两个部分，这两个部分是不相关的。

**2、 ** 其他的感触：

1）    靠自己的这点能力，想弄明白一个原理。对我来说还是很有难度的，但是在和同学之间的讨论后，即使依旧没理清逻辑关系，但也比一开始的无脑好了很多，很感谢孙浩和台昌杨两个同学的帮助。

2）    虽然只是一次实验，但是这种实验很能够学到东西，至少帮助我理解了存储过程和触发器，在对存储过程的编程中，也发现了存储过程的几个优点：①可实现模块化的程序设计，存储过程可以多次调用，提供统一的数据库访问接口，改进应用程序的可维护性。②存储过程加快系统运行速度，存储过程只在创建时编译，以后每次执行时不需要重新编译。对于触发器，他可以做到：①级联修改数据库中所有相关的表，自动触发其它与之相关的操作；②触发器可以调用更多的存储过程。当然，这些都是我所能得到的，至于其他的，我还没有做测试，所以不是很清楚。

      3)触发器和存储过程有着很大的区别，虽然在程序的设计上，感觉相差无几。但两者的区别在于运行方式的不同，触发器不能执行EXECUTE语句调用，而是在用户执行Transact-SQL语句时自动触发执行而存储过程需要用户，应用程序或者触发器来显示地调用并执行

SqlServer存储过程与触发器文档