数据库系统SSD7 实验3《存储过程》

实验3《存储过程》

一、实验目的

  理解存储过程的概念、建立和调用方法。进一步熟悉SQL语句对数据库进行完整性控制的方法。

二、实验内容

1、建立一个新的销售数据库，包含五张表，每张表至少需要10条记录。
（1）/*员工人事表employee */

emp_no	char(5)	Not null	primary key	员工编号
emp_name	char(10)			员工姓名
sex	char(1)			性别
dept	char(4)			所属部门
title	char(6)			职称
date_hired	datetime			到职日
birthday	datetime			生日
salary	int			薪水
addr	char(50)	null		住址

（2）/*客户表customer */

cust_id	char(5)	Not null	primary key	客户号
cust_name	char(20)			客户名称
addr	char(40)			客户住址
tel_no	char(10)			客户电话
zip	char(6)			邮政编码

（3）/*销售主表sales */

order_no	int	Not null	primary key	订单编号
cust_id	char(5)			客户号
sale_id	char(5)			业务员编号
tot_amt	numeric(9,2)			订单金额
order_date	datetime			订货日期
ship_date	datetime			出货日期
invoice_no	char(10)			发票号码

（4）/*销货明细表sale_item */

order_no	int	Not null，	primary key	订单编号
prod_id	char(5)	Not null,	primary key	产品编号
qty	int			销售数量
unit_price	numeric(7,2)			单价
order_date	datetime	null		订单日期

（5）/*产品名称表product */

pro_id	char(5)	Not null	primary key	产品编号
prod_name	char(20)	Not null		产品名称

2、建立表的同时创建表的约束。
（1）为每张表建立主键约束。
（2）通过拖放操作加入外键。
（3）在表employee加入CHECK约束：输入的员工编号必须以E开头的5位数编号，性别只能为M/F。
（4）为销售主表sales中的发票编号字段建立UNIQUE约束。

3、利用存储过程，给employee表添加一条业务部门员工的信息。
4、利用存储过程从employee、sales、customer表的连接中返回所有业务员的姓名、客户姓名、销售金额。
5、利用存储过程查找“刘德华”的员工编号、订单编号、销售金额。
6、利用存储过程查找姓“李”并且职称为“职员”的员工的员工编号、订单编号、销售金额。
7、利用存储过程计算出订单编号为10003的订单的销售金额。

三、实验方法

  本次实验方法主要是数据库中的存储技术，利用SQL Server数据库进行操作管理一个销售数据库。
  实验方案为依照实验指导书逐步进行操作，同时结合书本有关存储过程的知识点。

四、实验步骤

1．创建销售数据库，并建表、修改，要求将自己的信息包含其中；
  1.1 利用SSMS工具进行数据表的创建，同时按要求进行主键约束等操作。
（1）为每张表建立主键约束。
（2）通过拖放操作加入外键。
（3）在表employee加入CHECK约束：输入的员工编号必须以E开头的5位数编号，性别只能为M/F。
（4）为销售主表sales中的发票编号字段建立UNIQUE约束。
   1.2 向各个表中添加示例数据。
   1.3 查询建表结果。

2．利用存储过程，给employee表添加一条业务部门员工的信息。
3．利用存储过程从employee、sales、customer表的连接中返回所有业务员的姓名、客户姓名、销售金额。
4．利用存储过程查找“刘德华”的员工编号、订单编号、销售金额。
5．利用存储过程查找姓“李”并且职称为“职员”的员工的员工编号、订单编号、销售金。
6．利用存储过程计算出订单编号为10003的订单的销售金额。

五、实验结果

1．创建销售数据库，并建表、修改，要求将自己的信息包含其中；
  1.1 利用SSMS工具进行数据表的创建，同时按要求进行主键约束等操作。
（1）为每张表建立主键约束。
（2）通过拖放操作加入外键。
（3）在表employee加入CHECK约束：输入的员工编号必须以E开头的5位数编号，性别只能为M/F。
（4）为销售主表sales中的发票编号字段建立UNIQUE约束。

