SQL Server 存储过程、触发器、游标

清疚 2022-01-06 17:02 376阅读 0赞

存储过程

1、存储过程是事先编好的、存储在数据库中的程序，这些程序用来完成对数据库的指定操作。

2、系统存储过程: SQL Server本身提供了一些存储过程，用于管理有关数据库和用户的信息。

用户存储过程: 用户也可以编写自己的存储过程，并把它存放在数据库中，供客户端调用。

3、这样安排的主要目的就是要充分发挥数据库服务器的功能，尽量减少网络上的堵塞。

4、系统存储过程

概念：

它的目的在于能够方便地从系统表中查询信息，或者完成与更新数据库表相关的管理任务或其它的系统管理任务。

系统存储过程可以在任意一个数据库中执行。创建并存放于系统数据库master中，并且名称以sp\_或者xp\_开头。

部分系统存储过程：

sp\_addtype：用于定义一个用户定义数据类型。

sp\_configure：用于管理服务器配置选项设置。

xp\_sendmail：用于发送电子邮件或寻呼信息。

sp\_stored\_procedures：用于返回当前数据库中的存储过程的清单。

sp\_help：用于显示参数清单和其数据类型。

sp\_helptext：用于显示存储过程的定义文本。

sp\_rename：用于修改当前数据库中用户对象的名称。

Sp\_who:用于显示使用数据库的当前用户

sp\_help：用于显示参数清单和其数据类型。

sp\_depends：用于显示存储过程依据的对象或者依据存储过程的对象

sp\_helptext：用于显示存储过程的定义文本。

5、用户自定义的存储过程

定义格式：

CREATE PROC\[edure\] procedure\_name \[ ; number \]

\[ @parameter data\_type \[ = default \]\[output\], … uc1\]

AS sql\_statement

procedure\_name：给出存储过程名；

number：对同名的存储过程指定一个序号(允许同名)；

@parameter：给出参数名；

data\_type：指出参数的数据类型；

Output:返回值参数

default：给出参数的默认值；

sql\_statement：存储过程所要执行的SQL语句，它可以是一组SQL语句，可以包含流程控制语句等。

注意事项:

存储过程一般用来完成数据查询和数据处理操作，所以在存储过程中不可以使用创建数据库对象的语句，

即在存储过程中一般不能含有以下语句：

CREATETABLE ； CREATEVIEW ； CREATEDEFAULT ；

CREATERULE ；CREATETRIGGER ；CREATEPROCEDURE

存储过程的返回值和状态信息:

无论什么时候执行存储过程，总要返回一个结果码，用以指示存储过程的执行状态。

如果存储过程执行成功，返回的结果码是0；如果存储过程执行失败，返回的结果码一般是一个负数，它和失败的类型有关。

我们在创建存储过程时，也可以定义自己的状态码和错误信息。

执行存储过程：

例：执行带参数的存储过程,查询大于岁的学生

createproc show;3 ( @pno char(6) )

select\* from person where Pno = @pno

exec show;3 4

例：CREATEProcedure sp\_getstu;1

SELECT\* FROM学生

例：带参数的存储过程,查询大于指定年龄的学生

CREATEproc sp\_getstu;2 (@sage int)

SELECT\* FROM学生WHERE年龄> @sage

例: 带输出参数的存储过程,查询指定学生的年龄

CREATE proc sp\_getstu;3 ( @name char(10) , @age int output )

SELECT @age=年龄 FROM学生WHERE姓名= @name

Declare @sage int

Exec sp\_getstu;3 '张三',@sage

Print @sage

例：带参数和返回状态值的存储过程。

CREATE PROCedure sp\_getstu;3 (@sage int =NULL )

IF @sage IS NULL

BEGIN

PRINT '必须提供一个数值作参数！'

RETURN 13

END

IF NOT EXISTS (SELECT \* FROM student WHERE sage > @sage)

BEGIN

PRINT '没有满足条件的记录！'

RETURN \-103

END

SELECT \* FROM student WHERE sage > @sage

RETURN 0

DECLARE @status int

EXECUTE @status=sp\_getstu;3 22

print @status

触发器

