一、函数
函数分为(1)系统函数,(2)自定义函数。
其中自定义函数又可以分为(1)标量值函数(返回单个值),(2)表值函数(返回查询结果)
数据库技术:SQL Server中函数、存储过程与触发器的用法主要介绍自定义函数的使用。
(1)编写一个函数求该银行的金额总和
create function getsumcardmoney() returns money as begin declare @allmoney money select @allmoney = (select sum(cardmoney) from bankcard) return @allmoney end
函数调用
select dbo.getsumcardmoney()
上述函数没有参数,下面介绍有参数的函数的定义及使用
(2)传入账户编号,返回账户真实姓名
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)传递开始时间和结束时间,返回交易记录(存钱取钱),交易记录中包含 真实姓名,卡号,存钱金额,取钱金额,交易时间。
方案一(逻辑复杂,函数内容除了返回结果的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 --交易时间 ) 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 * from getexchangebytime('2018-6-1','2018-7-1')
方案二(逻辑简单,函数内容直接是一条sql查询语句):
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
函数调用:
select * from getexchangebytime('2018-6-19','2018-6-19')
(4)查询银行卡信息,将银行卡状态1,2,3,4分别转换为汉字“正常,挂失,冻结,注销”,根据银行卡余额显示银行卡等级 30万以下为“普通用户”,30万及以上为"vip用户",分别显示卡号,身份证,姓名,余额,用户等级,银行卡状态。
方案一:直接在sql语句中使用case when
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
方案二:将等级和状态用函数实现
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
函数调用实现查询功能
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 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
二、触发器
触发器分类:(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)) 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 department after delete as delete 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 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
测试触发器:
delete department where departmentid = '001' delete department where departmentid = '002' delete department where departmentid = '003'
我们会发现,当部门下没有员工的部门信息可以成功删除,而部门下有员工的部门并没有被删除。
(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 = 'zjb001' 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), @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
执行存储过程:
exec proc_cunqian '6225125478544587',3000
(3)有输入参数,没有输出参数,但是有返回值的存储过程(返回值必须整数)。
模拟银行卡取钱操作,传入银行卡号,取钱金额,实现取钱操作,取钱成功,返回1,取钱失败返回-1
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
执行存储过程:
declare @returnvalue int exec @returnvalue = proc_quqian '662018092100000002',1000000 print @returnvalue
(4)有输入参数,有输出参数的存储过程
查询出某时间段的银行存取款信息以及存款总金额,取款总金额,传入开始时间,结束时间,显示存取款交易信息的同时,返回存款总金额,取款总金额。
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
执行存储过程:
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位密码
--有输入输出参数(密码作为输入参数也作为输出参数) --密码升级,传入用户名和密码,如果用户名密码正确,并且密码长度<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中函数、存储过程与触发器的文章就介绍到这了。希望对大家的学习有所帮助,也希望大家多多支持<计算机技术网(www.ctvol.com)!!>。
需要了解更多数据库技术:SQL Server中函数、存储过程与触发器的用法,都可以关注数据库技术分享栏目—计算机技术网(www.ctvol.com)!
本文来自网络收集,不代表计算机技术网立场,如涉及侵权请联系管理员删除。
ctvol管理联系方式QQ:251552304
本文章地址:https://www.ctvol.com/dtteaching/1086903.html