Skip navigation links
Sakila Sample Database :: 5 Structure :: 5.3 Stored Procedures :: 5.3.2 The film_not_in_stock Stored Procedure
5.3.3 The rewards_report Stored Procedure »
Section Navigation      [Toggle]

5.3.2. The film_not_in_stock Stored Procedure

Description

The film_not_in_stock stored procedure is used to determine whether there are any copies of a given film not in stock (rented out) at a given store.

Parameters

  • p_film_id: The ID of the film to be checked, from the film_id column of the film table.

  • p_store_id: The ID of the store to check for, from the store_id column of the store table.

  • p_film_count: An OUT parameter that returns a count of the copies of the film not in stock.

Return Values

This procedure produces a table of inventory ID numbers for the copies of the film not in stock, and returns (in the p_film_count parameter) a count that indicates the number of rows in that table.

Sample Usage 
mysql> CALL film_not_in_stock(2,2,@count);
+--------------+
| inventory_id |
+--------------+
| 9            |
+--------------+
1 row in set (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

mysql> SELECT @count;
+--------+
| @count |
+--------+
| 1      |
+--------+
1 row in set (0.00 sec)
Copyright © 2007, 2011, Oracle and/or its affiliates. All rights reserved. Legal Notices