第九章 存储过程
9.1 游标的使用
使用游标(CURSOR)在需要一行一行处理时,游标十分有用。游标可以打开一个结果集合(按照指定的标准选择的行),并提供在结果集中一行一行处理的功能。基于游标的类型,可以对其进行回滚或者前进。
游标的声明
用DECLARE语句对游标进行声明,有两种方法可以指定一个游标。
SQL-92 语法
DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR
FOR select_statement
[ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ]
Transact-SQL 扩展语法
DECLARE cursor_name CURSOR
[ LOCAL | GLOBAL ]
[ FORWARD_ONLY | SCROLL ]
[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
[ TYPE_WARNING ]
FOR select_statement [ FOR UPDATE [ OF column_name [ ,...n ] ] ]
--例程9.1:定义一个游标,用于生成学生信息表中的所有记录。
DECLARE student_cursor CURSOR
FOR SELECT * FROM student
打开游标
打开游标就是创建结果集,执行游标定义时指定的 Transact-SQL 语句填充游标。语法如下:
OPEN { { [ GLOBAL ] cursor_name } | cursor_variable_name }
使用游标读取数据
在从游标中读取数据的过程中,可以在结果集中的每一行上来回移动和处理。如果游标定义成了可滚动的(在声明时使用SCROLL关键字),则任何时候都可取出结果集中的任意行。对于非滚动的游标,只能对当前行的下一行实施取操作。结果集可以取到局部变量中。Fetch命令的语法如下:
FETCH [NEXT | PRIOR| FIRST | LAST | ABSOLUTE {n | @nvar} | RELATIVE {n | @nvar}]
FROM [GLOBAL] cursor_name} | cursor_variable_name}
[INTO @variable_name ][,……n]]
--例程9.3:定义一个游标,返回学生信息表中所有的数据,打开游标,然后遍历学生信息表,
--直到找到学生名称为“张三丰”的记录为止,并且打印学生学号和学生名称。
/*定义游标*/
DECLARE student_cursor CURSOR
FOR SELECT sno,sname FROM student
DECLARE @ID char(10), @Name char(30)
OPEN student_cursor
FETCH NEXT FROM student_cursor INTO @ID, @Name
/*while循环*/
WHILE @@fetch_status = 0
BEGIN
IF @Name = '张三丰'
BEGIN
PRINT '找到张三丰'
PRINT @ID+@Name
BREAK
END
FETCH NEXT FROM student_cursor INTO @ID, @Name
END
/*关闭释放游标*/
CLOSE student_cursor
DEALLOCATE student_cursor
关闭和释放游标
游标使用之后,要及时对它进行关闭和释放操作。CLOSE语句用来关闭游标并释放结果集。游标关闭之后,不能再执行FETCH操作。如果还需要使用FETCH语句,则要重新打开游标。语法如下:
CLOSE [GLOBAL] cursor_name | cursor_variable_name
游标确实不再需要之后,要释放游标。DEALLOCATE语句释放数据结构和游标所加的锁。语法如下:
DEALLOCATE [GLOBAL] cursor_name | cursor_variable_name
9.2 存储过程的创建与管理
存储过程是一段在服务器上执行的程序,它在服务器端对数据库记录进行处理,再把结果返回到客户端。
包括系统存储过程和用户存储过程
系统存储过程中又分为一般系统存储过程和扩展存储过程。
9.2.2 使用系统存储过程管理SQL SERVER
存储过程分为两类,系统存储过程和用户自定义存储过程,SQL SERVER提供了大量的系统存储过程,用于管理SQL SERVER并显示有关数据库和用户的信息。
例程9.5:用系统存储过程列出当前SQL SERVER实例中的所有数据库以及数据库的大小,用sp_databases。
EXEC sp_databases
例程9.6:用系统存储过程列出当前数据中所有可以访问的表,用sp_tables
EXEC sp_tables
9.2.3 使用扩展存储过程
扩展存储过程是系统存储过程的一种,提供从 SQL Server 到外部程序的接口,以便进行各种维护活动。扩展存储过程的前缀是xp_
例程9.7:用扩展存储过程显示当前目录下的全部文件,可以用xp_cmdshell实现。 EXEC master..xp_cmdshell ‘dir *.*’
*9.2.4 定义存储过程
定义存储过程
可以用CREATE PROCEDURE语句来定义存储过程,语法如下:
CREATE PROC [ EDURE ] procedure_name [ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]
[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
[ FOR REPLICATION ]
AS sql_statement [ ...n ]
procedure_name
新存储过程的名称。过程名必须符合标识符规则,且对于数据库及其所有者必须唯一。要创建局部临时过程,可以在 procedure_name 前面加一个编号符 (#procedure_name),要创建全局临时过程,可以在 procedure_name 前面加两个编号符 (##procedure_name)。完整的名称(包括 # 或 ##)不能超过 128 个字符。指定过程所有者的名称是可选的。
--例程9.9:创建一个存储过程,该存储过程能够实现根据系部的编号查询出系部中
--男生、女生的人数,运行结果如图所示。
USE students
GO
CREATE PROCEDURE proc_countsex @xbbh varchar(4)
AS
BEGIN
SELECT sex as '性别',count(id) as '人数合计'
FROM student
WHERE substring(id,5,2)=@xbbh
GROUP BY sex
END
EXEC proc_countsex '02'
--例程9.10 在students学生数据库中,创建一个存储过程proc_ testOutput,
--要求实现返回成绩在@p1与@p3之间的学生人数,
--其中,@p1与@p3是输入参数,用于指定分数段。
CTEATE PROCEDURE dbo.proc_testOutput
(
@p1 int ,
@p2 int OUTPUT,
@p3 int
)
AS
BEGIN
select @p2 = count(*) from stu where score between @p1 and @p3
RETURN @@rowcount
PRINT @P2
END
--这个存储过程返回两个值,一个是output型参数@p2,调用存储过程后,返回结果存放在@p2中
--另外一个是由return值@@rowcount(语句所影响的行数)
存储过程使用例题



写一个存储过程,按产品金额从高到低的次序,找出占库存总金额70%以上的所有产品,将这些产品的产品基本信息表中的ABC_LEVEL字段赋值为’A’。 例如:如果数据库中内容如右表,则应将产品1001、1003的ABC_LEVEL字段赋值为’A’,因为这两个产品的金额最高,且合计(400+350)〉(1000*70%)。

create proc find_A
as declare @summ numeric(12,2),@summary numeric(12,2),@summ_t numeric(12,2), @pno char(10)
set @summary=0
set @summ_t=0
select @summ=sum(summary) from inventory
declare find_cursor cursor for select pno,summary from inventory order by summary desc
open find_cursor
fetch next from find_cursor into @pno,@summary
while @@fetch_status=0
begin
set @summ_t=@summ_t+@summary
begin
if @summ_t>@summ *0.7
begin
update products set abc_level='A' where pno=@pno
break
end
else
update products set abc_level='A' where pno=@pno
end
fetch next from find_cursor into @pno,@summary
end
close find_cursor
deallocate find_cursor
写一个存储过程,输入参数是学生的学号,计算这个学生成绩最好的三门课的平均成绩,并输出该平均成绩。当输入的学号不存在时,提示一条自定义错误信息
create proc cal_avg @sno char(10)
as
declare @score_avg numeric(3,0),@sid char(10)
select @sid=sno from transcripts where sno=@sno
begin
if @sid is null
begin
print '该学号不存在!'
goto t_over
end
end
select top 3 score into #temp1 from transcripts where sno=@sno order by score desc
select @score_avg=avg(score) from #temp1
print '前三科平均成绩'
print @score_avg
t_over:
例三
创建一个SQL Server存储过程,该存储过程可以统计某几年各个学院参加竞赛获奖的人数(起止年份作为输入参数),并在学院信息表里的备注里给获奖人数最多的前三个学院写上“最佳竞赛组织奖获得单位”。

create proc findschool @startyear date,@endyear date
as declare @ schoolname char(20) , @num numeric(1,0)
set @num=0
declare findcursor cursor for
select schname from student, school, prizeinfo
where student.schid=school.schid
and student.sid= prizeinfo.sid
and pyear between @startyear
and @endyear
group by schname
order by count(student.sid) desc
open findcursor
fetch next from findcursor into @ schoolname
while @@fetch_status=0
begin
set @num =@num +1
begin
if @num >=3
begin
update school set remark=“最佳竞赛组织奖获得单位”
where schname =@ schoolname
break
end
else
update school set remark=“最佳竞赛组织奖获得单位”
where schname =@ schoolname
end
fetch next from findcursor into @ schoolname
end
close findcursor
deallocate findcursor
--另外一种写法:
create proc findschool @startyear date,@endyear date
As
update school
set remark=“最佳竞赛组织奖获得单位”
where schname in
(
select top 3 schname
from student, school, prizeinfo
where student.schid=school.schid
and student.sid= prizeinfo.sid
and pyear between @startyear and @endyear
group by schname
order by count(student.sid) desc
)
Last updated
Was this helpful?