存储过程与游标的综合运用举例说明

亚米鸭的故事汇
256 阅读3分钟

携手创作，共同成长！这是我参与「掘金日新计划 · 8 月更文挑战」的第16天，点击查看活动详情

存储过程

定义

存储过程是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。

简单来说就是专门做某一件事的sql语句，感觉和c语言，java里面方法差不多。调用这段sql，需要数据库调用或者我们java程序调用。

他的关键字是procedure

创建存储过程，其关键语法如下：

CREATE PROCEDURE 存储过程名([IN|OUT|INOUT] 参数名 数据类型)
BEGIN
……
END

为什么使用

1.提高效率

存储过程编译一次后，就会存到数据库，每次调用时都直接执行。而普通的sql语句我们要保存到其他地方（例如：记事本 上），都要先分析编译才会执行。所以想对而言存储过程效率更高。

2.降低网络流量

存储过程编译好会放在数据库，我们在远程调用时，不会传输大量的字符串类型的sql语句。

3.复用性高

存储过程往往是针对一个特定的功能编写的，当再需要完成这个特定的功能时，可以再次调用该存储过程。

4.可维护性高

当功能要求发生小的变化时，修改之前的存储过程比较容易，花费精力少。

5.安全性高

完成某个特定功能的存储过程一般只有特定的用户可以使用，具有使用身份限制，更安全

游标

定义

游标（Cursor）是处理数据的一种方法，为了查看或者处理结果集中的数据，游标提供了在结果集中一次一行或者多行前进或向后浏览数据的能力。基本信息把游标当作一个指针，它可以指定结果中的任何位置，然后允许用户对指定位置的数据进行处理

作用

1.遍历数据行；
2.保存查询结果，方便下文调用。概念中提到使用游标会保存数据行的副本，那么创建游标后，下文查询即可从副本中查询，要比直接查数据库快很多。

综合实例

问题： 编写存储过程，完成以下功能：

1、  统计“离散数学”课程的成绩分布情况，即按照各分数段来统计人数；

2、  统计任意一门课程的平均成绩；

3、  将学生选课成绩从百分制改为等级制（即A、B、C、D、E）。

统计“离散数学”课程的成绩分布情况

分析：需要另外新建一个表，存储等级制后结果，然后从数据库里面查询课程是离散数学的元组，把他们的分数按照等级制度更改，最后把新等级插入等级表。

创建需要的表结构
create table Rank(
division char(20),
number int);
编写存储过程
create procedure proStatistiMark(@sName char(50))
as 
   begin
        declare @less60 int,@b60a70 int,@b70a80 int,@b80a90 int,@more90 int,@curcno char(4)
        SET @less60=0
        SET @b60a70=0
        SET @b70a80=0
        SET @b80a90=0
        SET @more90=0
        SET @curcno=''
        
        select @curcno=CNO
        from Course
        where CNAME=@sName
        IF @curcno=''
        SELECT '输入错误，不存在此用户。'
        
        SELECT  @less60=count(*)
        FROM SC
        WHERE CNO=@curcno AND GRADE<60
        SELECT  @b60a70=count(*)
        FROM SC
        WHERE CNO=@curcno AND GRADE>=60 AND GRADE<70
        SELECT @b70a80=count(*)
        FROM SC
        WHERE CNO=@curcno AND GRADE>=70 AND GRADE<80
        SELECT @b80a90=count(*)
        FROM SC
        WHERE CNO=@curcno AND GRADE>=80 AND GRADE<90
        SELECT @more90=count(*)
        FROM SC
        WHERE CNO=@curcno AND GRADE>=90
        DELETE FROM Rank    

 

     INSERT INTO Rank VALUES('[0,60)',@less60)

     INSERT INTO Rank VALUES('[60,70)',@b60a70)

     INSERT INTO Rank VALUES('[70,80)',@b70a80)

     INSERT INTO Rank VALUES('[80,90)',@b80a90)

     INSERT INTO Rank VALUES('[90,100]',@more90)
 END
执行

execute 存储过程名字（存储过程参数）； EXECUTE proStatisticMark '离散数学' 然后在表RANK中查看执行结果。

SELECT *

FROM Rank

avatar
文章
阅读
粉丝