1、定义格式

CREATE TRIGGER trigger\_name

ON table

FOR \{  INSERT | UPDATE | DELETE \}

\[IF UPDATE(column) \[\{AND | OR\} UPDATE(column)…\]\]

sql\_statement

2、插入视图和删除视图

为触发器运行而自动派生的两个视图：

inserted——存放刚插入的新记录

deleted——存放刚删除的旧记录

3、触发器分类

插入类触发器…insert删除类触发器…delete更新类触发器…update

4、触发时机

After|For触发器是在SQL Server服务器接到执行SQL语句请求之后，

先建立临时的Inserted表和Deleted表，然后实际更改数据，最后才激活触发器的。

InsteadOf触发器在SQL Server服务器接到执行SQL语句请求后，先建立临时的Inserted表和Deleted表，

然后就触发了InsteadOf触发器，至于那个SQL语句是插入数据、更新数据还是删除数据，一概不管，

把执行权全权交给了InsteadOf触发器，由它去完成之后的操作。

例：建立一个简单的触发器。

CREATETRIGGER test\_trigger

ON student FOR INSERT

ASPRINT '插入了一个元组'

例：定义一个触发器，使得当删除学生记录时，同时将所有该学生选课记录删除

CREATE TRIGGER del\_trigger

ON STUDENT FOR DELETE

DELETE FROM SC

WHERE SNO = (SELECT SNO FROM deleted)

例：对学生表的插入操作定义一个触发器，使得当插入记录时，检查相应的学生年龄是否满足条件，如果不存在则显示错误信息。

create trigger stu\_tri

on student instead of insert

if ( select age from inserted )>40

print '不能插入大于岁的学生纪录'

else insert into student select \* from inserted

游标

1、需要游标的数据操作

当select语句的结果中包含多个元组时，使用游标可以逐个存取这些元组

活动集：select语句返回的元组的集合

当前行：活动集中当前处理的那一行。游标即是指向当前行的指针。

2、游标分类

滚动游标：游标的位置可以来回移动，可在活动集中取任意元组。

非滚动游标：只能在活动集中顺序地取下一个元组。

更新游标：数据库对游标指向的当前行加锁，当程序读下一行数据时，本行数据解锁，下一行数据加锁。

3、定义与使用游标的语句

declare ：

declare游标名\[scroll\] cursor for select语句\[for update \[of列表名\]\]

定义一个游标，使之对应一个select语句

for update任选项，表示该游标可用于对当前行的修改与删除

open

打开一个游标，执行游标对应的查询，结果集合为该游标的活动集

open游标名

fetch

在活动集中将游标移到特定的行，并取出该行数据放到相应的变量中

关闭游标，释放活动集及其所占资源。需要再使用该游标时，执行open语句

close游标名

deallocate

删除游标，以后不能再对该游标执行open语句

deallocate游标名

@@FETCH\_STATUS

返回被FETCH语句执行的最后游标的状态.

0 fetch语句成功

\-1 fetch语句失败

\-2 被提取的行不存在

例:查询电子商务系学生信息,性别为女输出为female,否则输出为male？

declare c1 cursor for select sno,sname,ssex from student where sdept='ec'

declare @sno char(10),@sname char(10),@ssex char(2)

Open c1

Fetch c1 into @sno,@sname,@ssex

While @@fetch\_status==0

Begin

if @ssex='女'

begin set @ssex='female' end

else

begin set @ssex='male' end

Select @sno,@sname ,@ssex

Fetch c1 into @sno,@sname,@ssex

end

转载于:https://www.cnblogs.com/rainman/archive/2009/04/26/1443995.html

SQL Server 存储过程、触发器、游标

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 SQL存储过程和游标示例

相关存储过程，触发器，游标

相关 SQL SERVER触发器使用游标范例

相关 SQL Server视图、存储过程、触发器

相关存储过程，游标，触发器

相关 oracle（sql）基础篇系列（五）——PLSQL、游标、存储过程、触发器

相关 SQL Server存储过程

相关 SQL Server 存储过程、触发器、游标

相关 SQL Server 存储过程

相关 SQL Server存储过程