数据库 SQL Server 游标存储过程存储函数触发器

最新推荐文章于 2023-08-11 21:15:59 发布

十旬叶大叔

最新推荐文章于 2023-08-11 21:15:59 发布

阅读量2.1k

点赞数 2

分类专栏：数据库文章标签：数据库 sql database

本文链接：https://blog.csdn.net/qq_45059457/article/details/121747773

版权

数据库专栏收录该内容

11 篇文章 2 订阅

订阅专栏

文章目录

1 游标

我们在处理数据的时候，经常会出现需要循环处理数据的需求，这时候我们可以选择使用游标处理。

1.1 游标声明

游标分为游标类型和游标变量。
对于游标变量来说，遵循T-SQL变量的定义方法。
游标变量支持两种方式赋值，定义时赋值和先定义后赋值。
定义游标变量像定义其他局部变量一样，在游标前加”@”,注意，如果定义全局的游标，只支持定义时直接赋值，并且不能在游标名称前面加“@”，两种定义方式如下：

--定义全局游标后直接赋值
declare cursor1 cursor for select * from test


--先定义游标后赋值
declare @cursor2 cursor 
set @cursor2=cursor for select * from test

分析：用declare定义游标；用set给游标赋值；用for指定语句。

参数：local和global区别
local意味着游标的生存周期只在批处理或函数或存储过程中可见；
global意味着游标对于特定连接作为上下文，全局内有效，如果不指定游标作用域，默认作用域为global
例如：
在这里插入图片描述

分析：global游标，在批处理结束后依然有效；local游标，在批处理结束后，被隐式释放，无法在其他批处理中调用。

参数：forward_only和scroll的区别
forward_only意味着游标只能从数据集开始向数据集结束的方向读取，forward_only只有fetch next选项，fetch next表示让游标转到下一条记录，
如下图：
在这里插入图片描述
分析：
默认为forward_only选项值支持fetch next选项，不支持游标向后或者走向特定位置。
看看错误示范，如下图：

scroll支持游标在定义的数据集中向任何方向，或任何位置移动，如下图：

在这里插入图片描述

分析：scroll选项支持游标向任何方向移动，fetch last表示让游标指向最后一条记录，fetch prior表示让游标指向到上一条记录。

参数：static、keyset、dynamic、fast_forward的区别
这四个关键字是游标所在数据集所反应的表内数据和游标读取出的数据的关系。
static意味着，当游标被建立时，将会创建for后面的select语句所包含数据集的副本存入tempdb数据库中，任何对于底层表内数据的更改不会影响到游标的内容。
dynamic是和static完全相反的选项,当底层数据库更改时，游标的内容也随之得到反映，在下一次fetch中，数据内容会随之改变。
keyset可以理解为介于STATIC和DYNAMIC的折中方案。将游标所在结果集的唯一能确定每一行的主键存入tempdb,当结果集中任何行改变或者删除时，@@FETCH_STATUS会为-2,KEYSET无法探测新加入的数据。
fast_forward可以理解成forward_only的优化版本。forward_only执行的是静态计划，而fast_forward是根据情况进行选择采用动态计划还是静态计划，大多数情况下fast_forward要比forward_only性能略好。

参数read_only、scroll_locks和optimistic的区别
read_only意味着声明的游标只能读取数据,游标不能做任何更新操作。
scroll_locks是另一种极端，将读入游标的所有数据进行锁定，防止其他程序进行更改，以确保更新的绝对成功。
optimistic是相对比较好的一个选择，optimistic不锁定任何数据，当需要在游标中更新数据时,如果底层表数据更新，则游标内数据更新不成功，如果，底层表数据未更新，则游标内表数据可以更新。

1.2 打开游标

当定义完游标后，游标需要打开后使用。
语法格式:
open test_Cursor
注意，当全局游标和局部游标变量重名时，默认会打开局部变量游标。

1.3 使用游标

