Implicit 和 Explicit Cursors
Cursors
任何sql执行都对应一个Cursor：
- Implicit Cursor
由PL/SQL声明和管理，用于所有的DML和PL/SQL SELECT语句。 - Explicit Cursor
由程序员声明和管理
Explicit Cursor 操作
Ex_Cursor.JPG
Explicit Cursor 控制流
Cursor_control.JPG
Cursor_control_2.JPG
定义、控制和使用 Explicit cursors
定义Cursor
- 语法
CURSOR cursor_name IS
select_statement;
- 实例
DELCARE
CURSOR emp_cursor IS
SELECT employee_id, last_name FROM employees
WHERE department_id = 30;
DECLARE
locid NUMBER := 1700;
CURSOR dept_cursor IS
SELECT * FROM departments
WHERE location_id = locid;
打开 Cursor
DECLARE
CURSOR emp_cursor IS
SELECT employee_id, last_name FROM employees
WHERE department_id = 30;
..
BEGIN
OPEN emp_cursor;
从 Cursor 提取数据：例1
SET SERVEROUTPUT ON
DECLARE
CURSOR emp_cursor IS
SELECT employee_id, last_name FROM employees
WHERE department_id = 30;
empno employees.employee_id%TYPE;
lname employees.last_name%TYPE;
BEGIN
if NOT emp_cursor%ISOPEN then
OPEN emp_cursor;
end if;
FETCH emp_cursor INTO empno, lname;
DBMS_OUTPUT.PUT_LINE( empno ||' '||lname);
...
END;
/
从 Cursor 提取数据：例2
SET SERVEROUTPUT ON
DECLARE
CURSOR emp_cursor IS
SELECT employee_id, last_name FROM employees
WHERE department_id = 30;
empno employees.employee_id%TYPE;
lname employees.last_name%TYPE;
BEGIN
if NOT emp_cursor%ISOPEN then
OPEN emp_cursor;
end if;
LOOP
FETCH emp_cursor INTO empno, lname;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE( empno ||' '||lname);
END LOOP;
...
END;
/
Cursors 和 Records
直接将 cursor 当前活跃数据行 fetch 进入 record 变量。
DECLARE
CURSOR emp_cursor IS
SELECT employee_id, last_name FROM employees
WHERE department_id = 30;
emp_record emp_cursor%ROWTYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO emp_record;
...
Cursor FOR Loops
- 语法
FOR record_name IN cursor_name LOOP
statement1;
statement2;
...
END LOOP;
Cursor FOR Loop 是处理 Explicit Cursor 的快捷方式，它隐式的打开、获取、退出和关闭Cursor。这里，record是隐式声明的。
- 实例
SET SERVEROUTPUT ON
DECLARE
CURSOR emp_cursor IS
SELECT employee_id, last_name FROM employees
WHERE department_id = 30;
BEGIN
FOR emp_record IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE( emp_record.employee_id || ' ' ||emp_record.last_name);
END LOOP;
END;
/
Explicit Cursor 属性
| 属性 | 类型 | 描述 |
|---|---|---|
| %ISOPEN | Boolean | 如果Cursor已经打开，值为TRUE |
| %NOTFOUND | Boolean | 如果最近的一次FETCH操作没有返回一行数据，值为TRUE |
| %FOUND | Boolean | 如果最近的一次FETCH操作返回一行数据，值为TRUE |
| %ROWCOUNT | Number | 值为目前为止返回的数据行数 |
%ISOPEN
只有在Cursor打开的时候，才能FETCH数据。通常，用%ISOPEN来验证Cursor是否已经打开。
- 实例
IF NOT emp_cursor%ISOPEN THEN
OPEN emp_cursor;
END IF;
LOOP
FETCH emp_cursor...
%ROWCOUNT 和 %NOTFOUND
SET SERVEROUTPUT ON
DECLARE
empno employees.employee_id%TYPE;
ename employees.last_name%TYPE;
CURSOR emp_cursor IS
SELECT employee_id, last_name FROM employees
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO empno, ename;
EXIT WHEN emp_cursor%ROWCOUNT > 10 OR emp_cursor%NOTFOUND
DBMS_OUTPUT.PUT_LINE(TO_CHAR(empno) || ' ' || ename);
END LOOP;
CLOSE emp_cursor;
END;
/
在Cursor FOR Loops 使用子查询
在这种情况下不需要定义cursor
SET SERVEROUTPUT ON
BEGIN
FOR emp_record IN (SELECT employee_id, last_name FROM employees WHERE department_id = 30)
LOOP
DBMS_OUTPUT.PUT_LINE( emp_record.employee.employee_id || ' ' emp_record.last_name);
END LOOP;
END;
/
带参数的Cursor
- 语法
CURSOR cursor_name
[(parameter_name datatype, ...)]
IS
select_statement;
OPEN cursor_name(parameter_value, ...);
在cursor打开执行查询的时候，将参数传递给cursor。每次打开一个显式cursor，都可以传入不同的参数集合。
- 实例
SET SERVEROUTPUT ON
BEGIN
CURSOR emp_cursor (deptno NUMBER) IS
SELECT employee_id, last_name
FROM employees
WHERE department_id = deptno;
dept_id NUMBER;
lname VARCHAR2(15);
BEGIN
OPEN emp_cursor(10);
...
CLOSE emp_cursor;
OPEN emp_cursor(20);
- tip
假设你想查询包含dept的参数，但记不清全拼了，可以按照如下方式进行查询
show parameter dept
FOR UPDATE 语句
- 语法
SELECT...
FROM
FOR UPDATE [OF column_referene][NOWAIT | WAIT n];
在访问数据的时候对数据行加锁，以便后继的DML操作。NOWAIT的情况下，如果无法获得锁就报错；WAIT N就是可以等待n秒。加上OF column_reference并不是必须的，但是有利于代码可读性和维护性。
锁的释放和Cursor是否关闭没有关系，需要显式的执行commit。
WHERE CURRENT 语句
- 语法
WHERE CURRENT OF cursor;
使用 WHERE CURRENT OF 语句指向 explicit cursor 则操作当前行：
UPDATE employees
SET salary = ...
WHERE CURRENT OF emp_cursor;
网友评论