Stored Procedures
The article describes the stored procedures in the Sakila sample database.
There are 3 stored procedures defined in the Sakila database. The following describes each stored procedures.
film_in_stock
The film_in_stock stored procedure determines whether any copies of a given film are in stock at a given store.
Parameters
p_film_id
- The ID of the film to be checked, from the
film_id
column of thefilm
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 in stock.
Return Values
This procedure produces a table of inventory ID numbers for the copies of the film in stock, and returns (in the p_film_count parameter) a count that indicates the number of rows in that table.
Sample Usage
CALL film_in_stock(1,1,@count);
+--------------+
| inventory_id |
+--------------+
| 1 |
| 2 |
| 3 |
| 4 |
+--------------+
4 rows in set (0.01 sec)
Query OK, 1 row affected (0.01 sec)
SELECT @count;
+--------+
| @count |
+--------+
| 4 |
+--------+
1 row in set (0.00 sec)
film_not_in_stock
The film_not_in_stock
stored procedure determines 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 thefilm
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
CALL film_not_in_stock(2, 2, @count);
+--------------+
| inventory_id |
+--------------+
| 9 |
+--------------+
1 row in set (0.01 sec)
Query OK, 1 row affected (0.01 sec)
```sql
mysql> SELECT @count;
+--------+
| @count |
+--------+
| 1 |
+--------+
1 row in set (0.00 sec)
rewards_report
The rewards_report
stored procedure will generate a list of top customers in the last month specified by the parameter.
Parameters
min_monthly_purchases
- The minimum number of purchases or rentals a customer needed to make in the last month to qualify.
min_dollar_amount_purchased
- The minimum dollar amount a customer needed to spend in the last month to qualify.
count_rewardees
- An
OUT
parameter that returns a count of the customers who met the qualifications specified.
Return Values
This stored procedure produces a table of customers who met the qualifications specified. The table has the same structure as the customer
table. The stored procedure also returns (in the count_rewardees
parameter) a count that indicates the number of rows in that table.
Sample Usage
CALL rewards_report(7, 20.00, @count);
...
| 598 | 1 | WADE | DELVALLE | [email protected] | 604 | 1 | 2006-02-24 10:48:30 | 2006-02-15 04:57:20 |
| 599 | 2 | AUSTIN | CINTRON | [email protected] | 605 | 1 | 2006-02-24 10:48:30 | 2006-02-15 04:57:20 |
...
42 rows in set (0.11 sec)
Query OK, 0 rows affected (0.67 sec)
SELECT @count;
+--------+
| @count |
+--------+
| 42 |
+--------+
1 row in set (0.00 sec)