存储过程就是作为可执行对象存放在数据库中的一个或多个SQL命令。
通俗来讲:存储过程其实就是能完成一定操作的一组SQL语句。
存储过程的特点
触发器和存储过程的区别:
触发器与存储过程的区别是运行方式的不同:
触发器不能执行EXECUTE语句调用,而是在用户执行Transact-SQL语句时自动触发执行;
存储过程需要用户,应用程序或者触发器来显示地调用并执行。
触发器的优点
1.触发器是自动的。当对表中的数据做了任何修改之后立即被激活。
2.触发器可以通过数据库中的相关表进行层叠修改。
3.触发器可以强制限制。这些限制比用CHECK约束所定义的更复杂。与CHECK约束不同的是,触发器可以引用其他表中的列。
触发器的作用
触发器的主要作用是能够实现由主键和外键所不能保证的复杂参照完整性和数据的一致性,它能够对数据库中的相关表进行级联修改,提高比CHECK约束更复杂的的数据完整性,并自定义错误消息。触发器的主要作用主要有以下几个方面:
数据库建库建表操作,以下实例基于下面的数据库表
-- 创建数据库shared_bicycles
create database shared_bicycles;
-- 创建用户表
drop table users;
create table users(
userid int primary key,-- 用户编号
username varchar(10) not null unique,--用户姓名
userphone varchar(12) not null,--联系方式
userpassword varchar(12) not null,--用户密码
usercredit varchar(1) -- 用户信用(0不需交押金,1需交押金)
);
-- 插入数据
insert into users values(0,'李强','15914587023','12345678','0');
insert into users values(1,'王伟','13914897023','abcdefgh','1');
insert into users values(2,'李明','18714581593','123456def','0');
insert into users values(3,'张华','12314584923','123456ghj','1');
insert into users values(4,'小刘','15614580023','123456dfs','0');
--创建单车信息表
drop table bicycles;
create table bicycles(
bikeid char(5) primary key,--车辆编号
bikelocation varchar(12) not null,--车辆位置
onthedate date not null,--投放日期
bikestate varchar(1) not null--单车状态(0可使用,或1需维修)
);
insert into bicycles values('10000','建设路129号','2018-10-20','0');
insert into bicycles values('10001','人民公园','2018-10-21','0');
insert into bicycles values('10002','建设路9号','2018-11-20','0');
insert into bicycles values('10003','人民北路8号','2018-10-20','1');
insert into bicycles values('10004','光华大道1号','2018-09-05','0');
-- 创建订单表
drop table orders;
create table orders(
orderid int primary key,--订单编号
startingtime datetime not null,--起始时间
endingtime datetime,--结束时间
origin varchar(20) not null,--订单起始地点
endlocation varchar(20),--订单结束地点
userid int,--用户编号
bikeid char(5),
amount varchar(20),--金额
order_state char(1),--订单状态(0表示订单完成,1表示订单未完成)
foreign key(userid) references users(userid),--设置外键
foreign key(bikeid) references bicycles(bikeid)--设置外键
);
-- 创建管理员表:报修、位置、时间、车辆号
drop table admins;
create table admins(
repair varchar(1) not null,--报修情况(0未报修,1报修)
location varchar(12) not null,--位置
usingTime datetime not null,--时间
bikeid char(5),--车辆号
foreign key(bikeid) references bicycles(bikeid)--设置外键
);
-- 创建充值表
drop table deposits;
create table deposits(
duration datetime not null,--时长
userid int ,--用户编号
packagename varchar(6) not null,--套餐名
recordtime datetime not null,--充值时间
packageprice float not null,--价格
foreign key(userid) references users(userid)--设置外键
);
sqlserver存储过程实例:
--存储过程1 用户用车:输入userid、bikeid、起始地点
drop proc pr_use_bike;
go
create procedure pr_use_bike
@userid int,
@bikeid char(5),
@origin varchar(20)
as
declare @msg varchar(30)
declare @orderid int
declare @now_time datetime
begin
if (select usercredit from users where userid=@userid)='0'
begin
if (select bikestate from bicycles where bikeid=@bikeid)='0'
begin
set @msg='用户开始用车'
set @orderid=(select max(orderid) from orders)+1
set @now_time=(select convert(varchar,GETDATE(),20))
insert into orders(orderid,startingtime,origin,userid,bikeid,order_state) values(@orderid,@now_time,@origin,@userid,@bikeid,'1')
end
else
set @msg='单车损坏,请换一辆'
end
else
set @msg='用户还未充值,请先充值!'
select @msg
end
go
--执行存储过程,需要传入参数
exec pr_use_bike @userid=0,@bikeid='10000',@origin='新华公园';
-- 提示“用户开始用车”
exec pr_use_bike @userid=0,@bikeid='10003',@origin='新华公园';
--提示“单车损坏,请换一辆”
exec pr_use_bike @userid=1,@bikeid='10000',@origin='新华公园';
-- 提示“用户还未充值,请先充值!”
-- 存储过程2:用户还车:user_id,bikeid,订单号,结束地点,金额
drop proc pr_return_bike;
go
create procedure pr_return_bike
@userid int,
@bikeid char(5),
@endlocation varchar(20),
@amount varchar(20) = '1'
as
declare @msg varchar(30)
declare @now_time datetime
declare @orderid int
declare @order_state datetime
begin
set @now_time=(select convert(varchar,GETDATE(),20))
set @msg='用户还车成功'
set @orderid=(select orderid from orders where userid=@userid and bikeid=@bikeid and order_state='1')
update orders set endingtime=@now_time,endlocation=@endlocation,amount=@amount where orderid=@orderid
print @msg
print '订单生成完成'
select * from orders where orderid=@orderid
end
go
exec pr_return_bike @userid=0,@bikeid='10000',@endlocation='新华书店',@amount=1;
-- 存储过程3:用户充值:user_id,套餐名,价格
drop proc pr_recharge;
go
create procedure pr_recharge
@userid int,
@packagename varchar(6),
@packageprice float
as
declare @msg varchar(30)
declare @now_time datetime
declare @duration datetime
begin
set @now_time=(select convert(varchar,GETDATE(),20))
set @duration=(select dateadd(d,30,@now_time))
set @msg='用户充值'
insert into deposits values(@duration,@userid,@packagename,@now_time,@packageprice);
select * from deposits where userid=@userid and recordtime=@now_time
end
go
-- 执行
exec pr_recharge @userid=0,@packagename='套餐一',@packageprice=19.9;
创建存储过程需要注意的点Tips:
sqlserver触发器实例
--设置触发器check_user_passwd:检查创建新用户密码长度大于7小于等于12
go
create trigger check_user_passwd
on users After insert
as
if len((select userpassword from inserted))<=7 or len((select userpassword from inserted))>12
begin
print '用户密码长度必须大于7位小于等于12位'
rollback transaction
end
go
select * from users;
-- 测试触发器check_user_passwd
-- 插入错误,提示:‘用户密码长度必须大于7位小于等于12位’
insert into users values(8,'小吴','15614580023','12345','0');
-- 能正确插入,触发器正常工作
insert into users values(6,'小吴','15614580023','12345abcd','0');
--设置触发器after_insert_bicycle:
--表示在新插入一条单车数据时,如果单车需要维修,则在管理员维修表中新增一条需要维修的记录
go
create trigger after_insert_bicycle
on bicycles after insert
as
if (select bikestate from inserted)='1'
begin
insert into admins values ('1',(select bikelocation from inserted),(select onthedate from inserted),(select bikeid from inserted));
end
go
-- 测试触发器before_insert_bicycle
insert into bicycles values('10009','光华大道9号','2019-01-05','1');
select * from admins;
sqlserver触发器注意事项tips:
引用资料
https://www.cnblogs.com/genesis/p/4914010.html
https://www.cnblogs.com/wangprince2017/p/7827091.html
手机扫一扫
移动阅读更方便
你可能感兴趣的文章