数据库.sql

图书馆借书系统

create database DBtest
on
	 (name='DBtext_Data', 
          filename ='d:\DBtext.mdf',
   	 size=15MB,
          maxsize=100MB,
   	  filegrowth=15%)

GO

exec sp_addlogin 'DBUser','DBUser' use DBtest


exec sp_grantdbaccess 'DBUser','DBUser'
grant all to DBUser

use DBtest

create table shangpin
(xinghao char(7) primary key,
 spming char(20) unique,
 guige char(10),
 shuliang char(6),
 sccjia char(20)
 );
go

create table ckudanju
(xinghao char(7),
 riqi datetime,
 spming char(20),
 guige char(10),
 shijian char(20),
 ckuming char(20),
 threnming char(20),
 ckshuliang int
primary key (xinghao,riqi,shijian),
foreign key (xinghao)references shangpin(xinghao)
);
go

create table rkudanju
(xinghao char(7),
 riqi datetime,
 spming char(20),
 guige char(10),
 shijian char(20),
 rkuming char(20),
 shrenming char(20),
 rkshuliang int
primary key (xinghao,riqi,shijian),
foreign key (xinghao)references shangpin(xinghao)
);
go

use DBtest

CREATE PROCEDURE sumcalculater4
as
begin
declare @sum int;
declare @xh char(50);
declare @cksl int;
declare @rksl int;
declare sc_cur CURSOR for
  select s.shuliang,s.xinghao,c.ckshuliang,r.rkshuliang
  from shangpin s,ckudanju c,rkudanju r
  where s.xinghao=c.xinghao and s.xinghao=r.xinghao and c.xinghao=r.xinghao

open sc_cur;
fetch next from sc_cur into @sum,@xh,@cksl,@rksl

begin
   update shangpin set shuliang=@sum-@cksl+@rksl
       where xinghao=@xh
end
while @@FETCH_STATUS=0
begin
fetch next from sc_cur into @sum,@xh,@cksl,@rksl
begin
   update shangpin set shuliang=@sum-@cksl+@rksl
       where xinghao=@xh
end

end
end

Insert into shangpin values('TR10000','红丹0号','000*000','1234','广东0号厂家')
Insert into shangpin values('TR10001','红丹1号','000*001','1233','广东1号厂家')
Insert into shangpin values('TR10002','红丹2号','000*002','1232','广东2号厂家')
Insert into shangpin values('TR10003','红丹3号','000*003','1231','广东3号厂家')
Insert into shangpin values('TR10004','红丹4号','000*004','1230','广东4号厂家')
Insert into shangpin values('TR10005','红丹5号','000*005','1222','广东5号厂家')
Insert into shangpin values('TR10006','红丹6号','000*006','1224','广东6号厂家')
Insert into shangpin values('TR10007','红丹7号','000*007','1223','广东7号厂家')
Insert into shangpin values('TR10008','红丹8号','000*008','1265','广东8号厂家')
Insert into shangpin values('TR10009','红丹9号','000*009','1234','广东9号厂家')




insert into ckudanju values('TR10000',2001-01-29,'红丹0号','000*000','08:30:54','国纺0场','','636')
insert into ckudanju values('TR10000',2001-02-29,'红丹0号','000*000','07:31:54','','王华','854')
insert into ckudanju values('TR10001',2001-03-23,'红丹1号','000*001','09:32:32','国纺1场','','220')
insert into ckudanju values('TR10002',2001-03-24,'红丹2号','000*002','11:32:54','国纺0场','','321')
insert into ckudanju values('TR10004',2001-04-04,'红丹3号','000*003','08:34:54','国纺1场','','157')
insert into ckudanju values('TR10006',2001-06-04,'红丹6号','000*006','18:35:54','国纺0场','','342')
insert into ckudanju values('TR10005',2001-10-05,'红丹5号','000*005','08:36:54','','王华','265')
insert into ckudanju values('TR10007',2001-10-05,'红丹7号','000*007','13:37:54','','张方','253')
insert into ckudanju values('TR10008',2002-05-24,'红丹8号','000*008','17:41:54','','王华','874')
insert into ckudanju values('TR10009',2002-05-24,'红丹9号','000*009','18:42:54','国防1场','','574')


insert into rkudanju values('TR10001',2001-01-24,'红丹0号','000*000','11:32:54','国纺3场','','424')
insert into rkudanju values('TR10004',2001-02-04,'红丹4号','000*004','08:34:54','国纺4场','','300')
insert into rkudanju values('TR10000',2001-02-04,'红丹0号','000*000','18:35:54','国纺3场','','400')
insert into rkudanju values('TR10002',2001-02-24,'红丹2号','000*002','10:46:54','','刘南','311')
insert into rkudanju values('TR10008',2001-03-06,'红丹8号','000*008','18:47:54','国纺4场','','413')
insert into rkudanju values('TR10009',2001-05-29,'红丹9号','000*009','11:48:54','国纺3场','','263')
insert into rkudanju values('TR10006',2001-06-21,'红丹6号','000*006','23:49:54','国纺4场','','243')
insert into rkudanju values('TR10005',2002-07-29,'红丹5号','000*005','09:53:54','国纺3场','','347')
insert into rkudanju values('TR10003',2002-08-09,'红丹3号','000*003','08:54:54','国纺3场','','246')
insert into rkudanju values('TR10007',2002-08-09,'红丹7号','000*007','09:55:54','','李蕾','432')


exec sumcalculater4