SQL Server中函數(shù)、存儲(chǔ)過程與觸發(fā)器的用法
一、函數(shù)
函數(shù)分為（1）系統(tǒng)函數(shù)，（2）自定義函數(shù)。
其中自定義函數(shù)又可以分為（1）標(biāo)量值函數(shù)（返回單個(gè)值），（2）表值函數(shù)（返回查詢結(jié)果）
本文主要介紹自定義函數(shù)的使用。
（1）編寫一個(gè)函數(shù)求該銀行的金額總和
create function GetSumCardMoney() returns money as begin declare @AllMOney money select @AllMOney = (select SUM(CardMoney) from BankCard) return @AllMOney end
函數(shù)調(diào)用
select dbo.GetSumCardMoney()
上述函數(shù)沒有參數(shù)，下面介紹有參數(shù)的函數(shù)的定義及使用
（2）傳入賬戶編號(hào)，返回賬戶真實(shí)姓名
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
函數(shù)調(diào)用
print dbo.GetNameById(2)
（3）傳遞開始時(shí)間和結(jié)束時(shí)間，返回交易記錄（存錢取錢），交易記錄中包含 真實(shí)姓名，卡號(hào)，存錢金額，取錢金額，交易時(shí)間。
方案一（邏輯復(fù)雜，函數(shù)內(nèi)容除了返回結(jié)果的sql語句還有其他內(nèi)容，例如定義變量等）：
create function GetExchangeByTime(@StartTime varchar(30),@EndTime varchar(30)) returns @ExchangeTable table ( RealName varchar(30), --真實(shí)姓名 CardNo varchar(30), --卡號(hào) MoneyInBank money, --存錢金額 MoneyOutBank money, --取錢金額 ExchangeTime smalldatetime --交易時(shí)間 ) 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
函數(shù)調(diào)用
select * from GetExchangeByTime('2018-6-1','2018-7-1')方案二（邏輯簡單，函數(shù)內(nèi)容直接是一條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
函數(shù)調(diào)用：
select * from GetExchangeByTime('2018-6-19','2018-6-19')（4）查詢銀行卡信息，將銀行卡狀態(tài)1,2,3,4分別轉(zhuǎn)換為漢字“正常,掛失,凍結(jié),注銷”，根據(jù)銀行卡余額顯示銀行卡等級(jí) 30萬以下為“普通用戶”,30萬及以上為"VIP用戶"，分別顯示卡號(hào)，身份證，姓名，余額，用戶等級(jí)，銀行卡狀態(tài)。
方案一：直接在sql語句中使用case when
select * from AccountInfo select * from BankCard select CardNo 卡號(hào),AccountCode 身份證,RealName 姓名,CardMoney 余額, case when CardMoney < 300000 then '普通用戶' else 'VIP用戶' end 用戶等級(jí), case when CardState = 1 then '正常' when CardState = 2 then '掛失' when CardState = 3 then '凍結(jié)' when CardState = 4 then '注銷' else '異常' end 卡狀態(tài) from BankCard inner join AccountInfo on BankCard.AccountId = AccountInfo.AccountId
方案二：將等級(jí)和狀態(tài)用函數(shù)實(shí)現(xiàn)
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 = '凍結(jié)' else if @myNum = 4 set @result = '注銷' else set @result = '異常' return @result end go
函數(shù)調(diào)用實(shí)現(xiàn)查詢功能
select CardNo 卡號(hào),AccountCode 身份證,RealName 姓名,CardMoney 余額, dbo.GetGradeByMoney(CardMoney) 賬戶等級(jí),dbo.GetStatusByNumber(CardState) 卡狀態(tài) from BankCard inner join AccountInfo on BankCard.AccountId = AccountInfo.AccountId
（5）編寫函數(shù)，根據(jù)出生日期求年齡，年齡求實(shí)歲，例如：
? 生日為2000-5-5，當(dāng)前為2018-5-4，年齡為17歲
? 生日為2000-5-5，當(dāng)前為2018-5-6，年齡為18歲
測試數(shù)據(jù)如下：
create table Emp
(
EmpId int primary key identity(1,2), --自動(dòng)編號(hào)
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('關(guān)羽','男','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')函數(shù)定義：
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
函數(shù)調(diào)用實(shí)現(xiàn)查詢
select *,dbo.GetAgeByBirth(empBirth) 年齡 from Emp
二、觸發(fā)器
觸發(fā)器分類：（1） “Instead of”觸發(fā)器（2）“After”觸發(fā)器
“Instead of”觸發(fā)器：在執(zhí)行操作之前被執(zhí)行
“After”觸發(fā)器：在執(zhí)行操作之后被執(zhí)行
觸發(fā)器中后面的案例中需要用到的表及測試數(shù)據(jù)如下：
--部門
create table Department
(
DepartmentId varchar(10) primary key , --主鍵，自動(dòng)增長
DepartmentName nvarchar(50), --部門名稱
)
--人員信息
create table People
(
PeopleId int primary key identity(1,1), --主鍵,自動(dòng)增長
DepartmentId varchar(10), --部門編號(hào),外鍵,與部門表關(guān)聯(lián)
PeopleName nvarchar(20), --人員姓名
PeopleSex nvarchar(2), --人員性別
PeoplePhone nvarchar(20), --電話，聯(lián)系方式
)
insert into Department(DepartmentId,DepartmentName)
values('001','總經(jīng)辦')
insert into Department(DepartmentId,DepartmentName)
values('002','市場部')
insert into Department(DepartmentId,DepartmentName)
values('003','人事部')
insert into Department(DepartmentId,DepartmentName)
values('004','財(cái)務(wù)部')
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','關(guān)羽','男','13558788785')
insert into People(DepartmentId,PeopleName,PeopleSex,PeoplePhone)
values('002','張飛','男','13698547125')（1）假設(shè)有部門表和員工表，在添加員工的時(shí)候，該員工的部門編號(hào)如果在部門表中找不到，則自動(dòng)添加部門信息，部門名稱為"新部門"。
編寫觸發(fā)器：
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
測試觸發(fā)器：
insert People(DepartmentId,PeopleName,PeopleSex,PeoplePhone)
values('009','趙云','男','13854587456')我們會(huì)發(fā)現(xiàn)，當(dāng)插入趙云這個(gè)員工的時(shí)候會(huì)自動(dòng)向部門表中添加數(shù)據(jù)。
（2）觸發(fā)器實(shí)現(xiàn)，刪除一個(gè)部門的時(shí)候?qū)⒉块T下所有員工全部刪除。
編寫觸發(fā)器：
create trigger tri_DeleteDept on Department after delete as delete from People where People.DepartmentId = (select DepartmentId from deleted) go
測試觸發(fā)器：
delete Department where DepartmentId = '001'
我們會(huì)發(fā)現(xiàn)當(dāng)我們刪除此部門的時(shí)候，同時(shí)會(huì)刪除該部門下的所有員工
（3）創(chuàng)建一個(gè)觸發(fā)器，刪除一個(gè)部門的時(shí)候判斷該部門下是否有員工，有則不刪除，沒有則刪除。
編寫觸發(fā)器：
drop trigger tri_DeleteDept --刪除掉之前的觸發(fā)器，因?yàn)楫?dāng)前觸發(fā)器也叫這個(gè)名字 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
測試觸發(fā)器：
delete Department where DepartmentId = '001' delete Department where DepartmentId = '002' delete Department where DepartmentId = '003'
我們會(huì)發(fā)現(xiàn)，當(dāng)部門下沒有員工的部門信息可以成功刪除，而部門下有員工的部門并沒有被刪除。
（4）修改一個(gè)部門編號(hào)之后，將該部門下所有員工的部門編號(hào)同步進(jìn)行修改
編寫觸發(fā)器：
create trigger tri_UpdateDept on Department after update as update People set DepartmentId = (select DepartmentId from inserted) where DepartmentId = (select DepartmentId from deleted) go
測試觸發(fā)器：
update Department set DepartmentId = 'zjb001' where DepartmentId='001'
我們會(huì)發(fā)現(xiàn)不但部門信息表中的部門編號(hào)進(jìn)行了修改，員工信息表中部門編號(hào)為001的信息也被一起修改了。
三、存儲(chǔ)過程
存儲(chǔ)過程（Procedure）是SQL語句和流程控制語句的預(yù)編譯集合。
（1）沒有輸入?yún)?shù)，沒有輸出參數(shù)的存儲(chǔ)過程。
定義存儲(chǔ)過程實(shí)現(xiàn)查詢出賬戶余額最低的銀行卡賬戶信息,顯示銀行卡號(hào)，姓名，賬戶余額
--方案一
create proc proc_MinMoneyCard
as
select top 1 CardNo 銀行卡號(hào),RealName 姓名,CardMoney 余額
from BankCard inner join AccountInfo on BankCard.AccountId = AccountInfo.AccountId
order by CardMoney asc
go
--方案二：（余額最低，有多個(gè)人則顯示結(jié)果是多個(gè)）
create proc proc_MinMoneyCard
as
select CardNo 銀行卡號(hào),RealName 姓名,CardMoney 余額
from BankCard inner join AccountInfo on BankCard.AccountId = AccountInfo.AccountId
where CardMoney=(select MIN(CardMoney) from BankCard)
go執(zhí)行存儲(chǔ)過程：
exec proc_MinMoneyCard
（2）有輸入?yún)?shù)，沒有輸出參數(shù)的存儲(chǔ)過程
模擬銀行卡存錢操作，傳入銀行卡號(hào)，存錢金額，實(shí)現(xiàn)存錢操作
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執(zhí)行存儲(chǔ)過程：
exec proc_CunQian '6225125478544587',3000
（3）有輸入?yún)?shù)，沒有輸出參數(shù)，但是有返回值的存儲(chǔ)過程（返回值必須整數(shù)）。
模擬銀行卡取錢操作，傳入銀行卡號(hào)，取錢金額，實(shí)現(xiàn)取錢操作，取錢成功，返回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執(zhí)行存儲(chǔ)過程：
declare @returnValue int exec @returnValue = proc_QuQian '662018092100000002',1000000 print @returnValue
（4）有輸入?yún)?shù)，有輸出參數(shù)的存儲(chǔ)過程
查詢出某時(shí)間段的銀行存取款信息以及存款總金額，取款總金額，傳入開始時(shí)間，結(jié)束時(shí)間，顯示存取款交易信息的同時(shí)，返回存款總金額，取款總金額。
create proc proc_SelectExchange
@startTime varchar(20), --開始時(shí)間
@endTime varchar(20), --結(jié)束時(shí)間
@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執(zhí)行存儲(chǔ)過程：
declare @SumIn money --存款總金額 declare @SumOut money --取款總金額 exec proc_SelectExchange '2018-1-1','2018-12-31',@SumIn output,@SumOut output select @SumIn select @SumOut
（5）具有同時(shí)輸入輸出參數(shù)的存儲(chǔ)過程
密碼升級(jí)，傳入用戶名和密碼，如果用戶名密碼正確，并且密碼長度<8，自動(dòng)升級(jí)成8位密碼
--有輸入輸出參數(shù)（密碼作為輸入?yún)?shù)也作為輸出參數(shù)） --密碼升級(jí)，傳入用戶名和密碼，如果用戶名密碼正確，并且密碼長度<8，自動(dòng)升級(jí)成8位密碼 select FLOOR(RAND()*10) --0-9之間隨機(jī)數(shù) 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
到此這篇關(guān)于SQL Server中函數(shù)、存儲(chǔ)過程與觸發(fā)器的文章就介紹到這了。希望對(duì)大家的學(xué)習(xí)有所幫助，也希望大家多多支持腳本之家。
相關(guān)文章
MSSQL自動(dòng)重建出現(xiàn)碎片的索引的方法分享
無論何時(shí)對(duì)基礎(chǔ)數(shù)據(jù)執(zhí)行插入、更新或刪除操作，SQL Server 數(shù)據(jù)庫引擎都會(huì)自動(dòng)維護(hù)索引。隨著時(shí)間的推移，這些修改可能會(huì)導(dǎo)致索引中的信息分散在數(shù)據(jù)庫中（含有碎片）。當(dāng)索引包含的頁中的邏輯排序（基于鍵值）與數(shù)據(jù)文件中的物理排序不匹配時(shí)，就存在碎片。碎片非常多的索引可能會(huì)降低查詢性能，導(dǎo)致應(yīng)用程序響應(yīng)緩慢。2014-06-06
CentOS 7.3上SQL Server vNext CTP 1.2安裝教程
這篇文章主要為大家詳細(xì)介紹了CentOS 7.3上SQL Server vNext CTP 1.2安裝教程，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2017-01-01
sql update 觸發(fā)器 可獲得被update的行的信息
sql update 觸發(fā)器 可獲得被update的行的信息，需要的朋友可以參考下。2010-06-06
SQL Server數(shù)據(jù)庫的修復(fù)SQL語句
使用數(shù)據(jù)庫的過程中，由于斷電或其他原因,有可能導(dǎo)致數(shù)據(jù)庫出現(xiàn)一些小錯(cuò)誤，比如檢索某些表特別慢,查詢不到符合條件的數(shù)據(jù)等。2008-11-11
SqlServer參數(shù)化查詢之where in和like實(shí)現(xiàn)詳解
若有一天你不可避免的需要提高SQL的查詢性能，需要一次性where in 幾百、上千、甚至上萬條數(shù)據(jù)時(shí)，參數(shù)化查詢將是必然進(jìn)行的選擇2012-05-05
sql server中判斷表或臨時(shí)表是否存在的方法
這篇文章主要介紹了sql server中判斷表或臨時(shí)表是否存在的方法,需要的朋友可以參考下2015-11-11
sql server服務(wù)無法啟動(dòng)的解決方案
sql server軟件是一款關(guān)系型數(shù)據(jù)庫管理系統(tǒng),具有使用方便可伸縮性好與相關(guān)軟件集成程度高等優(yōu)點(diǎn),我們?cè)跀?shù)據(jù)編程操作時(shí)經(jīng)常會(huì)使用這款編程軟件,在編程時(shí)系統(tǒng)有時(shí)會(huì)提示sql server服務(wù)無法啟動(dòng),那么大家該如何正常啟動(dòng)呢,下面就由小編來給大家介紹一下如何解決2023-11-11
這篇文章主要介紹了淺談sqlserver下float的不確定性，文中涉及具體實(shí)例代碼，具有一定參考價(jià)值，需要的朋友可以了解下。2017-10-10
數(shù)據(jù)庫性能優(yōu)化三：程序操作優(yōu)化提升性能
程序訪問優(yōu)化也可以認(rèn)為是訪問SQL語句的優(yōu)化，一個(gè)好的SQL語句是可以減少非常多的程序性能的，下面列出常用錯(cuò)誤習(xí)慣，并且提出相應(yīng)的解決方案2013-01-01
SQL Server2019數(shù)據(jù)庫之簡單子查詢的具有方法
這篇文章主要介紹了SQL Server2019數(shù)據(jù)庫之簡單子查詢的具有方法，文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2021-04-04