【数据库详解】游标与存储过程(代码+例题）

一、游标

1、游标

• 一个对表举行操作的SQL语句（如 select）通常都可产生或处理一组记录，但是许多应用不能把整个结果集作为一个单位来处理，以是就须要一种机制来保证每次处理结果此中的一行或几行，游标（cursor）就提供了这种机制。
  
• SQL Server 通过游标提供了对一个结果集举行逐行处理的能力，游标可看做一种特别的指针，它与某个查询结果相联系，可以指向结果集的任意位置，以便对指定位置的数据举行处理。使用游标可以在查询数据的同时对数据举行处理。
  
• 游标是系统为用户开设的一个数据缓冲区，存放SQL语句的结果数据集，每个游标区都有一个名字，通过移动游标名代表的指针来访问数据集中的数据
  

 2、使用游标须要经历五个步调：

• 界说游标：DECLARE
  
• 打开游标：OPEN
  
• 逐行提取游标集中的行：FETCH
  
• 关闭游标：CLOSE
  
• 开释游标：DEALLOCA
  

3、游标的界说

   DECLARE <游标名>［SCROLL] CURSOR
FOR<SELECT语句>
[for［read only / update｛of<列名>}]
  
SCROLL：说明所声明的游标可从前滚，后滚，可使用全部的提取选项。如省略，则只能使用 NEXT 提取选项。
READ ONLY 表示当前游标集中的元组仅可以查询，不能修改；
UPDATE ｛OF <列名>｝表示可以对当前游标集中的元组举行更新操作。如果有OF <列名>，表示仅可以对游标集中指定的属性
如：界说一个能够存放sc表数据的游标

1. Declare cur_sc cursor

复制代码

 4、游标的打开

游标界说后，如果要使用游标，必须先打开游标。
打开游标操作表示：

• 系统按照游标的界说从数据库中将数据检索出来，放在内存的游标集中（如果内存不够，会放在临时数据库中）
  
• 为游标集指定一个游标，该游标指向游标集中的第1个元组
  

格式：Open 游标名；

1. Open cur_sc;

复制代码

打开游标后，可以使用全局变量@@CURSOR_ROWS查察游 标集中数据行的数目。全局变量@@CURSOR_ROWS中保存着最后打开的游标中的数据行数。当其值为0时，表示没有游标打开； 当其值为m（m为正整数）时，游标已被完全填充，m是游标中的数据行数。
例：界说游标XS_CUR3，然后打开该游标，输出其行数。

1. DECLARE XS_CUR3 CURSOR SCROLL
2. FOR
3. SELECT 学号,姓名,总学分
4. FROM XSB
5. FOR UPDATE OF 总学分
6. OPEN XS CUR3
7. SELECT '游标XS_CUR3数据行数'=@@CURSOR_ROWS

复制代码

5、游标的读取

