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 <表名>;

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

用户自定义函数

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

自定义函数调用

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

在自定义函数中，函数所有者一般默认情况下是dbo；
在自定义函数中，标量值函数一定要加上函数所有者，否则会报错：提示函数非内置函数；

表值函数

表值函数是返回一个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)
)asbegin
    --为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 dbo.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 dbo.getTransCount(‘1027 3526 1536 1135‘) as 次数
go

再次提示：
在自定义函数中，函数所有者一般默认情况下是dbo；
在自定义函数中，标量值函数一定要加上函数所有者，否则会报错：提示函数非内置函数；

自定义函数删除

-- 自定义函数删除语法

DROP FUNCTION [函数所有者.]<函数名称>;

-- 示例、

-- 删除查询交易总额大于1W的开户人个人信息内联表值函数

DROP FUNCTION getCustInfo

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

标签：