12.PL_SQL——游标CURSOR

SQL> edit

DECLARE

        CURSORc_emp_cursor IS

                SELECTemployee_id, last_name

                FROMemployees

                WHEREdepartment_id = 30;

        v_empnoemployees.employee_id%TYPE;

        v_lnameemployees.last_name%TYPE;

BEGIN

        OPEN c_emp_cursor;

        -- 1. 打开游标

LOOP

                FETCH c_emp_cursor

                INTOv_empno, v_lname;

               -- 2. 取数据

EXIT WHENc_emp_cursor%NOTFOUND;

                                       -- 3. 跳出循环

               DBMS_OUTPUT.PUT_LINE(v_empno || ‘ ‘ || v_lname);

         END LOOP;

         CLOSE c_emp_cursor;

                        --4. 关闭游标

END;

/

SQL> @notes/s52.sql

114 Raphaely

115 Khoo

116 Baida

117 Tobias

118 Himuro

119 Colmenares

PL/SQL procedure successfully completed.

===================Example1——Records=====================

SQL> edit

DECLARE

    CURSOR e IS

    SELECT * FROMemployees;

    emprec e%ROWTYPE;

BEGIN

    OPEN e;

    LOOP

        FETCH e INTOemprec;

        EXIT WHENe%NOTFOUND;

       DBMS_OUTPUT.PUT_LINE(‘First Name ==> ‘ || emprec.first_name);

    END LOOP;

    CLOSE e;

END;

/

SQL> @notes/s53.sql

First Name ==> Donald

First Name ==> Douglas

First Name ==> Jennifer

First Name ==> Michael

First Name ==> Pat

...

First Name ==> Vance

First Name ==> Alana

First Name ==> Kevin

PL/SQL procedure successfully completed.

SQL> edit

DECLARE

    CURSORc_emp_cursor IS

        SELECTemployee_id, last_name

        FROM employees

        WHEREdepartment_id = 30;

        v_emp_recordc_emp_cursor%ROWTYPE;

BEGIN

    OPEN c_emp_cursor;

    LOOP

        FETCHc_emp_cursor

        INTO v_emp_record;

        EXIT WHENc_emp_cursor%NOTFOUND;

       DBMS_OUTPUT.PUT_LINE(v_emp_record.employee_id || ‘ ‘ ||v_emp_record.last_name);

    END LOOP;

    CLOSEc_emp_cursor;

END;

/

SQL> @notes/s54.sql

114 Raphaely

115 Khoo

116 Baida

117 Tobias

118 Himuro

119 Colmenares

PL/SQL proceduresuccessfully completed

SQL> edit

DECLARE

    CURSORc_emp_cursor IS

    SELECTemployee_id, last_name

    FROM employees

    WHEREdepartment_id = 30;

BEGIN

    FOR emp_record INc_emp_cursor

    LOOP

        DBMS_OUTPUT.PUT_LINE(emp_record.employee_id|| ‘ ‘ ||emp_record.last_name);

       DBMS_OUTPUT.PUT_LINE(‘Rowcount ==>‘ || c_emp_cursor%ROWCOUNT);

    END LOOP;

END;

/

SQL> @notes/s55.sql

114 Raphaely

Rowcount ==>1

115 Khoo

Rowcount ==>2

116 Baida

Rowcount ==>3

117 Tobias

Rowcount ==>4

118 Himuro

Rowcount ==>5

119 Colmenares

Rowcount ==>6

PL/SQL procedure successfully completed.

Cursor FOR LOOPs Using Subqueries——No need to declare the cursor

SQL> edit

BEGIN

    FOR i IN

    (SELECTemployee_id, last_name

    FROM employees

    WHEREdepartment_id = 30)

    LOOP

       DBMS_OUTPUT.PUT_LINE(i.employee_id || ‘ --> ‘ || i.last_name);

    END LOOP;

END;

/

SQL> @notes/s56.sql

114 --> Raphaely

115 --> Khoo

116 --> Baida

117 --> Tobias

118 --> Himuro

119 --> Colmenares

PL/SQL proceduresuccessfully completed

SQL> edit

DECLARE

    CURSORc_emp_cursor (deptno NUMBER) IS

        SELECTemployee_id, last_name

        FROM employees

        WHEREdepartment_id = deptno;

    v_emp_recordc_emp_cursor%ROWTYPE;

BEGIN

    OPEN c_emp_cursor(10);

    LOOP

         FETCHc_emp_cursor

         INTOv_emp_record;

         EXIT WHENc_emp_cursor%NOTFOUND;

        DBMS_OUTPUT.PUT_LINE( v_emp_record.employee_id || ‘ ‘||v_emp_record.last_name);

    END LOOP;

         CLOSEc_emp_cursor;

END;

/

SQL> @notes/s58.sql

200 Whalen

PL/SQL proceduresuccessfully completed

================ Example 1 =====================

SQL> edit

DECLARE

    TYPE emp_type ISTABLE OF employees%ROWTYPE

    INDEX BYPLS_INTEGER;

    l_emp emp_type;

    l_row PLS_INTEGER;

BEGIN

    SELECT * BULKCOLLECT

    INTO l_emp

    FROM employees;

   DBMS_OUTPUT.PUT_LINE(‘The count is: ‘ || l_emp.COUNT);

    l_row :=l_emp.FIRST;

    WHILE (l_row ISNOT NULL)

    LOOP

       DBMS_OUTPUT.PUT_LINE(l_row || ‘: ‘ || l_emp(l_row).employee_id || ‘--> ‘ || l_emp(l_row).first_name);

        l_row :=l_emp.NEXT(l_row);

        END LOOP;

END;

/

SQL> @notes/s60.sql

The count is: 108

1: 198 --> Donald

2: 199 --> Douglas

3: 200 --> Jennifer

4: 201 --> Michael

5: 202 --> Pat

6: 203 --> Susan

================ Example 2 =====================

SQL> edit

DECLARE

    CURSOR e IS SELECT* FROM employees;

    TYPE emp_type ISTABLE OF e%ROWTYPE

    INDEX BYPLS_INTEGER;

    l_emp emp_type;

    l_row PLS_INTEGER;

BEGIN

    OPEN e;

    FETCH e BULKCOLLECT INTO l_emp;

    CLOSE e;

   DBMS_OUTPUT.PUT_LINE(‘The count is: ‘ || l_emp.COUNT);

    l_row :=l_emp.FIRST;

    WHILE (l_row ISNOT NULL)

    LOOP

        DBMS_OUTPUT.PUT_LINE(l_row|| ‘: ‘ || l_emp(l_row).employee_id || ‘ --> ‘ || l_emp(l_row).first_name);

        l_row :=l_emp.NEXT(l_row);

        END LOOP;

END;

/

SQL> @notes/s61.sql

The count is: 108

1: 198 --> Donald

2: 199 --> Douglas

3: 200 --> Jennifer

4: 201 --> Michael

5: 202 --> Pat

本文出自 “重剑无锋 大巧不工” 博客，请务必保留此出处http://wuyelan.blog.51cto.com/6118147/1550348