7 sql server
This presentation is the property of its rightful owner.
Sponsored Links
1 / 70

第 7 章 SQL Server 存储过程和触发器 PowerPoint PPT Presentation


  • 83 Views
  • Uploaded on
  • Presentation posted in: General

第 7 章 SQL Server 存储过程和触发器. 任课教师:郭黎明. 7.2 存储过程的使用. [ 应用实例 1] 统计每一个学生的平均成绩 , 要求显示学生姓名和平均成绩。 分析 : 关联查询和分组查询 使用 T-SQL 语句实现:. SELECT sname,avg(grade) FROM sc,student WHERE sc.sno=student.sno GROUP BY sname. 7.1 存储过程的使用. [ 应用实例 1] 统计学生所选课程所得的总学分 , 要求显示系科名 , 学号 , 姓名和总学分。 使用存储过程实现:.

Download Presentation

第 7 章 SQL Server 存储过程和触发器

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


7 sql server

第7章 SQL Server存储过程和触发器

任课教师:郭黎明


7 sql server

7.2 存储过程的使用

  • [应用实例1] 统计每一个学生的平均成绩,要求显示学生姓名和平均成绩。

  • 分析:关联查询和分组查询

  • 使用T-SQL语句实现:

SELECT sname,avg(grade)

FROM sc,student

WHERE sc.sno=student.sno

GROUP BY sname


7 sql server

7.1 存储过程的使用

  • [应用实例1]统计学生所选课程所得的总学分,要求显示系科名,学号,姓名和总学分。

  • 使用存储过程实现:

CREATE PROCEDURE Avg_of_Grade AS

SELECT sname,avg(grade)

FROM sc,student

WHERE sc.sno=student.sno

GROUP BY sname

在查询分析器中运行过程:

EXEC Avg_of_Grade


7 sql server

7.1 存储过程概述

  • 存储过程的概念

    • 存储过程是SQL Server服务器上一组预先定义并编译好的Transact-SQL语句,它可以接受参数,返回状态值和参数值。

  • 存储过程应用

    • 涉及服务器处理多,而与用户较少的程序

  • 存储过程的分类

    • 系统存储过程:sp开头 SP_database

    • 本地存储过程

    • 临时存储过程

    • 远程存储过程

    • 扩展存储过程


7 sql server

7.1 存储过程概述

  • 存储过程的优点

    • 模块化的程序设计

      创建一个存储过程存放在数据库中后,就可以被其他程序反复使用

    • 快速执行-当存储过程被编译并存储在高速缓冲区中时,它能以相当高的效率执行。

    • 减少网络通信量-时常会有长达数百行的S Q L语句。如果能把那些功能封装进存储过程,就可以通过向SQL Server发送一行代码起到成百行的语句的作用,从而节省大量的网络带宽

    • 安全性-存储过程能作为一种安全性机制使用。当你授权许可一个用户或一组用户使用存储过程,他们将能够执行这个存储过程而不必有访问在存储过程中被访问的下层对象的许可。


7 sql server

7.2 存储过程的使用

  • 存储过程创建

  • 存储过程的执行

  • 存储过程修改

  • 存储过程删除


7 sql server

7.2 存储过程的使用

  • 存储过程创建

    • 存储过程的创建方法

      • ①利用SQL Server 企业管理器创建存储过程。

      • ②使用Transact-SQL语句中的CREATE。

      • ③使用创建存储过程向导创建存储过程。

    • 存储过程的组成

      • ①所有的输入参数以及传给调用者的输出参数。

      • ②被执行的针对数据库的操作语句,包括调用其它存储过程的语句。

      • ③返回给调用者的状态值,以指明调用是成功还是失败。


7 sql server

7.2 存储过程的使用

  • 存储过程创建

    • 使用Transact-SQL语句中的CREATE。

CREATE PROC[EDURE] procedure_name[;number] [[email protected] data_type}

[VARYING][=default][OUTPUT]][,...n] WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}] [FORREPLICATION] AS sql_statement [ ...n ]


7 sql server

