金沙棋牌app手机下载

当前位置:金沙棋牌 > 金沙棋牌app手机下载 > 中用户定义函数的使用方法,自定义函数

中用户定义函数的使用方法,自定义函数

来源:http://www.logblo.com 作者:金沙棋牌 时间:2019-11-20 14:30

--用户定义函数的分类:

SQL Server 自定义函数

定义

对于SQL Server来讲,我们声明一个变量的方式是用@变量名,而且相对于编程来讲,SQL Server声明的方式跟我们开了个玩笑,是先变量后面才是类型。对于需要传参跟不需要传参的方式,其实跟我们编程的方式一样。有参数则是如下方式:

CREATE FUNCTION GetSum
(
    @firstNum int,
    @secondNum int
)

如果没有参数,则只要保留括号即可。跟我们理解的函数写法一致。

CREATE FUNCTION GetSum
(
)

/*
1.标量函数
2.表值函数
  2.1内联表值函数  返回单个SELECT语句, 它没有相关的返回变量和函数体
  2.2多语句表值函数  是视图和存储过程的结合 可嵌套

在SQL Server中不仅可以使用系统函数(如:聚合函数,字符串函数,时间日期函数等)还可以根据需要自定义函数。

标量函数

所谓标量函数简单点来讲就是返回的结果只是一个标量,对于我来讲,返回的结果就是一种类型的一个值。
写法如下:

CREATE FUNCTION <Scalar_Function_Name, sysname, FunctionName> 
(
    -- Add the parameters for the function here
    <@Param1, sysname, @p1> <Data_Type_For_Param1, , int>
)
RETURNS <Function_Data_Type, ,int>
AS
BEGIN
    -- Declare the return variable here
    DECLARE <@ResultVar, sysname, @Result> <Function_Data_Type, ,int>

    -- Add the T-SQL statements to compute the return value here
    SELECT <@ResultVar, sysname, @Result> = <@Param1, sysname, @p1>

    -- Return the result of the function
    RETURN <@ResultVar, sysname, @Result>

END

例子:

CREATE FUNCTION GetSum
(
    @firstNum int,
    @secondNum int
)
RETURNS int
AS
BEGIN
    -- Declare the return variable here
    DECLARE @result int

    -- Add the T-SQL statements to compute the return value here
    SELECT @result=@firstNum+@secondNum

    -- Return the result of the function
    RETURN @result

END
GO

*/

自定义函数分为标量值函数和表值函数。

内联表值函数

内联表值函数返回的是表数据,表数据就是Table类型。
写法如下:

CREATE FUNCTION <Inline_Function_Name, sysname, FunctionName> 
(    
    -- Add the parameters for the function here
    <@param1, sysname, @p1> <Data_Type_For_Param1, , int>, 
    <@param2, sysname, @p2> <Data_Type_For_Param2, , char>
)
RETURNS TABLE 
AS
RETURN 
(
    -- Add the SELECT statement with parameter references here
    SELECT 0
)
GO

例子:

CREATE FUNCTION selectTeacherTest
(   
    @Name varchar(20)
)
RETURNS TABLE 
AS
RETURN 
(
    select * from Teacher where Teacher.Name=@Name
)
GO

调用: 必须从返回表里面进行“查询”

select * from selectTeacherTest('刘英')

标量函数

--标量函数
IF EXISTS(SELECT * FROM sysobjects    WHERE name='fun_SeeEverySortAmount')
    DROP FUNCTION fun_SeeEverySortAmount
GO
CREATE FUNCTION fun_SeeEverySortAmount(@class varchar(20))
RETURNS INT
AS
    BEGIN
        DECLARE @Amount int
        SELECT @Amount=Amount FROM CommoditySort C INNER JOIN CommodityInfo I ON C.SortId=I.SortId
        WHERE I.CommodityName=@class
        RETURN @Amount
    END
GO

其中,标量值函数用于返回单个值,而表值函数用于返回一个结果集。

多语句表值函数

多语句表值函数跟内联表值函数都是表值函数,它们返回的结果都是Table类型。多语句表值函数顾名思义,就是可以通过多条语句来创建Table类型的数据。这里不同于内联表值函数,内联表值函数的返回结果是由函数体内的SELECT语句来决定。而多语句表值函数,则是需要指定具体的Table类型的结构。也就是说返回的Table,已经定义好要哪些字段返回。所以它能够支持多条语句的执行来创建Table数据。
写法如下:

