[bbk5160]第16集 - Chapter 07-Using Explicit cursors

阅读量：5017 次

发布时间：2019-06-12

本文共 7522 字，大约阅读时间需要 25 分钟。

Cursor For Loops

Syntax:

FOR record_name IN cursor_name     LOOP        statement1;        statement2;        ...       END LOOP;

The cursor FOR loop is a shortcut to process explict cursors.

Implict open,fetch,exit and close cursor.

The record is implicit declared.

Example:

DECLARE        CURSOR c_emp_cursor        IS                SELECT employee_id,last_name FROM employees                WHERE department_id = 30;BEGIN        FOR emp_record IN c_emp_cursor                LOOP                        DBMS_OUTPUT.PUT_LINE(emp_record.employee_id || '--->' || emp_record.last_name);                END LOOP;END;/

DECLARE        CURSOR e IS SELECT * FROM emp;BEGIN        FOR i IN e                LOOP                        DBMS_OUTPUT.PUT_LINE('Rowcount ->' || e%ROWCOUNT || ',First Name ==> ' || i.first_name);                END LOOP;END;/

Cursor FOR Loops Using subqueries

There is no need to declare the cursor.

BEGIN        FOR emp_record IN (SELECT employee_id,last_name FROM employees WHERE department_id = 30)                LOOP                        DBMS_OUTPUT.PUT_LINE(emp_record.employee_id || '->' || emp_record.last_name);                END LOOP;END;/

Explicit Cursor Attributes

Use explicit cursor attributes to obtain status infromation about a cursor.

Attribute	Type	Description
%ISOPEN	Boolean	Evaluates to TRUE if the cursor is open
%NOTFOUND	Boolean	Evaluates to TRUE if the most recent fetch does not return a row
%FOUND	Boolean	Evaluates to TRUE if the most recent fetch returns a row;complement of %NOTFOUND
%ROWCOUNT	Number	Evaluates to the total number of rows returned so far

获取游标信息属性，使用方法：在属性前面添加游标名称即可.

%ISOPEN Attribute

You can fetch rows only when the cursor is open

Use the %ISOPEN cursor attribute before performing a fetch to test whether the cursor is open.

Example:

IF NOT c_emp_cursor%ISOPEN THEN        OPEN c_emp_cursor;END IF;LOOP        FETCH c_emp_cursor ...~

%ROWCOUNT and %NOTFOUND:Example

DECLARE        CURSOR c_emp_cursor        IS                SELECT employee_id,last_name FROM employees ORDER BY employee_id ASC;        v_emp_record c_emp_cursor%ROWTYPE;BEGIN        OPEN c_emp_cursor;        LOOP                FETCH c_emp_cursor INTO v_emp_record;                EXIT WHEN c_emp_cursor%ROWCOUNT > 10 OR c_emp_cursor%NOTFOUND;                DBMS_OUTPUT.PUT_LINE('Rowcount:' || c_emp_cursor%ROWCOUNT || ',' || v_emp_record.employee_id || '->' || v_emp_record.last_name);        END LOOP;        CLOSE c_emp_cursor;END;/

Cursor with Parameters

Syntax:

CURSOR cursor_name        [(parameter_name datatype,...)]IS        select_statement;

Pass parameter values to a cursor when the cursor is opened and the query is executed.

Open an explicit cursor several times with a different active set eache time.

OPEN cursor_name(parameter_value,...);

Cursor with Parameters

DECLARE        CURSOR c_emp_cursor        (                deptno NUMBER        )        IS                SELECT employee_id,last_name FROM employees WHERE department_id = deptno;        v_emp_employee_id employees.employee_id%TYPE;        v_emp_last_name   employees.last_name%TYPE;BEGIN        OPEN c_emp_cursor(10);        LOOP                FETCH c_emp_cursor INTO v_emp_employee_id,v_emp_last_name;                EXIT WHEN c_emp_cursor%NOTFOUND;                DBMS_OUTPUT.PUT_LINE('Rowcount:' || c_emp_cursor%ROWCOUNT || ',' || v_emp_employee_id || '->' || v_emp_last_name);        END LOOP;        CLOSE c_emp_cursor;END;/

