【数据库详解】游标与存储过程(代码+例题）
一、游标1、游标
[*]一个对表举行操作的SQL语句（如 select）通常都可产生或处理一组记录，但是许多应用不能把整个结果集作为一个单位来处理，以是就须要一种机制来保证每次处理结果此中的一行或几行，游标（cursor）就提供了这种机制。
[*]SQL Server 通过游标提供了对一个结果集举行逐行处理的能力，游标可看做一种特别的指针，它与某个查询结果相联系，可以指向结果集的任意位置，以便对指定位置的数据举行处理。使用游标可以在查询数据的同时对数据举行处理。
[*]游标是系统为用户开设的一个数据缓冲区，存放SQL语句的结果数据集，每个游标区都有一个名字，通过移动游标名代表的指针来访问数据集中的数据
2、使用游标须要经历五个步调：
[*]界说游标：DECLARE
[*]打开游标：OPEN
[*]逐行提取游标集中的行：FETCH
[*]关闭游标：CLOSE
[*]开释游标：DEALLOCA
3、游标的界说
DECLARE <游标名>［SCROLL] CURSOR
FOR<SELECT语句>
SCROLL：说明所声明的游标可从前滚，后滚，可使用全部的提取选项。如省略，则只能使用 NEXT 提取选项。
READ ONLY 表示当前游标集中的元组仅可以查询，不能修改；
UPDATE ｛OF <列名>｝表示可以对当前游标集中的元组举行更新操作。如果有OF <列名>，表示仅可以对游标集中指定的属性
如：界说一个能够存放sc表数据的游标
Declare cur_sc cursor 4、游标的打开
游标界说后，如果要使用游标，必须先打开游标。
打开游标操作表示：
[*]系统按照游标的界说从数据库中将数据检索出来，放在内存的游标集中（如果内存不够，会放在临时数据库中）
[*]为游标集指定一个游标，该游标指向游标集中的第1个元组
格式：Open 游标名；
Open cur_sc; 打开游标后，可以使用全局变量@@CURSOR_ROWS查察游 标集中数据行的数目。全局变量@@CURSOR_ROWS中保存着最后打开的游标中的数据行数。当其值为0时，表示没有游标打开； 当其值为m（m为正整数）时，游标已被完全填充，m是游标中的数据行数。
例：界说游标XS_CUR3，然后打开该游标，输出其行数。
DECLARE XS_CUR3 CURSOR SCROLL
FOR
SELECT 学号,姓名,总学分
FROM XSB
FOR UPDATE OF 总学分
OPEN XS CUR3
SELECT '游标XS_CUR3数据行数'=@@CURSOR_ROWS 5、游标的读取
[*]FETCH [ NEXT | PRIOR | FIRST | LAST | ABSOLUTE {n| @nvar | RELATIVE {n|@nvar] FROM {游标}
[*]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中提取数据。设该游标已经声明并打开。
FETCH NEXT FROM XS_CUR1 FETCH语句的执行状态保存在全局变量 @@FETCH_STATUS中，其值为0表示上一个FETCH执行 成功；为-1表示所要读取的行不在结果集中；为2表示被提取的行已不存在（已被删除）。
比方，接着上例继承执行如下语句：(此时游标在倒数第三行）
FETCH RELATIVE 3 FROM XS_CUR2
SELECT'FETCH执行情况'= @@FETCH_STATUS 执行结果为：-1，此时游标已出界
6、游标的关闭
[*]关闭游标 CLOSE 游标
[*]开释游标：游标关闭后，其界说仍在，须要时可用OPEN语句打开它再使用。若确认游标不再须要，就要开释其界说占用的系统空间，即删除游标。DEALLOCATE 游标名
7、游标的使用
［例] 创建一个游标，逐行显示选修了《计算机原理》课程的学生姓名、相应成绩和该课程的均匀分。
①选修《计算机原理》课程的同砚可能不止一个，须要使用
游标查询选修该门课程的学生姓名和相应的选课成绩。
界说游标为：
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='计算机原理' ②要得到该课程的均匀分，必须首先计算选课人数和总分
•界说计数器和累加器变量@countScore、@sumScore，初始值为0
DECLARE @countScore smallint,@sumScore int
SET @countScore=0
SET @sumScore=0 3、界说两个变量@sName 和 @score，用于吸收游标集中当前游标中的学生姓名和相应的选课成绩
DECLARE @sName varchar(20), @score tinyint 4、由于 FETCH 下令每次仅从游标集中提取一条记录，必须通过一个循环来重复提取，直到游标集中的全部记录被提取
[*]全局变量@@FETCHL_STATUS 用于判定是否精确地从游标集中提取到了记录；
[*]@@FETCH_STATUS=0 表示已经精确提取到了游标记录；
循环语句为： WHILE (@@FETCH_STATUS=0）
⑤ 在循环体内：
•首先显示所提取到的学生姓名和相应的选课成绩，使用语句：
SELECT @sName 学生姓名，@score 课程成绩 其次，计数器 @countScore 举行计数，并将提取到的成绩累加到变量 @sumScore 中。语句为：
SET@sumScore=@sumScore+@score
SET @countScore=@countScore+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、存储过程的创建
[*]存储过程的创建
[*] CREATE｛PROC|PROCEDURE｝过程名［｛@参数1 数据类型，｝
［OUT | OUTPUT ］1/*定义参数的属性*/
［...n］AS 语句块/* 定义所需要执行的操作*/
[*]存储过程可不带参数，参数可以是变量、常量和表达式；
[*][OUT |OUTPUT］：表示参数为输出参数，输出参数可以从存储过程返回信息。缺省时表示输入参数
[*]如果存储过程的输出参数取集合值，则该输出参数不在存储过程的参数中界说，而是在存储过程中界说一个临时表来存储该集合值。
[*]临时表的表名前加一个#符号，如#myTemp
[*]在存储过程尾部，使用语句：
SELECT * FROM #myTemp
将结果集合返回给调用者。
[*]存储过程结束后，临时表主动被删
[*]留意：
[*]用户界说的存储过程只能在当前数据库中创建；
[*]一个存储过程最大不能超过128MB。若超过128MB，可将超出的部门编写为另一个存储过程，然后在存储过程中调用
例：创建一个存储过程，查询学号为“2011001”的学生的选课信息
CREATE PROCEDURE class_studied_nopar
AS
SELECT *
FROM SC
WHERE Sno= '2011001'; 例：创建一个存储过程，查询用户指定学号的学生选课信息
CREATE PROCEDURE class_studied_par @sno varchar(7)
AS
SELECT *
FROM SC
WHERE Sno = @sno; 例：创建一个存储过程，查询并返回用户指定学号的学生的均匀成绩
CREATE PROCEDURE studentaveragescore @sno varchar(7) , @avg_score decimal output
AS
SELECT @avg_score = avg(grade)
FROM SC
GROUP BY sno
HAVING sno = @sno 3、存储过程的应用与实践
例：输入某个同砚的学号，统计该同砚的均匀分，并返回该同砚的姓名宁静均分。
分析：该过程涉及三个参数：
[*]一个输入参数，设为 @sNo，用于吸收某同砚的学号；
[*]两个输出参数，用于返回查询到的同砚姓名宁静均分，设为
@sName #H@avg
[*]实现方法一：
[*]用一个查询，根据输入参数 @sNo，查询出该同砚的姓名并放到输出参数 @sName 中
[*]由于在学生表中学号是唯一的，使用下令： SELECT @snName = studentName
FROM Student
WHERE studentNo=@sno
[*]用另一个查询，根据输入参数 @sNo，查询出该同砚的选课均匀分并放到输出参数 @avg 中
[*]由于该同砚的均匀分也只有一个，使用下令： SELECT @avg=AVG(score)
FROM Score
WHERE studentNo=@sno 存储过程为：
[*] CREATE PROCEDURE proStudentByNo21 @sNo char(7), @sName
varchar(20) OUTPUT, @avg numeric(5, 1) OUTPUT
AS
BEGIN
--查询同学的姓名放入输出参数@sName中
SELECT @sName=studentName
FROM Student
WHERE studentNo=@sNo
--查询同学选课的平均分放入输出参数@avg中
SELECT@avg=AVG（score）
FROM Score
WHERE studentNo=@sNo
END
[*]实现方法二：
[*]用一个查询，根据输入参数 @sNo，查询出该同砚的姓名并放到输出参数 @sName 中，其下令同方法一
[*]界说一个游标，根据输入参数@sNo，查询该同砚全部的选课记录，使用下令：
DECLARE myCur CURSOR FOR SELECT score
FROM Score
WHERE studentNo=@sNo
[*]界说局部变量 @score，用于吸收从游标集中获取的成绩；
[*]界说局部变量@count，用于统计选课门数；
[*]界说局部变量 @sum，用于对成绩举行累加
[*]其存储过程为： CREATE PROCEDURE proStudentByNo22(@sNo char(7), @sName varchar (20) OUTPUT, @avg numeric(5, 1) OUTPUT)
AS BEGIN
DECLARE @score tinyint, @count tinyint,@sum int
-查找姓名，并放入到输出参数@sName中
SELECT@sName=studentName
FROM Student
WHERE studentNo=@sNo
--变量赋初值
SET@count=0
SET@sum=0
-统计学生选课门数@count和总分@sum，使用游标：
DECLARE myCur CURSOR FOR
SELECT score
FROM Score
WHERE studentNo=@sNo
OPEN myCur
FETCH myCur INTO @score
WHILE (@@FETCH_STATUS=0)
BEGIN
SET @count=@count+1
SET @sum=@sum+@score
CLOSE myCur
DEALLOCATE myCur
IF @count>0
SELECT @avg=@sum/@count
ELSE
SELECT @avg=0
END 其实就是不使用avg函数然后通过游标遍历score
例：输入某同砚的学号，使用游标统计该同砚的均匀分， 并返回均匀分，同时逐行显示该同砚的姓名、选课名称和选课成绩。
CREATE PROCEDURE proStudentAvg{@sNo char(7), @avg numeric(6, 2) OUTPUT)
AS
BEGIN
DECLARE @sName varchar(20), @cName varchar (20)
DECLARE @grade tinyint, @sum int, @count tinyint
SELECT @sum=0, @count=0
-定义、打开、获取游标
DECLARE curGrade CURSOR FOR SELECT studentName, courseName, score FROM Score a, Student b, Course c
WHERE b.studentNo=@sNo
AND a.studentNo=b.studentNo AND a.courseNo=c.courseNo
OPEN curGrade
FETCH curGrade INTO @sName,@cName,@grade
WHILE (@@FETCH_STATUS=0)
BEGIN
--业务处理
SELECT@SName，@cName，@grade --输出
SET @sum=@sum+@grade
SET @count=@count+1
FETCH curGrade INTO @sName, @cName,@grade
END
[*]本例使用了 SELECT 语句来显示变量的值，即
SELECT @sName, @cName, @grade
[*]由于存储过程仅在服务器端执行，其显示的内容只在服务器端出现，并不返回给客户端，这样的输出结果是没有价值的。
[*]显示内容在调试存储过程时有作用，一旦过程调试精确，使用存储过程的修改下令将显示内容
当然，存储过程中的游标也可以通过嵌套来达到更强的功能，这里不多分析
页:
[1]