3.2.6创建主键及外键等表的约束
在查询分析器中通过如下代码创建数据表的主键及外键等约束。
ALTER TABLE [dbo].[图书信息] WITH NOCHECK ADD
CONSTRAINT [PK_图书信息] PRIMARY KEY CLUSTERED
(
[编号]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[图书借阅] WITH NOCHECK ADD
CONSTRAINT [PK_图书借阅] PRIMARY KEY CLUSTERED
(
[借阅编号]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[图书归还] WITH NOCHECK ADD
CONSTRAINT [PK_图书归还] PRIMARY KEY CLUSTERED
(
[归还编号]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[图书征订] WITH NOCHECK ADD
CONSTRAINT [PK_图书征订] PRIMARY KEY CLUSTERED
(
[征订编号]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[图书注销] WITH NOCHECK ADD
CONSTRAINT [PK_图书注销] PRIMARY KEY CLUSTERED
(
[注销编号]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[图书类型] WITH NOCHECK ADD
CONSTRAINT [PK_图书类型] PRIMARY KEY CLUSTERED
(
[类型编号]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[图书罚款] WITH NOCHECK ADD
CONSTRAINT [PK_图书罚款] PRIMARY KEY CLUSTERED
(
[罚款编号]
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER ON
GO原文请找腾讯752018766辣,文^论~文.网http://www.751com.cn/
SET ANSI_NULLS ON
GO
3.2.6 创建存储过程
系统使用了“sl_图书征订”，“sf_图书借阅”，“sf_图书归还”三个存储过程，实现的功能分别为征订图书入库、借阅图书出库和归还图书入库，产生罚款信息等功能。具体计算方法在下面的脚本中有详细的注释。
create proc sf_图书借阅
as
begin tran
--借书出库,减少图书库存量
update 图书信息 set 现存量 = isnull(现存量,0) - 1
from 图书信息 as a, 图书借阅 as b
where a.编号=b.图书编号 and b.状态='新借'
--设置借阅状态
update 图书借阅 set 续借次数=0,状态='未还'
where 状态='新借'
commit
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
上一页 [1] [2] [3] [4] [5] [6] 下一页