《Oracle PL/SQL实例精讲》学习笔记13——游标 （高级概念）

这章的主要内容有：

1. 带参数游标

2. 游标的嵌套

3. For update (of)和where current of游标

 

代码如下：

-- *** Chapter Exercises *** --
-- For Example ch12_2.sql
SET SERVEROUTPUT ON
 /* Formatted on 2018/11/6 2:34:30 (QP5 v5.256.13226.35538) */
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   IN 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 (
            'Grades    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 Example ch12_3.sql
DECLARE
  CURSOR c_course IS
     SELECT course_no, cost
       FROM course FOR UPDATE;
BEGIN
   FOR r_course IN c_course
   LOOP
      IF r_course.cost < 2500
      THEN
         UPDATE course
            SET cost = r_course.cost + 10
          WHERE course_no = r_course.course_no;
      END IF;
   END LOOP;
END; 

select * from course

rollback

-- For Example ch12_4.sql
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; 
  
select * from enrollment 

 SELECT e.student_id, e.section_id,e.FINAL_GRADE
        FROM enrollment e, section s
       WHERE s.course_no = 135
         AND e.section_id = s.section_id;

-- For Example ch12_5.sql
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;
END; 

SELECT s.student_id, z.city
        FROM student s, zipcode z
       WHERE z.city = 'Brooklyn'
         AND s.zip = z.zip
         
/* Formatted on 2018/11/6 23:29:34 (QP5 v5.256.13226.35538) */
SELECT *
  FROM student
 WHERE student_id IN (SELECT s.student_id
                        FROM student s, zipcode z
                       WHERE z.city = 'Brooklyn' AND s.zip = z.zip)      

select * from student 
        
-- For Example ch12_6.sql
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;
   END LOOP;
END;