存储过程,事务,触发器等详解[宝典]
存储过程:
预先用SQL语句写好的，并用存储起来，如果需要的数据库提供与定义好的存储过程的功能相同时，只要调用execute()
方法
快递客服问题件处理详细方法山木方法pdf计算方法pdf华与华方法下载八字理论方法下载
，即可执行。
触发器:
个人认为是一种特殊的存储过程,因为它是当运行到标签所在的位置时，才触发这个SQL
语名的功能.
事务:
事务就是一个单元的工作，包括一系列的操作，这些操作要么全部成功，要么全部失败。
锁:
锁就是保护指定的资源，不被其他事务操作。
事务和锁的特点
事务的特点:
1. 事务是一个单元的工作，要么全做，要么全不做
2. 事务保证操作的一致性和可恢复性
3. 每一条Transact-SQL语句都可以是一个事务
4. 实际使用的事务是用户定义的事务，它包括一系列操作或者语句
5. 在多服务器环境中，使用用户定义的分布式事务，保证操作的一致性
锁的特点:
1. 锁是保证并发控制的手段
2. 可以锁定的资源包括行、页、簇、
表
关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf
和数据库
3. 锁的类型主要包括共享锁和排它锁
4. 特殊类型的锁包括意图锁、修改锁和模式锁
5. 共享锁允许其他事务继续使用锁定的资源
6. 排它锁只允许一个事务访问数据
7. 系统本身可以处理死锁
8. 用户可以根据实际情况定制锁的一些特征
==================================================
====
--创建数据库 scroll dynamic
create database StudentDB
GO
--置此数据库为当前数据库
use StudentDB
GO
--创建学生表
create table student
(
SId varchar(20) primary key, --学生编号
SName varchar(20), --学生姓名
SClass varchar(20), --学生班级
SSex varchar(10), --学生性别
SScore float default(0) check(SScore>=0) --学生平均分
)
GO
--创建课程表
create table class
(
EId varchar(20) primary key, --课程编号
EName varchar(20), --课程名称
ETime int check(ETime>=0) --课程课时 )
GO
--创建分数表
create table score
(
SId varchar(20), --学生编号
EId varchar(20), --课程编号
EScore float, --课程分数
primary key(SID,EID), --定义主码
foreign key (SID) references student(SID) on delete cascade, --声明及联删除
foreign key (EID) references class(EID) on delete cascade --声明及联删除
)
GO
--创建计算平均值得触发器
create trigger trigger_avg_insert on score for insert as
begin transaction
declare @count int
update student set SScore=(select avg(EScore) from score where SId=(select SI
d from inserted)) where SId=(select SId from inserted) select @count=@@error
if(@count=0)
commit transaction
else
rollback transaction
--创建计算平均值得触发器
create trigger trigger_avg_delete on score for delete as
begin transaction
update student set SScore=(select avg(EScore) from score where SId=(select SI
d from deleted)) where SId=(select SId from deleted) declare @count int
select @count=@@error
if(@count=0)
commit transaction
else
rollback transaction
--创建计算平均值得触发器
create trigger trigger_avg_update on score for update
as
begin transaction
declare @count int
update student set SScore=(select avg(EScore) from score where SId=(select SI
d from inserted)) where SId=(select SId from deleted)
select @count=@@error
if(@count=0)
commit transaction
else
rollback transaction
GO
--创建查询学生信息的存储过程
create proc proc_student_select
as
begin transaction
declare @count int
select * from student
select @count=@@error
if(@count=0)
commit transaction
else
rollback transaction
GO
--创建查找平均分存储过程
CREATE PROCEDURE proc_student_avg
(
@SID varchar(20)
)
AS
begin transaction
select avg(EScore) as SAvg from score where SId=@SId
declare @count int
select @count=@@error if(@count=0)
commit transaction
else
rollback transaction
GO
--创建通过学号查询学生信息的存储过程 create proc proc_student_select_bySId
(
@SId varchar(20)
)
as
begin transaction
declare @count int
select * from student where SId=@SId
select @count=@@error if(@count=0)
commit transaction
else
rollback transaction
GO
--创建插入学生信息的存储过程 create proc proc_student_insert
(
@SId varchar(20),
@SName varchar(20),
@SClass varchar(20),
@SSex varchar(10) )
as
begin transaction
declare @count int
insert into student(SID,SName,SClass,SSex) values(@SId,@SName,@SClass,@S
Sex)
select @count=@@error if(@count=0)
commit transaction else
rollback transaction GO
--删除学生信息的存储过程 create proc proc_student_delete
(
@SId varchar(20) )
as
begin transaction
declare @count int
delete from student where SId=@SId
select @count=@@error if(@count=0)
commit transaction else
rollback transaction
GO
--修改学生信息的存储过程 create proc proc_student_update
(
@SId varchar(20),
@SName varchar(20),
@SClass varchar(20),
@SSex varchar(10) )
as
begin transaction
declare @count int
update student set SName=@SName,SClass=@SClass,SSex=@SSex where SId=
@SId
select @count=@@error if(@count=0)
commit transaction else
rollback transaction GO
--创建查询课程信息的存储过程 create proc proc_class_select
as
begin transaction
declare @count int
select * from class select @count=@@error if(@count=0)
commit transaction else
rollback transaction
GO
--创建通过课程号查询课程信息的存储过程 create proc proc_class_select_byEId
(
@EId varchar(20)
)
as
begin transaction
declare @count int
select * from class where EId=@EId select @count=@@error
if(@count=0)
commit transaction
else
rollback transaction
--创建插入课程信息的存储过程 GO
create proc proc_class_insert
(
@EId varchar(20),
@EName varchar(20),
@ETime int
)
as
begin transaction
declare @count int
insert into class(EId,EName,ETime) values(@EId,@EName,@ETime)
select @count=@@error
if(@count=0)
commit transaction
else
rollback transaction --创建删除课程信息的存错过程 GO
create proc proc_class_delete
(
@EId varchar(20) )
as
begin transaction
declare @count int
delete from class where EId=@EId
select @count=@@error if(@count=0)
commit transaction
else
rollback transaction --创建修改课程信息的存储过程 GO
create proc proc_class_update
(
@EId varchar(20),
@EName varchar(20),
@ETime int
)
as
begin transaction
declare @count int
update class set EName=@EName,ETime=@ETime where EId=@EId
select @count=@@error if(@count=0)
commit transaction else
rollback transaction GO
--创建查询成绩信息的存储过程 create proc proc_score_select
as
begin transaction
declare @count int
select * from score select @count=@@error if(@count=0)
commit transaction else
rollback transaction GO
--创建通过学号查询成绩信息的存储过程
create proc proc_score_select_bySId
(
@SId varchar(20) )
as
begin transaction
declare @count int
select * from score where SId=@SId
select @count=@@error if(@count=0)
commit transaction
else
rollback transaction
GO
--创建通过查询成绩信息的存储过程 create proc proc_score_select_byEId
(
@EId varchar(20)
)
as
begin transaction
declare @count int
select * from score where EId=@EId
select @count=@@error if(@count=0)
commit transaction
else
rollback transaction
GO
--创建插入成绩信息的存储过程 create proc proc_score_insert
(
@SId varchar(20),
@EId varchar(20),
@EScore float
)
as
begin transaction
declare @count int
insert into score(SId,EId,EScore) values(@SId,@EId,@EScore)
select @count=@@error
if(@count=0)
commit transaction
else
rollback transaction
GO
--创建删除成绩信息的存错过程 create proc proc_score_delete
(
@SId varchar(20),
@EId varchar(20)
)
as
begin transaction
declare @count int
delete from score where SId=@SId and EId=@EId
select @count=@@error if(@count=0)
commit transaction
else
rollback transaction
GO
--创建通过学号删除成绩信息的存错过程 create proc proc_score_delete_bySId
(
@SId varchar(20)
)
as
begin transaction
declare @count int
delete from score where SId=@SId
select @count=@@error if(@count=0)
commit transaction else
rollback transaction GO
--创建通过课程号删除成绩信息的存错过程
create proc proc_score_delete_byEId
(
@EId varchar(20) )
as
begin transaction
declare @count int
delete from score where EId=@EId
select @count=@@error if(@count=0)
commit transaction else
rollback transaction GO
--创建修改成绩信息的存储过程 create proc proc_score_update
(
@SId varchar(20),
@EId varchar(20),
@EScore float
)
as
begin transaction
declare @count int
update score set EScore=@EScore where SId=@SId and EId=@EId select @count=@@error
if(@count=0)
commit transaction
else
rollback transaction
GO
--创建查询学生所有信息的存储过程
create proc proc_student_one_information (
@SId varchar(20)
)
as
begin transaction
declare @count int
select student.SName,student.SClass,student.SSex,class.EName,class.ETime,scor
e.EScore,student.SScore from student,class,score where student.SId=score.SId a
nd class.EId=score.EId and student.SId=@SId select @count=@@error
if(@count=0)
commit transaction
else
rollback transaction
GO
--创建查询所有学生所有信息的存储过程
create proc proc_student_all_information as
begin transaction
declare @count int
select student.SName,student.SClass,student.SSex,class.EName,class.ETime,scor
e.EScore,student.SScore from student,class,score where student.SId=score.SId a
nd class.EId=score.EId select @count=@@error
if(@count=0)
commit transaction
else
rollback transaction
GO
--创建某个学生已经有了分数的课程 create proc proc_class_in (
@SId varchar(20)
)
as
begin transaction
declare @count int
select class.EId,class.EName from class,score where class.EId=score.EId and scor
e.SId=@SId
select @count=@@error
if(@count=0)
commit transaction
else
rollback transaction
GO
--创建某个学生没有分数的课程 create proc proc_class_notin
(
@SId varchar(20)
)
as
begin transaction
declare @count int
select EId,EName from class where EId not in (select EId from score where SId=
@SId)
select @count=@@error
if(@count=0)
commit transaction
else
rollback transaction
![存储过程,事务,触发器等详解[宝典]](https://swf.iask.com/35JxSgHhIYw.jpg?ssig=cj61Yd7sp%2F&Expires=1718738912&KID=sina,ishare&range=0-226801)
浙公网安备 33021202002483