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

前言

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

本文介绍的是函数。

函数分为两种:

  1. 系统函数

  2. 用户自定义函数

准备工作

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

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 的值。

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

用户自定义函数

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

表值函数

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

内联表值函数

语法

CREATE FUNCTION
(
-- 添加函数所需的参数,可以没有参数
[ ]
[, ]…
)
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
    (
    -- 添加函数所需的参数,可以没有参数
    [ ]
    [, ]…
    )
    RETURNS
    TABLE
    (
    -- 添加返回数据表的列
    ,
    ,
    ……
    ]
    )
    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
(
-- 添加函数所需的参数,可以没有参数
[ ]
[, ]…
)
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, sqlserver, 数据库, 数据库设计

版权声明

本文来自互联网用户投稿,文章观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处。如若内容有涉嫌抄袭侵权/违法违规/事实不符,请点击 举报 进行投诉反馈!

相关文章

立即
投稿

微信公众账号

微信扫一扫加关注

返回
顶部