思享工具箱

2019-2-14SQLserver中function函数和存储过程、触发器、CURSOR

摘要：
CREATEFUNCTIONIsDateoutreturnsnvarcharASBEGINDECLARE@myresultnvarcharIFBEGINSET@Myresult=“过期”endelsebeginset@myresult=“未过期”endRETURENDSELECTdbo。IsDateout--结果已过期SELECTdbo。IsDateout--结果尚未过期2。内联表值函数定义格式：功能：内联表值功能支持在WHERE子句中使用参数CREATEFUNCTION函数_Name--CREATEFUUNCTION函数名RETURNSTABLE--返回表[WITH ENCRYPTION]--如果指定了加密，则对函数进行加密[AS]return具有格式，编写一个实例：CREATEFUNCTION_dbo.func_date_get_TableRETURNSstage--[WITHENCRYPTION]--如果指定了加密，则将函数加密为RETURNSelectstatdate，Value_namefromtest_ceshihithistdate=@date_table--select*fromdbo.func_date_get_table；得到以下结果：3。多语句表值函数定义格式：多语句表表值函数和内联表值函数是表值函数，它们返回的结果类型为table。
Sqlserver 自定义函数 Function使用介绍
2019-2-14SQLserver中function函数和存储过程、触发器、CURSOR第1张2019-2-14SQLserver中function函数和存储过程、触发器、CURSOR第2张
前言：

        在SQL server中不仅可以可以使用系统自带的函数（时间函数、聚合函数、字符串函数等等），还可以根据需要自定义函数。

一、定义：

用户自定义函数的类型：

1、标量值函数（返回一个标量值）

2、表格值函数（内联表格值函数、多语句表值函数，返回一个结果集即返回多个值）

二、三种自定函数的异同点

1、同点：

创建定义相同：         CREATE FUNCTION F_NAME(传入的参数名称    传入参数的类型)                                
                                  RETURNS         返回值类型                                 
                                  AS 

2、异点：

              a.标量值函数返回的是一个数据类型值，
                 内联表值函数返回的是一个table，而多语句表值函数返回的是一个table的变量（类似前面两个的结合）；  
              b.语法的结构：标量值函数和多语句表值函数都是要有begin.........................end，内联表值函数就没有；      
              c.调用：标量函数要写成在dbo.function_name；

三、函数参数

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

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

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

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

四、举例说明：

1、标量值函数定义格式：

CREATE FUNCTION function_name(@parameter_name parameter_data_type) 
--CREATE FUNCTION 函数名称（@参数名 参数的数据类型）
RETURNS date_type   --返回返回值的数据类型
[WITH ENCRYPTION]  --如果指定了 encryption 则函数被加密
[AS]
BEGIN
  function_body --函数体(即 Transact-SQL 语句)
  RETURN  表达式;
END
拿个具体的例子说事：

准备数据：之前有一篇博文写了新建表和插入数据的语句，可参考：

https://mp.csdn.net/postedit/81702708

想要输入时间得到名字的函数

CREATE FUNCTION dbo.func_date_get_name(@date_into varchar(8)) 
--CREATE FUNCTION 函数名称（@参数名 参数的数据类型）
RETURNS varchar(20)   --返回返回值的数据类型
--[WITH ENCRYPTION]  --如果指定了 encryption 则函数被加密
as
BEGIN
    declare @result_name varchar(20)
    select  @result_name = Value_name from test_ceshi where statdate = @date_into   
  RETURN  @result_name
END
 
--select dbo.func_date_get_name('20180808') name;
--select * from test_ceshi;
测试这个自定义函数：func_date_get_name ，即可得到name的结果为：Test1

 

以下是test_ceshi表的全量数据。

  

例子二：这个函数使用了if...else条件语句

--编写一个函数，该函数，可以通过输入借书时间来判断是否到期，当借阅时间大于30天，返回已经过期；否则返回还未到期。
 
CREATE FUNCTION IsDateout(@BDate datetime)
returns nvarchar(20)
AS
BEGIN
    DECLARE @myresult nvarchar(20)
    IF (datediff(day,@BDate,getdate())>30)
    BEGIN
    SET @myresult='已过期'
    end
    else    
    begin
    set @myresult='未到期'
    end
    RETURN (@myresult)