游标经常会和全局变量@@fetch_status与while循环来共同使用,以达到遍历游标所在数据集的目的，例如：
游标的使用分为两部分,一部分是操作游标在数据集内的指向，另一部分是将游标所指向的行的部分或全部内容进行操作，只有支持6种移动选项，分别为到第一行（first)，最后一行(last)，下一行(next)，上一行(prior)，直接跳到某行(absoulute(n))，相对于目前跳几行(relative(n))，例如：

--定义游标
declare t_cursor cursor scroll for select * from test

--打开游标
open t_cursor

--取下一行
fetch next from t_cursor

在这里插入图片描述
分析：scroll支持游标在定义的数据集中向任何方向，或任何位置移动。

--取最后一行
fetch last from t_cursor

在这里插入图片描述

--取第一行
fetch first from t_cursor

在这里插入图片描述

--取第二行
fetch absolute 2 from t_cursor

在这里插入图片描述

--取当前行（就是第二行）的上一行
fetch prior from t_cursor

在这里插入图片描述

--取相对目前行（第一行）的下两行，即第三行
fetch relative 2 from t_cursor

在这里插入图片描述

对于forward_only选项的游标来说，只支持next取值.
第一步操作完成后，就通过into关键字将这行的值传入局部变量，比如下面语句:

--定义forward_only游标
declare cursor8 cursor forward_only for select id,name from test

--打开游标
open cursor8

--定义两个变量
declare @id int
declare @name nvarchar(10)

--取下一行
fetch next from cursor8 into @id,@name

--输出变量结果
print @id 
print @name

在这里插入图片描述

游标经常会和全局变量@@fetch_status与while循环来共同使用,以达到遍历游标所在数据集的目的,例如：

open cursor8

declare @id int
declare @name nvarchar(10)

--利用while循环遍历
while @@FETCH_STATUS=0
begin
print @id 
print @name
fetch next from cursor8 into @id,@name
end

close cursor8
deallocate cursor8

在这里插入图片描述

1.4 关闭游标

在游标使用完之后，一定要记得关闭。
语法格式：
close+游标名称
例如：
close test_Cursor

1.5 释放游标

当游标不再需要被使用后，释放游标。
语法格式：
deallocate+游标名称
例如：
deallocate test_Cursor

2 存储过程

存储过程的定义
存储过程就是预先编译和优化并存储于数据库中的过程，是由一系列对数据库进行复杂操作的SQL语句、流程控制语句或函数组成的批处理作业。
存储过程的特点
（1）大大增强了SQL语言的功能和灵活性
（2）可保证数据的安全性和完整性
（3）更快的执行速度
（4）将体现企业规则的运算放入数据库服务器中以便集中控制

2.1 创建存储过程

当创建存储过程时，需要确定存储过程的以下3个组成部分：
① 所有的输入参数及传给调用者的输出参数。
② 被执行的针对数据库的操作语句，包括调用其他存储过程的语句。
③ 返回给调用者的状态值，以指明调用是成功还是失败。

CREATE PROCEDURE的语法格式如下：
CREATE { PROC | PROCEDURE } [schema_name.] procedure_name
[ { @parameter [ type_schema_name. ] data_type }
[ VARYING ] [ = default ] [ OUT | OUTPUT ] ]
[,…n ] [ WITH ENCRYPTION ]
AS { <sql_statement> [;][,…n ] }
<sql_statement> ::= { [ BEGIN ] statements [ END ] }

例如：
创建不带参数的存储过程
在数据库OASystem中，创建一个名为up_id_Info的存储过程，用于查询所有id为3的人的信息。

 create procedure up_id_info
as
select * from test where id='3'

在这里插入图片描述
分析：as后结果查询语句。

创建带输入参数的存储过程
创建一个名为up_name_infod的存储过程，来用查询test表的name字段。

create procedure up_name_info
@name varchar(30)
as
select * from test where name=@name

在这里插入图片描述
分析：创建完存储过程后定义参数。

2.2 执行存储过程

语法格式：
[[EXEC[UTE]] {[@return_status=] {procedure_name|@procedure_name_var}
[[@parameter=]{value|@variable[OUTPUT]|[DEFAULT]}[,…n]]}]

执行不带参数的存储过程
执行不带参数的存储过程，直接使用“EXEC 存储过程名”命令即可完成。

