弦月下的SQL<8> SQL存储过程和触发器代码

1、创建一个存储过程

create proc 学生信息查询

as

select * from student

 

2、可以创建更复杂的存储过程，有多张表构成存储过程

create proc 成绩查询

as

select s_name,c_id,a_average 

from student ,course,average

where student.s_no=average.a_no

order by s_class

 

 

3、执行存储过程

exec 学生信息查询

 

4、查询存储过程的代码

sp_helptext 学生信息查询

 

5、查询存储过程的信息

sp_help 学生信息查询

 

6、删除存储过程

drop procedure 学生信息查询

 

7、带参数的存储过程

use[KING]

go

 

create proc 按照姓名查询 @xm varchar(8)

as

if exists(select * from student where s_name=@xm)

select * from student where s_name=@xm

else

print 'nothing!!!'

 

关于带参数的存储过程的使用方法：

exec 按照姓名查询 'killer'

 

 

 

 

8、在存储过程中使用参数

USE [KING]

GO

 

create proc 成绩审核 @xh char(11),@x tinyint

as

select @x=count(*) from average where a_no=@xh and a_average<60

if @x=0

print '成绩合格'

else

print '有'+str(@x)+'门课挂掉了'

 

使用参数的存储过程的使用方法：

USE [KING]

GO

 

DECLARE @return_value int

 

EXEC @return_value = [dbo].[成绩审核]

@xh = NULL,

@x = NULL

 

SELECT 'Return Value' = @return_value

 

GO

 

 

9、更新表中信息的存储过程

USE [KING]

GO

create proc 学分填充

as

begin

update average set c_score=(select c_score from course where average.a_id=course.c_id)

update average set c_score=0 where a_average<60

end

 

 

 

10、带有输出参数的存储过程

create proc 总学分查询 @xh char(11),@zxf decimal(4,1) output

as

set @zxf=(select sum(c_score) from average where a_id=@xh)

print @xh+'已修学分： '+str(@zxf,4,1)+'分'

 

执行带输出参数的存储过程的时候，需要先定义参数，并且给输入参数@xh赋值，输出参数不需要赋值。从存储过程中获得的返回值可以提供给用户使用。

 

查询方法：

USE [KING]

GO

 

DECLARE @return_value int,

@zxf decimal(4, 1)

 

EXEC @return_value = [dbo].[总学分查询]

@xh = N'killer',

@zxf = @zxf OUTPUT

 

SELECT @zxf as N'@zxf'

 

SELECT 'Return Value' = @return_value

 

GO

 

 

11、触发器建立

use[KING]

go

create trigger 学分生成 on average

for insert

as

 

declare @xh char(11),@cj decimal(4,1) ,@kch char(6) 

select @xh=(select a_no from inserted)

select @cj=(select a_average from inserted)

select @kch=(select a_id from inserted)

 

if @cj>=60

update average set c_score =(select c_score from course where course.c_id=@kch)

where a_no=@xh and a_id =@kch

else

update average set c_score =0

where a_no=@xh and a_id =@kch

 

12、查看触发器

 

use[KING]

go

 

exec sp_helptrigger average

 

注意：其触发器一定是和表所绑定的。

 

13、修改触发器(格式)

 

AlterTRIGGER trigger_name

ON table_name

for ([INSERT][,][DELETE][,][UPDATE])

AS

 

14、删除触发器

Drop trigger 学分生成

 

注意：调用Delete:因为删除操作而引起触发行为。

 

弦月下的SQL<4> SQL语言基础