SQL Server自定义函数、存储过程与触发器实战详解

一、自定义函数的编写与调用

SQL Server中的函数主要分为系统函数和自定义函数。自定义函数根据返回值类型不同，可分为标量值函数（返回单个值）和表值函数（返回表格数据）。以下针对几种典型业务场景进行说明。

第1步：创建无参标量函数求和

目的：获取所有银行卡的总金额。

操作：定义返回类型为 money 的函数，使用聚合函数计算总和并返回。

create function GetSumCardMoney()
returns money 
as
begin
	declare @AllMOney money
	select @AllMOney = (select SUM(CardMoney) from BankCard)
	return @AllMOney
end

调用方式：在Select语句中需带上架构名（如dbo）。

select dbo.GetSumCardMoney()

第2步：创建带参标量函数查找姓名

目的：根据传入的账户ID获取对应的真实姓名。

操作：定义输入参数 @AccountId int，通过变量接收查询结果并返回。

create function GetNameById(@AccountId int)
returns varchar(20)
as
begin
	declare @RealName varchar(20)
	select @RealName = (select RealName from AccountInfo where AccountId = @AccountId)
	return @RealName
end

调用方式：

print dbo.GetNameById(2)

第3步：创建表值函数查询交易记录

目的：根据时间段查询复杂的关联交易数据。

方案一（多语句表值函数）：适合逻辑较复杂，包含变量定义或中间处理的场景。

create function GetExchangeByTime(@StartTime varchar(30),@EndTime varchar(30))
returns @ExchangeTable table
(
	RealName varchar(30),
	CardNo varchar(30),
	MoneyInBank money,
	MoneyOutBank money,
	ExchangeTime smalldatetime
)
as
begin
	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'
	return
end

方案二（内联表值函数）：逻辑简单，直接返回一个SELECT语句，性能通常更高且语法更简洁。

create function GetExchangeByTime(@StartTime varchar(30),@EndTime varchar(30))
returns table
as
	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'

调用方式：表值函数类似表使用，无需加 dbo. 前缀即可直接放在 FROM 后。

select * from GetExchangeByTime('2018-6-19','2018-6-19')

第4步：利用函数实现数据状态映射

目的将数字状态码转换为可读文本，并根据余额分级。

操作分别建立 GetGradeByMoney 和 GetStatusByNumber 两个标量函数，内部使用 IF...ELSE 判断。

create function GetGradeByMoney(@myMoney int)
returns varchar(10)
as
begin
	declare @result varchar(10)
	if @myMoney < 300000 
		set @result = '普通用户'
	else
		set @result = 'VIP用户'
	return @result
end

create function GetStatusByNumber(@myNum int)
returns varchar(10)
as
begin
	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 @result
end

查询调用：

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

第5步：计算精确实岁

目的：根据出生日期计算当前实足年龄。

测试数据准备：

create table Emp
(
	EmpId int primary key identity(1,2),
	empName varchar(20),
	empSex varchar(4),   
	empBirth smalldatetime 
)

函数逻辑：先计算年份差，若本月小于出生月，或本月等于出生月但本日小于出生日，则年龄减1。

create function GetAgeByBirth(@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.GetAgeByBirth(empBirth) 年龄 from Emp

二、触发器的逻辑与控制

触发器是一种特殊的存储过程，它在特定事件发生时自动执行。主要类型包括：

• After 触发器：在完成INSERT、UPDATE或DELETE操作之后执行。
• Instead of 触发器：取代原本的操作执行，原操作不会发生，除非在触发器内部显式执行。

以下案例基于 Department （部门表）和 People （人员表）进行测试。

第1步：After Insert - 自动补全缺失部门

需求：插入员工时，若其所属部门不存在于部门表，则自动添加该部门，名称为“新部门”。

操作：利用 inserted 临时表检查是否存在。

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

第2步：After Delete - 级联删除员工

需求：删除部门时，同时删除该部门下的所有员工。

操作：利用 deleted 临时表找到被删部门ID并清理人员表。

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

第3步：Instead of Delete - 条件性删除

需求：仅当部门下没有员工时才允许删除该部门。

操作：使用 Instead of 拦截删除动作，判断 deleted 表中的ID在人员表中是否存在。

drop trigger tri_DeleteDept --若已存在需先删除
create trigger tri_DeleteDept on Department
Instead of delete
as
  if not exists(select * from People where DepartmentId = (select DepartmentId from deleted))
  begin
	delete from Department where DepartmentId = (select DepartmentId from deleted)
  end

第4步：After Update - 同步更新子表

需求：修改主表（部门）的主键ID时，同步更新外键表（人员）。

注意：在多行更新时，上述写法可能只处理最后一行或产生非预期结果，单行操作适用。

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

三、存储过程的高级应用

存储过程是预编译的SQL集合，支持输入输出参数及返回值，适用于复杂的业务流程。

第1步：无参存储过程查询极值

需求：查询余额最低的银行卡信息。

操作：

--方案一：使用Top 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

--方案二：使用MIN函数（若有多人余额同为最低，会全部显示）
create proc proc_MinMoneyCard_v2
as
    select CardNo 银行卡号,RealName 姓名,CardMoney 余额
    from BankCard inner join AccountInfo on BankCard.AccountId = AccountInfo.AccountId
    where CardMoney=(select MIN(CardMoney) from BankCard)

第2步：含输入参数的写入操作

需求：模拟存钱，传入卡号和金额，更新余额并记录流水。

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

第3步：含返回值的取钱操作

需求：模拟取钱，成功返回1，失败返回-1。

注意：RETURN只能返回整数。

create proc proc_QuQian
@CardNo varchar(30),
@MoneyOutBank money
as
    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 1

调用方法必须通过变量接收返回值：

declare @returnValue int
exec @returnValue = proc_QuQian '662018092100000002',1000000
print @returnValue

第4步：含Output参数的汇总统计

需求：统计某时间段内的存款总额和取款总额，同时返回明细。

操作：声明参数为 OUTPUT，并在过程中赋值。

create proc proc_SelectExchange
    @startTime varchar(20),
    @endTime varchar(20),
    @SumIn money output,
    @SumOut money output
as
select @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'

调用方法：

declare @SumIn money     
declare @SumOut money   
exec proc_SelectExchange '2018-1-1','2018-12-31',@SumIn output,@SumOut output
select @SumIn
select @SumOut

第5步：输入输出参数混合使用（密码升级）

需求：验证密码正确性，若长度不足8位则自动填充随机数至8位并更新数据库。

操作：将 @pwd 既作为输入又标记为 output，内部通过循环拼接随机字符。

create proc procPwdUpgrade
@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

调用演示：

declare @pwd nvarchar(20) = '123456'
exec procPwdUpgrade '6225547854125656',@pwd output
select @pwd

以上就是SQL Server中函数、存储过程与触发器的详细内容，更多关于SQL Server高级查询与流程控制的资料请关注本站其它相关文章！