Loops -> Repetitive tasks
3 types of loops in MySQL stored program:
- Simple loop using LOOP and END LOOP clauses
- WHILE and END WHILE clauses that runs while a condition is true
- REPEAT and UNTIL clauses that runs until a condition is true
Termination of loop -> LEAVE statement.
I will demonstrate the use of loops the simple loop way.
Simple counter from 1 to 10:
Store below stored procedure in a file named my_loop.sql
DROP PROCEDURE IF EXISTS my_loop$$
CREATE PROCEDURE my_loop()
Loops -> Repetitive tasks
Sometimes there is need to use conditional expression inside
stored procedure to control the flow of execution.
We can use IF or CASE statements for this.
Below is a stored procedure to check the performance of a student based on its score.
Store the below stored procedure in a file called get_performance.sql
DROP PROCEDURE IF EXISTS get_performance$$
CREATE PROCEDURE get_performance
(score NUMERIC(8, 2),
OUT result VARCHAR(11))
IF (score >= 90) THEN
SET result = 'OUTSTANDING';
ELSEIF (score >= 70 …
It is sometimes required to get the result of a stored procedure
in a variable which can be used later or to output the
To do so, we can use the "OUT" parameter mode while defining stored procedures.
In the below section, we will be writing a stored procedure to get the square root of a number returned in an output variable provided by us.
Stored Procedure Definition:
Store the below stored procedure in a file named my_sqrt.sql and save it.
DROP PROCEDURE IF EXISTS my_sqrt$$
CREATE PROCEDURE my_sqrt(inp_number INT, OUT op_number FLOAT)
August 15, 2014 By Severalnines
We're looking for an energetic and talented web developer to join our small but agile web team. This position is full-time and pay is negotiable. The hours are flexible and work can be done remotely.
Severalnines is a self-funded startup with a dozen employees; headquartered in Stockholm, Sweden and with a globally distributed, home-office based team. We provide automation and management software for database clusters. Our ClusterControl product is the leading management application for database clusters and is used by thousands of companies.
We were founded in 2010 and launched our product that same year, so you’ll be …[Read more]
This week we are talking about size, which is a subject that should matter to any system administrator in charge of the backup system of any project, and in particular database backups.
I sometimes get questions about what should be the best compression tool to apply during a particular backup system: gzip? bzip2? any other?
The testing environment
In order to test several formats and tools, I created a .csv file (comma-separated values) that was 3,700,635,579 bytes in size by transforming a recent dump of all the OpenStreetMap nodes of the European portion of Spain. It had a total of 46,741,126 rows and looked like this:
171773 38.6048402 -0.0489871 4 2012-08-25 00:37:46 12850816 472193 rubensd 171774 38.6061981 -0.0496867 2 2008-01-19 10:23:21 666916 9250 …[Read more]
To ensure that your MySQL Database works as efficiently as possible, it is important to know how to handle error messages and warnings.
Error messages have three components:
- A MySQL-specific error code, such as 1146
- A SQLSTATE error code. These codes are defined by standard SQL and the ODBC standard.
- A text message that describes the problem
MySQL Server generates a warning when it is not fully able to comply with a request or when an action has possibly unintended side effects. You can display these warnings with the SHOW WARNINGS statement.
To learn about handling error messages and warnings along with other developer topics, consider taking the MySQL for Developers training course.…[Read more]
In the article How MariaDB makes Stored Procedures usable I explained how to use the MariaDB CONNECT Storage Engine to workaround Stored Procedures limitations. Please read that article, if you didn’t, for details.
Since the technique to workaround such limitations is verbose and error-proof, I also proposed a procedure which makes it easy and convenient. Even thought this is a simple country blog, I have immediately received positive feedback after that article. But then, people started to write me: “Hey, doesn’t work!”. The reason is… MariaDB/MySQL bugs. While investigating these problems, I was able to report the following bugs:
I already wrote how MariaDB makes the debug of stored procedures much easier via the SQL Error Log. But I recently found out that MariaDB provides a usable workaround for some big limitations of their procedural SQL.
SELECT is not the only SQL statement which
returns a resultset. Other examples are
EXPLAIN and administrative
ANALYZE TABLE or
TABLE. But these commands cannot be used in place of
SELECT in the following contexts:
- Subqueries, derived tables,
CREATE TABLE …
A very old version of the MySQL site contained a nice header: Speed, Power, Ease of Use. I don’t think that stored programs (routines, triggers, events) were supported, at that time. Now they are, and… developing them is amazingly hard.
There are many problems, for example the language is not flexible and the execution is sloooow. But the biggest problem is that there is no debug API.
In details, one of the biggest problems is that, if you have complex procedures (accessing multiple tables, having error handling, and/or calling other procedures) in practice you have no idea of what warnings occur within your procedures.
MariaDB 10.0 makes things much easier by adding the …[Read more]
“There are four things that motivate open source
1. The challenge/puzzle of programming, 2. Need for the software, 3. Personal advancement, 4. Belief in open source” — Bruce Momjian.
On PostgreSQL and the challenges of motivating and managing open source teams, I have interviewed Bruce Momjian, Senior Database Architect at EnterpriseDB, and Co-founder of the PostgreSQL Global Development Group and Core Contributor.
Q1. How did you manage to transform PostgreSQL from an abandoned academic project into a commercially viable, now enterprise relational database?
Bruce Momjian: Ever since I was a developer of database …[Read more]