Practice Exercise for Oracle PL/SQL
To practice the exercise you need the schema. You can found the schema from the post http://arjudba.blogspot.com/2009/12/basic-oracle-sql-exercise.html and before practice these exercises you must first run those sqls so that you can get tables and data to practice. After you know the answer of each step please post your PL/SQL inside comment section so that other can check and understand it and you will be more clear about it.
As business is becoming strong and the movie stock is growing for the More Movie Rentals, the manager wants to do more inventory evaluations. One item of interest concerns any movie for which the company is holding $75 or more in value. The manager wants to focus on these movies in regards to their revenue generation to ensure the stock level is warranted. To make these stock queries more efficient, the application team decides that a column should be added to the MM_MOVIE table named STK_FLAG that will hold a value '*' if stock is $75 or more. Otherwise the value should be NULL. Add the new column to the MM_MOVIE table as a CHAR data type.Execute a DESC MM_MOVIE on the table both before you add the new column and after the column is added.Note: Since this is code will be in your script file you will need to comment it out after the first time you have execute the ALTER TABLE statement successfully to avoid getting errors each additional time your script file is run.
Create an anonymous block of PL/SQL code that contains a CURSOR FOR loop to accomplish the task described above in step 1. Your loop will need to interrogate the value (using an IF statement) found in the movie_qty field of the cursor loop variable to see if it is >= 75. If this is true then you will need to update the new column in the table with an '*' WHERE CURRENT OF the table. If the quantity is not >= 75 (the ELSE side of the IF statement) then update the new column with a NULL.Execute a SELECT * from MM_MOVIE both before and after you execute the new PL/SQL block of code to show that the process works.
Here is a block that retrieves the movie title and rental count based on a movie id provided via a host variable.SET SERVEROUTPUT ONVARIABLE g_movie_id NUMBER
BEGIN
:g_movie_id := 4;
END;
/DECLARE
v_count NUMBER;
v_title mm_movie.movie_title%TYPE;
BEGIN
SELECT m.movie_title, COUNT(r.rental_id)
INTO v_title, v_count
FROM mm_movie m, mm_rental r
WHERE m.movie_id = r.movie_id
AND m.movie_id = :g_movie_id
GROUP BY m.movie_title;DBMS_OUTPUT.PUT_LINE(v_title || ': ' || v_count);
END;
/Modify the block of code to add exception handlers for errors that you can and cannot anticipate. You will need to execute the entire code listing shown above each time you wish to test it by changing the value of :g_movie_id for each test.Once finished then test your exception handling by running the modified block for the following values of :g_movie_id. Be sure that you can capture the value in the :g_movie_id host variable.- 2 - normal output will display title and number of rentals
- 13 - exception - there is no movie ID for 13
- 1 - exception - Movie with ID 1 has never been rented
No comments:
Post a Comment