Book-SQLSERVER数据库脚本

--删除同名数据库Book
if exists (select * from   master..sysdatabases where  name ='Book')
drop database Book
go

--使用默认设置创建Book(默认在C盘SQLSERVER安装目录中创建)
create database Book

 

--创建图书库存表
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Book_stock]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Book_stock]
GO

CREATE TABLE [dbo].[Book_stock] (
 [ID] [int] IDENTITY (1, 1) NOT NULL ,
 [book_name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
 [book_price] [money] NULL ,
 [book_quantity] [int] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Book_stock] WITH NOCHECK ADD
 CONSTRAINT [PK_Book_stock] PRIMARY KEY  CLUSTERED
 (
  [ID]
 )  ON [PRIMARY]
GO

--向“库存表”插入记录
use book

insert into book_stock (book_name,book_price,book_quantity)
values ('VB高手',15.50,100)

insert into book_stock (book_name,book_price,book_quantity)
values ('ASP指南',20.50,500)

insert into book_stock (book_name,book_price,book_quantity)
values ('JAVA基础',25.50,400)

insert into book_stock (book_name,book_price,book_quantity)
values ('VC之路',25.50,200)

insert into book_stock (book_name,book_price,book_quantity)
values ('C#基础',25.50,300)

----创建存储过程
--根据ID、图书名称，返回数量；
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[getQuantity]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[getQuantity]
GO
CREATE proc dbo.getQuantity       ----输出参数
@bkid int,@bkname nvarchar(50),@result int output
as
select @result=book_quantity from book_stock
where id=@bkid and book_name=@bkname

--在查询分析器中测试存储过程
declare @n int
exec dbo.getQuantity 3,'JAVA基础',@n output
select @n