[bbk5153]第15集 - Chapter 07-Using Explicit cursors

Objectives

After completing this lesson,you should be able to do the following:

• Distinguish between implict and explicit cursors
• Discuss the reasons for using explicti cursors
• Declare and control explicit cursors
• Use simple loops and cursor FOR loops to fetch data
• Declare and use cursors with parameters
• Lock rows with the FOR UPDATE clause
• Reference the current row with the WHERE CURRENT OF clause
• USE BULK COLLECT to retriveve multiple rows of data with a single statement.

Agenda

• What are explicit cursors?
• Using explicit cursors
• Using cursors with parameters
• Locking rows and referencing current row
• Use BULK COLLECT to retrieve multiple rows of data with a single statement

Cursors

Every SQL statement that is executed by the Oracle Server has an associated individual cursor:

• Implicit cursors:declared and managed by PL/SQL for all DML and PL/SQL SELECT statements
• Explicit cursors:declared and managed by the programmer

Explicit Cursor Operations

Controlling Explicit Cursors

Declaring the Cursor

• Syntax:

CURSOR cursor_name IS
    SELECT statement;

• Examples:

DECLARE
        CURSOR c_emp_cursor IS
        SELECT employee_id,last_name FROM employees
        WHERE department_id = 30;

DECLARE
        v_locid NUMBER := 1700;

        CURSOR c_dept_cursor
        IS
        SELECT * FROM dept
        WHERE location_id = v_locid;

Opening the Cursor

DECLARE
        CURSOR c_emp_cursor
        IS
                SELECT employee_id,last_name FROM employees
                WHERE department_id = 30;
        ...
BEGIN

        OPEN c_emp_cursor;

Fetching Data from the Cursor

DECLARE
        --声明游标 c_emp_cursor
        CURSOR c_emp_cursor
        IS
                SELECT employee_id,last_name
                FROM employees
                WHERE department_id = 30;


        --declare variables v_empno and v_lname
        v_empno employees.employee_id%TYPE;
        v_lname employees.last_name%TYPE;

BEGIN

        OPEN c_emp_cursor;

        FETCH c_emp_cursor INTO v_empno,v_lname;

        DBMS_OUTPUT.PUT_LINE(v_empno || '->'  || v_lname);

END;
/

DECLARE
        --声明游标 c_emp_cursor
        CURSOR c_emp_cursor
        IS
                SELECT employee_id,last_name
                FROM employees
                WHERE department_id = 30;


        --declare variables v_empno and v_lname
        v_empno employees.employee_id%TYPE;
        v_lname employees.last_name%TYPE;

BEGIN

        OPEN c_emp_cursor;

        LOOP

                FETCH c_emp_cursor INTO v_empno,v_lname;

                EXIT WHEN c_emp_cursor%NOTFOUND;

                DBMS_OUTPUT.PUT_LINE(v_empno || '->'  || v_lname);

        END LOOP;

        CLOSE c_emp_cursor;
END;
/

Closing the Cursor 

...
        LOOP
                FETCH c_emp_cursor INTO empno,lname;

                EXIT WHEN c_emp_cursor%NOTFOUND;

                DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || v_lname);
        END LOOP;

        CLOSE c_emp_cursro;
END;
/

Cursors and Records

Process the rows of the active set by fetching values into a PL/SQL record.

DECLARE
        CURSOR c_emp_cursor IS
                SELECT employee_id,last_name
                FROM employees
                WHERE department_id = 30;

        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%NOTFOUND;

                DBMS_OUTPUT.PUT_LINE(v_emp_record.employee_id || '--->' || v_emp_record.last_name);
        END LOOP;

        CLOSE c_emp_cursor;
END;
/

基于表锁定和基于游标锁定；