7.2 存储过程的使用

  • 存储过程创建

    • procedure_name:用于指定要创建的存储过程的名称。

    • number:该参数是可选的整数,它用来对同名的存储过程分组,以便用一条 DROP PROCEDURE 语句即可将同组的过程一起除去。

    • @parameter:过程中的参数。在 CREATE PROCEDURE 语句中可以声明一个或多个参数。

    • data_type:用于指定参数的数据类型。

    • VARYING:用于指定作为输出OUTPUT参数支持的结果集。

    • Default:用于指定参数的默认值。

    • OUTPUT:表明该参数是一个返回参数。


7 sql server

7.2 存储过程的使用

  • 存储过程创建

    【实例1--无参数的存储过程】:统计学生所选课程所得的总学分,要求显示系科名,学号,姓名和总学分。

    分析:关联查询和分组查询

CREATE PROCEDURE Total_of_Credit AS

SELECT dname, student.sno,sname, sum(ccredit)

FROM student,sc,course,dept

WHERE student.sno=sc.sno AND course.cno=sc.cno and dept.dno=student.dno

GROUP BY dname,student.sno,student.sname

Total_of_Credit

EXEC Total_of_Credit


7 sql server

7.2 存储过程的使用

  • 存储过程创建

    【实例2----带输入参数的存储过程】 】建立一存储过程,用户输入学生姓名,学号,如果student表中存在此项,则删除后再插入,若不存在此学生,则直接插入该记录。

  • 说明:exists(select_statement)函数是指select语句是否能返回行,如果能返回,则为真,否则为假。

  • 分析:显然学号和姓名应该作为存储过程的传入参数


7 sql server

7.2 存储过程的使用

  • 【Ins_Del_S】代码

    CREATE PROCEDURE Ins_Del_S

    @sno AS CHAR(6),

    @sname AS CHAR(10)

    AS

    IF EXISTS(SELECT * FROM student

    WHERE [email protected] )

    BEGIN

    DELETE student WHERE [email protected]

    INSERT INTO student(sno,sname) VALUES( @sno ,@sname)

    END

    ELSE

    INSERT INTO student(sno,sname) VALUES( @sno ,@sname)

    GO

exec ins_del_s '1244','df'


7 sql server

7.2 存储过程的使用

  • 存储过程创建

    【实例3--带输出参数的存储过程】:根据用户键入的学生姓名,查询学生的年龄。

CREATE PROCEDURE Getage

@name CHAR(8)=NULL, @age INT OUTPUT

AS

IF @name=NULL

BEGIN

PRINT '查找哪个同学的年龄'

RETURN

END

SELECT @age=YEAR(Getdate())-YEAR(sbirthday)

FROM student

WHERE [email protected]

RETURN

DECLARE @age INT

EXEC Getage '李一名',@age OUTPUT

SELECT @age


7 sql server

7.2 存储过程的使用

  • 存储过程创建

    【实例4--通过Return参数返回状态】:创建一个存储过程,检查某学生的选课的门数

    RETURN语句返回的是整形值

CREATE PROCEDURE SelCount

@sno CHAR(8)=NULL

AS

DECLARE @count integer

SELECT @count=count(*)

FROM sc

WHERE [email protected]

RETURN @count

declare @count integer

execute @count=selcount '95001'

print @count


7 sql server

7.2 存储过程的使用

  • 存储过程的执行

    • 直接执行存储过程可以使用EXECUTE命令来执行,其语法形式如下:

[[EXEC[UTE]]{ [@return_status=]{procedure_name[;number][email protected]_name_var} [[@parameter=][email protected][OUTPUT]|[DEFAULT]}[,...n] [ WITH RECOMPILE ]。


7 sql server

7.2 存储过程的使用

  • 存储过程的修改

    • 使用Transact-SQL语句中的ALTER。

ALTER PROC[EDURE]procedure_name[;number] [[email protected]_type} [VARYING][=default][OUTPUT]][,...n] WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}] [FORREPLICATION] AS sql_statement [ ...n ]


7 sql server

7.2 存储过程的使用

  • 存储过程的删除

    • 使用Transact-SQL语句中的DROP。

DROP PROC[EDURE] <procedure_name>


7 sql server

7.2 存储过程的使用

  • 存储过程的错误处理

    • 可以使用@@ERROR捕捉错误代码, @@ERROR在执行每一个T-SQL语句之后都会得到一个值,对于成功的执行, @@ERROR的值为0,如果出现错误@@ERROR的值大于0

