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

数据库系统 专栏收录该内容
11 篇文章 0 订阅

实验3《存储过程》

一、实验目的

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

二、实验内容

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

emp_nochar(5)Not nullprimary key员工编号
emp_namechar(10)员工姓名
sexchar(1)性别
deptchar(4)所属部门
titlechar(6)职称
date_hireddatetime到职日
birthdaydatetime生日
salaryint薪水
addrchar(50)null住址

(2)/*客户表customer */

cust_idchar(5)Not nullprimary key客户号
cust_namechar(20)客户名称
addrchar(40)客户住址
tel_nochar(10)客户电话
zipchar(6)邮政编码

(3)/*销售主表sales */

order_nointNot nullprimary key订单编号
cust_idchar(5)客户号
sale_idchar(5)业务员编号
tot_amtnumeric(9,2)订单金额
order_datedatetime订货日期
ship_datedatetime出货日期
invoice_nochar(10)发票号码

(4)/*销货明细表sale_item */

order_nointNot null,primary key订单编号
prod_idchar(5)Not null,primary key产品编号
qtyint销售数量
unit_pricenumeric(7,2)单价
order_datedatetimenull订单日期

(5)/*产品名称表product */

pro_idchar(5)Not nullprimary key产品编号
prod_namechar(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的订单的销售金额”中的订单编号可以设置为变量。

  • 3
    点赞
  • 0
    评论
  • 5
    收藏
  • 打赏
    打赏
  • 扫一扫,分享海报

©️2022 CSDN 皮肤主题:编程工作室 设计师:CSDN官方博客 返回首页

打赏作者

渣渣的夏天

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值