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

摘要：函数分为两种系统函数用户自定义函数准备工作这里以银行存取款为例说明。具体的函数如果不清楚请自行搜索，本文着重介绍用户自定义函数。

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

本文介绍的是函数。

函数分为两种：

系统函数

用户自定义函数

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

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 <函数名称>([参数列表]);

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

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

除了系统提供的函数，用户可以根据自己的需求自定义函数；
用户自定义函数，顾名思义，就是数用户自己定义的函数；
用户自定义函数分为两类：表值函数和标量值函数；
其中，表值函数也分两种：内联表值函数和多语句表值函数。

表值函数是返回一个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  
(
    -- 添加函数所需的参数，可以没有参数
    [<@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

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

谢谢各位看官的浏览。