函數
函數分為：(1)系統函數 (2)自定義函數
自定義函數又分為：
- 純量值函數/標量值函數(返回單個值)
select 純量值函數 資料表值函數/表值函數(返回查詢結果)
select * from 資料表值函數- 刪除函數：
drop function 函數名
drop function GetRecordByTime
語法：純量值函數(返回單個值)
1
2
3
4
5
6
7
8
9
10
--寫函數
create function GetSumMoney(參數列表) returns 類型
as
begin
declare 變數
select 變數 = (SQL語句)
return 變數
end
--調用函數
select GetSumMoney()
語法：資料表值函數(返回查詢結果)
標準寫法
可以處理複雜的邏輯，函數體除了sql語句之外，還可以有其他邏輯代碼。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
create function GetRecordByTime(@start varchar(30), @end varchar(30)) returns @result table
(
--返回的數據結構
RealName varchar(20), --真實姓名
CardNo varchar(30), --銀行卡號(與銀行卡表形成主外鍵關係)
MoneyInBank money,--存錢金額
MoneyOutBank money,--取錢金額
ExchangeTime smalldatetime--交易時間
)
as
begin
--向結構表裡面添加數據，加入下面SQL語句查詢出來的結果
insert into @result
--放 Step1 的SQL語句
return
end
簡易寫法
這方法有個缺點：函數體內只能有return+sql查詢結果，不能處理複雜的邏輯，如果想要裡面宣告變數，就會報錯。
1
2
3
4
5
6
create function GetRecordByTime(@start varchar(30), @end varchar(30)) returns table
as
--declare @i int --不能處理複雜的邏輯，如果想要裡面宣告變數，就會報錯。
return
--放查詢的SQL語句
go
範例1：純量值函數(無參數)(返回單值)
編寫一個函數求該銀行的金額總和
1
2
3
4
5
6
7
8
9
10
11
12
--(1)編寫一個函數求該銀行的金額總和
--select sum(CardMoney) from BankCard
create function GetSumMoney() returns money
as
begin
declare @sum money
select @sum = (select sum(CardMoney) from BankCard)
return @sum
end
--調用函數
select GetSumMoney()
範例2：純量值函數(有參數)(返回單值)
傳入帳戶編號，返回帳戶真實姓名
1
2
3
4
5
6
7
8
9
10
11
--(2)傳入帳戶編號，返回帳戶真實姓名
--select RealName from AccountInfo where AccountId = ?
create function GetRealNameById(@accid int) returns varchar(30)
as
begin
declare @name varchar(30)
select @name = (select RealName from AccountInfo where AccountId = @accid)
return @name
end
--調用函數
select dbo.GetRealNameById(2)
範例3：資料表值函數(2個參數)(返回查詢結果)
傳遞開始時間和結束時間，返回交易記錄(存錢/取錢)
方案一：返回數據結構
可以處理複雜的邏輯，函數體除了sql語句之外，還可以有其他邏輯代碼。
Steps： 1.先查出交易記錄：三表join
1
2
3
4
5
select RealName 真實姓名, CardExchange.CardNo 卡號, MoneyInBank 存錢金額, MoneyOutBank 取錢金額, ExchangeTime 交易時間
from CardExchange
inner join BankCard on CardExchange.CardNo = BankCard.CardNo
inner join AccountInfo on BankCard.AccountId = AccountInfo.AccountId
where ExchangeTime between ? and ?
2.建立函數
(1)建立函數
(2)向結構表裡面添加數據，加入下面SQL語句查詢出來的結果
(3)放要查詢的SQL語句
(4)return
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
create function GetRecordByTime(@start varchar(30), @end varchar(30)) returns @result table
(
--返回的數據結構
RealName varchar(20), --真實姓名
CardNo varchar(30), --銀行卡號(與銀行卡表形成主外鍵關係)
MoneyInBank money,--存錢金額
MoneyOutBank money,--取錢金額
ExchangeTime smalldatetime--交易時間
)
as
begin
--向結構表裡面添加數據，加入下面SQL語句查詢出來的結果
insert into @result
--放 Step1 的SQL語句
return
end
3.將Step1 的SQL語句問號?改成傳入的參數@start、@end，這兩個變數後面要加字串’ 00:00:00’和’ 23:59:59’
@start +' 00:00:00' and @end + ' 23:59:59'
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
--(3)傳遞開始時間和結束時間，返回交易記錄(存錢/取錢)
--交易記錄中包含：真實姓名、卡號、存錢金額、取錢金額、交易時間
create function GetRecordByTime(@start varchar(30), @end varchar(30))
returns @result table
(
--返回的數據結構
RealName varchar(20), --真實姓名
CardNo varchar(30), --銀行卡號(與銀行卡表形成主外鍵關係)
MoneyInBank money,--存錢金額
MoneyOutBank money,--取錢金額
ExchangeTime smalldatetime--交易時間
)
as
begin
insert into @result
--放SQL語句
select RealName 真實姓名, CardExchange.CardNo 卡號, MoneyInBank 存錢金額, MoneyOutBank 取錢金額, ExchangeTime 交易時間
from CardExchange
inner join BankCard on CardExchange.CardNo = BankCard.CardNo
inner join AccountInfo on BankCard.AccountId = AccountInfo.AccountId
where ExchangeTime between @start +' 00:00:00' and @end + ' 23:59:59'
return
end
go
--調用資料表值函數
select * from GetRecordByTime('2023-5-11','2023-5-12')
方案二：寫什麼就返回什麼
這方法有個缺點：函數體內只能有return+sql查詢結果，不能處理複雜的邏輯，如果想要裡面宣告變數，就會報錯。
(1)直接return table。不寫返回的數據結構 returns @result table
(2)直接return 查詢的SQL語句。所以拿掉insert into @result向結構表裡面添加數據
語法：
1
2
3
4
5
6
create function GetRecordByTime(@start varchar(30), @end varchar(30)) returns table
as
--declare @i int --不能處理複雜的邏輯，如果想要裡面宣告變數，就會報錯。
return
--放查詢的SQL語句
go
實作：
1
2
3
4
5
6
7
8
9
10
11
create function GetRecordByTime(@start varchar(30), @end varchar(30))
returns table
as --return + sql查詢結果
return
--放SQL語句
select RealName 真實姓名, CardExchange.CardNo 卡號, MoneyInBank 存錢金額, MoneyOutBank 取錢金額, ExchangeTime 交易時間
from CardExchange
inner join BankCard on CardExchange.CardNo = BankCard.CardNo
inner join AccountInfo on BankCard.AccountId = AccountInfo.AccountId
where ExchangeTime between @start +' 00:00:00' and @end + ' 23:59:59'
go