PLSQL==>>游标

1.记录类型：基于表的（table_based），基于游标（cursor_based），以及程序员定义的（programmer-defined）,为创建一个基于表或者基于游标的记录，可以使用%ROWTYPE属性:

DECLARE
    vr_student student%ROWTYPE;
BEGIN
    SELECT * INTO vr_student FROM student WHERE student_id = 156;
    dbms_output.put_line(vr_student.first_name || ' ' ||
                         vr_student.last_name || ' has and ID of 156');
EXCEPTION
    WHEN no_data_found THEN
        raise_application_error(-2001,
                                'The Student ' || 'is not in the database');
END;

2.处理显示游标：

DECLARE
    CURSOR c_student_name IS
        SELECT first_name, last_name FROM student WHERE rownum <= 5;
    vr_student_name c_student_name%ROWTYPE;
BEGIN
    OPEN c_student_name;
    LOOP
        FETCH c_student_name
            INTO vr_student_name;
        EXIT WHEN c_student_name%NOTFOUND;
        dbms_output.put_line('Student name: ' ||
                             vr_student_name.first_name || ' ' ||
                             vr_student_name.last_name);
    END LOOP;
    CLOSE c_student_name;
END;

3.处理用户定义的记录：

DECLARE
    TYPE instructor_info IS RECORD(
        first_name instructor.first_name%TYPE,
        last_name  instructor.last_name%TYPE,
        sections   NUMBER);
    rv_instructor instructor_info;
BEGIN
    SELECT rtrim(i.first_name), rtrim(i.last_name), COUNT(*)
      INTO rv_instructor
      FROM instructor i, section s
     WHERE i.instructor_id = s.instructor_id
       AND i.instructor_id = 102
     GROUP BY i.first_name, i.last_name;
    dbms_output.put_line('Instructor, ' || rv_instructor.first_name || ' ' ||
                         rv_instructor.last_name || ',teachees ' ||
                         rv_instructor.sections || ' section(s)');
EXCEPTION
    WHEN OTHERS THEN
        dbms_output.put_line('There is no instructor');
END;

游标属性rowcount：

DECLARE
    v_city zipcode.city%TYPE;
BEGIN
    SELECT city INTO v_city FROM zipcode WHERE zip = 07002;
    IF SQL%ROWCOUNT = 1
    THEN
        dbms_output.put_line(v_city || 'has a ' || 'zipcode of 07002');
    ELSIF SQL%ROWCOUNT = 0
    THEN
        dbms_output.put_line('The zipcode 07002 is ' ||
                             ' not in the database');
    ELSE
        dbms_output.put_line('Stop harassing me');
    END IF;
END;

集成所有游标属性：

DECLARE
    v_sid student.student_id%TYPE;
    CURSOR c_student IS
        SELECT student_id FROM student WHERE student_id < 200;
BEGIN
    OPEN c_student;
    LOOP
        FETCH c_student
            INTO v_sid;
        EXIT WHEN c_student%NOTFOUND;
        dbms_output.put_line('STUDNET ID: ' || v_sid);
    END LOOP;
    CLOSE c_student;
EXCEPTION
    WHEN OTHERS THEN
        IF c_student%ISOPEN
        THEN
            CLOSE c_student;
        END IF;
END;

修改版本：

DECLARE
    v_sid student.student_id%TYPE;
    CURSOR c_student IS
        SELECT student_id FROM student WHERE student_id < 110;
BEGIN
    OPEN c_student;
    LOOP
        FETCH c_student
            INTO v_sid;
        IF c_student%FOUND
        THEN
            dbms_output.put_line('Just FETCHED row ' ||
                                 to_char(c_student%ROWCOUNT) ||
                                 ' Student ID: ' || v_sid);
        ELSE
            EXIT;
        END IF;
    END LOOP;
    CLOSE c_student;
EXCEPTION
    WHEN OTHERS THEN
        IF c_student%ISOPEN
        THEN
            CLOSE c_student;
        END IF;
END;

DECLARE
    CURSOR c_student_enroll IS
        SELECT s.student_id,
               s.first_name,
               s.last_name,
               COUNT(*) enroll,
               (CASE
                   WHEN COUNT(*) = 1 THEN
                    'class.'
                   WHEN COUNT(*) IS NULL THEN
                    'no classes.'
                   ELSE
                    'classes.'
               END) CLASS
          FROM student s, enrollment e
         WHERE s.student_id = e.student_id
           AND s.student_id < 110
         GROUP BY s.student_id, first_name, last_name;
    r_student_enroll c_student_enroll%ROWTYPE;
BEGIN
    OPEN c_student_enroll;
    LOOP
        FETCH c_student_enroll
            INTO r_student_enroll;
        EXIT WHEN c_student_enroll%NOTFOUND;
        dbms_output.put_line('Student INFO: ID ' ||
                             r_student_enroll.student_id || ' is ' ||
                             r_student_enroll.first_name || ' ' ||
                             r_student_enroll.last_name ||
                             ' is enrolled in ' || r_student_enroll.enroll || ' ' ||
                             r_student_enroll.class);
    END LOOP;
    CLOSE c_student_enroll;
EXCEPTION
    WHEN OTHERS THEN
        IF c_student_enroll%ISOPEN
        THEN
            CLOSE c_student_enroll;
        END IF;
