前言
在關(guān)系型數(shù)據(jù)庫(kù)中除了前面幾篇基本的數(shù)據(jù)庫(kù)和數(shù)據(jù)表操作之外靡馁，還提供了可編程性的函數(shù)、存儲(chǔ)過程抄瑟、事務(wù)、觸發(fā)器及游標(biāo)枉疼。
本文介紹的是函數(shù)皮假。
函數(shù)分為兩種：
- 系統(tǒng)函數(shù)
- 用戶自定義函數(shù)
準(zhǔn)備工作
這里以銀行存取款為例說明鞋拟。
1、創(chuàng)建數(shù)據(jù)表
--創(chuàng)建賬戶信息表
create table AccountInfo
(
--賬戶ID
CustID int identity(1,1) primary key,
--帳戶名稱
CustName varchar(20) not null,
--身份證號(hào)
IDCard varchar(18),
--電話
TelePhone varchar(13) not null,
--地址
Address varchar(50) default('地址不詳')
)
go
--創(chuàng)建卡信息表
create table CardInfo
(
--銀行卡卡號(hào)
CardID varchar(19) primary key,
--銀行卡密碼
CardPassWord varchar(6) not null default('888888'),
--身份證號(hào)
CustID int references AccountInfo(CustID),
--存款類型
SaveType varchar(10) not null ,
--開戶日期
OpenDate datetime not null default(getdate()),
--開戶金額
OpenMoney money not null check(OpenMoney>1),
--可用余額
LeftMoney money not null check(LeftMoney>1),
--是否掛失
IsLost varchar(2) not null default('否')
)
go
--交易信息表
create table TransInfo
(
--交易編號(hào)
transID int identity primary key,
--銀行卡卡號(hào)
CardID varchar(19) not null,
--交易類型
TransType varchar(4) not null,
--交易金額
TransMoney money not null,
--交易時(shí)間
TransDate datetime default(getdate())
)
go
/*
1.使用T-SQL語(yǔ)句為交易信息表（TransInfo）的銀行卡卡號(hào)（CardID）字段創(chuàng)建外鍵
*/
--外鍵(銀行卡卡號(hào)CardID)
alter table [dbo].TransInfo with check add constraint [fk_CardInfo_TransInfo] foreign key(CardID)
references [dbo].[CardInfo] (CardID)
go
/*
2.使用T-SQL語(yǔ)句為賬戶信息表（AccountInfo）的銀行卡卡號(hào)（IDCard）字段創(chuàng)建唯一約束
*/
alter table [dbo].[AccountInfo] add unique (IDCard ASC)
go
/*
3.使用T-SQL語(yǔ)句創(chuàng)建約束惹资，使銀行卡信息表（CardInfo）的銀行卡卡號(hào)（CardID）字段值長(zhǎng)度只能為19位
*/
alter table [dbo].[CardInfo] add constraint CK_CardID check(len(CardID) = 19)
go
/*
4.使用T-SQL語(yǔ)句使交易信息表（TransInfo）的交易類型（TransType）字段只能選擇'存款'和'取款'贺纲，
銀行卡信息表(CardInfo)的存款類型（SaveType）字段只能選擇'定期'和'活期'
*/
alter table [dbo].TransInfo add constraint CK_TransType
check(TransType = '存款' or TransType = '取款')
go
alter table [dbo].CardInfo add constraint CK_SaveType
check(SaveType = '定期' or SaveType = '活期')
go
2、插入基本數(shù)據(jù)
--插入三個(gè)賬戶信息
insert into AccountInfo values
('孫悟空','422322001502110017','027-88888888','花果山'),
('唐僧','420322001902140019','027-85368962','大唐'),
('沙和尚','410340001572144714','13295654665','通天河')
insert into CardInfo values
('1027 3526 1536 1135','888888',1,'定期',default,500,500,'否'),
('1029 3326 1536 1235','888888',2,'活期',default,1500,1500,'否'),
('1324 3626 7532 1935','888888',1,'活期',default,4500,4500,'否')
系統(tǒng)函數(shù)
數(shù)據(jù)庫(kù)系統(tǒng)定義的函數(shù)褪测，即內(nèi)置函數(shù)猴誊。
內(nèi)置函數(shù)調(diào)用
SELECT 內(nèi)置函數(shù)名(列名稱) FROM <表名>;
| 函數(shù)列別 | 說明 |
|---|---|
| 聚合函數(shù) | 執(zhí)行的操作是將多個(gè)值合并為一個(gè)值。例如 COUNT侮措、SUM懈叹、MIN 和MAX。 |
| 配置函數(shù) | 是一種標(biāo)量函數(shù)分扎，可返回有關(guān)配置設(shè)置的信息澄成。 |
| 加密函數(shù) | 支持加密、解密笆包、數(shù)字簽名和數(shù)字簽名驗(yàn)證环揽。 |
| 游標(biāo)函數(shù) | 返回有關(guān)游標(biāo)狀態(tài)的信息。 |
| 日期和時(shí)間函數(shù) | 可以更改日期和時(shí)間的值庵佣。 |
| 數(shù)學(xué)函數(shù) | 執(zhí)行三角歉胶、幾何和其他數(shù)字運(yùn)算。 |
| 元數(shù)據(jù)函數(shù) | 返回?cái)?shù)據(jù)庫(kù)和數(shù)據(jù)庫(kù)對(duì)象的屬性信息巴粪。 |
| 排名函數(shù) | 是一種非確定性函數(shù)通今，可以返回分區(qū)中每一行的排名值。 |
| 行集函數(shù) | 返回可在 Transact-SQL 語(yǔ)句中表引用所在位置使用的行集肛根。 |
| 安全函數(shù) | 返回有關(guān)用戶和角色的信息辫塌。 |
| 字符串函數(shù) | 可更改 char、varchar派哲、nchar臼氨、nvarchar、binary 和 varbinary 的值芭届。 |
| 系統(tǒng)函數(shù) | 對(duì)系統(tǒng)級(jí)的各種選項(xiàng)和對(duì)象進(jìn)行操作或報(bào)告储矩。 |
| 系統(tǒng)統(tǒng)計(jì)函數(shù) | 返回有關(guān) SQL Server 性能的信息。 |
| 文本和圖像函數(shù) | 可更改 text 和 image 的值褂乍。 |
具體的函數(shù)如果不清楚請(qǐng)自行搜索持隧，本文著重介紹用戶自定義函數(shù)。
用戶自定義函數(shù)
除了系統(tǒng)提供的函數(shù)逃片，用戶可以根據(jù)自己的需求自定義函數(shù)屡拨；
用戶自定義函數(shù)，顧名思義，就是數(shù)用戶自己定義的函數(shù)呀狼；
用戶自定義函數(shù)分為兩類：表值函數(shù)和標(biāo)量值函數(shù)裂允；
其中，表值函數(shù)也分兩種：內(nèi)聯(lián)表值函數(shù)和多語(yǔ)句表值函數(shù)赠潦。
自定義函數(shù)調(diào)用
SELECT 字段列表/* FROM [函數(shù)所有者.]<函數(shù)名稱>([參數(shù)列表]);
在自定義函數(shù)中叫胖，
函數(shù)所有者一般默認(rèn)情況下是dbo；
在自定義函數(shù)中她奥，標(biāo)量值函數(shù)一定要加上函數(shù)所有者瓮增，否則會(huì)報(bào)錯(cuò)：提示函數(shù)非內(nèi)置函數(shù)；
表值函數(shù)
表值函數(shù)是返回一個(gè)Table類型哩俭，相當(dāng)與一張存儲(chǔ)在內(nèi)存中的一張?zhí)摂M表绷跑。
內(nèi)聯(lián)表值函數(shù)
語(yǔ)法
CREATE FUNCTION [函數(shù)所有者.]<函數(shù)名稱>
(
-- 添加函數(shù)所需的參數(shù)，可以沒有參數(shù)
[<@param1> <參數(shù)類型>]
[,<@param1> <參數(shù)類型>]…
)
RETURNS TABLE
AS
RETURN
(
-- 查詢返回的SQL語(yǔ)句
SELECT查詢語(yǔ)句
)
示例
/*
* 創(chuàng)建內(nèi)聯(lián)表值函數(shù)凡资，查詢交易總額大于1W的開戶人個(gè)人信息
*/
create function getCustInfo()
returns @CustInfo table --返回table類型
(
--賬戶ID
CustID int,
--帳戶名稱
CustName varchar(20) not null,
--身份證號(hào)
IDCard varchar(18),
--電話
TelePhone varchar(13) not null,
--地址
Address varchar(50) default('地址不詳')
)
as
begin
--為table表賦值
insert into @CustInfo
select CustID,CustName,IDCard,TelePhone,Address from AccountInfo
where CustID in (select CustID from CardInfo
where CardID in (select CardID from TransInfo group by CardID,transID,TransType,TransMoney,TransDate having sum(TransMoney)>10000))
return
end
go
-- 調(diào)用內(nèi)聯(lián)表值函數(shù)
select * from getCustInfo()
go
多語(yǔ)句表值函數(shù)
語(yǔ)法
CREATE FUNCTION [函數(shù)所有者.]<函數(shù)名稱>
(
-- 添加函數(shù)所需的參數(shù)砸捏，可以沒有參數(shù)
[<@param1> <參數(shù)類型>]
[,<@param1> <參數(shù)類型>]…
)
RETURNS
<@定義的表名> TABLE
(
-- 添加返回?cái)?shù)據(jù)表的列
<列名1> <數(shù)據(jù)類型1>,
<列名2> <數(shù)據(jù)類型2>,
……
<列名n> <數(shù)據(jù)類型n>]
)
AS
BEGIN
--為<@定義的表名>賦值的SQL語(yǔ)句
RETURN
END
示例
/*
* 創(chuàng)建多語(yǔ)句表值函數(shù)，可以查詢出一個(gè)月內(nèi)有交易記錄的用戶姓名隙赁，聯(lián)系電話垦藏，身份證號(hào)碼，銀行卡卡號(hào)和賬戶余額
*/
create function getCustInfoMonth()
returns @CustInfo table --返回table類型
(
--帳戶名稱
CustName varchar(20) not null,
--電話
TelePhone varchar(13) not null,
--身份證號(hào)
IDCard varchar(18),
--銀行卡卡號(hào)
CardID varchar(19) not null,
--可用余額
LeftMoney money not null check(LeftMoney>1)
)
as
begin
--為table表賦值
insert into @CustInfo
select A.CustName, A.TelePhone, A.IDCard, T.CardID, C.LeftMoney
from AccountInfo as A inner join CardInfo as C on A.CustID = C.CustID
inner join TransInfo as T on C.CardID = T.CardID
where exists(select * from TransInfo
group by CardID,TransDate,transID,TransType,TransMoney
having datediff(MONTH,TransDate,GETDATE())=0)
return
end
go
-- 調(diào)用多語(yǔ)句表值函數(shù)
select * from dbo.getCustInfo()
go
標(biāo)量值函數(shù)
返回一個(gè)標(biāo)量值
語(yǔ)法
CREATE FUNCTION [函數(shù)所有者.]<函數(shù)名稱>
(
-- 添加函數(shù)所需的參數(shù)伞访，可以沒有參數(shù)
[<@param1> <參數(shù)類型>]
[,<@param1> <參數(shù)類型>]…
)
RETURNS <函數(shù)返回?cái)?shù)據(jù)類型>
AS
BEGIN
-- 定義返回?cái)?shù)據(jù)變量
DECLARE @變量名 數(shù)據(jù)類型
-- 通過SQL語(yǔ)句為返回變量賦值
SELECT @變量名 = SQL語(yǔ)句
-- 返回結(jié)果
RETURN @變量名
END
示例
/*
7.創(chuàng)建標(biāo)量值函數(shù)掂骏，根據(jù)用戶傳入的銀行卡卡號(hào)，獲得該卡交易次數(shù)
*/
create function getTransCount
(
@CardID varchar(19) --參數(shù)銀行卡卡號(hào)
)
returns int --返回int類型
as
begin
declare @count int
select @count = count(*) from TransInfo where CardID = @CardID
return @count
end
go
-- 調(diào)用標(biāo)量值函數(shù)
-- `標(biāo)量值函數(shù)`一定要加上`函數(shù)所有者`厚掷，否則會(huì)報(bào)錯(cuò)：提示函數(shù)非內(nèi)置函數(shù)
select dbo.getTransCount('1027 3526 1536 1135') as 次數(shù)
go
再次提示：
在自定義函數(shù)中弟灼，函數(shù)所有者一般默認(rèn)情況下是dbo；
在自定義函數(shù)中冒黑，標(biāo)量值函數(shù)一定要加上函數(shù)所有者田绑，否則會(huì)報(bào)錯(cuò)：提示函數(shù)非內(nèi)置函數(shù)；
自定義函數(shù)刪除
-- 自定義函數(shù)刪除語(yǔ)法
DROP FUNCTION [函數(shù)所有者.]<函數(shù)名稱>;
-- 示例
-- 刪除查詢交易總額大于1W的開戶人個(gè)人信息內(nèi)聯(lián)表值函數(shù)
DROP FUNCTION getCustInfo
函數(shù)部分介紹到這里抡爹，如有疑問掩驱，請(qǐng)留言。
謝謝各位看官的瀏覽冬竟。
本文采用知識(shí)共享署名-相同方式共享 4.0 國(guó)際許可協(xié)議進(jìn)行許可欧穴。
基于簡(jiǎn)書上的作品創(chuàng)作。 可轉(zhuǎn)載诱咏、引用，但需經(jīng)本人同意后署名作者且注明文章出處缴挖，并以相同方式共享袋狞。
知識(shí)共享許可協(xié)議
