数据库：存储过程实验

一、实验目的及要求

目的

掌握存储过程的编写与调用

要求

掌握存储过程的编写；掌握存储过程的调用

二、实验条件

安装有SQL Server2014数据库的计算机

三、实验内容

使用事务、锁和游标；编写和使用存储过程；使用触发器

四、实验结果

1.使用事务方式往院系表（t_department）中添加院系信息。信息分别为：（1）04，电器工程系，伏特；（2）04，机械工程系，墨子。要求两条记录同时添加，若错误则回滚，最后查询当前院系表中的数据信息。注意要查看输出的结果信息和消息信息。

参考代码：

use EMISE

go

begin tran mytran

insert into t_department values('04','电器工程系','欧姆')

insert into t_department values('04','机械工程系','墨子')

if @@ERROR>0

rollback tran mytran

else

commit tran mytran

select *from t_department

 

查看结果：

正在上传…重新上传取消

查看信息：

 

正在上传…重新上传取消

 

2.把题目 1 中的数据改为（1）04，电器工程系，伏特；（2）05，机械工程系，墨子；再次运行代码，查看结果和消息。

结果：

正在上传…重新上传取消

 

消息：

正在上传…重新上传取消

 

3.使用存储过程中定义事务实现银行转账业务处理。

use master

go

if DB_ID('bankdb') is not null

drop database bankdb;

go

create database bankdb

go

use bankdb

go

create table account(

id int identity(1,1) primary key,

cardno char(20) unique not null,

balance numeric(18,2)

)

go

insert into account values('01',100.0)

insert into account values('02',200.0)

insert into account values('03',300.0)

go

--创建存储过程

create procedure sp_transfer_money

@out_cardno char(20),

@in_cardno char(20),

@money numeric(18,2)

as

begin

declare @remain numeric(18,2)

select @remain=balance from account where cardno=@out_cardno

if @money>0

if @remain>=@money --余额足够

begin

begin tran t1

update account set balance=balance-@money where cardno=@out_cardno

update account set balance=balance+@money where cardno=@in_cardno

if @@error>0

begin ---wrong

rollback tran t1

return 0

end

else

begin ---right

commit tran t1

print '转账成功!'

end

end

else ---余额少

begin

print '余额不足'

end

else --- 转账金额小于0

print '转账金额应大于0！'

end

go

exec sp_transfer_money '01','02',50

正在上传…重新上传取消

 

4.在SQL Server中查看数据库中的锁。

（1）使用快捷键“Ctrl+2”查看锁的信息。

打开SQL Server2014的SSMS，在查询分析器中使用快捷键“Ctrl+2”，即可看到进程、锁已经对象等信息，如下图所示：

正在上传…重新上传取消

 

（2）使用系统存储过程sp_lock来查看数据库中的锁。

在新建查询的编辑区内，键入“EXEC sp_lock”,执行这个命令也可以查看当前数据库中的锁。

EXEC sp_lock

正在上传…重新上传取消

 

5.使用表级锁。在 SQL Server 中模拟两个用户对表进行操作，其中一个用户查看学生表信息，并锁定 20ms；另一个用户也查看学生表，并要更新其中的数据。

（1）创建一个新的查询窗口，在编辑区窗口内键入如下代码，并保存为user1_lock.sql。

use EMISE

go

begin tran t1

declare @sd_time varchar(8)

select *from t_student with(holdlock)

select @sd_time=convert(varchar,getdate(),8)

print '用户1锁定时间为：'+@sd_time

waitfor delay '00:00:20'

select @sd_time=convert(varchar,getdate(),8)

print '用户1锁定时间为：'+@sd_time

commit tran t1

正在上传…重新上传取消

 

（2）创建一个新的查询窗口，在编辑区窗口键入如下代码，并保存为user2_update.sql。

use EMISE

go

begin tran t2

declare @sd_time varchar(8)

--select *from t_student with(holdlock)

select @sd_time=convert(varchar,getdate(),8)

print '用户2开始时间为：'+@sd_time

select *from t_student

select @sd_time=convert(varchar,getdate(),8)

print '用户2执行查询的时间为：'+@sd_time

update t_student set name='wang' where name='王庆子'

select @sd_time=convert(varchar,getdate(),8)

print '用户2更新数据的时间为：'+@sd_time

commit tran t2

正在上传…重新上传取消