-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
CREATE FUNCTION <Table_Function_Name, sysname, FunctionName> 
(
    -- Add the parameters for the function here
    <@param1, sysname, @p1> <data_type_for_param1, , int>, 
    <@param2, sysname, @p2> <data_type_for_param2, , char>
)
RETURNS 
<@Table_Variable_Name, sysname, @Table_Var> TABLE 
(
    -- Add the column definitions for the TABLE variable here
    <Column_1, sysname, c1> <Data_Type_For_Column1, , int>, 
    <Column_2, sysname, c2> <Data_Type_For_Column2, , int>
)
AS
BEGIN
    -- Fill the table variable with the rows for your result set

    RETURN 
END
GO

例子:

-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
ALTER FUNCTION DemoFun
(
)
RETURNS 
@result TABLE 
(
    name nvarchar(20),
    city nvarchar(20),
    age int,
    salary int
)
AS
BEGIN
    -- Fill the table variable with the rows for your result set
    insert into @result(name, city, age, salary)
    select FName,FCity,FAge,FSalary from dbo.T_Person where FSalary>8000
    insert into @result(name, city, age, salary) values
    ('测试','China', 1, 0)
    RETURN 
END
GO

执行:

函数参数

其他

可以看得出,多语句表值函数的返回结果是定义好表结构的虚拟表。这又跟标量函数一样了吧,只不过标量函数是返回一种类型的标量值而已。而且在多语句表值函数里面,你也会发现最后一句是RETURN。告诉执行程序,多语句表值函数已经执行完成。函数体结构跟标量函数的结构一样。对于类型放在变量后面这种方式确实需要好好转换一下观念。

RETURNS 
<@Table_Variable_Name, sysname, @Table_Var> TABLE 
(
    -- Add the column definitions for the TABLE variable here
    <Column_1, sysname, c1> <Data_Type_For_Column1, , int>, 
    <Column_2, sysname, c2> <Data_Type_For_Column2, , int>
)

参考:http://www.cnblogs.com/csdbfans/p/3514538.html

--执行
DECLARE @Amount int
EXEC @Amount= fun_SeeEverySortAmount '苹果iPhone6'
PRINT '苹果iPhone6的库存量为:'+CONVERT(varchar(20),@Amount)

参数可以是常量、表中的某个列、表达式或其他类型的值。在函数中有三种类型的参数。

结果:

1、输入:指必须输入一个值。

图片 1

2、可选值:在执行该参数时,可以选择不输入参数。

内联表值函数

--内联表值函数 返回单个SELECT语句, 它没有相关的返回变量和函数体
IF EXISTS (SELECT * FROM sysobjects WHERE name='fun_inlineSeeEverySortAmount')
    DROP FUNCTION fun_inlineSeeEverySortAmount
GO
CREATE FUNCTION fun_inlineSeeEverySortAmount(@class varchar(20))
RETURNS table
AS
    RETURN
        (
            SELECT Amount FROM CommodityInfo I 
            WHERE I.CommodityName=@class
        )
GO

3、默认值:函数中默认有值存在,调用时可以不指定该值。

执行:

创建标量值函数

--执行 调用内联函数和视图的使用一样
SELECT Amount 数量 FROM fun_inlineSeeEverySortAmount('苹果iPhone6')

语法:

结果:

Create function 函数名(参数)
Returns 返回值数据类型
[with {Encryption | Schemabinding }]
[as]
begin
  SQL语句(必须有return 变量或值)
End

Schemabinding :将函数绑定到它引用的对象上(注:函数一旦绑定,则不能删除、修改,除非删除绑定)

图片 2

例子:

多语句表值函数

drop function dbo.input  --删除函数
go
create function dbo.input --定义函数 架构.方法名
(
@num1 int,   --输入参数
@num2 int = null,  --可选参数
@oper varchar = '+' --默认参数
)
returns int
as
begin
    declare @sum int
    if(@oper='+')
    begin
        set @sum = @num1 + @num2
    end
    else
    begin
        set @sum = 0
    end
    return @sum
end
go

select dbo.input(1,null,default)  --参1必填,参2可选,参3默认

select dbo.input(1,2,default) --输出3

