数据库实验报告4-5

实验4《存储过程》实验学时： 4每组人数： 1实验类型： 2 （1：基础性2：综合性3：设计性4：研究性）实验要求： 1 （1：必修2：选修3：其它）实验类别： 3 （1：基础2：专业基础3：专业4：其它）一、实验目的理解存储过程的概念、建立和调用方法。

进一步熟悉SQL语句对数据库进行完整性控制的方法。

二、实验内容1、建立一个新的销售数据库，包含五张表，每张表至少需要10条记录。

（1）/*员工人事表employee */（2）/*客户表customer */（3）/*销售主表sales */（4）/*销货明细表sale_item */（5）/*产品名称表product */（1）为每张表建立主键约束。

（2）通过拖放操作加入外键。

（3）在表employee加入CHECK约束：输入的员工编号必须以E开头的5位数编号，性别只能为M/F。

（4）为销售主表sales中的发票编号字段建立UNIQUE约束。

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

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

5、利用存储过程查找“刘德华”的员工编号、订单编号、销售金额。

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

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

三、实验要求：1．熟悉SQL SERVER 工作环境；2．建立销售数据库3．复习有关约束与存储过程的SQL语言命令。

4．备份数据库，作为实验5 的操作数据库。

四、实验步骤1．创建销售数据库，并建表、修改，要求将自己的信息包含其中；2、利用存储过程，给employee表添加一条业务部门员工的信息。

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

4、利用存储过程查找“刘德华”的员工编号、订单编号、销售金额。

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

五、实验结果1、建立一个新的销售数据库，包含五张表，每张表至少需要10条记录。

（1）/*员工人事表employee */（2）/*客户表customer */（3）/*销售主表sales */（4）/*销货明细表sale_item */（5）/*产品名称表product */解：首先写出每一个表的创建语句CREATE TABLE employee(emp_no char(5)not null,emp_name char(10),sex char(1),dept char(4),title char(6),date_hired datetime,birthday datetime,salary int,addr CHAR (50),primary key (emp_no))CREATE TABLE customer(cust_id char(5)not null,cust_name char(20),addr char(40),tel_no char(10),zip char(6),primary key (cust_id))CREATE TABLE sales(order_no int not null,cust_id char(5),sale_id char(5),tot_amt numeric(9,2),order_date datetime,ship_date datetime,invoice_no char(10),primary key (order_no))CREATE TABLE sale_item (order_no int not null,prod_id char(5) not null,qty int,unit_price numeric(7,2),order_date datetime not null, primary key (order_no,prod_id) )CREATE TABLE product(pro_id char(5) NOT NULL, prod_name char(20) NOT NULL, primary key (pro_id))以上建表结果：为每一个表输入数据：2、建立表的同时创建表的约束。

（1）为每张表建立主键约束。

已建立主键约束（2）通过拖放操作加入外键。

步骤如下：首先点击数据库，可以看见，数据库下方有数据库关系表：右键其，创建一个数据库关系图接着可以看见选择添加以上五个数据库，拖动键后便可以·设立建立（1）在表employee加入CHECK约束：输入的员工编号必须以E开头的5位数编号，性别只能为M/F。

步骤：第一步右键第二步：又按着鼠标右键：第三步：点击添加接着再表达式里面写着约束条件（2）为销售主表sales中的发票编号字段建立UNIQUE约束。

alter table sales add constraint order_no——uniqueunique (order_no);3、利用存储过程，给employee表添加一条业务部门员工的信息。

create procedure proAddEmployee(@emp_no char(5),@emp_name char(10),@sex char(1),@dept char(10),@title char(6),@date_hired datetime,@birthday datetime,@salary int,@addr char(50))asinsert into employee values(@emp_no,@emp_name,@sex,@dept,@title,@date_hired,@birthda y,@salary,@addr)goexec proAddEmployee'E0022','罗刚','M','业务','经理','2009-07-08','1988-02-03',13000,'都匀市'执行后：刷新表格后，查看表格，可以发现，表中写入了信息结果为：4、利用存储过程从employee、sales、customer表的连接中返回所有业务员的姓名、客户姓名、销售金额。

create procedure findasselect employee.emp_name,customer.cust_name,sales.tot_amtfrom employee,customer,saleswhere sales.sale_id = employee.emp_no and sales.cust_id = customer.cust_idgoexec find执行后结果为：刷新后，旁边的存储过程的显示为：5、利用存储过程查找“刘德华”的员工编号、订单编号、销售金额。

create procedure findasselect employee.emp_name,customer.cust_name,sales.tot_amtfrom employee,customer,saleswhere sales.sale_id = employee.emp_no and sales.cust_id = customer.cust_idgoexec find执行结果：刷新后，可看见：此结果采用的数据为：Employee表：Sales表：6、利用存储过程查找姓“李”并且职称为“职员”的员工的员工编号、订单编号、销售金额。

create procedure findLi@emp_name varchar(10)asselect employee.emp_no,sales.order_no,sales.tot_amtfrom employee,saleswhere employee.emp_no = sales.sale_id and employee.title = '职员' and (employee.emp_name like @emp_name)goexec findLi'李%'结果：刷新后，在旁边可看见：结果的数据中所查询的表的内容为：Employee表：Sales表：7、利用存储过程计算出订单编号为10003的订单的销售金额。

CREATE PROCEDURE PRO_ORDER@order_no varchar(6)asselect sales.tot_amtfrom saleswhere sales.order_no = @order_nogoexec PRO_ORDER'10003'实验结果：刷新后发现左边更新：附录：实验示例1、模糊查询create procedure sp_empname @E_name varchar(10) asselect a.emp_name,a.dept,b.tot_amtfrom employee a inner join sales bon a.emp_no=b.sale_idwhere a.emp_name like @E_namegoexec sp_empname '陈%'2、利用存储过程计算出’E0014’业务员的销售总金额。

create procedure sp_saletot @E_no char(5),@p_tot int output as select @p_tot=sum(tot_amt)from saleswhere sale_id=@E_nogodeclare @tot_amt intexec sp_saletot E0014, @tot_amt outputselect @tot_amt六、实验结论存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。

存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。

sql中的存储过程：CREATE PROCEDURE [拥有者.]存储过程名[;程序编号][(参数#1,…参数#1024)][WITH{RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}][FOR REPLICATION]AS 程序行其中存储过程名不能超过128个字。