MySQL Forums
Forum List  »  Stored Procedures

Need to understand a MySQL Procedure
Posted by: Tonya Lepski
Date: August 23, 2014 06:33PM

Hi,
I need to understand what the following procedure is doing as do not know MySQL procedural language so I can convert it into oracle PL/SQL

CREATE PROCEDURE `sakila`.`film_not_in_stock`(IN p_film_id INT, IN p_store_id INT, OUT p_film_count INT)
READS SQL DATA
BEGIN
     SELECT inventory_id
     FROM inventory
     WHERE film_id = p_film_id
     AND store_id = p_store_id
     AND NOT inventory_in_stock(inventory_id); -- What this line does in particular

     SELECT FOUND_ROWS() INTO p_film_count;
END

I need to know what the line "AND NOT inventory_in_stock(inventory_id)" does in the where clause of the above procedure if you can put it into a simple SQL statment that will be great.

Here is the inventory_in_stock function :

CREATE FUNCTION inventory_in_stock(p_inventory_id INT) RETURNS BOOLEAN
READS SQL DATA
BEGIN
    DECLARE v_rentals INT;
    DECLARE v_out     INT;

    #AN ITEM IS IN-STOCK IF THERE ARE EITHER NO ROWS IN THE rental TABLE
    #FOR THE ITEM OR ALL ROWS HAVE return_date POPULATED

    SELECT COUNT(*) INTO v_rentals
    FROM rental
    WHERE inventory_id = p_inventory_id;

    IF v_rentals = 0 THEN
      RETURN TRUE;
    END IF;

    SELECT COUNT(rental_id) INTO v_out
    FROM inventory LEFT JOIN rental USING(inventory_id)
    WHERE inventory.inventory_id = p_inventory_id
    AND rental.return_date IS NULL;

    IF v_out > 0 THEN
      RETURN FALSE;
    ELSE
      RETURN TRUE;
    END IF;
END

This procedure is executed as for example :
CALL film_not_in_stock(2,2,@count); what @count does in MySQL.

Your help would be much much appreciated.
Thanks.
Tonya.

Options: ReplyQuote


Subject
Views
Written By
Posted
Need to understand a MySQL Procedure
2731
August 23, 2014 06:33PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.