Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MySQL

Posted Sep 13, 2005

MySQL Stored Procedures: Part 2

By Ian Gilfillan

Introduction

Last month I introduced MySQL Stored Procedures. If you have not yet read that article, you should do so first, as I continue to build upon the examples from then. This month I look at some more advanced concepts, including conditions and loops.

Characteristics

Last month we created skeleton stored procedures, to introduce the concepts. Let's start off this month by looking at all of the characteristics that you can define when creating a stored procedure. Here is a sample CREATE PROCEDURE statement making use of all of the clauses.

CREATE PROCEDURE sp_full() 
LANGUAGE SQL 
NOT DETERMINISTIC 
MODIFIES SQL DATA 
SQL SECURITY DEFINER 
COMMENT 'Returns a random number' 
SELECT RAND()

LANGUAGE SQL simply means that the language used to write the procedure is SQL. MySQL cannot yet use procedures in any other languages, but it probably will in the future. It is, unsurprisingly, the default.

NOT DETERMINISTIC implies that the procedure may produce different results, given the same inputs. The alternative is DETERMINISTIC, if the procedure always gives the same results given the same input. The default is NOT DETERMINISTIC. Procedures that are NOT DETERMINISTIC have implications for binary logging and replication (if it performs updates, it means that slave data could be different to master data, and for this reason you can't define a NOT DETERMINISTIC procedure if binary logging is enabled). Besides binary logging, MySQL as yet makes no use of this information (potentially the optimizer could use it to improve performance).

MODIFIES SQL DATA indicates that data could be modified by the procedure. Alternatives are CONTAINS SQL (which would be SQL that does not read or write data), NO SQL or READS SQL DATA. The default is CONTAINS SQL.

SQL SECURITY DEFINER indicates that MySQL should check the privileges of the user that defined the procedure when executing the procedure. The alternative is SQL SECURITY INVOKER, which causes MySQL to use the privileges of the user calling the procedure. The default is SQL SECURITY DEFINER.

COMMENT is self-explanatory, and can be used to describe the procedure. It is a non-standard MySQL extension.

SQL statements unique to Stored Procedures

A procedure may need to use conditions or loops, and standard SQL is not sufficient for these purposes. For that reason, the SQL standard includes a number of statements that only occur inside stored procedures. We already looked at the DECLARE statement last month. Let's take a look now at conditions and loops. The concepts should all be familiar to anyone with programming experience.

Conditions: IF THEN ELSE

MySQL implements conditions in the stock standard, tried and tested way.

IF condition 
THEN statement/s 
ELSE statement/s
END IF

The logic is simple. If the condition is met, then a set of statements are carried out. Else, another set of statements are carried out.

Here is an example:

mysql> CREATE PROCEDURE sp_condition(IN var1 INT) 
BEGIN 
 IF (var1 > 10)  
  THEN SELECT 'greater'; 
  ELSE SELECT 'less than or equal'; 
 END IF; 
END|
Query OK, 0 rows affected (0.06 sec)
mysql> CALL sp_condition(5)\G
*************************** 1. row ***************************
less than or equal: less than or equal
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

mysql> CALL sp_condition(15)\G
*************************** 1. row ***************************
greater: greater
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

I presume you can follow the logic easily enough. NULL values throw a curveball into the mix - an argument against their usage is that they undermine standard boolean logic. Nevertheless, you may come across instances where they are used. Let's see what happens if we pass a NULL to the procedure we have just created:

mysql> CALL sp_condition(NULL)\G
*************************** 1. row ***************************
less than or equal: less than or equal
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

As you probably expected, NULL > 10 evaluates to false, so the ELSE statement is run. Let's switch things around:

mysql> CREATE PROCEDURE sp_condition2(IN var1 INT) 
BEGIN 
 IF (var1 <= 10) 
  THEN SELECT 'less than or equal'; 
  ELSE SELECT 'greater';
 END IF; 
END|
Query OK, 0 rows affected (0.00 sec)
mysql> CALL sp_condition2(NULL)\G
*************************** 1. row ***************************
greater: greater
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

Using what should in boolean logic be an identical test, we get a differing result. NULL <= 10 also evaluates to false. NULL's are beyond such dualistic thinking. This should be enough of a warning to be cautious of NULL's!

Conditions: CASE

The other construct used for condition is CASE.

CASE variable 
WHEN condition1 statement/s 
WHEN condition2 statement/s
ELSE statement/s
END CASE

This construct is used when the same variable is being tested against multiple conditions. Instead of long nested IF statements, using a CASE statement allows the procedure code to be more compact and easily readable. Here is an example:

mysql> CREATE PROCEDURE sp_case(IN var1 INT) 
BEGIN 
 CASE var1 
  WHEN 1 THEN SELECT 'One'; 
  WHEN 2 THEN SELECT 'Two'; 
  ELSE SELECT 'Something else'; 
 END CASE; 
END|
Query OK, 0 rows affected (0.00 sec)
mysql> CALL sp_case(1)\G
*************************** 1. row ***************************
One: One
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> CALL sp_case(2)\G
*************************** 1. row ***************************
Two: Two
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> CALL sp_case(3)\G
*************************** 1. row ***************************
Something else: Something else
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)


MySQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MySQL Forum
Topic By Replies Updated
MySQL in high availability and transction secure banking application klamor 1 August 28th, 10:24 AM
MySQL rollback UAL225 1 August 28th, 10:15 AM
Browsing a DB file that uses MySql finleytech 1 July 26th, 10:51 AM
php cookie won't pass variable to next page ITdevGirl 0 June 22nd, 12:13 PM


















Thanks for your registration, follow us on our social networks to keep up-to-date