END
 
SELECT dbo.IsDateout(cast('2018-01-01' AS datetime))--结果已过期
SELECT dbo.IsDateout(cast('2018-08-01' AS datetime))--结果未到期
2、内联表格值函数定义格式：

特点：内联表格值函数支持在WHERE子句中使用参数

CREATE FUNCTION function_name(@parameter_name parameter_data_type) 
--CREATE FUNCTION 函数名称（@参数名 参数的数据类型）
RETURNS table    --返回一个表
[WITH ENCRYPTION]  --如果指定了 encryption 则函数被加密
[AS]
RETURN   （一条SQL语句）
有了格式，写个实例：

CREATE FUNCTION dbo.func_date_get_table(@date_into varchar(8)) 
 
RETURNS table
--[WITH ENCRYPTION]  --如果指定了 encryption 则函数被加密
as
  RETURN  select  statdate,Value_name from test_ceshi where statdate = @date_into
 
--select * from dbo.func_date_get_table('20180808') ;
得到如下的结果：



3、多语句表值函数定义格式：

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

CREATE FUNCTION function_name(@parameter_name parameter_data_type) 
--CREATE FUNCTION 函数名称（@参数名 参数的数据类型）
RETURNS @Table_Variable_Name table (Column_1 culumn_type,Column_2 culumn_type)    
--RETURNS @表变量 table 表的定义(即列的定义和约束)
[WITH ENCRYPTION]  --如果指定了 encryption 则函数被加密
[AS]
BEGIN
     函数体(即 Transact-SQL 语句)
 RETURN  
END
因为此类型的自定义函数在实际工作中使用最多，我多举几个例子说明，有些是别人写的内容：

例子1：

CREATE FUNCTION dbo.func_date_get_table_test(@date_into varchar(8)) 
RETURNS @table_test table(date varchar(8),ID varchar(20),name varchar(20))
--[WITH ENCRYPTION]  --如果指定了 encryption 则函数被加密
as
begin
  insert @table_test select  statdate,value_id,Value_name from test_ceshi where statdate = @date_into
   RETURN 
end
 
--select * from dbo.func_date_get_table_test(20180808);
测试函数dbo.func_date_get_table_test()，结果如下图：



例子2：出处--海盗船长  https://www.cnblogs.com/baidawei/p/4732969.html

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
五、修改和删除自定义函数

1、使用alter语句修改自定义函数：

--格式：
alter function 函数名（参数）
returns table
as
return(一条SQL语句)
2、使用drop语句删除：

drop function func_date_get_name
六、注意事项：

在编写自定义函数时需要注意的：

标量函数：

1.      所有的入参前都必须加@

2.      create后的返回，单词是returns，而不是return

3.      returns后面的跟的不是变量，而是返回值的类型，如：int，char等。

4.      在begin/end语句块中，是return。

内联表格值函数：

1.      只能返回table，所以returns后面一定是TABLE

2.      AS后没有begin/end，只有一个return语句来返回特定的记录。

多语句表值函数：

1.      returns后面直接定义返回的表类型，首先是定义表名，表明前面要加@，然后是关键字TABLE，最后是表的结构。

2.      在begin/end语句块中，直接将需要返回的结果insert到returns定义的表中就可以了，在最后return时，会将结果返回。

3.      最后只需要return，return后面不跟任何变量。
用户自定义函数

一.FUNCTION: 在sqlserver2008中有3中自定义函数：标量函数/内联表值函数/多语句表值函数，首先总结下他们语法的异同点： 同点：1.创建定义是一样的：                                        a, CREATE FUNCTION F_NAME(传入的参数名称    传入参数的类型)                                        b,RETURNS         返回值类型                                        c,AS 异点：1.标量函数返回的是一个数据类型值，内联表值函数返回的是一个table，而多语句返回的是一个table的变量（类似前面两个的结合）；            2.语法的结构：标量函数和多语句函数都是要有begin，，，end，内联表值函数就没有；            3.调用：标量函数要写成在dbo,function_name；
标量函数，利用上篇文章写的数据表，在[T_员工信息]表中查姓名为李异峰员工的工号：

