Oracle SQL 学习笔记20 - Cursor 2

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;