实验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 infor

3、创建带一个输入参数的存储过程,实现按员工姓名进行模糊查找,查找员工编号、订单编号、销售金额。

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 infer3

5、利用存储过程计算出订单编号为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_amt

6、创建一存储过程,根据给出的职称,返回该职称的所有员工的平均工资。(带一输入参数和返回值)

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))

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏深度学习之tensorflow实战篇

R分词继续,\"不|知道|你在|说|什么\"分词添加新词

* 中文分词常用实现: 单机:R语言+Rwordseg分词包 (建议数据量<1G) 分布式:Hadoop+Smallseg库 词库:Sougou词库,Soug...

3429
来自专栏文渊之博

关于SQLServer 中行列互转的实例说明

这几天在做一个招标系统中审批模块,其中关于报价信息这块,用到了pivot和unpivot来实现数据的行列互转,下面简单介绍一下,实际案例,便于回忆和记录相关的条...

2087
来自专栏GreenLeaves

SQL学习之计算字段的用法与解析

一、计算字段 1、存储在数据库表中的数据一般不是应用程序所需要的格式。大多数情况下,数据表中的数据都需要进行二次处理。下面举几个例子。 (1)、我们需要一个字段...

1965
来自专栏SAP最佳业务实践

想学FM系列(21)-SAP FM模块:派生规则推导策略(4)-派生规则推导步骤-功能模块

4.1.6 功能模块 在推导步骤过程中调用一个功能函数,将源数据字段值传入功能函数的参数值,再把功能函数执行后的结果数据值传出到目标字段。 在选择步骤类型为功能...

63610
来自专栏屈定‘s Blog

由需求而产生的一款db导出excel的工具

程序员最大的毛病可能就是懒,因为懒所以做出了许许多多提高自己工作效率的工具. 起因于我是商业开发,既然是商业项目避免不了各种数据统计,虽然公司有专门的数据平台,...

1675
来自专栏个人随笔

C# 操作 access 数据库

随笔: (1)   命名空间             using System.Data.OleDb; (2)   连接字符串             priv...

2975
来自专栏个人随笔

C# 操作 access 数据库

private staticstring connStr = @"Provider= Microsoft.Ace.OLEDB.12.0;...

45613

视觉搜索和Neo4j的最后一公里

“ 最后一公里 ”是电信行业使用的一个术语,指系统为实际使用该系统的客户提供链接。就图形数据库而言,它指的是终端用户可以从图中提取有价值的信息和洞察力。我们...

1993
来自专栏牛客网

Java面经:去哪儿四轮面试真题分享

废话不多说,前几天参加去哪网面试,面经如下: 去哪网java实习生面总共分为四轮(我也不知道为什么这么多)。 一面(技术面) 1.自我介绍,并分析简历上的项目,...

3726
来自专栏企鹅号快讯

Access/VBA/Excel-左连接-11

系统:Windows 7 软件:Excel 2010/Access 2010 这个系列开展一个新的篇章,重点关注Access数据库 主体框架:以Excel作为操...

2197

扫码关注云+社区

领取腾讯云代金券