存储过程的建立与使用 一、实验目的
理解存储过程的概念、作用、建立和调用方法。 二、实验原理
使用CREATE PROCEDURE语句创建存储过程，ALTER PROCEDURE语句修改存储过程，DROP PROCEDURE语句删除存储过程，存储过程有不带参数的、有带输入参数的、有带输出参数(output)的，还可以有带返回值的。创建好的存储过程可以使用EXEC procedure_name语句执行。 三、实验设备
安装有SQL SERVER 2000的计算机。 四、实验示例 1、模糊查询
create procedure proc_empname @E_name varchar(10) as
select a.emp_name,a.dept,b.tot_amt from employee a inner join sales b on a.emp_no=b.sale_id
where a.emp_name like @E_name go
exec proc_empname '刘%'
2、利用存储过程计算出’E0008’业务员的销售总金额。
create procedure proc_saletot @E_no char(5), @p_tot int output as
select @p_tot=sum(tot_amt) from sales
where sale_id=@E_no go
declare @tot_amt int
exec proc_saletot E0014, @tot_amt output select @tot_amt
3、创建一带返回值的存储过程，返回某一部门的平均工资 create proc proc_avg_salary @Dept char(4) as
declare @avg_salary int select @avg_salary=avg(salary) from employee where dept=@Dept
return @avg_salary
declare @avg int
exec @avg=proc_avg_salary '人事' print '返回值='+cast(@avg as char(10)) 五、实验内容
1、利用存储过程，给employee表添加一条业务部门员工的信息。
2、利用存储过程从employee、sales、customer表的连接中返回所有业务员的姓名、客户姓名、销售金额。 3、创建带一个输入参数的存储过程，实现按员工姓名进行模糊查找，查找员工编号、订单编号、销售金额。 4、创建带两个输入参数的存储过程，查找姓“刘”并且职称为“职员”的员工的员工编号、订单编号、销售金额。
3、 利用存储过程计算出订单编号为10003的订单的销售金额。（带一输入参数和一输出参数）（提示：sales
表中的tot_amt应该等于sale_item表中的同一张订单的不同销售产品的qty*unit_price之和） 4、 创建一存储过程，根据给出的职称，返回该职称的所有员工的平均工资。（带一输入参数和返回值） 解答：：：：
1.利用存储过程，给employee表添加一条业务部门员工的信息。 //创建存储过程
create proc emp_in (
@emp_no char(5) ,
@emp_name varchar(10) , @sex char(2) ,
@dept varchar(10) , @title varchar(10), @date_hired datetime , @birthday datetime , @salary int,
@telephone varchar(20) , @addr varchar(50) ) as insert into employee values(@emp_no ,@emp_name @dept ,@title ,@date_hired ,@birthday , @salary , @telephone,@addr)
//执行存储过程 exec emp_in 'E10015','大王','女','计算机','1991-09-05','1998-12-19','5000','18974554130','珊瑚海'
, @sex ,
系','学生
2.利用存储过程从employee、sales、customer表的
连接中返回所有业务员的姓名、客户姓名、销售金额。 //创建存储过程
create proc find as
select emp_name,cust_name,tot_amt from employee ,sales, customer
where sales.sale_id=employee.emp_no and sales.cust_id = customer.cust_id //执行存储过程 exec find
3、创建带一个输入参数的存储过程，实现按员工姓名进行模糊查找， 查找员工编号、订单编号、销售金额。 //创建存储过程
create proc mohu (@emp_name varchar(10) ) as
select emp_no, order_no , tot_amt from employee , sales
where employee.emp_no = sales.sale_id and (employee.emp_name like @emp_name) //执行存储过程 declare @emp_name
exec mohu @emp_name='刘%'
4.创建带两个输入参数的存储过程，
查找姓“刘”并且职称为“职员”的员工的员工编号、订单编号、销售金额。 //创建存储过程
create proc find1( @emp_name varchar(10),@title varchar(10) ) as
select emp_no,order_no,tot_amt from employee , sales
where employee.emp_no = sales.sale_id and (emp_name like @emp_name)and (title=@title) //执行存储过程
exec find1 @emp_name='刘%',@title='职员'
5.利用存储过程计算出订单编号为的订单的销售金额。 （带一输入参数和一输出参数）
（提示：sales表中的tot_amt应该等于sale_item
表中的同一张订单的不同销售产品的qty*unit_price之和） //创建存储过程
create proc sum1 (@order_no int ,@summoney int output) as
select @summoney=SUM(tot_amt) from sales
where @order_no=order_n
//执行存储过程
declare @summoney int
exec sum1 10003 ,@summoney output select @summoney
6.创建一存储过程，根据给出的职称， 返回该职称的所有员工的平均工资。（带一输入参数和返回值）
//创建存储过程
create proc avg_salary @title char(4) as
declare @avg_salary int
select @avg_salary=avg(salary) from employee where title=@title return @avg_salary //执行存储过程 declare @avg int
exec @avg=avg_salary '职员'
print '返回值='+cast(@avg as char(10))