复制代码
复制代码
复制代码
 1 USE SQL_SYSTEM 
 2 GO
 3 CREATE FUNCTION F_GONGHAO(@XINGMING NVARCHAR(5))
 4 RETURNS INT 
 5 AS
 6 BEGIN
 7 DECLARE @GONGHAO INT
 8 SET @GONGHAO =(SELECT Y.工号  FROM[T_员工信息] AS Y WHERE Y.姓名 =@XINGMING )
 9 RETURN @GONGHAO
10 END
11 GO
12 /*上面是标量函数的一个简单的例举，下面就开始调用，注意是在dbo.下*/
13 SELECT [姓名] FROM[T_员工信息] WHERE [姓名]='李异峰'  /**对比的查询*/
14 /*请注意观察，和对比*/
15 SELECT [姓名],DBO.F_GONGHAO ('李异峰')AS 工号 FROM[T_员工信息] WHERE [姓名]='李异峰'
16 GO
复制代码
复制代码
复制代码

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

复制代码
复制代码
复制代码
 1 USE SQL_SYSTEM 
 2 GO
 3 CREATE FUNCTION F_DAKAJILU(@RIQI DATE)
 4 RETURNS TABLE
 5 AS
 6 RETURN(
 7 SELECT* FROM[T_考勤]AS K WHERE K.日期 =@RIQI 
 8 )
 9 GO
10 /*需要注意的就是他的写法上没有begin+end*/
11 /*下面就是将date带入函数中*/
12 SELECT*
13 FROM [F_DAKAJILU]('2014/02/28')
14 GO
复制代码
复制代码
复制代码

F5：

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

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

F5：

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

复制代码
复制代码
复制代码
1 USE SQL_SYSTEM 
2 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
3 BEGIN TRANSACTION CURSOR_READ_T_员工信息
4 DECLARE CUR_T_员工信息 SCROLL CURSOR FOR
5 SELECT Y.姓名 ,Y.工号 ,Y.职位 ,Y.部门  FROM[T_员工信息]AS Y ORDER BY Y.工号 ASC 
6 COMMIT TRANSACTION CURSOR_READ_T_员工信息
7 GO
复制代码
复制代码
复制代码

1.2.打开：

1 OPEN GLOBAL CUR_T_员工信息
2 GO

1.3.阅历每条数据：

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

F5:

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

1 /*关闭*/
2 CLOSE GLOBAL CUR_T_员工信息
3 /*释放内存*/
4 DEALLOCATE GLOBAL CUR_T_员工信息
5 GO

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

复制代码
复制代码
复制代码
1 USE SQL_SYSTEM 
2 /*下面声明和打开update——cursor*/
3 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
4 BEGIN TRANSACTION TR_UPDATE_YG 
5 DECLARE CUR_T_YG  CURSOR FOR
6 SELECT YG.姓名 ,YG.工号 ,YG.职位 ,YG.部门  FROM[T_yuangongxinxi]AS YG FOR 
7 UPDATE 
8 OPEN GLOBAL CUR_T_YG
9 COMMIT TRANSACTION TR_UPDATE_YG
复制代码
复制代码
复制代码

UPDATE:

复制代码
复制代码
复制代码
1 PRINT'当前有'+CONVERT(VARCHAR(3),@@CURSOR_ROWS)+'条数据行。'
2 FETCH NEXT FROM[CUR_T_YG]
3 WHILE(@@FETCH_STATUS=0)
4 BEGIN
5 UPDATE[T_yuangongxinxi] 
6 SET[工号] =[工号]+100
7 WHERE CURRENT OF CUR_T_YG
8 FETCH NEXT FROM[CUR_T_YG] 
9 END
复制代码
复制代码
复制代码

SELECT:

关闭释放：

1 CLOSE GLOBAL CUR_T_YG
2 DEALLOCATE GLOBAL CUR_T_YG

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

复制代码
复制代码
复制代码
1 USE SQL_SYSTEM 
2 GO
3 CREATE PROCEDURE PRO_SELECT_T
4 WITH RECOMPILE 
5 AS
6 SELECT* FROM[T_yuangongxinxi] 
7 GO
8  EXECUTE:
9 EXECUTE PRO_SELECT_T
复制代码
复制代码
复制代码

