• 企业400电话
  • 微网小程序
  • AI电话机器人
  • 电商代运营
  • 全 部 栏 目

    企业400电话 网络优化推广 AI电话机器人 呼叫中心 网站建设 商标✡知产 微网小程序 电商运营 彩铃•短信 增值拓展业务
    Sqlserver 自定义函数 Function使用介绍

    一.FUNCTION:
    在sqlserver2008中有3中自定义函数:标量函数/内联表值函数/多语句表值函数,首先总结下他们语法的异同点: 

    同点:
    1.创建定义是一样的: a, CREATE FUNCTION F_NAME(传入的参数名称 传入参数的类型) b,RETURNS 返回值类型 c,AS 异点:1.标量函数返回的是一个数据类型值,内联表值函数返回的是一个table,而多语句返回的是一个table的变量(类似前面两个的结合);
    2.语法的结构:标量函数和多语句函数都是要有begin,,,end,内联表值函数就没有;
    3.调用:标量函数要写成在dbo,function_name;

    标量函数,利用上篇文章写的数据表,在[T_员工信息]表中查姓名为李异峰员工的工号:

    USE SQL_SYSTEM 
    GO
    CREATE FUNCTION F_GONGHAO(@XINGMING NVARCHAR(5))
    RETURNS INT 
    AS
    BEGIN
    DECLARE @GONGHAO INT
    SET @GONGHAO =(SELECT Y.工号 FROM[T_员工信息] AS Y WHERE Y.姓名 =@XINGMING )
    RETURN @GONGHAO
    END
    GO
    /*上面是标量函数的一个简单的例举,下面就开始调用,注意是在dbo.下*/
    SELECT [姓名] FROM[T_员工信息] WHERE [姓名]='李异峰' /**对比的查询*/
    /*请注意观察,和对比*/
    SELECT [姓名],DBO.F_GONGHAO ('李异峰')AS 工号 FROM[T_员工信息] WHERE [姓名]='李异峰'
    GO
    

    F5下:

    内联表值函数:他返回的可是一个table哦,比如:筛选出2014.2.28号所有员工的打卡记录:

    USE SQL_SYSTEM 
    GO
    CREATE FUNCTION F_DAKAJILU(@RIQI DATE)
    RETURNS TABLE
    AS
    RETURN(
    SELECT* FROM[T_考勤]AS K WHERE K.日期 =@RIQI 
    )
    GO
    /*需要注意的就是他的写法上没有begin+end*/
    /*下面就是将date带入函数中*/
    SELECT*
    FROM [F_DAKAJILU]('2014/02/28')
    GO

    F5:

    多语句表值函数就是,表值函数+内联表值函数综合版:就写个返回当天打卡记录和涉及员工的个人信息:

    USE SQL_SYSTEM 
    GO
    CREATE FUNCTION F_D_DAKAJILU(@RIQI DATE)
    RETURNS @TEMP_TABLE TABLE( /*这里要注意的就是既然是多语句的话,那你就要告诉计算机你要查询的列是哪些*/
    [姓名]NVARCHAR(5) NOT NULL,
    [工号]INT NOT NULL,
    [职位]NVARCHAR(10) NOT NULL,
    [部门]NVARCHAR(5) NOT NULL,
    [是否夜班]NCHAR(1) NOT NULL,
    [日期]DATE NOT NULL,
    [当天上班时间]FLOAT(1)NOT NULL
    ) 
    /*以上就是要告诉计算机你要的一个基本信息列*/
    AS
    BEGIN
    INSERT INTO @TEMP_TABLE /*这句的意思是将下面的查询结果插入到@TEMP_TABLE变量中去*/
    SELECT Y.姓名 ,Y.工号 ,Y.职位 ,Y.部门 ,K.夜班 ,K.日期 ,K.当天上班时间 
    FROM[T_员工信息] AS Y CROSS JOIN [T_考勤] AS K  /*这里我用的是交叉连接*/
    WHERE Y.工号 =K.工号 AND K.日期=@RIQI 
    RETURN 
    END
    GO
    SELECT*
    FROM[F_D_DAKAJILU]('2014/02/28')
    GO

    F5:

    二:CURSOR,当要检索复杂的数据的每条数据的时候就可以用到它,类似于c语言的指针一样,他可以在你的数据表中阅历每条数据和更新。 1.新写个cursor阅历数据表(T_员工信息)的数据过程: 1.1.声明一个只读cursor:

    USE SQL_SYSTEM 
    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
    BEGIN TRANSACTION CURSOR_READ_T_员工信息
    DECLARE CUR_T_员工信息 SCROLL CURSOR FOR
    SELECT Y.姓名 ,Y.工号 ,Y.职位 ,Y.部门 FROM[T_员工信息]AS Y ORDER BY Y.工号 ASC 
    COMMIT TRANSACTION CURSOR_READ_T_员工信息
    GO

    1.2.打开:

    OPEN GLOBAL CUR_T_员工信息
    GO

    1.3.阅历每条数据:

    /*声明4个变量用来接收暂存游标中的数据*/
    DECLARE @XINGMING NVARCHAR(3),@GONGHAO INT,@ZHIWEI NVARCHAR(10),@BUMEN NVARCHAR(8)
    /*利用全局变量求取当前数据表中的数据行数和*/
    PRINT'当前数据表中有'+CAST(@@CURSOR_ROWS AS NVARCHAR(6))+'行数据。'
    /*读取第一条数据并存入暂存变量中*/
    FETCH FIRST FROM[CUR_T_员工信息]INTO @XINGMING,@GONGHAO,@ZHIWEI,@BUMEN
    /*利用@@FETCH_STATUS返回的数值来确定cursor在数据表中读取的情况=0为fetch执行成功*/
    WHILE(@@FETCH_STATUS =0)
    BEGIN
    PRINT'姓名:'+@XINGMING+' 工号:'+CONVERT(VARCHAR(3),@GONGHAO)+' 职位:'+@ZHIWEI+' 部门:'+@BUMEN
    FETCH NEXT FROM[CUR_T_员工信息] INTO @XINGMING,@GONGHAO,@ZHIWEI,@BUMEN
    END
    GO

    F5:

    1.4.用完后就关闭和释放:

    /*关闭*/
    CLOSE GLOBAL CUR_T_员工信息
    /*释放内存*/
    DEALLOCATE GLOBAL CUR_T_员工信息
    GO

    这样的话就实现了读取每条记录的功能,然后就可以用它来更新相关的数据条(所有的工号值+100):

    USE SQL_SYSTEM 
    /*下面声明和打开update——cursor*/
    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
    BEGIN TRANSACTION TR_UPDATE_YG 
    DECLARE CUR_T_YG CURSOR FOR
    SELECT YG.姓名 ,YG.工号 ,YG.职位 ,YG.部门 FROM[T_yuangongxinxi]AS YG FOR 
    UPDATE 
    OPEN GLOBAL CUR_T_YG
    COMMIT TRANSACTION TR_UPDATE_YG

    UPDATE:

    PRINT'当前有'+CONVERT(VARCHAR(3),@@CURSOR_ROWS)+'条数据行。'
    FETCH NEXT FROM[CUR_T_YG]
    WHILE(@@FETCH_STATUS=0)
    BEGIN
    UPDATE[T_yuangongxinxi] 
    SET[工号] =[工号]+100
    WHERE CURRENT OF CUR_T_YG
    FETCH NEXT FROM[CUR_T_YG] 
    END

    SELECT:

    关闭释放:

    CLOSE GLOBAL CUR_T_YG
    DEALLOCATE GLOBAL CUR_T_YG

    三:PROCEDURE,存储过程是利用SQL Server所提供的Transact-SQL语言所编写的程序,同时也能在高级语言上调用其存储过程。 3.1.无参数:说一个调用查询数据表的pro:

    USE SQL_SYSTEM 
    GO
    CREATE PROCEDURE PRO_SELECT_T
    WITH RECOMPILE 
    AS
    SELECT* FROM[T_yuangongxinxi] 
    GO
    EXECUTE:
    EXECUTE PRO_SELECT_T
    

    F5:

    3.1.2.EXECUTE PRO_SELECT_T在这里其实就是一个view,还可以把execute调用结果插入到新表:

    USE SQL_SYSTEM 
    SELECT*
    INTO AA 
    FROM[T_yuangongxinxi] 
    GO
    TRUNCATE TABLE AA --TRUNCATE与DELETE的区别就是TR效率高于DE,而且DE清空时会在日志里面留下恢复记录
    GO
    INSERT INTO AA
    EXECUTE PRO_SELECT_T
    GO

    结果就不截图了, 3.2.带参pro,写个修改后输出修改的信息: 例如公司的员工经常有职位变动的情况,就写个这方面的,给出变动员工的工号就可以修改该员工的职位,然后将修改的操作反馈出来:

    USE SQL_SYSTEM 
    GO
    CREATE PROCEDURE PRO_DAUP_ZHIWEI
    @GONGHAO INT,@ZHIWEI NVARCHAR(10),@RETURNS NVARCHAR(50) OUTPUT
    AS
    BEGIN
    /*获取更新前的职位信息*/
    DECLARE @QIAN_RETURN_ZHIWEI NVARCHAR(10),@XINGMING NVARCHAR(3)
    SELECT @QIAN_RETURN_ZHIWEI =AA.职位 ,@XINGMING =AA.姓名 
    FROM[AA]
    WHERE AA.工号 =@GONGHAO 
    /*更新*/
    UPDATE[AA] 
    SET[职位] =@ZHIWEI WHERE AA.工号 =@GONGHAO 
    SET @RETURNS = '已经成功将工号为:【'+CONVERT(VARCHAR(3),@GONGHAO)+'】,姓名为:【'+@XINGMING+'】,职位【'+
    @QIAN_RETURN_ZHIWEI+'】更新为:【'+@ZHIWEI
    END

    EXECUTE:

    DECLARE @PRINTF NVARCHAR(50);
    EXECUTE PRO_DAUP_ZHIWEI 101,'SQL工程师',@PRINTF OUTPUT 
    SELECT @PRINTF AS '更新消息'
    GO

    F5:

    PRO就说到这里了;
    四:TRIGGER,触发器,类似于一个地雷一样,只要你触犯了他的要求的话,他就开始工作了,也可以利用这个功能去维持或阻挡某些不想发生的错误操作, DDL:如:不许删除某个【AA】表:

    USE SQL_SYSTEM 
    GO
    CREATE TRIGGER DROP_T
    ON DATABASE FOR DROP_TABLE 
    AS
    BEGIN
    ROLLBACK TRANSACTION 
    PRINT'不能删除的,因为我已经添加了触发保护!'
    END

    drop:

    DROP TABLE AA 

    F5:

    DML,是解决对最低层数据的问题: 在这里就存在临时的2个表:deleted 和 inserted 逻辑(概念)表,要搞明白就要知道trigger的工作原理: insert 操作时:

    delete 操作时:

    update 操作时:

    简单举个例子来说下update的操作:也就是重点看inserted和deleted表上:

    USE [SQL_SYSTEM]
    GO
    /****** Object: Trigger [dbo].[UPDATE_T] Script Date: 03/04/2014 16:04:21 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TRIGGER [dbo].[UPDATE_T]
    ON [dbo].[AA] INSTEAD OF UPDATE
    AS
    BEGIN
    INSERT INTO T_UPDATE_HOU
    SELECT* 
    FROM[inserted] 
    /*将更新成的数据插入到【T_UPDATE_HOU】中*/
    INSERT INTO T_UPDATE_QIAN
    SELECT*
    FROM[deleted] 
    /*将更新前的数据插入到【T_UPDATE_QIAN】中*/
    PRINT'更新完毕,更新前的数据已经写入到【T_UPDATE_QIAN】,更新后的数据插入到【T_UPDATE_HOU】。'
    END

    update:

    USE SQL_SYSTEM 
    GO
    UPDATE[AA] 
    SET AA.职位 ='SQL高级工程师'
    WHERE AA.工号=101
    /*以上是个简单的更新操作,用于update触发*/
    SELECT* FROM[T_UPDATE_HOU] --修改成的数据
    SELECT* FROM[T_UPDATE_QIAN] --待修改的数据
    GO

    F5:

    在去年的的时候我写了一个利用trigger对多表连接的view进行更新的操作:

    https://www.jb51.net/article/95473.htm

    您可能感兴趣的文章:
    • SqlServer2000+ 身份证合法校验函数的示例代码
    • SQLServer行列互转实现思路(聚合函数)
    • SQLSERVER分页查询关于使用Top方式和row_number()解析函数的不同
    • SQLServer中Partition By及row_number 函数使用详解
    • SQLServer中merge函数用法详解
    • sqlserver四舍五入使用round函数及cast和convert函数
    • 谈谈sqlserver自定义函数与存储过程的区别
    • SQLServer之常用函数总结详解
    上一篇:SQL Function 自定义函数详解
    下一篇:SQl Function 创建函数实例介绍
  • 相关文章
  • 

    © 2016-2020 巨人网络通讯 版权所有

    《增值电信业务经营许可证》 苏ICP备15040257号-8

    Sqlserver 自定义函数 Function使用介绍 Sqlserver,自定义,函数,Function,