实验5.1 存储过程的建立与使用
一、实验目的
理解存储过程的概念、作用、建立和调用方法。
二、实验原理
使用CREATE PROCEDURE语句创建存储过程，ALTER PROCEDURE语句修改存储过程，DROP PROCEDURE语句删除存储过程，存储过程有不带参数的、有带输入参数的、有带输出参数(output)的，还可以有带返回值的。创建好的存储过程可以使用EXECprocedure_name语句执行。
三、实验设备
安装有SQL SERVER 2008的计算机。
四、实验示例
1、模糊查询
create procedure proc_empname @E_name varchar(10)
as
selecta.emp_name,a.dept,b.tot_amt
fromemployee a inner join sales b
ona.emp_no=b.sale_id
wherea.emp_name like @E_name
go
exec sp_empname'陈%'
2、利用存储过程计算出’E0014’业务员的销售总金额。
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 sp_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表添加一条业务部门员工的信息。
Create proc sp_Insert_employee
@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),@rtn int output
as
declare
@emp_name2 varchar(10), @Sex2 char(2), @dept2 varchar(10), @title2 varchar(10),
@date_hired2 datetime ,@birthday2 datetime,
@salary2 int,@telephone2 varchar(20)
if exists(select * from employee where emp_no=@emp_no)
begin
select @emp_name2=emp_name, @Sex2=sex, @dept2=dept, @title2=title,
@date_hired2=date_hired,@birthday2=birthday,
@salary2=salary,@telephone2=telephone
from Student where emp_no=@emp_no
if ((@emp_name2=@emp_name)and (@Sex2=@sex)and( @dept2=@dept)and ( @title2=@title)
and (@date_hired2=@date_hired)and (@birthday2=@birthday) and (@salary2=@salary) and (@telephone2=@telephone))
/*有完全相同的数据*/
begin
set @rtn=0
end
else
begin
/*更新数据*/
update employee set emp_name=@emp_name, Sex=@sex, dept=@dept, title=@title2,
date_hired=@date_hired,birthday=@birthday,salary=@salary,telephone=@telephone
where emp_no=@emp_no
set @rtn=2
end
end
else
begin
insert into employee values(@emp_no, @emp_name, @Sex, @dept , @title, @date_hired ,@birthday ,
@salary ,@telephone ,@addr )
set @rtn=1
end
insert employee values('E000145','王大华','e','业务','经理','1976-10-13','1951-08-01',8000,'0218120440','上海市');2、利用存储过程从employee、sales、customer表的连接中返回所有业务员的姓名、客户姓名、销售金额。
create proc infor
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 infor3、创建带一个输入参数的存储过程，实现按员工姓名进行模糊查找，查找员工编号、订单编号、销售金额。
create procedure query @name varchar(10)
as
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
exec query '刘%'4、创建带两个输入参数的存储过程，查找姓“李”并且职称为“职员”的员工的员工编号、订单编号、销售金额。
create proc infer3
as
begin
select emp_name,emp_no,order_no,tot_amt
from employee,sales
where title='职员'and emp_name like '李%'and
emp_no=sale_id
end
go
exec infer35、利用存储过程计算出订单编号为10003的订单的销售金额。（带一输入参数和一输出参数）（提示：sales表中的tot_amt应该等于sale_item表中的同一张订单的不同销售产品的qty*unit_price之和）
create procedure inout(
@order_no char(5),
@p_tot int output
)
as
select @p_tot=sum(sale_item.qty*sale_item.unit_price)
from sales,sale_item
where sales.order_no=@order_no
declare @tot_amt int
exec inout 10003,@tot_amt output
select @tot_amt6、创建一存储过程，根据给出的职称，返回该职称的所有员工的平均工资。（带一输入参数和返回值）
create procedure avg_salary @title char(10)
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))