一、函数函数分为（1）系统函数，（2）自定义函数。其中自定义函数又可以分为（1）标量值函数（返回单个值），（2）表值函数（返回查询结果）本文主要介绍自定义函数的使用
一、函数
函数分为（1）系统函数，（2）自定义函数。
其中自定义函数又可以分为（1）标量值函数（返回单个值），（2）表值函数（返回查询结果）
本文主要介绍自定义函数的使用。
（1）编写一个函数求该银行的金额总和
create function GetSumCardMoney()returns money asbegin declare @AllMOney money select @AllMOney = (select SUM(CardMoney) from BankCard) return @AllMOneyend
函数调用
select dbo.GetSumCardMoney()
上述函数没有参数，下面介绍有参数的函数的定义及使用
（2）传入账户编号，返回账户真实姓名
create function GetNameById(@AccountId int)returns varchar(20)asbegin declare @RealName varchar(20) select @RealName = (select RealName from AccountInfo where AccountId = @AccountId) return @RealNameend
函数调用
print dbo.GetNameById(2)
（3）传递开始时间和结束时间，返回交易记录（存钱取钱），交易记录中包含 真实姓名，卡号，存钱金额，取钱金额，交易时间。
方案一（逻辑复杂，函数内容除了返回结果的sql语句还有其他内容，例如定义变量等）：
create function GetExchangeByTime(@StartTime varchar(30),@EndTime varchar(30))returns @ExchangeTable table( RealName varchar(30), --真实姓名 CardNo varchar(30), --卡号 MoneyInBank money, --存钱金额 MoneyOutBank money, --取钱金额 ExchangeTime smalldatetime --交易时间)asbegin insert into @ExchangeTable select AccountInfo.RealName,CardExchange.CardNo,CardExchange.MoneyInBank, CardExchange.MoneyOutBank,CardExchange.ExchangeTime from CardExchange left join BankCard on CardExchange.CardNo = BankCard.CardNo left join AccountInfo on BankCard.AccountId = AccountInfo.AccountId where CardExchange.ExchangeTime between @StartTime+' 00:00:00' and @EndTime+' 23:59:59' returnend
函数调用
select * from GetExchangeByTime('2018-6-1','2018-7-1')方案二（逻辑简单，函数内容直接是一条sql查询语句）：
create function GetExchangeByTime(@StartTime varchar(30),@EndTime varchar(30))returns tableas return select AccountInfo.RealName,CardExchange.CardNo,CardExchange.MoneyInBank, CardExchange.MoneyOutBank,CardExchange.ExchangeTime from CardExchange left join BankCard on CardExchange.CardNo = BankCard.CardNo left join AccountInfo on BankCard.AccountId = AccountInfo.AccountId where CardExchange.ExchangeTime between @StartTime+' 00:00:00' and @EndTime+' 23:59:59'go
函数调用：
select * from GetExchangeByTime('2018-6-19','2018-6-19')（4）查询银行卡信息，将银行卡状态1,2,3,4分别转换为汉字“正常,挂失,冻结,注销”，根据银行卡余额显示银行卡等级 30万以下为“普通用户”,30万及以上为"VIP用户"，分别显示卡号，身份证，姓名，余额，用户等级，银行卡状态。
方案一：直接在sql语句中使用case when
select * from AccountInfoselect * from BankCardselect CardNo 卡号,AccountCode 身份证,RealName 姓名,CardMoney 余额,case when CardMoney < 300000 then '普通用户' else 'VIP用户' end 用户等级,case when CardState = 1 then '正常' when CardState = 2 then '挂失' when CardState = 3 then '冻结' when CardState = 4 then '注销' else '异常'end 卡状态from BankCard inner join AccountInfo on BankCard.AccountId = AccountInfo.AccountId
方案二：将等级和状态用函数实现
create function GetGradeByMoney(@myMoney int)returns varchar(10)asbegin declare @result varchar(10) if @myMoney < 3000 set @result = '普通用户' else set @result = 'VIP用户' return @resultendgocreate function GetStatusByNumber(@myNum int)returns varchar(10)asbegin declare @result varchar(10) if @myNum = 1 set @result = '正常' else if @myNum = 2 set @result = '挂失' else if @myNum = 3 set @result = '冻结' else if @myNum = 4 set @result = '注销' else set @result = '异常' return @resultendgo
函数调用实现查询功能
select CardNo 卡号,AccountCode 身份证,RealName 姓名,CardMoney 余额,dbo.GetGradeByMoney(CardMoney) 账户等级,dbo.GetStatusByNumber(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 GetAgeByBirth(@birth smalldatetime)returns intasbegin 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 @ageend
函数调用实现查询
select *,dbo.GetAgeByBirth(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 Peopleafter insertasif not exists(select * from Department where DepartmentId = (select DepartmentId from inserted)) insert into Department(DepartmentId,DepartmentName) values((select DepartmentId from inserted),'新部门')go
测试触发器：
insert People(DepartmentId,PeopleName,PeopleSex,PeoplePhone)values('009','赵云','男','13854587456')我们会发现，当插入赵云这个员工的时候会自动向部门表中添加数据。
（2）触发器实现，删除一个部门的时候将部门下所有员工全部删除。
编写触发器：
create trigger tri_DeleteDept on Departmentafter deleteasdelete from People where People.DepartmentId = (select DepartmentId from deleted)go
测试触发器：
delete Department where DepartmentId = '001'
我们会发现当我们删除此部门的时候，同时会删除该部门下的所有员工
（3）创建一个触发器，删除一个部门的时候判断该部门下是否有员工，有则不删除，没有则删除。
编写触发器：
drop trigger tri_DeleteDept --删除掉之前的触发器，因为当前触发器也叫这个名字create trigger tri_DeleteDept on DepartmentInstead of deleteas if not exists(select * from People where DepartmentId = (select DepartmentId from deleted)) begin delete from Department where DepartmentId = (select DepartmentId from deleted) endgo
测试触发器：
delete Department where DepartmentId = '001'delete Department where DepartmentId = '002'delete Department where DepartmentId = '003'
我们会发现，当部门下没有员工的部门信息可以成功删除，而部门下有员工的部门并没有被删除。
（4）修改一个部门编号之后，将该部门下所有员工的部门编号同步进行修改
编写触发器：
create trigger tri_UpdateDept on Departmentafter updateas update People set DepartmentId = (select DepartmentId from inserted) where DepartmentId = (select DepartmentId from deleted)go
测试触发器：
update Department set DepartmentId = 'zjb001' where DepartmentId='001'
我们会发现不但部门信息表中的部门编号进行了修改，员工信息表中部门编号为001的信息也被一起修改了。
三、存储过程
存储过程（Procedure）是SQL语句和流程控制语句的预编译集合。
（1）没有输入参数，没有输出参数的存储过程。
定义存储过程实现查询出账户余额最低的银行卡账户信息,显示银行卡号，姓名，账户余额
--方案一create proc proc_MinMoneyCardas select top 1 CardNo 银行卡号,RealName 姓名,CardMoney 余额 from BankCard inner join AccountInfo on BankCard.AccountId = AccountInfo.AccountId order by CardMoney ascgo--方案二：（余额最低，有多个人则显示结果是多个）create proc proc_MinMoneyCardas 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),@MoneyInBank moneyas update BankCard set CardMoney = CardMoney + @MoneyInBank where CardNo = @CardNo insert into CardExchange(CardNo,MoneyInBank,MoneyOutBank,ExchangeTime) values(@CardNo,@MoneyInBank,0,GETDATE())--go
执行存储过程：
exec proc_CunQian '6225125478544587',3000
（3）有输入参数，没有输出参数，但是有返回值的存储过程（返回值必须整数）。
模拟银行卡取钱操作，传入银行卡号，取钱金额，实现取钱操作，取钱成功，返回1，取钱失败返回-1
create proc proc_QuQian@CardNo varchar(30),@MoneyOutBank moneyas update BankCard set CardMoney = CardMoney - @MoneyOutBank where CardNo = @CardNo if @@ERROR <> 0 return -1 insert into CardExchange(CardNo,MoneyInBank,MoneyOutBank,ExchangeTime) values(@CardNo,0,@MoneyOutBank,GETDATE()) return 1go
执行存储过程：
declare @returnValue intexec @returnValue = proc_QuQian '662018092100000002',1000000print @returnValue
（4）有输入参数，有输出参数的存储过程
查询出某时间段的银行存取款信息以及存款总金额，取款总金额，传入开始时间，结束时间，显示存取款交易信息的同时，返回存款总金额，取款总金额。
create proc proc_SelectExchange @startTime varchar(20), --开始时间 @endTime varchar(20), --结束时间 @SumIn money output, --存款总金额 @SumOut money output --取款总金额asselect @SumIn = (select SUM(MoneyInBank) from CardExchange where ExchangeTime between @startTime+' 00:00:00' and @endTime+' 23:59:59')select @SumOut = (select SUM(MoneyOutBank) from CardExchange where ExchangeTime between @startTime+' 00:00:00' and @endTime+' 23:59:59')select * from CardExchange where ExchangeTime between @startTime+' 00:00:00' and @endTime+' 23:59:59'go
执行存储过程：
declare @SumIn money --存款总金额declare @SumOut money --取款总金额exec proc_SelectExchange '2018-1-1','2018-12-31',@SumIn output,@SumOut outputselect @SumInselect @SumOut
（5）具有同时输入输出参数的存储过程
密码升级，传入用户名和密码，如果用户名密码正确，并且密码长度<8，自动升级成8位密码
--有输入输出参数（密码作为输入参数也作为输出参数）--密码升级，传入用户名和密码，如果用户名密码正确，并且密码长度<8，自动升级成8位密码select FLOOR(RAND()*10) --0-9之间随机数create proc procPwdUpgrade@cardno nvarchar(20),@pwd nvarchar(20) outputas 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 endgodeclare @pwd nvarchar(20) = '123456'exec procPwdUpgrade '6225547854125656',@pwd outputselect @pwd
到此这篇关于SQL Server中函数、存储过程与触发器的文章就介绍到这了。希望对大家的学习有所帮助，也希望大家多多支持脚本之家。
标签: SQL nbsp Server 函数 存储 过程 触发器 用法
声明：本文内容来源自网络，文字、图片等素材版权属于原作者，平台转载素材出于传递更多信息，文章内容仅供参考与学习，切勿作为商业目的使用。如果侵害了您的合法权益，请您及时与我们联系，我们会在第一时间进行处理！我们尊重版权，也致力于保护版权，站搜网感谢您的分享！
