这章的主要内容有:
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;