CREATE PROCEDURE INSERT_STUDENT

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

as

insert into student(sno,sname,ssex) values(@sno,@sname,@ssex)

if @@error=0

print '执行成功'

else

print '执行失败'

print @@error


7 sql server

练习

  • 综合练习

  • 1.根据用户键入的学生学号和课程号,查询学生的成绩。

CREATE PROC Getgrade

@sno VARCHAR(10),

@cno VARCHAR(10)

AS

SELECT student.sno,sname,cno,grade

FROM sc

WHERE [email protected] and [email protected]


7 sql server

练习

  • 二、存储过程综合练习

  • 2. 建立一存储过程,使用户输入系别,查询该系学生选择各课程的情况。(模糊查询)列出系别、课程号以及对应的人数,并按系别排序。【Sel_Course】

  • 3. 建立一存储过程,使用户输入课程名时,列出该课程名、对应的课程号、选择该课程的学生总人数、平均分【Sel_Stu】


7 sql server

参考答案

二、存储过程综合练习 【2】

CREATE PROCEDURE Sel_Course

@dno AS CHAR(2)

AS

SELECT dept.dno,dname,sc.cno,count(*)

FROM sc,dept,student

WHERE sc.sno=student.sno AND student.dno=dept.dno

AND [email protected]

GROUP BY dept.dno,dname,sc.cno

GO


7 sql server

参考答案

二、存储过程综合练习 【3】

CREATE PROCEDURE Sel_Stu

@cname AS CHAR(20)

AS

SELECT sc.cno,cname,count(*),avg(grade)

FROM sc,course

WHERE sc.cno=course.cno AND [email protected]

GROUP BY sc.cno,cname

GO


7 sql server

7.3 触发器

  • 7.3.1触发器概述

    • 问题的引入

    • 触发器的概念

    • 触发器的原理

    • 触发器的分类

  • 7.3.2触发器的创建,删除

  • 应用示例


7 3 1

7.3.1 触发器概述

  • 问题的引入

    • 在[学生管理数据库]中,某学生的[选课门数]是一个经常访问的属性,因此在student表中增加一个属性sselnum (smallint).

    • 在实际应用

    • 实例展示

4

1