exec up_id_info

在这里插入图片描述
分析：exec执行存储过程。

执行带参数的存储过程
使用参数名传递参数值

创建存储过程

create proc up_showinfo @name varchar(4)='张'
as
select * from test where name like @name+'%'

使用up_showinfo存储过程

exec up_showinfo

在这里插入图片描述
分析：参数默认值为“张”

修改参数的值，如下：

exec up_showinfo '李'

在这里插入图片描述
分析：参数为“李”。

2.3 修改存储过程

修改存储过程的语法格式如下：
alter { PROC | PROCEDURE } [schema_name.] procedure_name
[ { @parameter [ type_schema_name. ] data_type }
[ VARYING ] [ = default ] [ OUT | OUTPUT ] ]
[,…n ] [ WITH ENCRYPTION ]
AS { <sql_statement> [;][,…n ] }
<sql_statement> ::= { [ BEGIN ] statements [ END ] }

将up_showinfo存储过程加密。
使用with recompile子句，利用encryption对存储过程进行加密，语句如下：

alter proc up_showinfo @name varchar(4)='张'
with recompile,encryption
as
select * from test where name like @name+'%'

试图查看存储过程的定义文本

exec sp_helptext up_showinfo

在这里插入图片描述

2.4 删除存储过程

语法格式：
drop procedure 存储过程名
在这里插入图片描述

3 存储函数

略

4 触发器

触发器是一种特殊类型的存储过程。触发器是在指定的时间发生时触发执行的。
触发器的主要作用就是其能够实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性。

触发器和存储过程有什么不同呢？
触发器主要是通过事件触发而执行的，而存储过程是通过调用存储过程名称而执行的。

触发器的分类
after类型触发器
只有执行某一操作（INSERT、UPDATE、DELETE）之后，触发器才被触发只能在表上定义。

instead of触发器
并不执行其所定义的操作（INSERT、 UPDATE、 DELETE），而仅是执行触发器本身。
既可在表上定义instead of触发器，也可以在视图上定义INSTEAD OF 触发器。

创建触发器的语法
create trigger trigger_name
on {table|view} (触发事件所操作的表或视图)
{ { { for| alter | inserted of } { [delete][,][ insert ] [ , ] [update] } (触发事件)
as
…(被触发的程序段)

在对表进行insert，delete，update操作时，SQL Server自动执行触发器定义的一段程序，触发器用到的专用临时表：Inserted和Deleted。
在这里插入图片描述

4.1 创建触发器

例子：
创建一个insert触发器，功能是当在sc表（sno,cno,grade）上插入一行数据时，自动使得student表（sno,snamessex,sbirthday,dno,sselnum）对应学号上的sselnum字段的值加1。
sc表数据如下：
在这里插入图片描述
student表数据如下：

分析：
insert操作的表是sc,而要求系统自动更新的表是student，因此关键是获得sc表上新插入记录的学号，然后在student表上修改该学号的sselnum的值。

create trigger sc_insert on sc
for insert
as
update student set sselnum=sselnum+1
where sno in(select sno from inserted)

注意：sselnum默认初值设置为0，null值不能参加算术运算。
局限性：只能对单行的处理，可能存在一条语句影响多条记录的情况 .

建立一个触发器，当删除student表中某学生记录时，同时删除sc表中所有该学生的选课记录。
分析：触发事件所操作的表是student，获取student表中的学号，然后根据学号找到该学生的选课记录。

create trigger del_s_sc on student
for delete
as
delete sc where sno in  
(select sno from deleted)

例子：
创建一个update触发器，功能是当在sc上修改一行数据时，显示修改的记录的修改前的值和修改后的值。

create trigger su_update on sc
update
as 
slect *,'修改前' from deleted
slect *,'修改后' from inserted

4.2 修改触发器

修改触发器语法，如下：
alter trigger tirgger_name
on 表名或视图 withencryption
{
{ { for|after|instead of} { [delete][,][ insert ] [ , ] [update] }
[withhappend]
[ontforreplication]
as
与创建触发器类似，就是关键字换为alter