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

 1/*
 2REM
 3REM q04.sp
 4REM
 5REM Find the names and addresses of movie stars
 6REM of movies produced by 'Steven Spielberg'.
 7REM
 8*/
 9
10CREATE OR REPLACE
11PROCEDURE q04 IS   
12
13  CURSOR movie_cursor IS     
14    SELECT MS.name, MS.address
15    FROM MovieStar MS, MovieExec ME, Movie M, StarsIn SI
16    WHERE ME.name = 'Steven Spielberg'
17      AND ME.cn = M.producercn
18      AND M.title = SI.title
19      AND SI.name = MS.name;
20
21  movie_rec movie_cursor%ROWTYPE;
22
23BEGIN
24
25  DBMS_OUTPUT.PUT_LINE('Name' || '        ' || 'Address');
26
27  OPEN movie_cursor;
28
29  FETCH movie_cursor INTO movie_rec;
30
31  WHILE movie_cursor%FOUND 
32  LOOP
33    DBMS_OUTPUT.PUT_LINE(movie_rec.name || '     ' || movie_rec.address);
34    FETCH movie_cursor INTO movie_rec;
35  END LOOP;
36
37END q04;
38/