F5：

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

复制代码
复制代码
复制代码
 1 USE SQL_SYSTEM 
 2 SELECT*
 3 INTO AA 
 4 FROM[T_yuangongxinxi] 
 5 GO
 6 TRUNCATE TABLE AA  --TRUNCATE与DELETE的区别就是TR效率高于DE，而且DE清空时会在日志里面留下恢复记录
 7 GO
 8 INSERT INTO AA
 9 EXECUTE PRO_SELECT_T
10 GO
复制代码
复制代码
复制代码

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

复制代码
复制代码
复制代码
 1 USE SQL_SYSTEM 
 2 GO
 3 CREATE PROCEDURE PRO_DAUP_ZHIWEI
 4 @GONGHAO INT,@ZHIWEI NVARCHAR(10),@RETURNS NVARCHAR(50) OUTPUT
 5 AS
 6 BEGIN
 7 /*获取更新前的职位信息*/
 8 DECLARE @QIAN_RETURN_ZHIWEI NVARCHAR(10),@XINGMING NVARCHAR(3)
 9 SELECT @QIAN_RETURN_ZHIWEI =AA.职位 ,@XINGMING =AA.姓名 
10 FROM[AA]
11 WHERE AA.工号 =@GONGHAO 
12 /*更新*/
13 UPDATE[AA] 
14 SET[职位] =@ZHIWEI WHERE AA.工号 =@GONGHAO 
15 SET @RETURNS = '已经成功将工号为：【'+CONVERT(VARCHAR(3),@GONGHAO)+'】,姓名为：【'+@XINGMING+'】,职位【'+
16 @QIAN_RETURN_ZHIWEI+'】更新为：【'+@ZHIWEI
17 END
复制代码
复制代码
复制代码

EXECUTE:

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

F5:

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

复制代码
复制代码
复制代码
1 USE SQL_SYSTEM 
2 GO
3 CREATE TRIGGER DROP_T
4 ON DATABASE FOR DROP_TABLE 
5 AS
6 BEGIN
7 ROLLBACK TRANSACTION 
8 PRINT'不能删除的,因为我已经添加了触发保护！'
9 END
复制代码
复制代码
复制代码

drop：

1 DROP TABLE AA

F5：

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

delete    操作时：

update   操作时：

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

复制代码
复制代码
复制代码
 1 USE [SQL_SYSTEM]
 2 GO
 3 /****** Object:  Trigger [dbo].[UPDATE_T]    Script Date: 03/04/2014 16:04:21 ******/
 4 SET ANSI_NULLS ON
 5 GO
 6 SET QUOTED_IDENTIFIER ON
 7 GO
 8 CREATE TRIGGER [dbo].[UPDATE_T]
 9 ON [dbo].[AA] INSTEAD OF UPDATE
10 AS
11 BEGIN
12 INSERT INTO T_UPDATE_HOU
13 SELECT* 
14 FROM[inserted] 
15 /*将更新成的数据插入到【T_UPDATE_HOU】中*/
16 INSERT INTO  T_UPDATE_QIAN
17 SELECT*
18 FROM[deleted] 
19 /*将更新前的数据插入到【T_UPDATE_QIAN】中*/
20 PRINT'更新完毕，更新前的数据已经写入到【T_UPDATE_QIAN】，更新后的数据插入到【T_UPDATE_HOU】。'
21 END
复制代码
复制代码
复制代码

update：

复制代码
复制代码
复制代码
1 USE SQL_SYSTEM 
2 GO
3 UPDATE[AA] 
4 SET AA.职位 ='SQL高级工程师'
5 WHERE AA.工号=101
6 /*以上是个简单的更新操作，用于update触发*/
7 SELECT* FROM[T_UPDATE_HOU] --修改成的数据
8 SELECT* FROM[T_UPDATE_QIAN] --待修改的数据
9 GO
复制代码
复制代码
复制代码

F5：

利用trigger(触发器)对多表连接的view(视图)进行更新的操作：

其步骤就是：利用update操作触发器产生的2个虚拟表【inserted】用来存储修改的数据信息和【deleted】表，然后将对应的数据更新到对应数据表中的字段信息中；

2019-2-14SQLserver中function函数和存储过程、触发器、CURSOR第81张

1.首先创建3个表：

a.信息表：

复制代码
 1 USE [SQL-LI]
 2 BEGIN TRANSACTION CHUANGJIAN_XINXIN_TAB 
 3 --创建命名为【XINXIN_TAB】的数据表，同时不允许字段为空
 4 CREATE TABLE XINXIN_TAB
 5 (
 6 姓名 NVARCHAR(10) NOT NULL,
 7 性别 NVARCHAR(1) NOT NULL,
 8 学号 INT NOT NULL,
 9 班级 NVARCHAR(20) NOT NULL,
10 出生日期 DATE NOT NULL,
11 CONSTRAINT XUEHAO_YUESU PRIMARY KEY CLUSTERED
12 ([学号]ASC)
13 )
14 COMMIT TRANSACTION CHUANGJIAN_XINXI_TAB
15 GO
复制代码

b.明细分数表：

复制代码
USE [SQL-LI] 
CREATE TABLE FENSHU_TAB
(
[学号] INT NOT NULL,
[语文] DECIMAL(3,1) NOT NULL,
[数学] DECIMAL(3,1) NOT NULL,
[英语] DECIMAL(3,1) NOT NULL
)
GO
复制代码

c.综合分数表：

复制代码
USE [SQL-LI] 
CREATE TABLE ZHONGHE_TAB
(
[姓名] NVARCHAR(10) NOT NULL,
[学号] INT NOT NULL,
[总分] DECIMAL(4,1) NOT NULL,
[平均分] DECIMAL(3,1) NOT NULL)
GO
复制代码

2.1.【信息表】和【明细分数表】插入对应表中的数据：

2019-2-14SQLserver中function函数和存储过程、触发器、CURSOR第88张插入【信息表】和【明细分数表】的数据

【信息表】的数据：

2019-2-14SQLserver中function函数和存储过程、触发器、CURSOR第89张

【明细分数表】的数据：

2019-2-14SQLserver中function函数和存储过程、触发器、CURSOR第90张

2.2.运算记录【综合分数表】的数据：

2019-2-14SQLserver中function函数和存储过程、触发器、CURSOR第91张插入【ZHONGHE_TAB】中的数据

 【综合分数表】的数据：

2019-2-14SQLserver中function函数和存储过程、触发器、CURSOR第92张

3.1.1.创建3个表关联的视图：

复制代码
 1 USE [SQL-LI] 
 2 GO
 3 CREATE VIEW SHITU_FFENSHU_XINXI(姓名,学号,平均分,总分,班级,出生日期)
 4 AS
 5 SELECT TOP 800 X.姓名 ,F.学号 ,Z.平均分 ,Z.总分 ,X.班级 ,X.出生日期 
 6 FROM[DBO].XINXIN_TAB AS X INNER JOIN [DBO].FENSHU_TAB AS F ON X.学号 =F.学号 
 7     INNER JOIN [DBO].ZHONGHE_TAB AS Z ON F.学号 =Z.学号 
 8     ORDER BY F.学号 ASC
 9     GO
10
复制代码

查看创建的视图：

2019-2-14SQLserver中function函数和存储过程、触发器、CURSOR第95张

3.2.1.通过视图修改多个数据表的信息????：

1 UPDATE [SQL-LI].[dbo].[SHITU_FFENSHU_XINXI]
2    SET [姓名] = 'aaaaa', --此字段在【信息表】中
3        [平均分] =111    --此次字段在【分数】中
4  WHERE [学号]=6080
5 GO

结果：

2019-2-14SQLserver中function函数和存储过程、触发器、CURSOR第96张

2019-2-14SQLserver中function函数和存储过程、触发器、CURSOR第97张

下面就写个利用触发器对其多表进行更新的方法：

a.这里就利用instead of 代替触发来代替对各表中的字段内的信息进行修改：

