实验六
一、 实验目的
理解存储过程的概念、建立和调用方法
二、 实验环境
Windows 7, SQL Server 2008
三、 实验要求
1、建立五张表，每张表至少需要10条记录。
（1）/*员工人事表employee */ emp_no emp_name sex dept title date_hired birthday salary addr
（2）/*客户表customer */ cust_id cust_name addr tel_no zip
（3）/*销售主表sales */ order_no cust_id sale_id tot_amt order_date ship_date invoice_no
（4）/*销货明细表sale_item */ order_no prod_id qty int char(5) int Not null, Not null, primary key primary key 订单编号 产品编号 销售数量 int char(5) char(5) numeric(9,2) datetime datetime char(10) Not null Not null, primary key 订单编号 客户号 业务员编号 订单金额 订货日期 出货日期 号码 char(5) char(20) char(40) char(10) char(6) Not null Not null, primary key 客户号 客户名称 客户住址 客户电话 邮政编码 char(5) char(10) char(1) char(4) char(6) datetime datetime int char(50) Not null Not null primary key 员工编号 员工姓名 性别 所属部门 职称 到职日 生日 薪水 住址 unit_price order_date
numeric(7,2) datetime 单价 订单日期 （5）/*产品名称表product */ pro_id prod_name char(5) char(20) Not null Not null primary key 产品编号 产品名称 2、建立表的同时创建表的约束。 （1） 为每张表建立主键约束。 （2） 通过拖放操作加入外键。
（3） 在表employee加入CHECK约束：输入的员工编号必须以E开头的5位数编号，性别只能为M/F。 （4） 为销售主表sales中的编号字段建立UNIQUE约束。
3、通过快捷菜单得到脚本。
4、利用存储过程，给employee表添加一条业务部门员工的信息。
5、利用存储过程从employee、sales、customer表的连接中返回所有业务员的姓名、客户姓名、销售金额。
6、 利用存储过程查找“刘德华”的员工编号、订单编号、销售金额。 7、 利用存储过程查找姓“李”并且职称为“职员”的员工的员工编号、订单编号、销售金额。 8、 利用存储过程计算出订单编号为10003的订单的销售金额。
四、 实验结果
CREATE DATABASE company ON PRIMARY
( NAME = 'company_data',
FILENAME ='H:\\wlwDatabase\\company_data.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) LOG ON
( NAME = N'company_log',
FILENAME = N'H:\\wlwDatabase\\company_log.ldf' , SIZE = 1MB , FILEGROWTH = 10%) GO
GO
SET ANSI_PADDING OFF GO
/****** 对象: Table [dbo].[product] 脚本日期: 05/19/2011 13:53:59 ******/
SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO
SET ANSI_PADDING ON GO
CREATE TABLE [dbo].[product]( (
[pro_id] [char](5) NOT NULL, [prod_name] [char](20) NOT NULL,
PRIMARY KEY CLUSTERED
[pro_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO
SET ANSI_PADDING OFF GO
/****** 对象: Table [dbo].[employee] 脚本日期: 05/19/2011 13:53:57 ******/
SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO
SET ANSI_PADDING ON GO
CREATE TABLE [dbo].[employee]( (
[emp_no] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO
SET ANSI_PADDING OFF GO
/****** 对象: Table [dbo].[sales] 脚本日期: 05/19/2011 13:54:06 ******/ SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO
SET ANSI_PADDING ON GO
CREATE TABLE [dbo].[sales](
[order_no] [int] NOT NULL, [cust_id] [char](5) NULL, [emp_no] [char](5) NOT NULL, [emp_name] [char](10) NOT NULL, [sex] [char](1) NULL, [dept] [char](4) NULL, [title] [char](6) NULL, [date_hired] [datetime] NULL, [birthday] [datetime] NULL, [salary] [int] NULL, [addr] [char](50) NULL,
PRIMARY KEY CLUSTERED
(
[sale_id] [char](5) NULL, [tot_amt] [numeric](9, 2) NULL, [order_date] [datetime] NULL, [ship_date] [datetime] NULL, [invoice_no] [char](10) NULL,
PRIMARY KEY CLUSTERED
[order_no] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO
SET ANSI_PADDING OFF GO
/****** 对象: Table [dbo].[sale_item] 脚本日期: 05/19/2011 13:54:01 ******/
SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO
SET ANSI_PADDING ON GO
CREATE TABLE [dbo].[sale_item]( GO
SET ANSI_PADDING OFF GO
/****** 对象: Check [CK__employee__014935CB] 脚本日期: 05/19/2011 13:53:57 ******/
ALTER TABLE [dbo].[employee] WITH CHECK ADD CHECK (([emp_no] like 'E____' AND ([sex]='F' OR [sex]='M'))) GO
/****** 对象: ForeignKey [FK__sale_item__order__07F6335A] 脚本日期: 05/19/2011 13:54:02 ******/
ALTER TABLE [dbo].[sale_item] WITH CHECK ADD FOREIGN KEY([order_no]) REFERENCES [dbo].[sales] ([order_no]) GO
/****** 对象: ForeignKey [FK__sale_item__prod___08EA5793] 脚本日期: 05/19/2011 13:54:02 ******/
[order_no] [int] NULL, [prod_id] [char](5) NULL, [qty] [int] NULL,
[unit_price] [numeric](7, 2) NULL, [order_date] [datetime] NULL
) ON [PRIMARY]
ALTER TABLE [dbo].[sale_item] WITH CHECK ADD FOREIGN KEY([prod_id]) REFERENCES [dbo].[product] ([pro_id]) GO
/****** 对象: ForeignKey [FK__sales__cust_id__060DEAE8] 脚本日期: 05/19/2011 13:54:06 ******/
ALTER TABLE [dbo].[sales] WITH CHECK ADD FOREIGN KEY([cust_id]) REFERENCES [dbo].[customer] ([cust_id]) GO
4、利用存储过程，给employee表添加一条业务部门员工的信息。
create proc add_infer as begin
insert into employee values ('e3244','宋士杰','M','业务',
'经理','2010-2-16','19-3-4',8000,'长沙') End go
Exec add_infer
9、 利用存储过程从employee、sales、customer表的连接中返回所有业务员的姓名、客户姓
名、销售金额。
create proc infer as begin
select emp_name,cust_name,tot_amt from sales,customer,employee
where sale_id = emp_no and customer.cust_id = sales.cust_id end go
exec infer
create proc sumary as begin
select emp_name,sum(tot_amt) as '销售金额' from sales,customer,employee
where sale_id = emp_no and customer.cust_id = sales.cust_id
group by emp_name end go
exec sumary
6、利用存储过程查找“刘德华”的员工编号、订单编号、销售金额。
create proc infer2 as begin
select emp_name,emp_no,order_no,tot_amt from employee,sales
where emp_name='刘德华'and emp_no=sale_id end go
exec infer2
create proc summary2 as begin
select emp_name,emp_no,sum(tot_amt)as '销售金额' from employee,sales
where emp_no=sale_id and emp_name='刘德华' group by emp_name,emp_no end go
exec summary2 drop proc summary2
7、利用存储过程查找姓“李”并且职称为“职员”的员工的员工编号、订单编号销售金额。
create proc infer3 as begin
select emp_name,emp_no,order_no,tot_amt from employee,sales
where title='职员'and emp_name like '李%'and emp_no=sale_id
end go
exec infer3
8、利用存储过程计算出订单编号为10003的订单的销售金额。
create proc infer4 as begin
select order_no,sum(qty*unit_price) as '消费金额' from sale_item
where order_no=10003 group by order_no end go
exec infer4
