数据库存储过程、触发器上机练习

/*本次练习基于以下关系模式：

某汽车租赁公司建立汽车租赁管理系统，其数据库的部分关系模式如下：

用户： USERS(UserId，Name，Balance)，各属性分别表示用户身份证号，姓名，账户余额；

汽车：CARS（CId，CType,CPrice，CStatus），各属性分别表示汽车车牌，汽车型号，日租金，状态。状态0表示未出租，1表示已出租。

租用记录BORROWS（BId，UserId，CId,SDate，EDate，bill）。各属性分别表示租车流水号，用户身份证号，汽车车牌，租用开始时间，归还时间，租车费用。其中已租但尚未还车的记录，其edate和bill均为Null

建表命令如下：*/

create database 汽车租赁管理系统;

use 汽车租赁管理系统;



--创建表

create table Users

(

  UserId nchar(18) primary key, --身份证

  Name  nvarchar(20) not null,  --姓名

  Balance int check( Balance>=0) --账户余额

)

create table Cars

(

 CId nchar(10) primary key, --车牌

 CType nvarchar(30) not null, --汽车型号

 CPrice int check(CPrice>=0), --日租金

 CStatus tinyint              --租车状态，0表示未出租，1表示正在出租

)

create table Borrows

(

 BId int primary key,  -- 租车流水号

 UserId nchar(18) foreign key references Users(UserId),

 CId nchar(10) foreign key references Cars(CId),

 SDate date,  -- 租车开始日期

 EDate date  ,-- 还车日期，未还车则为null

 Bill int --本次消费金额,  未还车则为null

);



--插入表数据：

insert into Users values('0001','张三',2000)

insert into Users values('0002','老四',3000)

insert into Users values('0003','阿里',1000)



insert into Cars values('0101','C19',100,0)

insert into Cars values('0102','C20',200,0)

insert into Cars values('0103','B10',150,0)

insert into Cars values('0104','A21',150,1)

insert into Cars values('0105','A19',300,0)

insert into Cars values('0106','B39',250,1)



insert into Borrows values (1,'0001','0101','2021-01-02','2021-01-03',100)

insert into Borrows values (2,'0001','0101','2021-01-04','2021-01-09',300)

insert into Borrows values (3,'0002','0102','2021-01-04',null,300)

insert into Borrows values (4,'0001','0101','2021-01-10',null,300)



--查询表数据

select * from Users;

select * from Borrows;

select * from Cars;



--第1题

--1、创建存储过程，根据用户身份证号码查询其租车总次数和总消费金额，

--并以输出参数的形式返回。存储过程名称自定：

-- 一个输入参数：用户身份证号码，

-- 两个输出参数：该用户租车总次数，该用户消费总金额。

--2、写出批处理代码：调用上述存储过程，求 xxxxx（身份证号码，具体值自定）

--租车总次数及消费总金额，并以以下格式输出信息：

--    xxxxx租车总次数为：xxxx   消费总金额为： xxxxx

create procedure a1

@uname varchar(20),

@bnull int output,

@sprice int output

as

    select @bnull=count(*),@sprice=sum(Bill) from Borrows

    where UserId in (select UserId from Users where Name=@uname)

--执行存储过程a1：

declare

@myuname varchar(20),

@mybnull int,

@mysprice int

set @myuname='张三'

exec a1 @myuname,@mybnull output,@mysprice output

    begin

         print @myuname+'的租车总次数为'+str(@mybnull)+'，消费总金额为'+str(@mysprice)

    end



--第2题

/*创建存储过程：

   查询日租金在某个范围的尚未出租的所有汽车信息，其中日租金下限默认值为100。*/

create procedure a2

@minprice int=100,

@maxprice int

as

    select * from Cars where CPrice between @minprice and @maxprice and CStatus=0

--执行存储过程a2：

exec a2 @minprice=150,@maxprice=200

exec a2 @maxprice=300



--第3题

/*创建存储过程：查询某用户最近一次的租车情况。

输入参数：用户身份证。

输出参数：租车情况代码。用0表示该用户从未租车，1表示用户有已租尚未归还的车辆，2表示用户租过车并已正常归还。

提示：

    最近一次租车，也就是租车起始时间最大，按照sdate降序排列，排在第一位的就是。*/

create procedure a3

@uid nchar(18),

@us int output

as

    if not exists(select * from Borrows where UserId=@uid)

         set @us=0

    else if exists (select top 1 * from Borrows where UserId=@uid and Edate is null order by SDate desc)

         set @us=1

    else if exists(select top 1 * from Borrows where UserId=@uid and EDate is not null order by SDate desc)

         set @us=2

--执行存储过程a3：

declare

@myuid nchar(18),

@myus int

set @myuid='0001'

exec a3 @myuid,@myus output

    begin

         print '租车情况代码'+str(@myus)

    end



--第4题

/* 在Users表创建级联删除触发器：

当删除某个用户时，同时删除该用户所有的租车记录。

提示： 1、请勿考虑本题的操作是否合理

           2、请根据题意自行确定触发器的类型。

           3、请注意外键对delete命令的影响       */

create trigger b1

on Users

instead of delete

as

    begin

         delete from Borrows where UserId=(select UserId from deleted)

         delete from Users where UserId=(select UserId from deleted)

    end

--触发b1：

delete from Users where UserId='0002'



--第5题

/* 请在borrows表创建insert触发器，实现复杂约束：

用户试图租车时，

如果用户有已经在租，尚未归还的车辆，则不允许租车，输出提示信息：有车尚未归还。

    如果账户余额小于所租车辆的日租金，则也不允许租车，并输出提示信息：账户余额不足。

    其他情况，允许租车（即允许该insert命令）

提示：

1、为简单起见，本题假设用户身份证，车牌号不会违反外键约束，因此不需要在触发器中检查。

2、触发器类型请自行根据题意决定。 */

create trigger insert_i

on Borrows

after insert

as

declare

@UserId nchar(18)

    if exists(select top 1 * from Borrows where UserId=@UserId and Edate is null order by SDate desc)

         begin

         print '有车尚未归还'

         rollback

         end

    else if (select Balance from Users where UserId in (select UserId from inserted)) < (select Cprice from Cars where CId in(select CId from inserted))

         begin

         print '账户余额不足'

         rollback

         end

--触发b2：

insert into Borrows(UserId) values('0001')