函数分为(1)系统函数,(2)自定义函数。
其中自定义函数又可以分为(1)标量值函数(返回单个值),(2)表值函数(返回查询结果)
感谢主要介绍自定义函数得使用。
(1)编写一个函数求该银行得金额总和
create function GetSumCardMoney()returns money asbegindeclare 等AllMoney moneyselect 等AllMoney = (select SUM(CardMoney) from BankCard)return 等AllMOneyend
函数调用
select dbo.GetSumCardMoney()
上述函数没有参数,下面介绍有参数得函数得定义及使用
(2)传入账户编号,返回账户真实姓名
create function GetNameById(等AccountId int)returns varchar(20)asbegindeclare 等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 --交易时间)asbegininsert into 等ExchangeTableselect AccountInfo.RealName,CardExchange.CardNo,CardExchange.MoneyInBank,CardExchange.MoneyOutBank,CardExchange.ExchangeTime from CardExchangeleft join BankCard on CardExchange.CardNo = BankCard.CardNoleft join AccountInfo on BankCard.AccountId = AccountInfo.AccountIdwhere 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 tableasreturnselect AccountInfo.RealName,CardExchange.CardNo,CardExchange.MoneyInBank,CardExchange.MoneyOutBank,CardExchange.ExchangeTime from CardExchangeleft join BankCard on CardExchange.CardNo = BankCard.CardNoleft join AccountInfo on BankCard.AccountId = AccountInfo.AccountIdwhere 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 余额,casewhen CardMoney < 300000 then '普通用户'else 'VIP用户' end 用户等级,casewhen 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)asbegindeclare 等result varchar(10)if 等myMoney < 3000 set 等result = '普通用户'elseset 等result = 'VIP用户'return 等resultendgocreate function GetStatusByNumber(等myNum int)returns varchar(10)asbegindeclare 等result varchar(10)if 等myNum = 1set 等result = '正常'else if 等myNum = 2set 等result = '挂失'else if 等myNum = 3set 等result = '冻结'else if 等myNum = 4set 等result = '注销'elseset 等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 intasbegindeclare 等age intset 等age = year(getdate()) - year(等birth)if month(getdate()) < month(等birth)set 等age = 等age - 1if month(getdate()) = month(等birth) and day(getdate()) < day(等birth)set 等age = 等age -1return 等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)) begindelete 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 updateasupdate 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) outputasif not exists(select * from BankCard where CardNo=等cardno and CardPwd=等pwd)set 等pwd = ''elsebeginif len(等pwd) < 8begindeclare 等len int = 8- len(等pwd)declare 等i int = 1while 等i <= 等lenbeginset 等pwd = 等pwd + cast(FLOOR(RAND()*10) as varchar(1))set 等i = 等i+1endupdate BankCard set CardPwd = 等pwd where CardNo=等cardnoendendgodeclare 等pwd nvarchar(20) = '123456'exec procPwdUpgrade '6225547854125656',等pwd outputselect 等pwd
感谢分享:農碼一生,
原文链接:感谢分享特别cnblogs感谢原创分享者/wml-it/p/16105503.html