• FETCH [ NEXT | PRIOR | FIRST | LAST | ABSOLUTE {n| @nvar | RELATIVE {n|@nvar] FROM {游标} [INTO @变量名,...]
  
• NEXT |PRIOR|FIRST |LAST：说明读取数据的位置。
  

1.Next：读取当前行的下一行，并使其置为当前行。如 fetch next 为对游标的第一次提取操作，则读取第一行，next 为默认值。
2.prior：读取当前行的前一行，并使其置为当前行。如是第一次操作，则无值返回，游标被置于第一行之前。
3.first ：读取第一行，并使其置为当前行。
4.last：读取最后一行，并使其置为当前行。
5. ABSOLUTE {n| @nvar } | RELATIVE {n| @nvar} : 给出读取数据的位置与游标头或当前位置的关系，此中n必须 为整型常量，@nvar必须smallint、tinyint或int型 
例：从游标XS_CURI中提取数据。设该游标已经声明并打开。

1. FETCH NEXT FROM XS_CUR1

复制代码

FETCH语句的执行状态保存在全局变量 @@FETCH_STATUS中，其值为0表示上一个FETCH执行 成功；为-1表示所要读取的行不在结果集中；为2表示被提取的行已不存在（已被删除）。
 比方，接着上例继承执行如下语句：(此时游标在倒数第三行）

1. FETCH RELATIVE 3 FROM XS_CUR2
2. SELECT'FETCH执行情况'= @@FETCH_STATUS

复制代码

执行结果为：-1，此时游标已出界
6、游标的关闭

• 关闭游标 CLOSE 游标
  
• 开释游标：游标关闭后，其界说仍在，须要时可用OPEN语句打开它再使用。若确认游标不再须要，就要开释其界说占用的系统空间，即删除游标。DEALLOCATE  游标名
  

7、游标的使用

［例] 创建一个游标，逐行显示选修了《计算机原理》课程的学生姓名、相应成绩和该课程的均匀分。
①选修《计算机原理》课程的同砚可能不止一个，须要使用
游标查询选修该门课程的学生姓名和相应的选课成绩。
界说游标为：

1. DECLARE myCur CURSOR FOR
2. SELECT studentName, score
3. FROM Student a, Course b, Score C
4. WHERE a.studentNo=c.studentNo AND b.courseNo=c.courseNo
5. ANDcourseName='计算机原理'

复制代码

②要得到该课程的均匀分，必须首先计算选课人数和总分
•界说计数器和累加器变量@countScore、@sumScore，初始值为0

1. DECLARE @countScore smallint,@sumScore int
2. SET @countScore=0
3. SET @sumScore=0

复制代码

3、界说两个变量@sName 和 @score，用于吸收游标集中当前游标中的学生姓名和相应的选课成绩

1. DECLARE @sName varchar(20), @score tinyint

复制代码

4、由于 FETCH 下令每次仅从游标集中提取一条记录，必须通过一个循环来重复提取，直到游标集中的全部记录被提取

• 全局变量@@FETCHL_STATUS 用于判定是否精确地从游标集中提取到了记录；
  
• @@FETCH_STATUS=0 表示已经精确提取到了游标记录；
  循环语句为：
  1. WHILE (@@FETCH_STATUS=0）

  复制代码

⑤ 在循环体内：
•首先显示所提取到的学生姓名和相应的选课成绩，使用语句：

1. SELECT @sName 学生姓名，@score 课程成绩

复制代码

其次，计数器 @countScore 举行计数，并将提取到的成绩累加到变量 @sumScore 中。语句为：

1. SET@sumScore=@sumScore+@score
2. SET @countScore=@countScore+1

复制代码

• -计算总分
  
• - 计算选课人数
  

提取下一条游标记录：

1. FETCH myCur INTO @sName, @score

复制代码

•重复⑤，直到全部游标记录处理
 ⑥处理完全部游标记录后：
关闭和开释游标
•对计数器@countscore举行判定：
如果为0，表示没有同砚选修，其均匀分为0；
香则，均匀分等于总分除以选课人数。
⑦程序如下：
/*界说变量及赋初值*/ DECLARE @sName varchar(20), @score tinyint
DECLARE@sumScore int, @countScore smallint
SET @sumScore=0
SET@countScore=0
--界说游标
DECLARE myCur CURSOR FOR
SELECT studentName, score
FROM Student a, Course b, Score c
WHERE a.studentNo=c.studentNo AND b.courseNo=c.courseNo
ANDcourseName='计算机原理'
OPEN myCur
-打开游标
--获取当前游标的值放到变量@sName和@score中
FETCH myCur INTO @sName, @score
WHILE (@@FETCH_STATUS=0)
BEGIN
-显示变量@sName和@score中的值
SELECT@sName学生姓名，@score课程成绩
SET@sumScore=@sumScore+@score
--计算总分
SET @countScore=@countScore+1
--计算选课人数
FETCH myCur INTO@sName, @score
--获取下一个游标值
END
IF @countScore>0
SELECT@sumScore/@countscore课程均匀分
ELSE
SELECT0.00课程均匀分
CLOSE myCur
-关闭游标
DEALLOCATE myCur
-开释游标
二、存储过程

1、存储过程概述

•存储过程和函数一样，是命名的语句块，它们被编译后保存在数据库中，可反复调用，并在 DBMS上得到执行。
存储过程的优点

• 经编译和优化后存储在数据库服务器中，运行效率高
  
• 有利于集中控制
  
• 实现一定程度的安全性保护
  
• 存储过程存放在数据库中，且在服务器端运行；
  对于不答应用户直接操作的表或视图，可通过调用存储过程来间接地访问这些表或视图，达到一定程度的安全性；
  
• 这种安全性缘于用户对存储过程只有执行权限，没有查察权限；
  
• 拥有存储过程的执行权限，主动获取了存储过程中对相应表或视图的操作权限；
  
• 这些操作权限仅能通过执行存储过程来实现，一旦脱离存储过程，也就失去了相应操作权限。留意：对存储过程只需授予执行权限，不需授予表或视图的操作权限
  
• 特别得当统计和查询操作
  
• 一样平常统计和查询，尤其是期末统计，往往涉及数据量大、表多，若在客户端实现，数据流量和网络通信量较大；
  许多环境下，管理信息系统的设计者，将复杂的查询和统计用存储过程来实现，免去客户端的大量编程
  
• 淘汰网络通信量：存储过程仅在服务器端执行，客户端只吸收结果；
  
• 由于存储过程与数据一样平常在一个服务器中，可淘汰大量的网络通信量。
  
• 使用存储过程前，首先要创建存储过程。可对存储过程举行修改和删除。
  
• 创建存储过程后，必须对存储过程授予执行 EXECUTE 的权限，否则该存储过程仅可以供创建者执行。
  

2、存储过程的创建

• 存储过程的创建
  
• 1. CREATE｛PROC|PROCEDURE｝过程名［｛@参数1 数据类型，｝
  2. ［OUT | OUTPUT ］1/*定义参数的属性*/
  4. ［...n］AS 语句块/* 定义所需要执行的操作*/

  复制代码

• 存储过程可不带参数，参数可以是变量、常量和表达式；
  
• [OUT |OUTPUT］：表示参数为输出参数，输出参数可以从存储过程返回信息。缺省时表示输入参数
  
• 如果存储过程的输出参数取集合值，则该输出参数不在存储过程的参数中界说，而是在存储过程中界说一个临时表来存储该集合值。
  
• 临时表的表名前加一个#符号，如#myTemp
  
• 在存储过程尾部，使用语句：
  SELECT * FROM #myTemp
  将结果集合返回给调用者。
  
• 存储过程结束后，临时表主动被删
  
• 留意：
  
• 用户界说的存储过程只能在当前数据库中创建；
  
• 一个存储过程最大不能超过128MB。若超过128MB，可将超出的部门编写为另一个存储过程，然后在存储过程中调用 
  

例：创建一个存储过程，查询学号为“2011001”的学生的选课信息

1. CREATE PROCEDURE class_studied_nopar
2. AS
3. SELECT *
4. FROM SC
5. WHERE Sno= '2011001';

复制代码

例：创建一个存储过程，查询用户指定学号的学生选课信息

1. CREATE PROCEDURE class_studied_par @sno varchar(7)
2. AS
3. SELECT *
4. FROM SC
5. WHERE Sno = @sno;

复制代码

例：创建一个存储过程，查询并返回用户指定学号的学生的均匀成绩

1. CREATE PROCEDURE studentaveragescore @sno varchar(7) , @avg_score decimal output
2. AS
3. SELECT @avg_score = avg(grade)
4. FROM SC
5. GROUP BY sno
6. HAVING sno = @sno

复制代码

3、存储过程的应用与实践 

 例：输入某个同砚的学号，统计该同砚的均匀分，并返回该同砚的姓名宁静均分。
 分析：该过程涉及三个参数：

• 一个输入参数，设为 @sNo，用于吸收某同砚的学号；
  
• 两个输出参数，用于返回查询到的同砚姓名宁静均分，设为
  @sName #H@avg  
  
• 实现方法一：
  
• 用一个查询，根据输入参数 @sNo，查询出该同砚的姓名并放到输出参数 @sName 中
  

• 由于在学生表中学号是唯一的，使用下令：
  1. SELECT @snName = studentName
  2. FROM Student
  3. WHERE studentNo=@sno

  复制代码
• 用另一个查询，根据输入参数 @sNo，查询出该同砚的选课均匀分并放到输出参数 @avg 中
  
• 由于该同砚的均匀分也只有一个，使用下令：
  1. SELECT @avg=AVG(score)
  2. FROM Score
  3. WHERE studentNo=@sno

  复制代码
  存储过程为：
  
• 1. CREATE PROCEDURE proStudentByNo21 @sNo char(7), @sName
  2. varchar(20) OUTPUT, @avg numeric(5, 1) OUTPUT
  3. AS
  4. BEGIN
  5. --查询同学的姓名放入输出参数@sName中
  6. SELECT @sName=studentName
  7. FROM Student
  8. WHERE studentNo=@sNo
  9. --查询同学选课的平均分放入输出参数@avg中
  10. SELECT@avg=AVG（score）
  11. FROM Score
  12. WHERE studentNo=@sNo
  13. END

  复制代码
• 实现方法二：
  
• 用一个查询，根据输入参数 @sNo，查询出该同砚的姓名并放到输出参数 @sName 中，其下令同方法一
  
• 界说一个游标，根据输入参数@sNo，查询该同砚全部的选课记录，使用下令：
  DECLARE myCur CURSOR FOR SELECT score
  FROM Score
  WHERE studentNo=@sNo
  
• 界说局部变量 @score，用于吸收从游标集中获取的成绩；
  
• 界说局部变量@count，用于统计选课门数；
  
• 界说局部变量 @sum，用于对成绩举行累加
  
• 其存储过程为：
  1. CREATE PROCEDURE proStudentByNo22(@sNo char(7), @sName varchar (20) OUTPUT, @avg numeric(5, 1) OUTPUT)
  2. AS BEGIN
  3. DECLARE @score tinyint, @count tinyint,@sum int
  4. -查找姓名，并放入到输出参数@sName中
  5. SELECT@sName=studentName
  6. FROM Student
  7. WHERE studentNo=@sNo
  8. --变量赋初值
  9. SET@count=0
  10. SET@sum=0
  11. -统计学生选课门数@count和总分@sum，使用游标：
  12. DECLARE myCur CURSOR FOR
  13. SELECT score
  14. FROM Score
  15. WHERE studentNo=@sNo
  16. OPEN myCur
  17. FETCH myCur INTO @score
  18. WHILE (@@FETCH_STATUS=0)
  19. BEGIN
  20. SET @count=@count+1
  21. SET @sum=@sum+@score
  22. CLOSE myCur
  23. DEALLOCATE myCur
  24. IF @count>0
  25. SELECT @avg=@sum/@count
  26. ELSE
  27. SELECT @avg=0
  28. END

  复制代码
  其实就是不使用avg函数然后通过游标遍历score
  

 例：输入某同砚的学号，使用游标统计该同砚的均匀分， 并返回均匀分，同时逐行显示该同砚的姓名、选课名称和选课成绩。

1. CREATE PROCEDURE proStudentAvg{@sNo char(7), @avg numeric(6, 2) OUTPUT)
2. AS
3. BEGIN
4. DECLARE @sName varchar(20), @cName varchar (20)
5. DECLARE @grade tinyint, @sum int, @count tinyint
6. SELECT @sum=0, @count=0
8. -定义、打开、获取游标
9. DECLARE curGrade CURSOR FOR SELECT studentName, courseName, score FROM Score a, Student b, Course c
10. WHERE b.studentNo=@sNo
11. AND a.studentNo=b.studentNo AND a.courseNo=c.courseNo
12. OPEN curGrade
13. FETCH curGrade INTO @sName,@cName,@grade
14. WHILE (@@FETCH_STATUS=0)
15. BEGIN
16. --业务处理
17. SELECT@SName，@cName，@grade --输出
18. SET @sum=@sum+@grade
19. SET @count=@count+1
20. FETCH curGrade INTO @sName, @cName,@grade
21. END

复制代码

• 本例使用了 SELECT 语句来显示变量的值，即
  SELECT @sName, @cName, @grade
  
• 由于存储过程仅在服务器端执行，其显示的内容只在服务器端出现，并不返回给客户端，这样的输出结果是没有价值的。
  
• 显示内容在调试存储过程时有作用，一旦过程调试精确，使用存储过程的修改下令将显示内容
  

当然，存储过程中的游标也可以通过嵌套来达到更强的功能，这里不多分析

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。