PLSQL==>高级游标

1.在游标中使用参数：

DECLARE
    CURSOR c_zip(p_state IN zipcode.state%TYPE) IS
        SELECT zip, city, state FROM zipcode WHERE state = p_state;
BEGIN
    FOR r_zip IN c_zip('NJ')
    LOOP
        dbms_output.put_line(r_zip.city || ' ' || r_zip.zip);
    END LOOP;
END;

2.使用复杂的嵌套游标：

DECLARE
    CURSOR c_student IS
        SELECT first_name, last_name, student_id
          FROM student
         WHERE last_name LIKE 'J%';
    CURSOR c_course(i_student_id IN student.student_id%TYPE) IS
        SELECT c.description, s.section_id sec_id
          FROM course c, section s, enrollment e
         WHERE e.student_id = i_student_id
           AND c.course_no = s.course_no
           AND s.section_id = e.section_id;
    CURSOR c_grade(i_section_id IN section.section_id%TYPE,
                   i_student_id student.student_id%TYPE) IS
        SELECT gt.description grd_desc,
               to_char(AVG(g.numeric_grade), '999,99') num_grd
          FROM enrollment e, grade g, grade_type gt
         WHERE e.section_id = i_section_id
           AND e.student_id = g.student_id
           AND e.student_id = i_student_id
           AND e.section_id = g.section_id
           AND g.grade_type_code = gt.grade_type_code
         GROUP BY gt.description;
BEGIN
    FOR r_student IN c_student
    LOOP
        dbms_output.put_line(chr(10));
        dbms_output.put_line(r_student.first_name || ' ' ||
                             r_student.last_name);
        FOR r_course IN c_course(r_student.student_id)
        LOOP
            dbms_output.put_line('Grade for course: ' ||
                                 r_course.description);
            FOR r_grade IN c_grade(r_course.sec_id, r_student.student_id)
            LOOP
                dbms_output.put_line(r_grade.num_grd || ' ' ||
                                     r_grade.grd_desc);
            END LOOP;
        END LOOP;
    END LOOP;
END;

FOR UPDATE 和WHERE CURRENT游标：

DECLARE
    CURSOR c_grade(i_student_id IN enrollment.student_id%TYPE,
                   i_section_id IN enrollment.section_id%TYPE) IS
        SELECT final_grade
          FROM enrollment
         WHERE student_id = i_student_id
           AND section_id = i_section_id
           FOR UPDATE;
    CURSOR c_enrollment IS
        SELECT e.student_id, e.section_id
          FROM enrollment e, section s
         WHERE s.course_no = 135
           AND e.section_id = s.section_id;
BEGIN
    FOR r_enroll IN c_enrollment
    LOOP
        FOR r_grade IN c_grade(r_enroll.student_id, r_enroll.section_id)
        LOOP
            UPDATE enrollment
               SET final_grade = 90
             WHERE student_id = r_enroll.student_id
               AND section_id = r_enroll.section_id;
        END LOOP;
    END LOOP;
END;

DECLARE
    CURSOR c_stud_zip IS
        SELECT s.student_id, z.city
          FROM student s, zipcode z
         WHERE z.city = 'Brooklyn'
           AND s.zip = z.zip
           FOR UPDATE OF phone;
BEGIN
    FOR r_stud_zip IN c_stud_zip
    LOOP
        UPDATE student
           SET phone = '718' || substr(phone, 4)
         WHERE student_id = r_stud_zip.student_id;
    END LOOP;
COMMIT ;--或者ROLLBACK才会释放学生电话号码列
END;

DECLARE
    CURSOR c_stud_zip IS
        SELECT s.student_id, z.city
          FROM student s, zipcode z
         WHERE z.city = 'Brooklyn'
           AND s.zip = z.zip
           FOR UPDATE OF phone;
BEGIN
    FOR r_stud_zip IN c_stud_zip
    LOOP
        dbms_output.put_line(r_stud_zip.student_id);
        UPDATE student
           SET phone = '718' || substr(phone, 4)
         WHERE CURRENT OF c_stud_zip; --使用where current of子句，免于UPDATE语句中添加对应的where条件
--因为更新操作只针对游标的当前记录
    END LOOP;
END;