END;

使用游标FOR循环：

DECLARE
    CURSOR c_student IS
        SELECT student_id, last_name, first_name
          FROM student
         WHERE student_id < 110;
BEGIN
    FOR r_student IN c_student
    LOOP
        INSERT INTO table_log VALUES (r_student.last_name);
    END LOOP;
END;

DECLARE
    CURSOR c_group_discount IS
        SELECT DISTINCT s.course_no
          FROM section s, enrollment e
         WHERE s.section_id = e.section_id
         GROUP BY s.course_no, e.section_id, s.section_id
        HAVING COUNT(*) >= 8;
BEGIN
    FOR r_group_discount IN c_group_discount
    LOOP
        UPDATE course
           SET cost = cost * 0.95
         WHERE course_no = r_group_discount.course_no;
    END LOOP;
END;;

2.处理嵌套的游标(1)：

DECLARE
    v_zip          zipcode.zip%TYPE;
    v_student_flag CHAR;
    CURSOR c_zip IS
        SELECT zip, city, state FROM zipcode WHERE state = 'CT';
    CURSOR c_student IS
        SELECT first_name, last_name FROM student WHERE zip = v_zip;
BEGIN
    FOR r_zip IN c_zip
    LOOP
        v_student_flag := 'N';
        v_zip          := r_zip.zip;
        dbms_output.put_line(chr(10));
        dbms_output.put_line('Student living in ' || r_zip.city);
        FOR r_student IN c_student
        LOOP
            dbms_output.put_line(r_student.first_name || ' ' ||
                                 r_student.last_name);
            v_student_flag := 'Y';
        END LOOP;
        IF v_student_flag = 'N'
        THEN
            dbms_output.put_line('NO Students for this zipcode');
        END IF;
    END LOOP;
END;

3.处理嵌套的游标（2）:

DECLARE
    v_amount        course.cost%TYPE;
    v_instructor_id instructor.instructor_id%TYPE;
    CURSOR c_inst IS
        SELECT first_name, last_name, instructor_id FROM instructor;
    CURSOR c_cost IS
        SELECT c.cost
          FROM course c, section s, enrollment e
         WHERE s.instructor_id = v_instructor_id
           AND c.course_no = s.course_no
           AND s.section_id = e.section_id;
BEGIN
    FOR r_inst IN c_inst
    LOOP
        v_instructor_id := r_inst.instructor_id;
        v_amount        := 0;
        dbms_output.put_line('Amount generated by instructor ' ||
                             r_inst.first_name || ' ' || r_inst.last_name || ' is ');
        FOR r_cost IN c_cost
        LOOP
            v_amount := v_amount + nvl(r_cost.cost, 0);
        END LOOP;
        dbms_output.put_line(' ' || to_char(v_amount, '$999,999'));
    END LOOP;
END;

练习：（1）

DECLARE
    CURSOR c_course IS
        SELECT course_no, description FROM course WHERE course_no < 120;
    CURSOR c_enrollment(p_course_no IN course.course_no%TYPE) IS
        SELECT s.section_no section_no, COUNT(*) COUNT
          FROM section s, enrollment e
         WHERE s.course_no = p_course_no
           AND s.section_id = e.student_id
         GROUP BY s.section_no;
BEGIN
    FOR r_course IN c_course
    LOOP
        dbms_output.put_line(r_course.course_no || ' ' ||
                             r_course.description);
        FOR r_enroll IN c_enrollment(r_course.course_no)
        LOOP
            dbms_output.put_line(chr(9) || 'Section: ' ||
                                 r_enroll.section_no ||
                                 ' has an enrollment of: ' ||
                                 r_enroll.count);
        END LOOP;
    END LOOP;
END;

练习：（2）

DECLARE

    CURSOR c_student_enroll IS
        SELECT s.student_id,
               s.first_name,
               s.last_name,
               COUNT(*) enroll,
               /*(CASE
                   WHEN COUNT(*) = 1 THEN
                    'class'
                   WHEN COUNT(*) IS NULL THEN
                    'no clasr'
                   ELSE
                    'classes'
               END) CLASS*/
               decode(COUNT(*), 1, 'CLASS', NULL, 'no classes.', 'classes') CLASS
          FROM student s, enrollment e
         WHERE s.student_id = e.student_id
           AND s.student_id < 110
         GROUP BY s.student_id, first_name, last_name;
    r_student_enroll c_student_enroll%ROWTYPE;
BEGIN
    OPEN c_student_enroll;
    LOOP
        FETCH c_student_enroll
            INTO r_student_enroll;
        EXIT WHEN c_student_enroll%NOTFOUND;
        dbms_output.put_line('Student INFO: ID ' ||
                             r_student_enroll.student_id || ' is ' ||
                             r_student_enroll.first_name || ' ' ||
                             r_student_enroll.last_name ||
                             ' is enrolled in  ' ||
                             r_student_enroll.enroll || ' ' ||
                             r_student_enroll.class);
    END LOOP;
    CLOSE c_student_enroll;
EXCEPTION
    WHEN OTHERS THEN
        IF c_student_enroll%ISOPEN
        THEN
            CLOSE c_student_enroll;
        END IF;
END;