[bbk5160]第16集 - Chapter 07-Using 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功能 :方便把大量的数据导入到一个集合里面.

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

 

 

 

posted @ 2013-04-21 12:00  ArcerZhang  阅读(180)  评论(0编辑  收藏  举报