七、函式-儲存過程-觸發器

農碼一生發表於2022-04-08
函式儲存過程觸發器

一、函式

函式分為（1）系統函式，（2）自定義函式。

其中自定義函式又可以分為（1）標量值函式（返回單個值），（2）表值函式（返回查詢結果）

本文主要介紹自定義函式的使用。

（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

相關文章