/*员工人事表*/
create table employee (
	emp_no	char(5)	Not null primary key check (emp_no LIKE 'E%' AND LEN(emp_no)=5),
	emp_name varchar(10) Not null,
	sex	char(2)	Not null check(sex IN('M','F')),
	dept	varchar(4)	Not null,
	title	varchar(6)	Not null,
	date_hired	datetime	Not null,
	birthday	datetime	Null,
	salary	int	Not null,
	addr	varchar(50)	null
)
/*客户表*/
create table customer(
	cust_id	char(5)	Not null	primary key,
	cust_name	varchar(20)	Not null,	
	addr	varchar(40)	Not null,	
	tel_no	varchar(10)	Not null,	
	zip	char(6)	null
)

/*销售主表*/
create table sales( 
	order_no	int	Not null	primary key,
	cust_id	char(5)	Not null	,
	sale_id	char(5)	Not null	,
	tot_amt	numeric(9,2)	Not null,	
	order_date	datetime	Not null,
	ship_date datetime Not null,
	invoice_no char(10) UNIQUE
)
/*销货明细表*/
create table sale_item(
	order_no	int	Not null,
	prod_id	char(5)	Not null,
	qty	int	Not null,
	unit_price	numeric(7,2)	Not null,
	order_date	datetime	null,
)
alter table sale_item add primary key(order_no,prod_id) 

/*产品名称表*/
create table product(
	prod_id	char(5)	Not null	primary key,
	prod_name	varchar(20)	Not null,	
)

  1.2 向各个表中添加示例数据。

/*人事表（employee）数据：*/
insert into employee values('E0001','王大华','M','业务','经理','1976-10-13','1951-08-01',8000,'上海市')
insert employee values('E0003','陈自强','M','会计','科长','1986-09-15','1963-06-09',4800,'南京市')
insert employee values('E0014','周小梅','F','业务','职员','1996-03-01','1970-03-28',3200,'上海市')
insert employee values('E0009','陈建国','M','管理','科长','1987-04-15','1967-09-01',4500,'天津市')
insert employee values('E0017','林光华','M','业务','职员','1995-10-13','1973-08-17',3000,'上海市')
insert employee values('E0006','李珠珠','F','管理','经理','1988-01-01','1961-07-12',6000,'北京市')
insert employee values('E0002','李庄敬','M','人事','科长','1980-09-15','1958-05-13',8000,'广州市')
insert employee values('E0010','王成功','M','信息','职员','1993-02-15','1969-04-15',4500,'北京市')
insert employee values('E0013','陈中华','M','业务','职员','1993-02-15','1966-07-01',4300,'天津市')
insert employee values('E0008','刘  刚','M','业务','职员','1994-11-01','1968-08-01',4000,'上海市')
insert employee values('E0005','李珊珊','F','会计','职员','1990-03-20','1967-04-25',3800,'上海市')
insert employee values('E0011','李小蓉','F','人事','职员','1994-11-01','1970-11-18',3000,'重庆市')
insert employee values('E0012','蔡文钦','M','制造','厂长','1984-08-15','1960-07-21',5000,'上海市')
insert employee values('E0015','张大山','M','制造','职员','1993-12-15','1968-09-23',3500,'上海市')
insert employee values('E0007','吴铁雄','M','信息','科长','1989-10-01','1965-04-18',5000,'武汉市')
insert employee values('E0016','方美美','F','业务','职员','1992-05-20','1966-06-23',4000,'上海市')
insert employee values('E0004','刘中兴','M','制造','经理','1984-05-01','1960-05-23',6000,'上海市')
insert employee values('E0019','王仁华','M','信息','经理','1985-09-15','1959-03-24',6000,'上海市')
insert employee values('E0020','陈火旺','M','业务','职员','1992-08-01','1964-05-12',4000,'天津市')
insert employee values('E0018','林中正','M','管理','总经理','1974-10-01','1953-05-04',10000,'上海市')

/*  客户（customer）表数据：*/
insert customer values('C0001','ABC股份有限公司','上海市','0218120440','100')
insert customer values('C0002','客户乙','天津市','0255344441','400')
insert customer values('C0003','客户丙','北京市','0207180787','800')
insert customer values('C0004','客户丁','上海市','0218120564','104')
insert customer values('C0005','客户戊','北京市','0224120477','803')
insert customer values('C0006','客户己','上海市','0218128091','103')
insert customer values('C0007','客户庚','上海市','0218506110','102')

/*销售（sales）主表数据:*/
insert sales values (10002,'C0002','E0013',22700.00,'1996-11-10','1997-1-11','I000000002')
insert sales values(10003,'C0003','E0014',13960.00,'1996-10-15','1996-12-5','I000000003')
insert sales values(10004,'C0003','E0014',33000.00,'1996-11-10','1996-12-25','I000000004')
insert sales values (10001,'C0001','E0008',60000.00,'1996-11-10','1996-12-26','I000000001')
insert sales values (10007,'C0007','E0008',20000.00,'1996-11-10','1997-1-2','I000000008')

/*销售明细表（sale_item）数据:*/
insert sale_item values (10001,'P0001',5 , 2500.00,'1996-10-22')
insert sale_item values (10001,'P0002',3 , 6500.00,'1996-10-22')
insert sale_item values (10001,'P0003',2 , 5300.00,'1996-10-22')
insert sale_item values (10001,'P0004',2 , 1600.00,'1996-10-22')
insert sale_item values (10002,'P0001',3 , 2600.00,'1996-11-10')
insert sale_item values (10002,'P0003',1 , 5300.00,'1996-11-10')
insert sale_item values (10002,'P0008',2 , 4800.00,'1996-11-10')
insert sale_item values (10003,'P0001',4 , 2700.00,'1996-10-15')
insert sale_item values (10003,'P0004',2 , 1580.00,'1996-10-15')

/*产品名称(product)表数据：*/
insert product values ('P0001','16M DRAM')
insert product values ('P0002','14寸显示器')
insert product values ('P0003','1.2GB硬盘')
insert product values ('P0004','3.5寸软驱')
insert product values ('P0005','键盘')
insert product values ('P0006','VGA显示卡')
insert product values ('P0007','网卡')
insert product values ('P0008','Pentium100CPU')
insert product values ('P0009','激光打印机')
insert product values ('P0010','8倍速光驱')
insert product values ('P0011','计算机字典')
insert product values('P0012','9600bits-s调制解调器')
insert product values('P0013','Pentium主板')

  1.3 查询建表结果。

SELECT * from employee
SELECT * from customer
SELECT * from sales
SELECT * from sale_item
SELECT * from product

2．利用存储过程，给employee表添加一条业务部门员工的信息。

create procedure insertEmployee
as
begin
	insert into employee values ('E0025','张增','M','管理','总经理','2019-10-01','1998-05-04',10000,'长沙市')
end
go
exec insertEmployee

select * from employee where emp_no='E0025'

3．利用存储过程从employee、sales、customer表的连接中返回所有业务员的姓名、客户姓名、销售金额。

create procedure returnInfo
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 returnInfo

4．利用存储过程查找“刘德华”的员工编号、订单编号、销售金额。（注：表格中创建数据未加入“刘德华”，故用“周小梅”代替）

create procedure queryZXM
as
begin
	select sale_id,order_no,sum(tot_amt)销售金额
	from sales where sale_id in(select emp_no from employee 
	where emp_name = '周小梅')
	group by sale_id,order_no
end
go
exec queryZXM

5．利用存储过程查找姓“刘”并且职称为“职员”的员工的员工编号、订单编号、销售金。

create procedure queryBy @name varchar(10)
as
begin
	select sale_id,order_no,sum(tot_amt)销售金额
	from sales where sale_id in(select emp_no from employee 
	where emp_name like @name)
	group by sale_id,order_no
end
go
exec queryBy '%刘'

6．利用存储过程计算出订单编号为10003的订单的销售金额。

create procedure saleMoney1003
as
begin
	select sum(sale_item.qty*sale_item.unit_price)
	from sales,sale_item
	where sales.order_no='10003'
end
go
exec saleMoney1003

六、实验结论

  实验数据和结果详情见第五点，本次实验主要是创建了五个彼此有一定约束关系的表的销售数据库，之后对数据库中的数据利用存储过程进行一定的操作获取相应的结果。

七、实验小结

  本次实验主要学会了如何在多张有约束关系表中进行添加约束以及验证check，再就是利用存储过程对数据库中的数据进行一定操作。
  遇到的问题主要是对存储过程的使用不熟练，通过查阅课程PPT、书本和网络资料即可解决。
  实验有待改进的地方主要是存储过程的通用性，可以将指定的变量改为用户输入，这样就可以更加方便用户使用，比如“计算出订单编号为10003的订单的销售金额”中的订单编号可以设置为变量。