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

太过爱你忘了你带给我的痛 2021-11-09 03:34 349阅读 0赞

#### 实验3《存储过程》 ####

##### 一、实验目的 #####

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

##### 二、实验内容 #####

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

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

（2）/\*客户表customer \*/

<table> 
 <thead> 
  <tr> 
   <th>cust_id</th> 
   <th>char(5)</th> 
   <th>Not null</th> 
   <th>primary key</th> 
   <th>客户号</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>cust_name</td> 
   <td>char(20)</td> 
   <td></td> 
   <td></td> 
   <td>客户名称</td> 
  </tr> 
  <tr> 
   <td>addr</td> 
   <td>char(40)</td> 
   <td></td> 
   <td></td> 
   <td>客户住址</td> 
  </tr> 
  <tr> 
   <td>tel_no</td> 
   <td>char(10)</td> 
   <td></td> 
   <td></td> 
   <td>客户电话</td> 
  </tr> 
  <tr> 
   <td>zip</td> 
   <td>char(6)</td> 
   <td></td> 
   <td></td> 
   <td>邮政编码</td> 
  </tr> 
 </tbody> 
</table>

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

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

（4）/\*销货明细表sale\_item \*/

<table> 
 <thead> 
  <tr> 
   <th>order_no</th> 
   <th>int</th> 
   <th>Not null，</th> 
   <th>primary key</th> 
   <th>订单编号</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>prod_id</td> 
   <td>char(5)</td> 
   <td>Not null,</td> 
   <td>primary key</td> 
   <td>产品编号</td> 
  </tr> 
  <tr> 
   <td>qty</td> 
   <td>int</td> 
   <td></td> 
   <td></td> 
   <td>销售数量</td> 
  </tr> 
  <tr> 
   <td>unit_price</td> 
   <td>numeric(7,2)</td> 
   <td></td> 
   <td></td> 
   <td>单价</td> 
  </tr> 
  <tr> 
   <td>order_date</td> 
   <td>datetime</td> 
   <td>null</td> 
   <td></td> 
   <td>订单日期</td> 
  </tr> 
 </tbody> 
</table>

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

<table> 
 <thead> 
  <tr> 
   <th>pro_id</th> 
   <th>char(5)</th> 
   <th>Not null</th> 
   <th>primary key</th> 
   <th>产品编号</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>prod_name</td> 
   <td>char(20)</td> 
   <td>Not null</td> 
   <td></td> 
   <td>产品名称</td> 
  </tr> 
 </tbody> 
</table>

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的订单的销售金额。

##### 五、实验结果 #####

/*员工人事表*/
    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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NTY0NTU1_size_16_color_FFFFFF_t_70]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NTY0NTU1_size_16_color_FFFFFF_t_70 1]  
![在这里插入图片描述][20190804194642100.png]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NTY0NTU1_size_16_color_FFFFFF_t_70 2]  
![在这里插入图片描述][20190804194653454.png]  
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

![在这里插入图片描述][20190804194910864.png]  
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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NTY0NTU1_size_16_color_FFFFFF_t_70 3]  
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 '%刘'

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NTY0NTU1_size_16_color_FFFFFF_t_70 4]

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NTY0NTU1_size_16_color_FFFFFF_t_70 5]

##### 六、实验结论 #####

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

##### 七、实验小结 #####

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

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NTY0NTU1_size_16_color_FFFFFF_t_70]: /images/20211109/29b2686381424ef8a40e59167b727b2b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NTY0NTU1_size_16_color_FFFFFF_t_70 1]: /images/20211109/3d89ea439264416e81b1058073f131c2.png
[20190804194642100.png]: /images/20211109/b3f753bad27341018948b1627bdccebc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NTY0NTU1_size_16_color_FFFFFF_t_70 2]: /images/20211109/d7c2f46f40d94188bb9e89e386c62eee.png
[20190804194653454.png]: /images/20211109/6f6e8244b8c04a2aadd0cae62a1878b6.png
[20190804194910864.png]: /images/20211109/c18b1b8ecaa942d88e4b9db86d835613.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NTY0NTU1_size_16_color_FFFFFF_t_70 3]: /images/20211109/870f8846a9dd43e6a838201b352a7b24.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NTY0NTU1_size_16_color_FFFFFF_t_70 4]: /images/20211109/5801f000030e4a24bc67fda710d3d11b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NTY0NTU1_size_16_color_FFFFFF_t_70 5]: /images/20211109/2851459ebdbd49318ac518e4c6e30a1c.png