您好,登錄后才能下訂單哦!
這篇文章主要講解了“SQL Server中函數(shù)、存儲過程與觸發(fā)器怎么用”,文中的講解內(nèi)容簡單清晰,易于學(xué)習(xí)與理解,下面請大家跟著小編的思路慢慢深入,一起來研究和學(xué)習(xí)“SQL Server中函數(shù)、存儲過程與觸發(fā)器怎么用”吧!
函數(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), --自動編號 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ā)器分類:(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 , --主鍵,自動增長 DepartmentName nvarchar(50), --部門名稱 ) --人員信息 create table People ( PeopleId int primary key identity(1,1), --主鍵,自動增長 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í)候,該員工的部門編號如果在部門表中找不到,則自動添加部門信息,部門名稱為"新部門"。
編寫觸發(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')
我們會發(fā)現(xiàn),當(dāng)插入趙云這個(gè)員工的時(shí)候會自動向部門表中添加數(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'
我們會發(fā)現(xiàn)當(dāng)我們刪除此部門的時(shí)候,同時(shí)會刪除該部門下的所有員工
(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'
我們會發(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'
我們會發(fā)現(xiàn)不但部門信息表中的部門編號進(jìn)行了修改,員工信息表中部門編號為001的信息也被一起修改了。
存儲過程(Procedure)是SQL語句和流程控制語句的預(yù)編譯集合。
(1)沒有輸入?yún)?shù),沒有輸出參數(shù)的存儲過程。
定義存儲過程實(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í)行存儲過程:
exec proc_MinMoneyCard
(2)有輸入?yún)?shù),沒有輸出參數(shù)的存儲過程
模擬銀行卡存錢操作,傳入銀行卡號,存錢金額,實(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í)行存儲過程:
exec proc_CunQian '6225125478544587',3000
(3)有輸入?yún)?shù),沒有輸出參數(shù),但是有返回值的存儲過程(返回值必須整數(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í)行存儲過程:
declare @returnValue int exec @returnValue = proc_QuQian '662018092100000002',1000000 print @returnValue
(4)有輸入?yún)?shù),有輸出參數(shù)的存儲過程
查詢出某時(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í)行存儲過程:
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ù)的存儲過程
密碼升級,傳入用戶名和密碼,如果用戶名密碼正確,并且密碼長度<8,自動升級成8位密碼
--有輸入輸出參數(shù)(密碼作為輸入?yún)?shù)也作為輸出參數(shù)) --密碼升級,傳入用戶名和密碼,如果用戶名密碼正確,并且密碼長度<8,自動升級成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
感謝各位的閱讀,以上就是“SQL Server中函數(shù)、存儲過程與觸發(fā)器怎么用”的內(nèi)容了,經(jīng)過本文的學(xué)習(xí)后,相信大家對SQL Server中函數(shù)、存儲過程與觸發(fā)器怎么用這一問題有了更深刻的體會,具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是億速云,小編將為大家推送更多相關(guān)知識點(diǎn)的文章,歡迎關(guān)注!
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。