SQL SERVER数据库基础（9）函数，触发器，存储过程

转载

LDX1998 2021-09-01 18:34:16

一、函数

函数分为（1）系统函数，（2）自定义函数。

其中自定义函数又可以分为（1）标量值函数（返回单个值），（2）表值函数（返回查询结果）

本文主要介绍自定义函数的使用。

（1）编写一个函数求该银行的金额总和

create function GetSumMoney() returns money
as
begin
  declare @sum money
  select @sum=(select sum(CardMoney)from BankCard)
  return @sum
end

函数调用

select dbo.GetSumMoney()

上述函数没有参数，下面介绍有参数的函数的定义及使用（2）传入账户编号，返回账户真实姓名

create function GetRealNameById(@accid int)returns varchar(30)
as
begin
  declare @name varchar(30)
  select @name=(select RealName from AccountInfo where AccountId=@accid)
  return @name
end

函数调用

select dbo.GetRealNameById(3)

（3）传递开始时间和结束时间，返回交易记录（存钱取钱），交易记录中包含真实姓名，卡号，存钱金额，取钱金额，交易时间。

方案一（逻辑复杂，函数内容除了返回结果的sql语句还有其他内容，例如定义变量等）：

create function GetRecordByTime(@start varchar(30),@end varchar(30)) 
returns @result table
(
    RealName varchar(30),  --真实姓名
    CardNo varchar(30),    --卡号
    MoneyInBank money,     --存钱金额
    MoneyOutBank money,    --取钱金额
    ExchangeTime smalldatetime  --交易时间
)
as
begin
insert into @result
select RealName 姓名, CardExchange.CardNo 卡号,MoneyInBank 存钱金额,MoneyOutBank 取钱金额,ExchangeTime 交易时间
from CardExchange
inner join BankCard on CardExchange.CardNo=BankCard.CardNo
inner join AccountInfo on AccountInfo.AccountId=BankCard.AccountId
where ExchangeTime between @start+' 00:00:00' and @end+' 23:59:59'
return 
end

函数调用

select*from GetRecordByTime('2021-1-1','2021-8-31')

方案二（逻辑简单，函数内容直接是一条sql查询语句）：

create function GetRecordByTime(@start varchar(30),@end varchar(30)) 
returns table
as
	return
	select RealName 姓名, CardExchange.CardNo 卡号,MoneyInBank 存钱金额,MoneyOutBank 取钱金额,ExchangeTime 交易时间
	from CardExchange
	inner join BankCard on CardExchange.CardNo=BankCard.CardNo
	inner join AccountInfo on AccountInfo.AccountId=BankCard.AccountId
	where ExchangeTime between @start+' 00:00:00' and @end+' 23:59:59'
go

函数调用：

select*from GetRecordByTime('2021-1-1','2021-8-31')

（4）查询银行卡信息，将银行卡状态1,2,3,4分别转换为汉字“正常,挂失,冻结,注销”，根据银行卡余额显示银行卡等级 30万以下为“普通用户”,30万及以上为"VIP用户"，分别显示卡号，身份证，姓名，余额，用户等级，银行卡状态。

方案一：直接在sql语句中使用case when

select CardNo 卡号,AccountCode 身份证号,RealName 姓名,CardMoney 余额 ,
case
	when CardMoney>=300000 then 'VIP用户'
	else'普通用户'
end 用户等级,
case  CardState
	when 1 then '正常'
	when 2 then '挂失'
	when 3 then '冻结'
	when 4 then '注销'
	else '异常'
end 银行卡状态
from BankCard
inner join AccountInfo on BankCard.AccountId=AccountInfo.AccountId

方案二：将等级和状态用函数实现

--用户等级函数
create function GetGrade(@cardmoney money)returns varchar(30)
as
begin
  declare @result varchar(30)
  if @cardmoney>=300000
    set @result='VIP用户'
  else
    set @result='普通用户'
  return @result   
end
--银行卡状态函数
create function GetState(@state int)returns varchar(30)
as
begin
  declare @result varchar(30)
  if @state=1
    set @result='正常'
  else if @state=2
    set @result='挂失'
  else if @state=3
    set @result='冻结'
  else if @state=4
    set @result='注销'
  else
    set @result='异常'
  return @result   
end

函数调用实现查询功能

select CardNo 卡号,AccountCode 身份证号,RealName 姓名,CardMoney 余额 ,
dbo.GetGrade(CardMoney) 用户等级,dbo.GetState(CardState) 银行卡状态
from BankCard inner join AccountInfo on BankCard.AccountId=AccountInfo.AccountId

（5）编写函数，根据出生日期求年龄，年龄求实岁，例如：

生日为2000-5-5，当前为2018-5-4，年龄为17岁生日为2000-5-5，当前为2018-5-6，年龄为18岁

