ORACLE游标概念讲解

一、概念解析

1.游标是什么？

游标是一种数据库对象。游标类似于程序中数组的使用。使用游标，编写的PL/SQL程序能够引用一段SQL查询所返回的结果集中的每一行数据。

2.游标的作用是什么？

游标提供了对结果集的对称选取，可以逐行对结果集进行处理。尤其对于需要对结果集进行复杂分析、处理数据的场景，使用游标更加方便。

3.游标的分类？

游标分为显式游标和隐式游标两种。显式游标需进行显式的打开、关闭操作，需要显式的申明游标并绑定SQL语句才能使用；而隐式游标是Oracle隐含在PL/SQL语句中的游标。

二、使用方法

1、游标的声明

游标分为显示游标和隐式游标，下面分别简述：

1) 显式游标

游标的声明格式为：

DECLARE

cursor_name CURSOR [(arguments)]

IS select_statement;

BEGIN

...

END;

其中，游标名cursor_name是用户定义的游标名，将用于对游标的引用。快速定位游标的字段通常与其对应的SELECT语句相关。 例如：

DECLARE

cursor_name CURSOR

IS SELECT column_name(s)

FROM table_name

WHERE conditions;

BEGIN

...

END;

如果要获取表的所有信息，则可以使用SELECT *语句。

下面是一个示例：

DECLARE

cursor_emp CURSOR

IS SELECT * FROM EMPLOYEE;

BEGIN

...

END;

2) 隐式游标

PL/SQL程序中的select语句就是一个隐式游标。

隐式声明格式为：

SELECT列1，列2，列n INTO 变量1，变量2，变量n FROM 表 WHERE 条件;

其中，必须使用INTO子句将数据读入变量，因为不使用INTO子句会导致块变量没有明确定义的目标，从而无法接收数据。

下面是一个示例：

SELECT COUNT(*) INTO cnt FROM EMPLOYEE;

2、游标的使用

游标的使用主要包括游标的打开、定位、提取数据、判断查询结果是否尽头、关闭等几个主要步骤：

1) 游标的打开

在获取数据前，必须打开游标：

OPEN cursor_name;

下面是一个示例：

DECLARE

cursor_emp CURSOR

IS SELECT * FROM EMPLOYEE;

BEGIN

OPEN cursor_emp;

END;

2) 定位

游标的定位指光标所指的行，光标可定位到第一行、最后一行、下一行、上一行或由用户编号指定的行。下面是几个典型的定位：

%ROWTYPE – 自动把查询的结果集对应数据表的行的每一个列给对应到游标指向的变量中；

%TYPE – 当数据表有新的列添加或列类型有变化时, 隐式游标记录时可以自动适应新的数据类型;

%FOUND: 游标指向结果集中至少有一行，则返回TRUE；否则返回FALSE。

%NOTFOUND： 与%FOUND相反，返回游标是否指向结果集中任何行。

%ISOPEN：如果游标已经打开，则返回TRUE；否则返回FALSE。下面是一个示例：

DECLARE

cnt NUMBER;

cursor_emp CURSOR

IS SELECT COUNT(*) cnt FROM EMPLOYEE;

BEGIN

OPEN cursor_emp;

FETCH cursor_emp INTO cnt;

IF cursor_emp%FOUND THEN

DBMS_OUTPUT.PUT_LINE(cnt);

END IF;

CLOSE cursor_emp;

END;

3) 提取数据

定位相当于设置指针，提取数据才是最终目的。这里我们可以使用FETCH语句来取出数据：

FETCH cursor_name INTO variable_list;

下面是一个示例：

DECLARE

v_empno NUMBER;

v_ename VARCHAR2(50);

cursor_emp CURSOR

IS SELECT EMPNO, ENAME FROM EMPLOYEE;

BEGIN

OPEN cursor_emp;

LOOP

FETCH cursor_emp INTO v_empno, v_ename;

EXIT WHEN cursor_emp%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || v_ename);

END LOOP;

CLOSE cursor_emp;

END;

4) 判断查询结果是否尽头

使用游标操作结果集，往往是需要进行循环，所以需要判断是否遍历完整个结果集。这里可以使用EXIT和WHILE语句。下面是一个示例：

