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.