Yurttas/PL/DBL/oracle/F/02/Movie/q05.sp

 1/*
 2REM
 3REM q05.sp
 4REM
 5REM Which stars appeared in movies produced by
 6REM 'Disney' studios in 1997 but not in 1998
 7REM 'Disney' movies.
 8REM
 9*/
10
11CREATE OR REPLACE 
12PROCEDURE q05 IS   
13
14  CURSOR movie_cursor IS
15    SELECT SI.name
16    FROM Movie M, StarsIn SI
17    WHERE M.year = 1997
18      AND M.studioname = 'Disney'
19      AND M.title = SI.title
20      AND SI.name NOT IN (SELECT SI.name
21                          FROM Movie M, StarsIn SI
22                          WHERE M.year = 1998
23                            AND M.studioname = 'Disney'
24                            AND M.title = SI.title);
25
26  movie_rec movie_cursor%ROWTYPE;  
27
28BEGIN
29
30  DBMS_OUTPUT.PUT_LINE('Name');
31
32  OPEN movie_cursor;
33
34  FETCH movie_cursor INTO movie_rec;   
35
36  WHILE movie_cursor%FOUND 
37  LOOP
38    DBMS_OUTPUT.PUT_LINE(movie_rec.name);       
39    FETCH movie_cursor INTO movie_rec;
40  END LOOP;
41
42END q05;
43/