Introducing Stored Procedures
Just as programming languages such as PHP support the ability to create custom functions, which encapsulate code and allow it to be repeatedly called using a designated keyword, so can you create functions that operate in the same fashion within the database server (known as stored procedures).
For instance, suppose you wanted a simple way to calculate the sales tax of an order based on a 5.5% rate. Because this rate might change over time and you may need to calculate an order’s sales tax within multiple locations on the site, consider using a stored procedure to perform the calculation for you. An example of such stored procedure follows. Like the previously created view, all you need to do is log in to the MySQL client or phpMyAdmin and execute the following statement:
DELIMITER // CREATE FUNCTION calculate_salestax (order_id INT) RETURNS DECIMAL(5,2) BEGIN DECLARE tax DECIMAL(5,2); SELECT SUM(price) INTO tax FROM order_details WHERE order_id = order_id; SET tax = tax * .055; RETURN tax; END; // DELIMITER ;As you can probably see from this example, you can not only pass input parameters into the stored procedure (the lone parameter in this example is identified as
order_id), but you also can return calculated output back to the caller.After defining the stored procedure, you can call it like this:
SELECT calculate_salestax(45753);
Conclusion
Advanced database features such as views and stored procedures can go a long way towards helping consolidate your application code, in addition to centralizing control over the domain model’s behavior within the database server.
About the Author
Jason Gilmore is the founder of WJGilmore.com and the author of
several popular books, including “Easy PHP Websites with the Zend Framework”, “Easy PayPal with PHP”, and “Beginning PHP and MySQL, Third Edition”.
several popular books, including “Easy PHP Websites with the Zend Framework”, “Easy PayPal with PHP”, and “Beginning PHP and MySQL, Third Edition”.
