cs342 → programming → lab8

In these lab exercises, we will be using a new movies database based on the Internet Movies Database (IMDB). See code/databases/imdb/.

When working with PL/SQL code, try the following.

Activate outputs in your SQL*Plus session using this command (once per session).
```
SET SERVEROUTPUT ON;
```
SQL commands (terminated by ;) are automatically executed by SQL*Plus. PL/SQL programs are loaded into the SQL*Plus buffer but not run automatically; use a run-buffer command (/) on the trailing line to run them. Do this for all anonymous blocks, procedures and triggers.
```
Your PL/SQL program
/
```
If you get a “...created with compiler errors”, use this command to list the errors.
```
SHOW ERRORS
```
This must be the command you run immediately after generating the errors. And if you’re wondering, yes, the errors are stored in a proper table (cf. SELECT * FROM Sys.User_Errors; ).

Exercise 8.1

Use an appropriate combination of SQL, stored procedures and/or triggers to implement the ability to allow programmers to cast a new actor (by ID#) to a movie (by ID#). Do not allow actors to be cast more than once for each movie and do not allow more than 230 castings for any movie. Demonstrate that your system works by trying the following.

Cast George Clooney (# 89558) as “Danny Ocean” in Oceans Eleven (#238072).
Cast George Clooney as “Danny Ocean” in Oceans Twelve (#238073).
Cast George Clooney as “Danny Ocean” in JFK (#167324).

Record in the script comments what happens with each of these updates and why.

Now, try a recursive procedure.

Exercise 8.2 [Homework]

Write a recursive procedure to creates a temporary table and loads it with the IDs and names of all the sequels of a given movie (identified by movie ID #), including sequels of sequels. Look forward in time (i.e., list Ocean’s Twelve for Ocean’s Eleven but not vice-versa) and you can assume that there will be no cycles in the sequel chains. Demonstrate that your procedure works by trying the following.

Find the sequels of Ocean’s 11 (#238072).
Find the sequels of Ocean’s Fourteen (#238075).

For this last exercise, I suggest using a script file based on the following.

-- Insert your results into this table.
CREATE TABLE SequelsTemp (
  id INTEGER,
  name varchar2(100),
  PRIMARY KEY (id)
 );
 
CREATE OR REPLACE PROCEDURE getSequels (movieIdIn IN Movie.id%type) AS
	-- Fill this in based on:
	--     the cursor example in class exercise 8.2.b.
	--     the recursive procedure example in class exercise 8.3.b.
END;
/

-- Get the sequels for Ocean's 11, i.e., 4 of them.
BEGIN  getSequels(238071);  END;
/
SELECT * FROM SequelsTemp;

-- Get the sequels for Ocean's Fourteen, i.e., none.
BEGIN  getSequels(238075);  END;
/
SELECT * FROM SequelsTemp;

-- Clean up.
DROP TABLE SequelsTemp;

Good luck...

Checking in

Submit the files specified above in lab08/* . We will grade your work according to the following criteria:

10% — Guide questions
90% — Exercise 8.1