Sql server-自定义函数
定义：一种方法
1.创建函数，求该银行的金额总和--（没有参数，返回标量值）
go
create function getsumcardbalance()
return money
as
begin
declare @sumcardbalance money
set @sumcardbalance =（select sum(cardbalance) from bankcard）
return @sumcardbalance
end
2.调用验证
select dbo.getsumcardbalance() 银行金额总和
3.删除函数
drop function getsumcardbalance
4.创建函数：传入账户编号（假设accid=1）,返回账户真实姓名
go
create function getrealname(@accid int)
returns nvarchar(30)
as
begin
declare @realname nvarchar（30）
set @realname =(select accname from accountinfo where accid=@accid)
return @realname
end
go
调用函数
select dbo.getrealname(1)
删除函数
drop function getrealname
验证是否删除成功
select dbo.getrealname(1)
创建函数：传递开始时间和结束时间，返回交易记录（存钱取钱），交易记录中包含真实姓名，卡号，存钱金额，取钱金额，交易时间，用中文标识别名
go
create function getcardchange(@start datetime, @end datetime)
returns @result table
{
真实姓名 nvarchar(30),
卡号 varchar(30),
存钱金额 money,
取钱金额 money,
交易时间 datetime
}
as
begin
insert into @result
select accname,bankcard.cardno,moneyin,moneyout,cextime
from bankcard inner join accountinfo
on bankcard ,accid = accountinfo.accid
inner join cardexchange
on bankcard.cardno = cardexchange.cardno
where cextime between @start and @end
return
end
go
调用函数
select * from cardexchange
select * from dbo.getcardchange('2022-11-25','2022-12-25')
--总结： datetime数据类型，如果值输入日期date部分，而不输入time部分，则默认time=00:00:00，
如果需要明确时间范围，需要明确time值。
删除函数
drop function getcardchange
不创建函数：查询银行卡信息，将银行卡状态1.2.3.4分别转换为汉字“正常，挂失，冻结，注销”
select *,case cardstate
when 1 then '正常'
when 2 then '挂失'
when 3 then '冻结'
when 4 then '冻结'
end 银行卡状态
from bankcard
不创建函数，根据银行卡余额显示银行卡等级30万以下为“普通用户”，30万及以上为“vip用户”
select *,case
when cardbalance > 300000 then 'VIP用户'
else '普通用户'
end 用户等级
from bankcard
创建函数：分别显示卡号、身份证，姓名，用户等级，银行卡状态,
select cardno 卡号,accident 身份证,accname 姓名,cardbalance 余额,dbo.getuserrank(cardbalance) 用户等级,dbo.getcardstate(cardstate) 银行卡状态 from bankcard inner join accountinfo
on bankcard,accid = accountinfo.accid
编写求用户等级函数
go
create function getuserrank (@cardbalance money)
returns nvarchar(30)
as
begin
declare @userrank nvarchar(30)
set @userrank =(select cardbalance from bankcard)
return @userrank
end
go
编辑求银行卡状态函数
go
create function getcardstate(@cardstate int)
returns nvarchar(30)
as
begin
deciare @state nvarchar(30)
if @cardstate = 1
set @state ='正常'
else if @cardstate = 2
set @state ='挂失'
else if @cardstate = 3
set @state ='冻结'
else if @cardstate = 4
set @state ='注销'
return @state
end
go
编写函数，根据出生日期求实岁年龄，例如
生日为2000-5-5，当前为2020-5-4，年龄为19岁
生日为2000-5-5，当前为2020-5-6，年龄为20岁
分析思路，定义出生日期：@dirthday,@getrealage:
--1.year(getdata())=year(@birthday),@getrealage =0
--2.year(getdate()) >year(@birthday) and month(getdate())>month(@birthday),@getrealage=year(getdate())-year(@birthday)
--3.year(getdate()) >year(@birthday) and month(getdate()) = month(@birthday) and day(getdate()) >= day(@birthday),@getrealage=year(getdate())-year(@birthday)
--4. year(getdate()) > year(@birthday) and month(getdate())=month(@birthday) and day(getdate()) < day(@birthday),@getrealage=year(getdate())-year(@birthday)-1
--5. year(getdate()) > year(@birthday) and month(getdate())<month(@birthday) ,@getrealage=year(getdate())-year(@birthday)-1
go
create function getrealage(@birthday datetime)
returns int
as
begin
declard @age int
if year(getdata())=year(@birthday)
set @age =0
else if year(getdate()) >year(@birthday) and month(getdate())>month(@birthday)
set @age=year(getdate())-year(@birthday)
else if year(getdate()) >year(@birthday) and month(getdate()) = month(@birthday) and day(getdate()) >= day(@birthday)
set @age=year(getdate())-year(@birthday)
else
set @age=year(getdate())-year(@birthday) -1
return @age
end
go
验证：当年出生的‘2022-1-23’
select dbo.getrealage('2022-1-23')
跨年的且当前月份大于出生月份的‘2020-10-23’
select dbo.getrealage('2022-10-23')
跨年的且当前月份等于出生月份的且当前日期=出生日期 “2020-11-27”
select dbo.getrealage('2022-11-27')
跨年的且当前月份等于出生月份的且当前日期》出生日期‘2020-11-21’
select dbo.getrealage('2020-11-23')
跨年的且当前月份等于出生月份的且当前日期<出生日期‘2020-11-28’
select dbo.getrealage('2020-11-28')
跨年的且当前月份小于出生月份‘2020-12-21’
select dbo.getrealage('2020-11-28')