DECLARE        CURSOR c_emp_cursor        (                deptno NUMBER        )        IS                SELECT employee_id,last_name FROM employees WHERE department_id = deptno;        r c_emp_cursor%ROWTYPE;BEGIN        FOR i IN c_emp_cursor(10)        LOOP                DBMS_OUTPUT.PUT_LINE(i.employee_id || '->'  || i.last_name);        END LOOP;        DBMS_OUTPUT.PUT_LINE('~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~');        FOR i IN c_emp_cursor(20)        LOOP                DBMS_OUTPUT.PUT_LINE(i.employee_id || '->' || i.last_name);        END LOOP;        DBMS_OUTPUT.PUT_LINE('~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~');        OPEN c_emp_cursor(30);        LOOP                FETCH c_emp_cursor INTO r;                EXIT WHEN c_emp_cursor%NOTFOUND;                DBMS_OUTPUT.PUT_LINE(r.employee_id || '->' || r.last_name);        END LOOP;        CLOSE c_emp_cursor;END;/

FOR UPDATE clause

Syntax:

SELECT ...FROMFOR UPDATE [OF clolumn_reference] [NOWAIT | WAIT n]

如果不加NOWAIT，就会一直等待着，直到上一个锁解锁完毕，才会继续执行；如果加NOWAIT，就会立即出错；或者还可以指定等待的时间WAIT n.

Oracle的锁形式:表级锁，整张表都被锁住，别人都无法访问.

　　　　　　行级锁，只锁定某一指定行.(锁的东西越少，并发性越高)

　　　　　　列级锁，只锁定某长表的某个列或者某几列

Use explicit locking to deny access to other sessions for the duration of a transaction.

Lock the rows before the update or delete.

WHERE CURRENT OF Clause

Syntax:

WHERE CURRENT OF cursor;

Use cursors to update or delete the current row.

Include the FOR UPDATE clause in the cursor query to first lock the rows.

Use the WHERE CURRENT OF clause to reference the current row from an explicit cursor.

UPDATE employee    SET salary = ...    WHERE CURRENT OF c_emp_cursor;

BULK COLLECT Clause

Oracle 8i introduced a very powerful new feature that improves the efficiency of query in PL/SQL:The BULK COLLECT clause.

WITH BULK COLLECT you can retrieve multiple rows of data through either an implict or an explicit query with a single roundtrip to and from the database.

BULK COLLECT reduces the number of context switches between the PL/SQL and SQL engines and thereby redures the overhead of retrieving data.

BULK COLLECT INTO collection1,collection2,...

DECLARE        TYPE emp_type IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER;        l_emp emp_type;        l_row PLS_INTEGER;BEGIN        SELECT * BULK COLLECT INTO l_emp FROM employees;        DBMS_OUTPUT.PUT_LINE('The count is:' || l_emp.COUNT);        l_row :=l_emp.FIRST;        WHILE(l_row IS NOT NULL)        LOOP                DBMS_OUTPUT.PUT_LINE(l_row || ':' || l_emp(l_row).employee_id || '-->' || l_emp(l_row).last_name);                l_row := l_emp.NEXT(l_row);        END LOOP;END;/

DECLARE        CURSOR e IS SELECT * FROM employees;        TYPE emp_type IS TABLE OF e%ROWTYPE INDEX BY PLS_INTEGER;        l_emp emp_type;        l_row PLS_INTEGER;BEGIN        OPEN e;                FETCH e BULK COLLECT INTO l_emp;        CLOSE e;        DBMS_OUTPUT.PUT_LINE('The count is :' || l_emp.COUNT);        l_row := l_emp.FIRST;        WHILE(l_row IS NOT NULL)        LOOP                DBMS_OUTPUT.PUT_LINE(l_row || ':' || l_emp(l_row).employee_id || '-->' || l_emp(l_row).first_name || '-->' || l_emp(l_row).last_name);                l_row := l_emp.NEXT(l_row);        END LOOP;END;/

Quiz

Implicit cursors are declared by PL/SQL implicitly for all DML and PL/SQL SELECT statements.The Oracle Server implicityly opens a cursor to process each SQL statement that is not associated with an explicityly declared cursor.

1、True

2、False

Summary

In this lesson,you should have learned to:

Distinguish cursor types:
- -Implicit cursors are used for all DML statements and single-row queries.
- -Explicit cursors are used for queries of zero,one,or more rows.