前言

在关系型数据库中除了前面几篇基本的数据库和数据表操作之外，还提供了可编程性的函数、存储过程、事务、触发器及游标。

本文介绍的是函数。

函数分为两种：

系统函数
用户自定义函数

准备工作

这里以银行存取款为例说明。

1、创建数据表

--创建账户信息表

create table AccountInfo

(

	--账户ID

	CustID int identity(1,1) primary key,

	--帐户名称

	CustName varchar(20) not null,

	--身份证号

	IDCard varchar(18),

	--电话

	TelePhone varchar(13) not null,

	--地址

	Address varchar(50) default('地址不详')

)

go

--创建卡信息表

create table CardInfo

(

	--银行卡卡号

	CardID varchar(19) primary key,

	--银行卡密码

	CardPassWord varchar(6) not null default('888888'),

	--身份证号

	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

(

	--交易编号

	transID int identity primary key,

	--银行卡卡号

	CardID varchar(19) not null,

	--交易类型

	TransType varchar(4) not null,

	--交易金额

	TransMoney money not null,

	--交易时间

	TransDate datetime default(getdate())

)

go

/*

1.使用T-SQL语句为交易信息表（TransInfo）的银行卡卡号（CardID）字段创建外键

*/

--外键(银行卡卡号CardID)

alter table [dbo].TransInfo  with check add  constraint [fk_CardInfo_TransInfo] foreign key(CardID)

references [dbo].[CardInfo] (CardID)

go

/*

2.使用T-SQL语句为账户信息表（AccountInfo）的银行卡卡号（IDCard）字段创建唯一约束

*/

alter table [dbo].[AccountInfo] add unique (IDCard ASC)

go

/*

3.使用T-SQL语句创建约束，使银行卡信息表（CardInfo）的银行卡卡号（CardID）字段值长度只能为19位

*/

alter table [dbo].[CardInfo] add constraint CK_CardID check(len(CardID) = 19)

go

/*

4.使用T-SQL语句使交易信息表（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、插入基本数据

--插入三个账户信息

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,'否')

函数调用

SELECT 字段列表/* FROM <函数名称>([参数列表]);

系统函数

数据库系统定义的函数，即内置函数。

函数列别	说明
聚合函数	执行的操作是将多个值合并为一个值。例如 COUNT、SUM、MIN 和MAX。
配置函数	是一种标量函数，可返回有关配置设置的信息。
加密函数	支持加密、解密、数字签名和数字签名验证。
游标函数	返回有关游标状态的信息。
日期和时间函数	可以更改日期和时间的值。
数学函数	执行三角、几何和其他数字运算。
元数据函数	返回数据库和数据库对象的属性信息。
排名函数	是一种非确定性函数，可以返回分区中每一行的排名值。
行集函数	返回可在 Transact-SQL 语句中表引用所在位置使用的行集。
安全函数	返回有关用户和角色的信息。
字符串函数	可更改 char、varchar、nchar、nvarchar、binary 和 varbinary 的值。
系统函数	对系统级的各种选项和对象进行操作或报告。
系统统计函数	返回有关 SQL Server 性能的信息。
文本和图像函数	可更改 text 和 image 的值。

具体的函数如果不清楚请自行搜索，本文着重介绍用户自定义函数。

用户自定义函数

除了系统提供的函数，用户可以根据自己的需求自定义函数；

用户自定义函数，顾名思义，就是数用户自己定义的函数；

用户自定义函数分为两类：表值函数和标量值函数；

其中，表值函数也分两种：内联表值函数和多语句表值函数。

表值函数

表值函数是返回一个Table类型，相当与一张存储在内存中的一张虚拟表。

内联表值函数

语法

CREATE FUNCTION <函数名称>

(

	-- 添加函数所需的参数，可以没有参数

	[<@param1> <参数类型>]

	[,<@param1> <参数类型>]…

)

RETURNS TABLE

AS

RETURN

(

	-- 查询返回的SQL语句

	SELECT查询语句

)

示例

/*

* 创建内联表值函数，查询交易总额大于1W的开户人个人信息

*/

create function getCustInfo()

returns @CustInfo table  --返回table类型

(

	--账户ID

	CustID int,

	--帐户名称

	CustName varchar(20) not null,

	--身份证号

	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

-- 调用内联表值函数

select * from getCustInfo()

go

多语句表值函数

语法

CREATE FUNCTION <函数名称>

(

	-- 添加函数所需的参数，可以没有参数

	[<@param1> <参数类型>]

	[,<@param1> <参数类型>]…

)

RETURNS

<@定义的表名> TABLE

(

	-- 添加返回数据表的列

	<列名1> <数据类型1>,

	<列名2> <数据类型2>,

	……

	<列名n> <数据类型n>]

)

AS

BEGIN

	--为<@定义的表名>赋值的SQL语句

	RETURN

END

示例

/*

* 创建多语句表值函数，可以查询出一个月内有交易记录的用户姓名，联系电话，身份证号码，银行卡卡号和账户余额

*/

create function getCustInfoMonth()

returns @CustInfo table  --返回table类型

(

	--帐户名称

	CustName varchar(20) not null,

	--电话

	TelePhone varchar(13) not null,

	--身份证号

	IDCard varchar(18),

	--银行卡卡号

	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

-- 调用多语句表值函数

select * from getCustInfo()

go

标量值函数

返回一个标量值

语法

CREATE FUNCTION <FunctionName>

(

	-- 添加函数所需的参数，可以没有参数

	[<@param1> <参数类型>]

	[,<@param1> <参数类型>]…

)

RETURNS <函数返回数据类型>

AS

BEGIN

	-- 定义返回数据变量

	DECLARE @变量名 数据类型

	-- 通过SQL语句为返回变量赋值

	SELECT @变量名 = SQL语句

	-- 返回结果

	RETURN @变量名

END

示例

/*

7.创建标量值函数，根据用户传入的银行卡卡号，获得该卡交易次数

*/

create function getTransCount

(

	@CardID varchar(19)  --参数银行卡卡号

)

returns int  --返回int类型

as

begin

	declare @count int

	select @count = count(*) from TransInfo where CardID = @CardID

	return @count

end

go

-- 调用标量值函数

select getTransCount('1027 3526 1536 1135') as 次数

go

函数部分介绍到这里，如有疑问，请留言。

谢谢各位看官的浏览。

秒客网

关系数据库SQL之可编程性函数（用户自定义函数）

前言

准备工作

函数调用

系统函数

用户自定义函数

表值函数

内联表值函数

语法

示例

多语句表值函数

语法

示例

标量值函数

语法

示例

相关文章