测试数据如下：

create table Emp
(
    EmpId int primary key identity(1,2), --自动编号
    empName varchar(20), --姓名
    empSex varchar(4),   --性别
    empBirth smalldatetime --生日
)
insert into Emp(empName,empSex,empBirth) values('刘备','男','2008-5-8')
insert into Emp(empName,empSex,empBirth) values('关羽','男','1998-10-10')
insert into Emp(empName,empSex,empBirth) values('张飞','男','1999-7-5')
insert into Emp(empName,empSex,empBirth) values('赵云','男','2003-12-12')
insert into Emp(empName,empSex,empBirth) values('马超','男','2003-1-5')
insert into Emp(empName,empSex,empBirth) values('黄忠','男','1988-8-4')
insert into Emp(empName,empSex,empBirth) values('魏延','男','1998-5-2')
insert into Emp(empName,empSex,empBirth) values('简雍','男','1992-2-20')
insert into Emp(empName,empSex,empBirth) values('诸葛亮','男','1993-3-1')
insert into Emp(empName,empSex,empBirth) values('徐庶','男','1994-8-5')

函数定义：

create function GetAge(@birth smalldatetime)returns int
as
begin
 declare @age int
 set @age=year(getdate())-year(@birth)
 if month(getdate())<month(@birth)
   set @age=@age-1
 if month(getdate())=month(@birth) and day(getdate())<day(@birth)
   set @age=@age-1
 return @age
end

函数调用实现查询

select*,dbo.GetAge(empBirth) 年龄 from Emp

二、触发器

触发器分类：（1） “Instead of”触发器（2）“After”触发器 “Instead of”触发器：在执行操作之前被执行 “After”触发器：在执行操作之后被执行

触发器中后面的案例中需要用到的表及测试数据如下：

--部门
create table Department
(
    DepartmentId varchar(10) primary key , --主键，自动增长
    DepartmentName nvarchar(50), --部门名称
)
--人员信息
create table People
(
    PeopleId int primary key identity(1,1), --主键,自动增长
    DepartmentId varchar(10), --部门编号,外键,与部门表关联
    PeopleName nvarchar(20), --人员姓名
    PeopleSex nvarchar(2), --人员性别
    PeoplePhone nvarchar(20), --电话，联系方式
)
insert into Department(DepartmentId,DepartmentName)
values('001','总经办')
insert into Department(DepartmentId,DepartmentName)
values('002','市场部')
insert into Department(DepartmentId,DepartmentName)
values('003','人事部')
insert into Department(DepartmentId,DepartmentName)
values('004','财务部')
insert into Department(DepartmentId,DepartmentName)
values('005','软件部')
insert into People(DepartmentId,PeopleName,PeopleSex,PeoplePhone)
values('001','刘备','男','13558785478')
insert into People(DepartmentId,PeopleName,PeopleSex,PeoplePhone)
values('001','关羽','男','13558788785')
insert into People(DepartmentId,PeopleName,PeopleSex,PeoplePhone)
values('002','张飞','男','13698547125')

（1）假设有部门表和员工表，在添加员工的时候，该员工的部门编号如果在部门表中找不到，则自动添加部门信息，部门名称为"新部门"。

编写触发器：

create trigger tri_insertPeople on People after insert
as
  if not exists(select*from Department where DepartmentId=(select DepartmentId from inserted))
    begin
	  insert into Department(DepartmentId,DepartmentName)
	  values((select DepartmentId from inserted),'新部门')
	end

go

测试触发器：

insert People(DepartmentId,PeopleName,PeopleSex,PeoplePhone)
values('006','马超','男','13854587456')

我们会发现，当插入马超这个员工的时候会自动向部门表中添加数据。

（2）触发器实现，删除一个部门的时候将部门下所有员工全部删除。

编写触发器：

create trigger tri_DeleteDept on Department after delete
as
  delete from people where DepartmentId=(select DepartmentId from deleted)
go

测试触发器：

delete from Department where DepartmentId='006'

我们会发现当我们删除此部门的时候，同时会删除该部门下的所有员工

（3）创建一个触发器，删除一个部门的时候判断该部门下是否有员工，有则不删除，没有则删除。

编写触发器：

create trigger tri_DeleteDept on Department instead of delete
as
  if not exists(select*from People where DepartmentId=(select DepartmentId from deleted))
    delete from Department where DepartmentId=(select DepartmentId from deleted)
go

测试触发器：

delete from Department where DepartmentId='001'
delete from Department where DepartmentId='005'

我们会发现，当部门下没有员工的部门信息可以成功删除，而部门下有员工的部门并没有被删除。

（4）修改一个部门编号之后，将该部门下所有员工的部门编号同步进行修改

编写触发器：