select dbo.input(1,2,'*') --输出0 *没判断
--多语句表值函数  是视图和存储过程的结合 可嵌套
--如果一件商品的销售数量超过3000则为销量品,1000-3000为一般商品,0-1000 为需要促销
IF EXISTS (SELECT * FROM sysobjects WHERE name='fun_MutilateStatementSalesSort')
    DROP FUNCTION fun_MutilateStatementSalesSort
GO
CREATE FUNCTION fun_MutilateStatementSalesSort(@class varchar(20))
RETURNS @salessort table(
            商品名 varchar(20),
            单价 money,
            商品类型 varchar(20),
            销售量     int,
            商品销售类型 varchar(20)
        )
AS
    BEGIN
        INSERT INTO @salessort
        SELECT I.CommodityName,I.InPrice,S.SortName,SUM(O.Amount),CASE
                                                                WHEN SUM(O.Amount)>3000 THEN '销量品'
                                                                WHEN SUM(O.Amount) BETWEEN 1000 AND 3000 THEN '一般商品'
                                                                WHEN SUM(O.Amount) BETWEEN 0 AND 1000 THEN '需要促销'
                                                            END
        FROM CommodityInfo I INNER JOIN CommoditySort S ON I.SortId=S.SortId
                            INNER JOIN OrderInfo O ON O.CommodityId =I.CommodityId
        WHERE I.CommodityName=@class
        GROUP BY I.CommodityName,I.InPrice,S.SortName
        RETURN
    END
GO

自定义函数可以将值放在局部变量中,用set select exec赋值

执行:

declare @number int
select @number = dbo.input(1,2,default)
print @number

declare @set int
set @set = dbo.input(1,2,default)
print @set

declare @exec int
exec @exec = dbo.input 1,2,'+'
print @exec
--执行 它相当于一个表
SELECT* FROM fun_MutilateStatementSalesSort('苹果MD760')
SELECT* FROM fun_MutilateStatementSalesSort('尼康D3300') 
SELECT* FROM fun_MutilateStatementSalesSort('小米平板')

在查询中引用函数

结果:

create table test(
    id int identity(1,1),
    name varchar(10),
    birthDay datetime
)

insert into test values('张三','1998-02-01'),
('李四','1981-10-1'),
('王五','1985-5-2')

select * from test --测试信息

图片 3

图片 4

 

创建函数并执行后

create function dbo.getAge(@birthDay datetime)
returns int
as
begin
declare @age int
set @age = datediff(yy,@birthDay,getdate())
return @age
end

select name as 姓名,dbo.getAge(birthDay) as 年龄 from test

图片 5

注意:标量值函数不可以返回文本(text、ntext)、图像、游标或时间戳类型的数据,并且不能用来修改数据库状态。

在select语句中使用函数可能会带来负面影响,因为每返回一行都会调用函数一次。所以在返回大型数据集时应该格外避免使用复杂的函数。

表值函数

表值函数包含两种类型:内联函数和多语句函数。

内联表值函数仅返回一个结果集,而多语句函数可以在函数体中包含一些控制逻辑。

1、内联表值函数

语法:

create function 函数名(参数)
returns table
[with {Encryption | Schemabinding }]
as
return(一条SQL语句)

例子:

--创建
create function getDetails(@id int)
returns table
as
return(select * from student where id = @id) --执行一条语句后返回

--调用
select * from dbo.getDetails(10)

2、多语句函数

多语句函数可以通过多条语句来创建临时表,具体需要哪些字段,以及符合哪些要求的数据被添加到临时表中等。

语法:

create function 函数名(参数)
returns 表格变量名 table (表格变量定义)
[with {Encryption | Schemabinding }]
as
begin
    SQL语句
end 

例子:

create function dbo.Test()
returns @temp table (
    name varchar(20),
    sex char(2),
    age int
)
as
begin
insert into @temp (name,sex,age) values ('多语句','嘛',18)
insert into @temp (name,sex,age) select name,sex,age from student where age > 18
return 
end

可以看出,多语句函数返回结果是定义好表结构的虚拟表,最后有一个return用来告诉sql多语句已经执行完毕。不写会返回不了。

本文由金沙棋牌发布于金沙棋牌app手机下载,转载请注明出处:中用户定义函数的使用方法,自定义函数

关键词: