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

免责声明:文章转载自《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...