Stored Functions

The article describes the stored functions in the Sakila sample database.

There are 3 stored functions defined in the Sakila database. The following describes each stored functions.

get_customer_balance

The get_customer_balance function returns the current balance of the specified customer account.

Parameter

p_customer_id
The ID of the customer to check, from the customer_id column of the customer table.
p_effective_date
The cutoff date for items that will be applied to the balance. Any rentals, payments, and so forth after this date are not counted.

Return Values

This function returns the current balance of the customer’s account.

Sample Usage

SELECT get_customer_balance(298,NOW());
Output
+---------------------------------+
| get_customer_balance(298,NOW()) |
+---------------------------------+
|                           22.00 |
+---------------------------------+
1 row in set (0.00 sec)

inventory_held_by_customer

The inventory_held_by_customer function returns the customer_id of the customer who has rented out the specified inventory item.

Parameter

p_inventory_id
The ID of the inventory item to be checked.

Return Values

This function returns the customer_id of the customer who is currently renting the item. If the item is not rented out, return NULL.

Sample Usage

SELECT inventory_held_by_customer(8);
Output
+-------------------------------+
| inventory_held_by_customer(8) |
+-------------------------------+
|                          NULL |
+-------------------------------+
1 row in set (0.00 sec)

SELECT inventory_held_by_customer(9);
Output
+-------------------------------+
| inventory_held_by_customer(9) |
+-------------------------------+
|                           366 |
+-------------------------------+
1 row in set (0.00 sec)

inventory_in_stock

The inventory_in_stock function returns a Boolean value indicating whether the specified inventory item is in stock.

Parameter

p_inventory_id
The ID of the inventory item to be checked.

Return Values

This function returns TRUE or FALSE to indicate whether the specified item in stock.

Sample Usage

SELECT inventory_in_stock(9);
Output
+-----------------------+
| inventory_in_stock(9) |
+-----------------------+
|                     0 |
+-----------------------+
1 row in set (0.00 sec)

SELECT inventory_in_stock(8);
Output
+-----------------------+
| inventory_in_stock(8) |
+-----------------------+
|                     1 |
+-----------------------+
1 row in set (0.00 sec)