数据库存储过程和触发器

存储过程

存储过程的相关知识

定义:由sql语句编写完成后,编译器优化后存储在数据库的服务器中,使用后就可以调用(我自己理解的时相当于编程语言中的预处理,或者是提前封装号的函数)

数据库的完整性包括:实体完整性、参照完整性、用户定义完整性、触发器

存储过程的优势

①增强了代码的复用率和共享性(一次编译多次使用);

②提高了系统的性能(预先编译和优化好);

③减少了网络流量(服务端执行,只向客户端返回结果);

④可以作为安全机制使用(只允许用户执行部分权限,不允许用户直接访问数据库对象

存储过程包括系统存储过程和本地存储过程。

系统存储过程:存储在master数据库中,其中前缀是sp_

eg:sp_hlep  ,  sp_hlepindex  ,  sp_rename  , sp_dbremove

创建存储过程的方式包括:1、直接运用鼠标创建  2、运用T-SQL语句创建(我们这里主要将这个)

注意事项:

存储过程不能够超过128MB;

用户定义的存储过程只能在当前数据库中创建;

create procedure必须是批处理的第一条语句;

SQL sever允许存储过程引用一个不存在的对象,它只检测语发问题。


创建存储过程

上代码

执行存储过程使用T-SQL 语中的EXECUTE命令。如果执行存储过程是批处理中的第一条语句,则可以不使用EXECUTE关键字。 注意: 对于存储过程的所有者或任何一名对此过程拥有EXECUTE权限的用户,都可以执行此存储过程。 输入参数在存储过程名后逐一给定, 用逗号隔开,不必使用括号。 如果没有使用@参数名= value这种方式传入值,则参数的排列必须和建立存储过程所定义的次序对应。 用来接受输出值的参数必须加上OUTPUT

题目:创建无参存储过程,查询每个学生的平均成绩

最后不要忘记执行存储过程哦!(execute stu_avg)

group by要放在最后!!!!

create procedure stu_avg
as
selecet s#,avg(score) as '平均成绩' 
from sc
group by s#

execute stu_avg

题目:创建存储过程,查询某个学生的平均成绩(有参数传入)

create procedure stu_avg2 @sno char(7)
as
select avg(score)
from sc
where s#=@sno

execute stu_avg2 '2019001'

题目:修改某个学生某门课的成绩

create procedure update_score @sno char(7),@cno char(3),@score int 
as
update sc set score=@score 
where s#=@sno add c#=cno

execute update_score '2019001','001',100

题目:创建加密过程,利用sp_helptext查看其内容

创建加密的存储过程只需要在后面加上with encryption

create procedure Encrypt_s with encryption
as
select *from student

execute sp_helptext Encrypt_s

题目:创建带有参数和默认值(通配符)的存储过程,返回指定的学生(提供姓名)的信息。该存储过程对传递的参数进行模式匹配,如果没有提供参数,则返回所有学生的信息。

注意:

这里需要给参数一个默认值;

同时后面的查询是模糊查询,所以需要用到like

create procedure student_name @sname varchar(40)='%'
as
select * from student 
where sname like @sname

execute student_name 
execute student_name '张三' 

题目:创建带OUTPUT参数的存储过程,用于计算指定课程的平均成绩,使用一个输入参数(课程号)和一个输出参数(平均成绩)。

注意:

因为有一个输出参数output,所以在参数命名时要在后面加上output

同时因为是输出参数,所以在执行这个存储过程的时候需要声明这个输出参数

create procedure avgscore2 @cno char(3),@avgs float output
as
select @avgscore=avg(score)
from sc
where c#=@cno

declare @avgs float
execute avgscore2 '001',@avgs output
select @avgs

查看、修改和删除存储过程

题目:修改存储过程student_avg,除了用于计算每个学生的平均成绩外,还要计算学生的最高分

alter procedure student_avg
as 
select s#,avgs(score),max(score)
from sc
group by s#

execute student_avg

题目:删除存储过程student_avg

drop procedure student_avg

触发器trigger

触发器相关的知识

定义:触发器也是一种存储过程,是一种特殊类型的存储过程。他是定义在关系表上的一类由事件驱动的过程。

完整性和安全性的区分:

①完整性:

对象:不合语义、不正确的数据

作用:阻止合法用户通过合法操作向数据库中加入不正确的数据

②安全性:

对象:非法用户和非法数据

作用:非法用户和非法操作存取数据库中的正确数据

触发器的作用:保持数据的完整性,而不是返回查询的结果

触发器的具体作用:

①强化约束

②使用自定义的错误提示信息

③实现数据库中多表的级联操作

④跟踪变化

⑤调用存储过程

触发器的分类:

第一种分类方式:

DML触发器:insert、update、delete

DDL触发器:drop、alter、create

第二种分类方式:

after触发器:记录变化后激活,用于记录变更后的处理或者检查

instead of 触发器:用于取代原本的操作,在记录变更前触发

创建触发器应该注意的问题

①create trigger语句必须要是批处理中的第一个语句;而且只能用于一个表或者视图

②创建触发器的权限默认为表的所有者,不能转让权限

③触发器可以引用当前数据库以外的对象,但只能在当前数据库中创建

④触发器可以引用临时表,但是不能够创建在临时表上面

⑤truncate  table语句不会激发delete触发器


创建触发器

上代码!

题目:为学生表创建一个简单DML触发器,在插入和修改数据时,都会自动显示提示信息。

注意数据是怎么插入的insert into....values()

create trigger reminder
on student
for insert,update
as print '你在插入或修改学生表的数据' 


insert into student values('2018005','李飞','男',20,'01')

题目:为学生表创建一个DML触发器,在插入和修改数据时,都会自动显示所有学生的信息。

create trigger print_table 
on student
for
insert,update 
as
select *from student


update student set sage=30 
where s#='2019001'

题目:在SC表上创建一个DELETE类型的触发器,删除数据时,显示删除记录的个数。

create trigger del_count
on sc
for delete
as
declare @count varchar(50)
set @count=str(@@rowcount)+'行记录被删除'
select @count
return 


delete sc where s#='2019002'

创建涉及inserted表和deleted表的触发器

什么是inserted表和deleted表?

 SQL Server 为每个DML触发器都定义了两个特殊的表,一个是插入表:Inserted,一个是删除表:Deleted。     它们建在数据库服务器的内存中,是由系统管理的逻辑表,而不是真正存储在数据库中的物理表。对于这两个表,用户只有读取的权限,没有修改的权限。     在触发器的执行过程中,SQL Server建立和管理这两个临时表。这两个表的结构与触发器所在数据表的结构是完全一致的,包含了在激发触发器的操作中插入或删除的所有记录。     当触发器的工作完成之后,这两个表也将会从内存中删除。

创建这类触发器须知:

① 在用户执行INSERT语句时,所有被添加的记录都会存储在Inserted表以及触发器所在的表中; ② 在用户执行DELETE语句时,从触发器所在的表中被删除的行会发送到Deleted表;    

③而对于UPDATE语句,SQL Server先将要进行修改的记录行存储到Deleted表中,然后再将修改后的数据行存储到Inserted表以及触发器所在的表中。

题目:为Major表创建一个名为d_tr的触发器,当执行增、删、改操作时,激活该触发器,查看Inserted表和Deleted表里的信息

create trigger d_tr
on major
for insert,update,delete
as
select * from deleted
select * from inserted


DELETE FROM major where  m#='06'
INSERT INTO Major  VALUES('07','大数据','AA')
UPDATE major SET dean='张三' WHERE M#='01'

题目:为教师表定义完整性规则:教师的工资不得低于4000元,如果低于4000元,自动改为4000元。

create trigger trig6 
on teacher
for update,insert
as
update teacher set salary=4000
from teacher t,insert i
where t.T#=i.T# add i.salary<4000


update teacher set salary=3900 where T#='001'

题目:在student表中增加一个属性Sumsc,Sumsc为该学生已选修课程的门数,初始值为其选课门数,以后每选修一门课程都要对其加1,设计一个触发器自动完成上述功能。

update student set sumsc=
(select count(*) from sc where sc.s#=student.s#)
go


create trigger trig1 
on sc
after insert
as
update student set sumsc=sumsc+1
where s#=(select s# from inserted)
select sumsc from student 
where s#=(select s# from inserted)

题目:SC与STUDENT有外码关系,创建一个实现级联删除的触发器。当删除student表中的某个学生时,先删除SC表中该学生的选课记录,再删除student表中的该学生

create trigger trig2 
on student
instead of delete
as
delete sc where s# in
(select s# from deleted)
delete student where s# 
in(select s# from delectd)

题目:设计一个触发器,教师的工资只能升不能降

create trigger change_sala1
on teacher 
for update
as
declare @newsalary int ,@oldsalary int
select @newsalary=inerted.salary,@oldsalary=deleted.salary
from insert,delete
if @newsalary<@oldsalary
begin 
    print '工资只能升不能降'
    rollback
end




update teacher set salary=7500 where tname='张三'

题目:在教师工资表上创建一个触发器:在对教师的工资进行录入和修改时,按职称级别进行约束。

create trigger tr1 
on teacher
for insret,update
as
declare @prof char(10),@min int,@max int ,@salary int
select @prof=i.prof,@salary=i.salary,@min=s.minsalary,@max=s.maxsalary
from salary s,insert i
where s.prof=i.prof
if @salary not between @min and @max
begin
    print  @prof+'工资应该在'+str(@min)+'到'+str(@max)+'之间'
    rollback
end



insert teacher valus('010’, '张三','教授',6000)

查看和修改触发器

查看触发器

1、在SQL Server Management Studio中查看触发器 在选择相应触发器名后,在“查询编辑器窗口”查看。

2、使用系统存储过程查看触发器 系统存储过程sp_help、sp_helptext和sp_helptrigger分别提供有关触发器的不同信息。

修改触发器

例题:修改教学库中的学生表上的触发器reminder,使得在用户执行添加或修改操作时,自动给出错误提示信息,并撤销此次操作。

ALTER TRIGGER reminder
ON student
INSTEAD OF INSERT , UPDATE  
AS print  '你执行的添加或修改操作无效! 




update student set sname='张三' 

上述知识仅是个人学习笔记分享,如有错误欢迎提出更正😊

  • 1
    点赞
  • 5
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值