存储过程的建立与使用
- 格式：docx
- 大小：17.14 KB
- 文档页数：5
存储过程的建立与使用
存储过程的建立与使用
一、实验目的
理解存储过程的概念、作用、建立和调用方法。
二、实验原理
使用CREA TE 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 , @sex ,
@dept ,@title ,@date_hired ,@birthday , @salary ,
@telephone,@addr)
//执行存储过程
exec emp_in 'E10015','大王','女','计算机系','学生','1991-09-05','1998-12-19','5000','189********','珊瑚海'
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))
