SQL Server中函數(shù)、存儲(chǔ)過程與觸發(fā)器的用法

更新時(shí)間：2022年04月09日 10:51:08 作者：農(nóng)碼一生

這篇文章介紹了SQL Server中函數(shù)、存儲(chǔ)過程與觸發(fā)器的用法，文中通過示例代碼介紹的非常詳細(xì)。對大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下

一、函數(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）傳入賬戶編號，返回賬戶真實(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í)姓名，卡號，存錢金額，取錢金額，交易時(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),    --卡號
	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ù)銀行卡余額顯示銀行卡等級 30萬以下為“普通用戶”,30萬及以上為"VIP用戶"，分別顯示卡號，身份證，姓名，余額，用戶等級，銀行卡狀態(tài)。

方案一：直接在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 '凍結(jié)'
	when CardState = 4 then '注銷'
	else '異常'
end 卡狀態(tài)
from BankCard inner join AccountInfo on BankCard.AccountId = AccountInfo.AccountId

方案二：將等級和狀態(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 卡號,AccountCode 身份證,RealName 姓名,CardMoney 余額,
dbo.GetGradeByMoney(CardMoney) 賬戶等級,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)編號
	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), --部門編號,外鍵,與部門表關(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í)候，該員工的部門編號如果在部門表中找不到，則自動(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è)部門編號之后，將該部門下所有員工的部門編號同步進(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)不但部門信息表中的部門編號進(jìn)行了修改，員工信息表中部門編號為001的信息也被一起修改了。

三、存儲(chǔ)過程

存儲(chǔ)過程（Procedure）是SQL語句和流程控制語句的預(yù)編譯集合。

（1）沒有輸入?yún)?shù)，沒有輸出參數(shù)的存儲(chǔ)過程。

定義存儲(chǔ)過程實(shí)現(xiàn)查詢出賬戶余額最低的銀行卡賬戶信息,顯示銀行卡號，姓名，賬戶余額

--方案一
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

--方案二：（余額最低，有多個(gè)人則顯示結(jié)果是多個(gè)）
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

執(zhí)行存儲(chǔ)過程：

exec proc_MinMoneyCard

（2）有輸入?yún)?shù)，沒有輸出參數(shù)的存儲(chǔ)過程

模擬銀行卡存錢操作，傳入銀行卡號，存錢金額，實(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ù)）。

模擬銀行卡取錢操作，傳入銀行卡號，取錢金額，實(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ǔ)過程

密碼升級，傳入用戶名和密碼，如果用戶名密碼正確，并且密碼長度<8，自動(dòng)升級成8位密碼

--有輸入輸出參數(shù)（密碼作為輸入?yún)?shù)也作為輸出參數(shù)）
--密碼升級，傳入用戶名和密碼，如果用戶名密碼正確，并且密碼長度<8，自動(dòng)升級成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ā)器的文章就介紹到這了。希望對大家的學(xué)習(xí)有所幫助，也希望大家多多支持腳本之家。

您可能感興趣的文章: