游标CURSOR的基本用法_cursor的用法

很多刚刚开始学习PLSQL的人都绕不开一个很重要的东西，游标CURSOR，那么今天我就说说游标都有哪些基本的用法。

1.什么是游标？
游标（cursor）是数据库系统在内存中开设的一个数据缓冲区，存放SQL语句的执行结果，很类似于java中的集合数组之类的。

2.游标类型（oracle有以下两种类型）
（1）显式游标
（2）隐式游标

3.显式游标的使用
（1）声明游标
（2）打开游标
（3）读取数据
（4）关闭游标

具体代码如下，因为公司用的是PostgreSQL数据库，所以我把记录类型定义为 stu_rec students%rowtype;而我今天用ORACLE数据库测试这段代码时老是会报一个类型异常错误，所以我就很纠结，觉得没错啊，后来突然想起来oracle好像不支持 students%rowtype这种写法，所以最后我就改为定义一个记录类型来实现。

DECLARE
--声明游标：分配存储空间，此时游标还没有执行
    CURSOR stu_cur IS
    SELECT
        stu.stu_no,     -- 学号
        stu.name,       -- 姓名
        stu.age,        -- 年龄
        stu.sex,        -- 性别
        stu.course,     -- 课程
        stu.score       -- 成绩
    FROM 
        students stu
    ORDER BY
        stu.stu_no;

    -- 声明记录类型
    TYPE type_stu_rec IS RECORD(
        stu_no  students.stu_no%type,     -- 学号
        name    students.name%type,       -- 姓名
        age     students.age%type,        -- 年龄
        sex     students.sex%type,        -- 性别
        course  students.course%type,     -- 课程
        score   students.score%type       -- 成绩 
    );

    -- 声明记录
    stu_rec type_stu_rec; 

    BEGIN
    
    --打开游标：
    OPEN stu_cur;
        -- LOOP 循环
        LOOP
            -- 循环终止条件
            exit WHEN stu_cur%notfound;
            --提取记录：移动游标取一条记录
            FETCH stu_cur INTO stu_rec;
            -- 输出数据
            DBMS_OUTPUT.PUT_LINE(stu_rec.stu_no);
            DBMS_OUTPUT.PUT_LINE(stu_rec.name);
            DBMS_OUTPUT.PUT_LINE(stu_rec.age);
            DBMS_OUTPUT.PUT_LINE(stu_rec.sex);
            DBMS_OUTPUT.PUT_LINE(stu_rec.course);
            DBMS_OUTPUT.PUT_LINE(stu_rec.score);
        -- 结束循环
        END LOOP;
    --关闭游标：将游标放入缓冲池中，没有完全释放资源，可重新打开。
    CLOSE stu_cur;
-- 结束执行
END;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52

以上就是显式游标的基本用法，有人还问我两层嵌套，我想说和loop基本一样，打开之后loop两次就好。

4.隐式游标的使用
（1）声明游标
（2）读取数据
隐式游标没有打开游标，关闭游标的操作，但并不是不打开和不关闭，而是系统替我们简化了这部分操作。

DECLARE
--声明游标：分配存储空间，此时游标还没有执行
    CURSOR stu_cur IS
    SELECT
        stu.stu_no,     -- 学号
        stu.name,       -- 姓名
        stu.age,        -- 年龄
        stu.sex,        -- 性别
        stu.course,     -- 课程
        stu.score       -- 成绩
    FROM 
        students stu
    ORDER BY
        stu.stu_no;

    -- 声明记录类型
    TYPE type_stu_rec IS RECORD(
        stu_no  students.stu_no%type,     -- 学号
        name    students.name%type,       -- 姓名
        age     students.age%type,        -- 年龄
        sex     students.sex%type,        -- 性别
        course  students.course%type,     -- 课程
        score   students.score%type       -- 成绩 
    );

    -- 声明记录
    stu_rec type_stu_rec; 

    BEGIN
    FOR stu_rec IN stu_cur LOOP
            -- 输出数据
            DBMS_OUTPUT.PUT_LINE(stu_rec.stu_no);
            DBMS_OUTPUT.PUT_LINE(stu_rec.name);
            DBMS_OUTPUT.PUT_LINE(stu_rec.age);
            DBMS_OUTPUT.PUT_LINE(stu_rec.sex);
            DBMS_OUTPUT.PUT_LINE(stu_rec.course);
            DBMS_OUTPUT.PUT_LINE(stu_rec.score);
    END LOOP;
END;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

从工作的这几年工作中发现相比显式游标，隐式游标的使用频率更高，但是面试的时候问的显式游标的问题要比隐式游标多的多，而且绝对跳不过这个话题。

5.补充点：
上面的隐式游标和显式游标统称为静态游标，还有一种动态游标（REF CURSOR）顾名思义就是可以动态的打开。代码如下（当系统时间是单数和复数的时候执行不同的SQL, 取得不同的成绩）

DECLARE
-- 声明REF游标类型
TYPE type_ref_cur IS REF CURSOR;
-- 声明REF游标
ref_cur type_ref_cur ;

-- 声明记录
v_score number;

BEGIN
-- 根据条件判断，动态的打开游标
IF(MOD(TO_NUMBER(TO_CHAR(SYSDATE,'dd')),2) = 1) THEN
    OPEN ref_cur FOR SELECT T.SCORE FROM STUDENTS T;
ELSE
    OPEN ref_cur FOR SELECT T.SCORE FROM  TEST T;
END IF;
    -- LOOP 循环
    LOOP
    -- 循环终止条件
    exit WHEN ref_cur%notfound;
    --提取记录：移动游标取一条记录
    FETCH ref_cur INTO v_score;
    -- 输出数据
    DBMS_OUTPUT.PUT_LINE(v_score);
    -- 结束循环
    END LOOP;
    --关闭游标：将游标放入缓冲池中，没有完全释放资源，可重新打开。
    CLOSE ref_cur;
-- 结束执行
END;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

6.动态游标和静态游标的区别：
（1）动态游标可以根据逻辑动态打开；而静态游标定义好了就无法修改了。
（2）动态游标可以返回给客户端，静态游标则不行。
（3）静态游标可以是定义为全局变量 ，动态游标则必须定义在过程或函数中。
（4）动态游标可以在子程序间传递，静态游标则不行。
（5）静态游标中定义的静态sql比动态游标效率高，所以动态游标通常用在：向客户端返回结果集。