游标是指向私有 SQL 区（private SQL area）的指针，私有 SQL 区中存储着关于 SELECT 或 DML 语句的处理信息。

(1) 隐式游标
隐式游标是指被后台 PL/SQL 创建和管理的会话游标。当你执行一条 SELECT 或 DML 语句的时候，PL/SQL 会打开一个隐式游标。
用户不可以控制隐式游标，但是获取隐式游标的属性。

隐式游标的属性如下：

SQL%ISOPEN 游标是否打开

SQL%FOUND 结果集中还有行数

SQL%NOTFOUND 结果集中没有行数

SQL%ROWCOUNT 执行的行数

SQL%BULK_ROWCOUNT FORALL 语句的执行行数

SQL%BULK_EXCEPTIONS 当 FORALL 语句完成时所处理的 FORALL 异常

--例子
DROP TABLE dept_temp;
CREATE TABLE dept_temp AS
SELECT * FROM dept;

CREATE OR REPLACE PROCEDURE p (
deptno NUMBER
) AUTHID DEFINER AS
BEGIN
DELETE FROM dept_temp
WHERE deptno = deptno;

IF SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE (
'Delete succeeded for department number ' || deptno
);
ELSE
DBMS_OUTPUT.PUT_LINE ('No department number ' || deptno);
END IF;
END;

SQL> BEGIN
2 p(40);
3 p(50);
4 END;
5 /
Delete succeeded for department number 40
No department number 50

PL/SQL 过程已成功完成。

(2) 显式游标
显式游标是指被用户创建和管理的会话游标。你必须声明和定义一个显式游标，让它和一个查询相关联。

--例子，循环显式游标中的结果

DECLARE
CURSOR c1 IS
SELECT last_name, job_id FROM hr.employees
WHERE REGEXP_LIKE (job_id, 'S[HT]_CLERK')
ORDER BY last_name;

v_lastname hr.employees.last_name%TYPE; -- variable for last_name
v_jobid hr.employees.job_id%TYPE; -- variable for job_id

CURSOR c2 IS
SELECT * FROM hr.employees
WHERE REGEXP_LIKE (job_id, '[ACADFIMKSA]_M[ANGR]')
ORDER BY job_id;

v_employees hr.employees%ROWTYPE; -- record variable for row of table

BEGIN
OPEN c1;
LOOP -- Fetches 2 columns into variables
FETCH c1 INTO v_lastname, v_jobid;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE( RPAD(v_lastname, 25, ' ') || v_jobid );
END LOOP;
CLOSE c1;
DBMS_OUTPUT.PUT_LINE( '-------------------------------------' );

OPEN c2;
LOOP -- Fetches entire row into the v_employees record
FETCH c2 INTO v_employees;
EXIT WHEN c2%NOTFOUND;
DBMS_OUTPUT.PUT_LINE( RPAD(v_employees.last_name, 25, ' ') ||
v_employees.job_id );
END LOOP;
CLOSE c2;
END;

-------------------------------------
Higgins AC_MGR
Greenberg FI_MGR
Hartstein MK_MAN
Russell SA_MAN
Partners SA_MAN
Errazuriz SA_MAN
Cambrault SA_MAN
Zlotkey SA_MAN

PL/SQL 过程已成功完成。

ITPub博客

Oracle PL/SQL 关于游标的介绍

最新文章