复制代码
USE [SQL-LI] 
GO
CREATE TRIGGER TRIGG_UPDATE --创建一个upda触发器DML
--关联到[SHITU_FFENSHU_XINXI]视图上
ON[DBO].[SHITU_FFENSHU_XINXI]
INSTEAD OF UPDATE  --代替触发器执行UPDATE功能；【但是只能定义一个增删改的INSTEAD OF代替触发】。
AS
--声明接受变量用于存储【inserted】表上的数据
DECLARE @XINGMING NVARCHAR(10),@XUEHAO INT,@AVGFEN DECIMAL(3,1),@ZONGFEN DECIMAL(4,1),
        @BANJI NVARCHAR(20),@CHUSHENGRIQI DATE;
        --筛选【inserted】表中学号最小的一行数据
SELECT @XUEHAO =MIN(学号) FROM[inserted] 
--遍历【inserted】表

WHILE(@XUEHAO IS NOT NULL)
BEGIN
--将【inserted】表中的数据存放到相应的变量中
SELECT @XUEHAO =MIN([学号])FROM[inserted] WHERE [学号]=@XUEHAO  
SELECT @XINGMING=[姓名] FROM[inserted] WHERE[学号] =@XUEHAO 
SELECT @AVGFEN=[平均分]FROM[inserted] WHERE[学号] =@XUEHAO 
SELECT @ZONGFEN=[总分] FROM[inserted] WHERE[学号] =@XUEHAO 
SELECT @BANJI =[班级]FROM[inserted] WHERE[学号] =@XUEHAO 
SELECT @CHUSHENGRIQI=[出生日期]FROM[inserted] WHERE[学号] =@XUEHAO 

--找出视图中的字段对应相应表的字段
/*因为视图中的[姓名]/[班级]/[出生日期]字段是XINXIN_TAB 中的字段，故修改【XINXIN_TAB】中对应的字段
数据之*/
UPDATE[DBO].XINXIN_TAB 
SET [姓名]=@XINGMING  ,[班级]=@BANJI  ,[出生日期]=@CHUSHENGRIQI 
WHERE[学号]=@XUEHAO 

--道理同上
UPDATE[DBO].FENSHU_TAB 
SET[学号]=@XUEHAO 
WHERE[学号]=@XUEHAO 

--道理同上
UPDATE[DBO].ZHONGHE_TAB 
SET[平均分]=@AVGFEN ,[总分]=@ZONGFEN 
WHERE[学号]=@XUEHAO 

--修改完成后就开始筛选【inserted】表中下一条数据记录
SELECT @XUEHAO =MIN([学号])FROM[inserted] WHERE[学号]>@XUEHAO
--然后给while中判断 
END
GO
复制代码

a1.注意的是视图不是数据表没有存放数据，将从【inserted】表中的数据提取后赋给对应数据表内的字段中；

2019-2-14SQLserver中function函数和存储过程、触发器、CURSOR第100张

对象资源管理器中的图示：

2019-2-14SQLserver中function函数和存储过程、触发器、CURSOR第101张

 3.万事俱备，开始通过视图修改多表中的数据（验证）：

a.

复制代码
USE [SQL-LI] 
--查看未修改前的视图数据信息
SELECT* FROM[DBO].SHITU_FFENSHU_XINXI 
GO

UPDATE[DBO].SHITU_FFENSHU_XINXI 
--修改【SHITU_FFENSHU_XINXI】中对应的字段数据
SET[姓名]='liyifeng' ,[平均分]=66.6 ,[总分]=88.8 ,[班级]='计算机SQLServer' ,[出生日期]='2013-05-05' 
--修改筛选
WHERE[学号]=6080 
GO
--查看修改后的视图数据信息
SELECT* FROM[DBO].SHITU_FFENSHU_XINXI 
GO
复制代码

修改前后对比的结果图示：

2019-2-14SQLserver中function函数和存储过程、触发器、CURSOR第104张

 修改后的数据表中的数据：

USE [SQL-LI] 
SELECT* FROM[XINXIN_TAB]  WHERE[学号]=6080
SELECT* FROM[FENSHU_TAB]  WHERE[学号]=6080
SELECT* FROM[ZHONGHE_TAB] WHERE[学号]=6080
GO

2019-2-14SQLserver中function函数和存储过程、触发器、CURSOR第105张

 4.触发器在数据库里面就像颗炸弹一样，只要满足气要求就会被触发，就会对数据库里面的数据进行触发修改，所以不需要室就尽量将其关闭掉，用的时候就将其开启：

