服务器之家：专注于VPS、云服务器配置技术及软件下载分享
分类导航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|数据库技术|

服务器之家 - 数据库 - Sql Server - SQL Server中函数、存储过程与触发器的用法

SQL Server中函数、存储过程与触发器的用法

2022-09-08 17:42農碼一生 Sql Server

这篇文章介绍了SQL Server中函数、存储过程与触发器的用法，文中通过示例代码介绍的非常详细。对大家的学习或工作具有一定的参考借鉴价值，需要的朋友可以参考下

一、函数

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

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

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

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

?
1
2
3
4
5
6
7
8
create function GetSumCardMoney()
returns money
as
begin
    declare @AllMOney money
    select @AllMOney = (select SUM(CardMoney) from BankCard)
    return @AllMOney
end

函数调用

?
1
select dbo.GetSumCardMoney()

上述函数没有参数，下面介绍有参数的函数的定义及使用

（2）传入账户编号，返回账户真实姓名

?
1
2
3
4
5
6
7
8
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

函数调用

?
1
print dbo.GetNameById(2)

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

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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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

函数调用

?
1
select * from GetExchangeByTime('2018-6-1','2018-7-1')

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

?
1
2
3
4
5
6
7
8
9
10
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'
go

函数调用：

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

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

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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
select * from AccountInfo
select * from BankCard
select 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

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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
create function GetGradeByMoney(@myMoney int)
returns varchar(10)
as
begin
    declare @result varchar(10)
    if @myMoney < 3000
        set @result = '普通用户'
    else
        set @result = 'VIP用户'
    return @result
end
go
 
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
go

函数调用实现查询功能

?
1
2
3
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岁

测试数据如下：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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')

函数定义：

?
1
2
3
4
5
6
7
8
9
10
11
12
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

函数调用实现查询

?
1
select *,dbo.GetAgeByBirth(empBirth) 年龄 from Emp

二、触发器

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

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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
--部门
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）假设有部门表和员工表，在添加员工的时候，该员工的部门编号如果在部门表中找不到，则自动添加部门信息，部门名称为"新部门"。

编写触发器：

?
1
2
3
4
5
6
7
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),'新部门')
go

测试触发器：

?
1
2
insert People(DepartmentId,PeopleName,PeopleSex,PeoplePhone)
values('009','赵云','男','13854587456')

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

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

编写触发器：

?
1
2
3
4
5
6
create trigger tri_DeleteDept on Department
after delete
as
delete from People where People.DepartmentId =
(select DepartmentId from deleted)
go

测试触发器：

?
1
delete Department where DepartmentId = '001'

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

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

编写触发器：

?
1
2
3
4
5
6
7
8
9
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
go

测试触发器：

?
1
2
3
delete Department where DepartmentId = '001'
delete Department where DepartmentId = '002'
delete Department where DepartmentId = '003'

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

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

编写触发器：

?
1
2
3
4
5
6
create trigger tri_UpdateDept on Department
after update
as
    update People set DepartmentId = (select DepartmentId from inserted)
    where DepartmentId = (select DepartmentId from deleted)
go

测试触发器：

?
1
update Department set DepartmentId = 'zjb001' where DepartmentId='001'

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

三、存储过程

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

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

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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
--方案一
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

执行存储过程：

?
1
exec proc_MinMoneyCard

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

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

?
1
2
3
4
5
6
7
8
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())
--go

执行存储过程：

?
1
exec proc_CunQian '6225125478544587',3000

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

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

?
1
2
3
4
5
6
7
8
9
10
11
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
go

执行存储过程：

?
1
2
3
declare @returnValue int
exec @returnValue = proc_QuQian '662018092100000002',1000000
print @returnValue

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

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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
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'
go

执行存储过程：

?
1
2
3
4
5
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位密码

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
--有输入输出参数（密码作为输入参数也作为输出参数）
--密码升级，传入用户名和密码，如果用户名密码正确，并且密码长度<8，自动升级成8位密码
select FLOOR(RAND()*10) --0-9之间随机数
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
go
declare @pwd nvarchar(20) = '123456'
exec procPwdUpgrade '6225547854125656',@pwd output
select @pwd

到此这篇关于SQL Server中函数、存储过程与触发器的文章就介绍到这了。希望对大家的学习有所帮助，也希望大家多多支持服务器之家。

原文链接：https://www.cnblogs.com/wml-it/p/16105503.html

  • SQL Server
  • 函数
  • 存储过程
  • 触发器

    • 延伸 · 阅读

    精彩推荐
    最近更新
    编辑推荐
    阅读排行
    热门标签
    2005 　 18052 　 3456 　 1355 　 29506 　 9003 　 5120 　 21002 　 5173 　 15138 　 15404 　 功能语句 　 去零函数 　 from 　 字符串聚合 　 备份记录 　 增长情况 　 多行记录 　 逻辑查询 　 实用语句 　 数据一致性 　 自动收缩 　 字段编号 　 约束增强 　 行列转换 　 @@ERROR 　 日志备份 　 分组统计 　 已经存在 　 numeric 　
    © 2019-2022 服务器之家(www.tuohang.net) 版权所有关于我们联系我们版权申明网站地图