Home » SQL & PL/SQL » SQL & PL/SQL » Procedure help
Procedure help [message #184739] Thu, 27 July 2006 12:11 Go to next message
DCrash
Messages: 9
Registered: July 2006
Junior Member
I need some major help understanding how to do some things. Yes, I am in a pl/sql class, and so my questions will be related to the class. However, I am trying to learn how to do these things and am unable to understand many different aspects. This is an online course and I'm not understanding from their examples, I'm hoping I might gain a bit of insight by one of you.

Well I suppose I should start with the problem description:
"Create procedure MOVIE_RETURN_SP takes in one parameter, the rental ID, that identifies the rental record in the MM_RENTAL table. It records the current date in the CHECKIN_DATE column of the MM_RENTAL table and updates the movie inventory in the MM_MOVIE table.

Make sure you validate the parameter: The the rental ID must indicate an existing rental record. Handle exceptional cases appropriately."

Alright, so here is what I have so far:

"CREATE OR REPLACE PROCEDURE movie_rent_sp
(p_rental_id mm_rental.rental_id%TYPE)
IS
v_count INTEGER;
v_checkin_date mm_rental.checkin_date%TYPE;
v_qty_available mm_movie.movie_qty%TYPE;

BEGIN

SELECT COUNT(rental_id) into v_count
FROM mm_rental WHERE rental_id = p_rental_id;
IF (v_count=0) THEN RAISE ex_unknown_rental_id;
END IF

v_checkin_date := DATE;

SELECT movie_qty INTO v_qty_available
FROM mm_movie NATURAL JOIN mm_rental where mm_rental.movie_id = mm_movie.movie_id;

v_qty_available := v_qty_available +1;
END;

EXCEPTION
WHEN ex_unknown_rental_id THEN
DBMS_OUTPUT.PUT_LINE('There is no rental with id: ' || p_rental_id);
DBMS_OUTPUT.PUT_LINE('Cannot proceed with return');
END;"

A few problems I am having are...
Line 23: PLS-00103: Encountered the symbol "EXCEPTION"

Attached I added the database part. Another problem is that I dont know how to get the movie quantity from the mm_rental table. I gave it a shot above, but I dont think that is right.

Any help would be greatly appreciated. I'm really quite lost
Re: Procedure help [message #184740 is a reply to message #184739] Thu, 27 July 2006 12:49 Go to previous messageGo to next message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
You had one too many END statements. You will also need to define your exception
CREATE OR REPLACE PROCEDURE movie_rent_sp(p_rental_id mm_rental.rental_id%TYPE) IS
    v_count         INTEGER;
    v_checkin_date  mm_rental.checkin_date%TYPE;
    v_qty_available mm_movie.movie_qty%TYPE;
    ex_unknown_rental_id EXCEPTION;

BEGIN

    SELECT COUNT(rental_id)
    INTO v_count
    FROM mm_rental
    WHERE rental_id = p_rental_id;
    IF (v_count = 0) THEN
        RAISE ex_unknown_rental_id;
    END IF
    
    v_checkin_date := DATE;

    SELECT movie_qty
    INTO v_qty_available
    FROM mm_movie NATURAL
    JOIN mm_rental
    WHERE mm_rental.movie_id = mm_movie.movie_id;

    v_qty_available := v_qty_available + 1;
EXCEPTION
    WHEN ex_unknown_rental_id THEN
        dbms_output.put_line('There is no rental with id: ' || p_rental_id);
        dbms_output.put_line('Cannot proceed with return');
END;

[Updated on: Thu, 27 July 2006 12:51]

Report message to a moderator

Re: Procedure help [message #184741 is a reply to message #184739] Thu, 27 July 2006 12:56 Go to previous messageGo to next message
DCrash
Messages: 9
Registered: July 2006
Junior Member
Thanks for pointing that out. I updated the code

CREATE OR REPLACE PROCEDURE movie_rent_sp
(p_rental_id mm_rental.rental_id%TYPE)
IS
v_count INTEGER;
v_checkin_date mm_rental.checkin_date%TYPE;
v_qty_available mm_movie.movie_qty%TYPE;
ex_unknown_rental_id EXCEPTION;
v_movie_id mm_movie.movie_id%TYPE;

BEGIN

SELECT COUNT(rental_id) into v_count
FROM mm_rental WHERE rental_id = p_rental_id;
IF (v_count=0) THEN RAISE ex_unknown_rental_id;
END IF;

v_checkin_date := SYSDATE;

update mm_movie
SET movie_qty = movie_qty - 1
WHERE movie_id = v_movie_id;


EXCEPTION
WHEN ex_unknown_rental_id THEN
DBMS_OUTPUT.PUT_LINE('There is no rental with id: ' || p_rental_id);
DBMS_OUTPUT.PUT_LINE('Cannot proceed with return');
END;
/

[Updated on: Thu, 27 July 2006 13:14]

Report message to a moderator

Re: Procedure help [message #184801 is a reply to message #184741] Fri, 28 July 2006 02:00 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
From this part of your requirement:
Quote:

It records the current date in the CHECKIN_DATE column of the MM_RENTAL table

I take that as meaning thatyou need to update the MM_RENTAL table and store the current date in the CEHECKIN_DATE column.

You are currently storing the date in v_checkin_date, which is a local variable of the same TYPE as CHECKIN_DATE, but which has no actual link to the contents of that column.

You also need to retrieve the movie_id from MM_RENTAL in order to be able to do the update on MM_MOVIE. Now, looking at your table structures, I'd have expected a NOT NULL constraint on MOVIE_ID (how can you hae a rental without a movie?) but we've not got one, so we'll just have to be a bit clever.

I'd replace
SELECT COUNT(rental_id) into v_count
FROM mm_rental WHERE rental_id = p_rental_id;
IF (v_count=0) THEN RAISE ex_unknown_rental_id;
END IF;

with
BEGIN
  SELECT movie_id
  INTO   v_movie_id
  FROM   mm_rental
  WHERE  rental_id = p_rental_id;

  IF v_movie_id IS NULL THEN
    raise ex_no_movie_id;
  END IF;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RAISE ex_unknown_rental_id;
END;

This will check that your rental id exists, and also get you the movie_id, and let you check that it is valid.

After that, you just need to do an UPDATE on MM_RENTAL, setting the CHECKIN_DATE.

It is possible to do all of this in one statement:
BEGIN
  UPDATE mm_rental
  SET    checkin_date = sysdate
  WHERE  rental_id = p_rental_id
  RETURNING movie_id INTO v_movie_id;

  IF SQL%ROWCOUNT = 0 THEN
    RAISE ex_unknown_rental_id;
  END IF;
  IF v_movie_id = 0 THEN
    RAISE x_no_movie_id;
  END IF;
END;

But they might suspect you'd had help.
Re: Procedure help [message #184920 is a reply to message #184739] Fri, 28 July 2006 12:34 Go to previous message
DCrash
Messages: 9
Registered: July 2006
Junior Member
Wow, I cant believe I was doing that. Thankyou so much for that bit of help. I wasnt understanding what was fully going on, but I think I got it now.

Ok, so just to make sure I know what all was going on.. All that we did was set the checkin_date to equal the system date to the rental_id that was sent through the procedure.

The SQL%ROWCOUNT is just seeing if there were no rows affected by the sql statement above?

Oops, noticed that it should be SET movie_qty = movie_qty +1 and not -1... they are returning a movie. whew. So we just update the quantity to show 1 more than there was.

[Updated on: Fri, 28 July 2006 12:49]

Report message to a moderator

Previous Topic: New to SQL Plus
Next Topic: another undocumented?
Goto Forum:
  


Current Time: Sat Dec 10 01:03:03 CST 2016

Total time taken to generate the page: 0.04749 seconds