关闭：

USE [SQL-LI] 
GO
DISABLE TRIGGER [DBO].TRIGG_UPDATE --关闭触发器【TRIGG_UPDATE】
ON SHITU_FFENSHU_XINXI
GO

2019-2-14SQLserver中function函数和存储过程、触发器、CURSOR第106张

开启：

USE [SQL-LI] 
GO
ENABLE TRIGGER [DBO].TRIGG_UPDATE  --开启触发器【TRIGG_UPDATE】
ON[DBO].[SHITU_FFENSHU_XINXI] --触发器所在的视图
GO

2019-2-14SQLserver中function函数和存储过程、触发器、CURSOR第107张

GO

标签：#触发器#数据表#select函数#date函数#数据库视图#存储过程#update#date#function函数#sql语言#sql数据库#sql触发器#sql函数 | 浏览：514 | 发布日期：

免责声明：文章转载自《2019-2-14SQLserver中function函数和存储过程、触发器、CURSOR》仅用于学习参考。如对内容有疑问，请及时联系本站处理。

上篇Python之字符编码与文件操作ZooKeeper 基础入门下篇

宿迁高防，2C2G15M，22元/月；香港BGP，2C5G5M，25元/月 雨云优惠码：MjYwNzM=

相关文章

python数据处理的常用操作

python数据处理 1.生成数据2.数据表检查3.数据表清洗4.数据预处理5.数据提取6.数据筛选7.数据汇总8.数据统计9.数据输出 1.生成数据 1.导入数据表 df=pd.read_excel('C:/Users/Admin/Desktop/types/output.xlsx')df1=pd.read_csv('C:/Users/Admin/...

Oracle视图和PL SQL编程.

Oracle中的视图 在Oracle数据库中视图是数据库中特有的对象。视图主要用于数据查询，它不会存储数据（物化视图除外）。这是视图和数据表的重要区别。 视图的数据来源于一个或等多个表中的数据，可以利用视图进行查询、插入、更新和删除数据，操作视图就是间接操作数据库表。 通常情况下，视图主要用于查询，不提倡通过视图而更新与视图相关的表，因为视图中的数据只是一...

oracle 学习之 PL/SQL

/* PL/SQL : Oracle内部语言 SQLServer的是:TSQL Procedural Language/SQL 是oracle在标准的SQL语言上的扩展,PL/SQL不仅允许嵌入SQL语言,还可以 定义变量和常量,允许使用条件语句和循环语句,允许使用例外处理各种错误,这样使得它的功能变得 更加的强大. 优点:提...

基于datax的数据同步平台

一、需求 由于公司各个部门对业务数据的需求，比如进行数据分析、报表展示等等，且公司没有相应的系统、数据仓库满足这些需求，最原始的办法就是把数据提取出来生成excel表发给各个部门，这个功能已经由脚本转成了平台，交给了DBA使用，而有些数据分析部门，则需要运维把生产库的数据同步到他们自己的库，并且需要对数据进行脱敏，比如客户的身份证号、手机号等等，且数据来...

SQL SERVER 2008 R2 自动备份并删除过期备份数据

    我们的系统维护的过程中肯定需要对数据库进行定期的备份，但是如果定时手工备份的话，不但浪费时间，也不能保证每次都可以按时备份，所以自动备份成为了我们的不二选择，但是定时备份需要定期清理备份文件，不然会因为磁盘空间不足导致备份失败，失去备份的准确性，下面我们就一步步来看一下如果自动备份并删除过期备份数据。 1.打开SQL SERVER 2008 R2,...

EF core的原生SQL查询以及用EF core进行分页查询遇到的问题

在用.net core进行数据库访问，需要处理一些比较复杂的查询，就不得不用原生的SQL查询了，然而EF Core 和EF6 的原生sql查询存在很大的差异。 在EF6中我们用SqlQuery和ExecuteSqlCommand进行sql语句的执行，而在EF Core中我们则使用FromSql和ExecuteSqlCommand 一.ExecuteSqlC...

最新文章

随机推荐

思享工具箱导航