DECLARE

cursor_rec SYS_REFCURSOR;

v_empid VARCHAR2(10);

v_ename VARCHAR2(50);

v_job VARCHAR2(20);

BEGIN

cursor_rec:=get_emp_cat_rec();

LOOP

FETCH cursor_rec INTO v_empid, v_ename, v_job;

EXIT WHEN cursor_rec%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(v_empid || ' ' || v_ename || ' ' || v_job);

END LOOP;

CLOSE cursor_rec;

END;

5) 关闭游标

当不再使用游标时，必须显式地关闭它以释放游标占用的资源。下面是一个示例：

DECLARE

cursor_emp CURSOR

IS SELECT * FROM EMPLOYEE;

BEGIN

OPEN cursor_emp;

...

CLOSE cursor_emp;

END;

三、使用案例

1、游标的基本应用

实现一个查询员工信息的功能：

CREATE OR REPLACE PROCEDURE proc_emp_select

IS

v_empno EMPLOYEE.EMPNO%TYPE;

v_ename EMPLOYEE.ENAME%TYPE;

v_job EMPLOYEE.JOB%TYPE;

v_sal EMPLOYEE.SAL%TYPE;

v_deptno EMPLOYEE.DEPTNO%TYPE;

CURSOR csr_emp IS

SELECT EMPNO, ENAME, JOB, SAL, DEPTNO

FROM EMPLOYEE;

BEGIN

OPEN csr_emp;

LOOP

FETCH csr_emp INTO v_empno, v_ename, v_job, v_sal, v_deptno;

EXIT WHEN csr_emp%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(v_empno || ' - ' || v_ename || ' - ' || v_job || ' - ' || v_sal || ' - ' || v_deptno);

END LOOP;

CLOSE csr_emp;

END;

2、游标的高级应用

实现互联网用户自动贷款审核：

CREATE TABLE LoanApply

(

ApplyID int,

UserAccount varchar2(50),

Credit int,

ApplyAmount int,

ApplyDate DATE

) ;

CREATE TABLE LoanLog

(

LogID int,

ApplyID int,

LoanDate DATE,

LoanAmount int

);

2.1 游标申明语句：

CURSOR csrLoanApply IS

SELECT ApplyId, UserAccount, Credit, ApplyAmount, ApplyDate

FROM LoanApply

WHERE Credit > 500 AND ROWNUM < 100

order by ApplyDate asc;

2.2 游标逻辑处理:

set serveroutput on

DECLARE

CURSOR csrLoanApply IS

SELECT ApplyId, UserAccount, Credit, ApplyAmount, ApplyDate

FROM LoanApply

WHERE Credit > 500 AND ROWNUM < 100

order by ApplyDate asc;

iLoanAmount NUMBER(8,2) := 0;

iMoney NUMBER(6,2) := 0;

BEGIN

FOR i IN csrLoanApply LOOP

IF iMoney+i.Applyamount <= 10000 THEN

iMoney:=iMoney+i.Applyamount;

iLoanAmount:=i.Applyamount;

ELSE

iLoanAmount:=NULL;

END IF;

dbms_output.put_line(i.Applyid||' '||i.UserAccount||' '||to_char(i.Applydate,'yyyy-mm-dd')||'申请金额:'||

i.Applyamount||'可贷款金额:'||iLoanAmount||' 可贷总额:'||iMoney);

END LOOP;

END;

2.3 结果：

735 userA 2017-07-01申请金额:2021可贷款金额:2021 可贷总额:2021

736 userB 2017-07-02申请金额:1500可贷款金额:7980 可贷总额:3521

737 userC 2017-07-03申请金额:1120可贷款金额:3920 可贷总额:5021

以上是游标的概念、使用方法和案例分析。通过掌握游标的使用方法，我们可以更好地处理复杂结果集并提高编码效率。同时，需要注意的是，游标是一种比较底层的查询方式，使用时需要注意性能问题。 如果你喜欢我们三七知识分享网站的文章， 欢迎您分享或收藏知识分享网站文章 欢迎您到我们的网站逛逛喔！https://www.37seo.cn/