删除一个记录(“95003”,”002”,56

95001 006 90

增加一个记录


7 3 11

7.3.1 触发器概述

  • 问题的引入

    • 考虑该问题涉及两个表之间的关系,可否用参照完整性来实现?

    • 可否用嵌套的SQL语句来实现?

      • update student set sselnum=(select count(*) from sc where sc.sno=student.sno)

      • 存在的问题

    • 问题的解决:使用触发器trigger

    • 触发器的主要作用就是其能够实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性


7 3 12

7.3.1 触发器概述

  • 触发器的概念

    • 触发器是一种特殊类型的存储过程。在SQL Server里面也就是对某一个表的一定的操作,触发某种条件,从而执行的一段程序。

    • 触发器触发原因对表进行插入、更新或删除操作触发事件。

    • 触发结果:另一段程序的执行

      • 思考 :在【问题的引入】中,?是触发原因

      • ?触发结果

    • 触发器主要是通过事件触发而执行的,而存储过程是通过调用存储过程名称而执行的。

      INSERT INTO sc VALUES(‘ 95001’, ‘ 006 ’, 90)

      EXEC Getgrade '00001','02'

    • 触发器可以用于SQL Server 约束、默认值和规则的完整性检查。


7 3 13

7.3.1 触发器概述

  • 触发器的原理

    • 在对表进行【INSERT】【DELETE】【UPDATE】操作时,SQL Server自动执行触发器定义的一段程序

    • 触发器用到的专用临时表:Inserted和Deleted

    • 特点(内存,时间,结构,只读)

      • 存储在内存中

      • 触发事件执行以后,逻辑表即刻被删除

      • 结构同触发事件所操作的表一致

      • 只读

操作

Inserted表

Deleted表

Insert

存放插入的记录

Delete

存放删除的记录

Update

存放要更新的新行

存放更新前的行


7 3 14

7.3.1 触发器概述

  • 触发器的原理

    • Inserted表和Deleted表的理解:

      (1)如果基于SC表的UPDATE操作定义了触发器

      (2)再执行语句:

      UPDATE SC SET grade=70 WHERE sno=‘95001’ AND cno=‘001’

      在UPDATE语句执行过程中

      Deleted表的内容:

      Inserted表的内容:

SC表


7 3 15

7.3.1 触发器概述

  • 触发器的分类

    • AFTER类型触发器:

      • 只有执行某一操作(INSERT UPDATE DELETE) 之后,触发器才被触发

      • 只能在表上定义。

    • INSTEAD OF 触发器:

      • 并不执行其所定义的操作(INSERT、 UPDATE、 DELETE),而仅是执行触发器本身。

      • 既可在表上定义INSTEAD OF 触发器,也可以在视图上定义INSTEAD OF 触发器.


7 3 2

7.3.2 触发器的使用

  • 触发器的创建

    • 触发器创建的方法

      • 用EM创建

        • 用右键单击该表,从弹出的快捷菜单中选择所有任务子菜单下的管理触发器选项,则会出现触发器属性对话框

      • 用SQL创建


7 3 21

7.3.2 触发器的使用

  • 触发器的创建

    • 创建触发器的语法

CREATE TRIGGER trigger_name ON{table|view} {{ { FOR | AFTER | INSTEAD OF } { [DELETE][,][ INSERT ] [ , ] [ UPDATE ] }AS

...

触发事件所操作的表或视图

触发事件

被触发的程序段


7 3 22

7.3.2 触发器的使用

  • 触发器的创建

    【实例1】创建一个Insert触发器,功能是当在sc上插入一行数据时,自动使得student表对应学号上的sselnum字段的值加1

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

    【思考】(1)如何获得sc表上新插入记录的学号?

    (2)触发器定义在?一个表上

SELECT sno FROM Inserted


7 3 23

7.3.2 触发器的使用

  • 触发器的创建

    【实例1】创建一个Insert触发器,功能是当在sc上插入一行数据时,自动使得student表对应学号上的sselnum字段的值加1

CREATE TRIGGER sc_insert ON sc

FOR INSERT

AS

UPDATE student SET sselnum=sselnum+1

FROM student

WHERE sno IN (SELECT sno FROM inserted)

局限性:只能对单行的处理,可能存在一条语句影响多条记录的情况

注意:Sselnum默认初值设置为0,否则NULL值不能参加算术运算。


7 3 24

7.3.2 触发器的使用

  • 触发器的创建

    • 【实例1的使用】创建了触发器sc_insert以后

    • 【测试】在sc表中插入新的记录(“95001”,”006”,90),

      INSERT INTO sc VALUES (‘95001’,’006’,90)

      检测student表中”95001”的sselnum是否自动增加1

    • 【解析】--【Insert】触发器的执行过程

      • 首先执行insert语句,如果语句正确,插入记录

      • 执行被触发的程序段:update语句

      • 注意:触发器触发的语句和触发它的语句是同一事务来执行的

      • 自己上机检测


7 3 25

7.3.2 触发器的使用

  • 触发器的创建

    【实例2】创建一个Insert触发器,功能是当在sc上插入数据时,自动更新student表对应学号上的sselnum字段,应考虑成批修改数据的情况。

    【举例】合并sc1表的数据到sc表中(两个表的结构相同)

    INSERT INTO sc SELECT * FROM sc1


7 3 26

7.3.2 触发器的使用

【实例2】创建一个Insert触发器,功能是当在sc上插入数据时,自动更新student表对应学号上的sselnum字段,应考虑成批修改数据的情况。

【分析】分成两种情况

(1)单行记录插入,同[实例1]

(2)多行记录插入,从inserted表中可以获得新插入记录的所有的sno,以及在每个sno上插入的记录的个数,以此更新Student表中的记录

(3)如何区别单行记录操作和多行记录操作?


7 3 27

7.3.2 触发器的使用

  • 触发器的创建

    【实例2】创建一个Insert触发器,功能是当在sc上插入数据时,自动更新student表对应学号上的sselnum字段,应考虑成批修改数据的情况。

CREATE TRIGGER sc_insert ON sc

FOR INSERT AS

IF @@ROWCOUNT=1

BEGIN

UPDATE student SET sselnum=sselnum+1 FROM student

WHERE student.snoIN(SELECT sno FROM inserted)

END

ELSE

BEGIN

UPDATE student SET sselnum=sselnum+

(SELECT COUNT(cno) FROM inserted WHERE student.sno=inserted.sno)

WHERE student.sno IN (SELECT sno FROM inserted)

END


7 3 28

7.3.2 触发器的使用

  • 触发器的创建

    • 【实例2的使用】创建了触发器sc_insert以后

    • 【测试】执行INSERT INTO sc SELECT * FROM sc1

      检测STUDENT表中Sselnum字段的值

    • 【问题】--检测正确?

    • 【扩展思考】

      如果在SC表的Insert操作上定义两个不同的触发器,系统怎样处理?


7 3 29

7.3.2 触发器的使用

  • 触发器的创建

    【实例3】建立一触发器,当删除student表中某学生记录时,同时删除sc表中所有该学生的选课记录。

CREATE TRIGGER Del_s_sc ON student]

FOR DELETE

AS

DELETE sc WHERE sno IN

(SELECT sno FROM deleted)

该问题还可以用其他方法解决。


7 3 210

7.3.2 触发器的使用

  • 触发器的创建

    【实例4】创建一个Update触发器,功能是当在sc上修改一行数据时,显示修改的记录的修改前的值和修改后的值

CREATE TRIGGER sc_update ON sc

FOR UPDATE

AS

SELECT *,’修改前’ FROM deleted

SELECT *,’修改后’ FROM inserted


7 3 211

7.3.2 触发器的使用

  • 触发器的创建

    在有些更新中,更新的内容并不是整个记录,而仅仅是一列或几列,这时就要用到用于检查列改变的更新型触发器。

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


7 3 212

7.3.2 触发器的使用

  • 触发器的创建

    【实例5】使用IF UPDATE()保留字,实现当修改Student表中的sno属性时,sc表中相应的sno也要修改

CREATE TRIGGER s_update ON

student

FOR UPDATE

AS

IF @@rowcount>1

PRINT '不允许同时修改多行记录的学号(SNO)'

ELSE

IF UPDATE(sno)

UPDATE sc set sno=(select sno from inserted)

WHERE sno=(select sno from deleted)


7 3 213

7.3.2 触发器的使用

  • 触发器的创建(INSTEAD OF)

    • INSTEAD OF 触发器的主要优点是使不可被修改的视图能够支持修改。其中典型的例子是分割视图(partitioned view)。分割视图通常是一个来自多个表的结果集

    • 【举例】全校学生人数多

      将学生表按系科分成若干表, 为了教务处统一管理数据,合并表到分割视图

      例如 :将student分成三个表Student01,Student02,Student03,

      表示01系科,02系科,03系科的学生,创建分割视图student_view统一管理数据

create view student_view

as

select * from student01

union all

select * from student02

union all

select * from student03

存在问题:不能更新


7 3 214

7.3.2 触发器的使用

【实例5】如何使用INSTEAD OF

触发器来支持对分割视图所引用的基本表的修改。

【分析】触发器创建在 视图上

触发事件是Insert

可以通过临时表Inserted获得插入的记录的”系科”和其他属性

在该”系科”对应表的上插入inserted中的记录.


7 3 215

7.3.2 触发器的使用

【实例5】如何使用INSTEAD OF

触发器来支持对分割视图所引用的基本表的修改。

CREATE TRIGGER studentviewtrg ON student_view

INSTEAD OF insert

AS

BEGIN

DEClARE @deptno CHAR(3)

SELECT @deptno=dno FROM inserted

IF @deptno='01'

BEGIN

INSERT INTO student01 SELECT * FROM inserted

END

IF @deptno='02'

BEGIN

INSERT INTO student02 SELECT * FROM inserted

END

IF @deptno='03'

BEGIN

INSERT INTO student03 SELECT * FROM inserted

END

END

思考:在视图上的insert语句是否执行


7 3 216

7.3.2 触发器的使用

【实例5】应用

执行INSERT INTO student_View(sno,dno) VALUES(“95010”,”01”)

视图本身不能更新,但是数据发生了改变,为什么?


7 3 217

7.3.2 触发器的使用

  • 触发器的修改

    • 修改触发器的语法

ALTER TRIGGER trigger_name ON{table|view} [WITHENCRYPTION] {{ { FOR | AFTER | INSTEAD OF } { [DELETE][,][ INSERT ] [ , ] [ UPDATE ] }[WITHAPPEND][NOTFORREPLICATION]AS


7 3 218

7.3.2 触发器的使用

  • 触发器的删除

    • 删除触发器的语法

DROP TRIGGER trigger_name


7 sql server

练习

基础练习题

1、触发器分为_____、_____两种。

2、激活触发器的操作有_____、____ 、____。

3、触发器定义在SC表上,触发事件是UPDATE,则在SC表S上执行UPDATE语句,会产生逻辑表_____、____ 。


7 sql server

练习

  • 二、触发器综合练习

  • 1. 建立一触发器,当向SC表插入记录时,若对应的课程号的选课人数小于等于60人,则可插入,否则发出出错信息“该课程选课人数已满!请另选其他课程。” sc_ins_60

  • 2. 建立一触发器,当修改某学生的系别时或插入记录时,判断该系学生人数是否小于等于100,如果是,则允许修改或插入,否则发出出错信息“该系人数已满!!” Ins_Up_100


7 sql server

练习

  • 二、触发器综合练习

  • 3. 建立一触发器,当删除student表中某学生记录时,同时删除sc表中所有该学生的选课记录。 Del_s_sc


7 sql server

参考答案

三、触发器综合练习 【1】

CREATE TRIGGER sc_ins_60 ON [dbo].[sc]

FOR INSERT

AS

IF (SELECT COUNT(*) FROM sc,inserted

WHERE sc.cno=inserted.cno )>=5

BEGIN

PRINT '该课程选课人数已满!请另选其他课程'

ROLLBACK

END


7 sql server

参考答案

三、触发器综合练习 【2】

CREATE TRIGGER Ins_Up_100 ON [dbo].[student]

FOR INSERT, UPDATE

AS

IF (SELECT COUNT(*) FROM student, inserted WHERE

student.dno= inserted.dno)>=5

BEGIN

PRINT ' 系人数已满,不能插入或修改!'

ROLLBACK

END


7 sql server

参考答案

三、触发器综合练习 【3】

CREATE TRIGGER Del_s_sc ON [dbo].[student]

FOR DELETE

AS

DELETE sc WHERE sno IN (SELECT sno FROM deleted)

注意 :要设置student的关系中,设置


7 sql server

7.4 游标

  • 问题的引入

    • 关系数据库管理系统实质是面向集合的,除非使用where 子句来限制只有一条记录被选中

    • 借助于游标来进行面向单条记录的数据处理

    • 游标允许应用程序对查询语句select 返回的行结果集中每一行进行相同或不同的操作,而不是一次对整个结果集进行同一种操作

    • 它还提供对基于游标位置而对表中数据进行删除或更新的能力

    • 游标把作为面向集合的数据库管理系统和面向行的程序设计两者联系起来


7 sql server

7.4 游标

  • 7.4.1游标的基础

    • 游标的概念:游标提供一种机制,使得应用程序每次处理结果集中的一行或一部分

    • 游标的功能

      • 定位在结果集的特定行

      • 从结果集当前位置检索一行或多行

      • 修改结果集当前位置的行

      • 在存储过程或触发器中使用


7 sql server

7.4 游标

  • 7.4.1游标的基础

    • 游标的种类

      • (1) Transact_SQL 游标

        Transact_SQL 游标是由DECLARE CURSOR 语法定义、主要用在Transact_SQL 脚本、存储过程和触发器中。Transact_SQL 游标主要用在服务器上。

      • (2) API 游标

        API 游标支持在OLE DB, ODBC 以及DB_library 中使用游标函数,主要用在服务器上。。

      • (3) 客户游标

        客户游标主要是当在客户机上缓存结果集时才使用。


7 sql server

7.4 游标

  • 7.4.2游标的使用

    • 每一个游标必须有四个组成部分这四个关键部分必须符合下面的顺序1.DECLARE 游标2.OPEN 游标3.从一个游标中FETCH 信息4.CLOSE 游标


7 sql server

7.4 游标

  • 7.4.2游标的使用

    • 每一个游标必须有四个组成部分这四个关键部分必须符合下面的顺序1.DECLARE 游标2.OPEN 游标3.从一个游标中FETCH 信息4.CLOSE 游标和Deallocate游标


7 sql server

7.4 游标

  • 7.4.2游标的使用

    • 声明游标

      通常我们使用DECLARE 来声明一个游标声明

      ·游标名字

      ·数据来源(表和列)

      ·选取条件

      ·属性(仅读或可修改)

      其语法格式如下:

      DECLARE cursor_name [INSENSITIVE] [SCROLL] CURSOR

      FOR select_statement

      [FOR {READ ONLY | UPDATE [OF column_name [,...n]]}]


7 sql server

7.4 游标

  • 7.4.2游标的使用

    • [示例1]定义一个查询student表的标准游标

declare cur_st cursor

for

select * from student

For readonly----只读游标的定义

For Update----更新游标的定义


7 sql server

7.4 游标

  • 7.4.2游标的使用

    • 打开游标

      打开游标游标在声明以后,如果要从游标中读取数据必须打开游标。

      OPEN { { [GLOBAL] cursor_name } | cursor_variable_name}

      各参数说明如下:

      • GLOBAL

      定义游标为一全局游标。

      • cursor_name

      为声明的游标名字。。

      • cursor_variable_name

      为游标变量。

      注意:在游标被成功打开之后,@@CURSOR_ROWS 全局变量将用来记录游标内数据行数。


7 sql server

7.4 游标

  • 7.4.2游标的使用

    • 读游标

      当游标被成功打开以后,就可以从游标中逐行地读取数据,以进行相关处理。


7 sql server

7.4 游标

  • 7.4.2游标的使用

    • 关闭游标

      使用CLOSE 命令关闭游标

      在处理完游标中数据之后必须关闭游标来释放数据结果集和定位于数据记录上的锁。

      CLOSE { { [GLOBAL] cursor_name } | cursor_variable_name }

    • 释放游标

      因此常使用DEALLOCATE 命令。通过该命令可以删除掉游标与游标名或游标变量之间的联系,并且释放游标占用的所有系统资源。其语法规则为:

      DEALLOCATE { { [GLOBAL] cursor_name } | @cursor_variable_name}


7 sql server

7.4 游标

  • 7.4.2游标的使用

    • [示例2]使用游标读取student表中的数据

declare st_cursor cursor

for

select sno,sname,sbirthday from student

open st_cursor

fetch next from st_cursor

while @@fetch_status=0

begin

fetch next from st_cursor

end

close st_cursor

deallocate st_cursor


7 sql server

7.4 游标

  • 7.4.2游标的使用

    • 游标变量

       首先先声明一个游标。declare cur_st cursor

      for

      select * from student

      使用SET 语句将一游标赋值给游标变量

      declare @cur_var

      set @cur_var= cur_st


7 sql server

7.4 游标

declare @curvar cursor

exec openSt @[email protected] output

declare @sno char(5),@sname char(10)

fetch next from @curvar into @sno,@sname

while (@@fetch_status<>-1)

begin

print @sno

fetch next from @curvar into @sno,@sname

end

close @curvar

deallocate @curvar

go

  • 7.4.2游标的使用

    • [示例3]存储过程

create procedure openSt

@outSt cursor varying output

as

set @outSt=cursor for

select sno,sname from student

open @outSt

go


7 sql server

7.4 游标

  • 7.4.2游标的使用

    • 游标变量

       首先先声明一个游标。declare cur_st cursor

      for

      select * from student

      使用SET 语句将一游标赋值给游标变量

      declare @cur_var

      set @cur_var= cur_st


7 sql server

7.4 游标

  • 7.4.2游标的使用

    • 如果在声明游标时使用了FOR UPDATE 语句,那么在UPDATE 或DELETE命令中以WHERE CURRENT OF 关键字直接修改或删除当前游标中所存储的数据。

      进行定位修改或删除游标中数据的语法规则为:


7 sql server

7.4 游标

  • 7.4.2游标的使用

    • [示例4]通过游标修改数据

fetch next from st_cur into @sno,@sname

while @@fetch_status=0

begin

if @sno='95001'

update student

set sname='黎明'

where current of st_cur

fetch next from st_cur into @sno,@sname

end

close st_cur

deallocate st_cur

declare @sno char(5), @sname char(10)

declare st_cur cursor

for

select sno,sname from student

for update of sno,sname

open st_cur


  • Login