create trigger tri_UpdateDept on Department after update
as
  update People set DepartmentId = (select DepartmentId from inserted)
  where DepartmentId =(select DepartmentId from deleted)
go

测试触发器：

update Department set DepartmentId = '005'where DepartmentId ='001'

我们会发现不但部门信息表中的部门编号进行了修改，员工信息表中部门编号为001的信息也被一起修改了。

三、存储过程

存储过程（Procedure）是SQL语句和流程控制语句的预编译集合。

（1）没有输入参数，没有输出参数的存储过程。

定义存储过程实现查询出账户余额最低的银行卡账户信息,显示银行卡号，姓名，账户余额

--方案一
create proc proc_MinMoneyCard
as
    select top 1 CardNo 银行卡号,RealName 姓名,CardMoney 余额
    from BankCard inner join AccountInfo on BankCard.AccountId = AccountInfo.AccountId
    order by CardMoney asc
go

--方案二：（余额最低，有多个人则显示结果是多个）
create proc proc_MinMoneyCard
as
    select CardNo 银行卡号,RealName 姓名,CardMoney 余额
    from BankCard inner join AccountInfo on BankCard.AccountId = AccountInfo.AccountId
    where CardMoney=(select MIN(CardMoney) from BankCard)
go

执行存储过程：

exec proc_MinMoneyCard

（2）有输入参数，没有输出参数的存储过程

模拟银行卡存钱操作，传入银行卡号，存钱金额，实现存钱操作

create proc proc_Cunqian
@CardNo varchar(30),
@Money money
as
  update BankCard set CardMoney=CardMoney+@Money
  where CardNo=@CardNo
  insert into CardExchange(CardNo,MoneyInBank,MoneyOutBank,ExchangeTime)
values(@CardNo,@Money,0,GETDATE())
go

执行存储过程：

exec proc_Cunqian '6225125478544587',3000

（3）有输入参数，没有输出参数，但是有返回值的存储过程（返回值必须整数）。

模拟银行卡取钱操作，传入银行卡号，取钱金额，实现取钱操作，取钱成功，返回1，取钱失败返回-1

create proc proc_Quqian
@CardNo varchar(30),
@Money money
as
  update BankCard set CardMoney=CardMoney-@Money
  where CardNo=@CardNo
  if @@ERROR<>0
    return -1
  insert into CardExchange(CardNo,MoneyInBank,MoneyOutBank,ExchangeTime)
  values(@CardNo,0,@Money,GETDATE())
  return 1
go

执行存储过程：

declare @returnValue int
exec @returnValue =proc_Quqian '6225125478544587',2000
select @returnValue

（4）有输入参数，有输出参数的存储过程

查询出某时间段的银行存取款信息以及存款总金额，取款总金额，传入开始时间，结束时间，显示存取款交易信息的同时，返回存款总金额，取款总金额。

create proc proc_selectExChange
  @start varchar(20),--开始时间
  @end varchar(20),--结束时间
  @sumIn money output,--存款总金额
  @sumOut money output--取款总金额
as
  select @sumIn=(select sum (MoneyInBank)from CardExchange
  where ExchangeTime between @start +' 00:00:00'and @end+' 23:59:59')
  select @sumOut=(select sum (MoneyOutBank)from CardExchange
  where ExchangeTime between @start +' 00:00:00'and @end+' 23:59:59')
  select*from CardExchange
  where ExchangeTime between @start +' 00:00:00'and @end+' 23:59:59'
go

执行存储过程：

declare @sumIn money 
declare @sumOut money 
exec  proc_selectExChange '2021-1-1','2021-12-30',@sumIn output, @sumOut output
select @sumIn 存款总金额
select @sumOut 取款总金额

（5）具有同时输入输出参数的存储过程

密码升级，传入用户名和密码，如果用户名密码正确，并且密码长度<8，自动升级成8位密码

create proc proc_PwdUpgrade
	@CardNo nvarchar(20),
	@pwd nvarchar(20) output
as 
	if not exists(select*from BankCard where CardNo=@CardNo and CardPwd=@pwd)
	  set @pwd=''
	else
	   begin
	     if len(@pwd)<8
		 begin
		   declare @len int=8-len(@pwd)
		   declare @i int=1
		   while @i<=@len
		   begin
		     set @pwd=@pwd+cast(floor(rand()*10)as varchar(1))
			 set @i=@i+1
		   end
		   update BankCard set CardPwd=@pwd where CardNo=@CardNo
		 end
	end
go

执行存储过程：

declare @pwd nvarchar(20)='123456'
exec proc_PwdUpgrade'6225125478544587',@pwd output
select @pwd

上一篇：SQL SERVER数据库基础（8）子查询，事务，索引，视图，游标

下一篇：WinForm基础（7）ADO.NET基础

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