[bbk5160]第16集 Chapter 07Using Explicit cursors

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 ...
FROM
FOR 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.
• Create and handle explicit cursors
• Use simple loops and cursor FOR loops to handle multiple rows in the cursors
• Evluate cursor status bby using cursor attributes
• Use the FOR UPDATE and WHERE CURRENT FO cluases to update or delete the current fetched row.

BULK COLLECT功能 ：方便把大量的数据导入到一个集合里面.

复合数据类型和游标结合后，就能够构造出比较实用的代码片段.