Most Popular stored routines

Optimizing MySQL Stored Routines

Stored routines are one of the newer additions to MySQL and, as such, are significantly less optimized than other components of the server. As a result, it’s generally not advisable for your application to rely entirely on stored routines, as doing so will likely degrade performance significantly. That said, stored routines do have a role […]

Read More

How to Return a Collection of Values from a MySQL Stored Function

Under MySQL’s current implementation, a stored function can only return a single value. However, there is a not-so-pretty workaround: create a temporary table within the function body to store the values returned, and then access this table outside the function. Here’s an example:  mysql> DELIMITER //   mysql> CREATE FUNCTION get_airport_names(min_terminals INT)       -> RETURNS INT       -> BEGIN       ->   DECLARE count INT DEFAULT 0;       ->   CREATE TEMPORARY TABLE       ->     IF NOT EXISTS       ->     get_airport_names_out (value VARCHAR(255));       ->   DELETE FROM get_airport_names_out;       ->   INSERT INTO get_airport_names_out (value)       ->   SELECT AirportName FROM airport       ->     WHERE NumTerminals >= min_terminals; […]

Read More

Why and When Use MySQL Stored Routines

As your SQL business logic becomes more complex, you might find yourself repeatedly writing blocks of SQL statements to perform the same database operation at the application level—for example, inserting a set of linked records or performing calculations on a particular result set. In these situations, it usually makes sense to turn this block of […]

Read More