It took a while before phpMyAdmin started to include support for stored procedures and functions. The reason is that these are blocks of code (like a sub-program) that are kept as a part of the database. phpMyAdmin, being a web interface, is more oriented towards operations that are performed quickly using a mouse.
Nonetheless, phpMyAdmin has a few features that permit a developer to create such routines, save them, recall them to make some modifications, and delete them.
Procedures are accessed by a CALL
statement to which we can pass parameters (more details at http://dev.mysql.com/doc/refman/5.5/en/call.html). On the other hand, functions are accessed from SQL statements (for example, SELECT)
, and are similar to other MySQL internal functions, thus returning a value.
The CREATE ROUTINE
and ALTER ROUTINE
privileges are needed to be able to create, see, and delete a stored procedure or function. The EXECUTE
privilege is needed to run the routine, although the privilege is normally granted automatically to the routine's creator.
We will create a procedure to change the page count for a specific book, by adding a specific number of pages. The book's ISBN and the number of pages to be added will be the input parameters to this procedure. We are using the SQL query box (refer to Chapter 11) to enter this procedure.
The standard SQL delimiter is the semicolon, and this character will be used inside our procedure to delimit SQL statements. However, the CREATE PROCEDURE
statement is by itself a SQL statement; hence, we must come up with a way to indicate to the MySQL parser where this statement ends. The query box has a Delimiter input box, which contains a semicolon by default. Therefore, we change it to another string, which, by convention, is a double slash "//"
.
We then enter the procedure's code in the main query box:
CREATE PROCEDURE `add_page`(IN param_isbn VARCHAR(25), IN param_pages INT, OUT param_message VARCHAR(100)) BEGIN IF param_pages > 100 THEN SET param_message = 'the number of pages is too big'; ELSE UPDATE book SET page_count = page_count + param_pages WHERE isbn=param_isbn; SET param_message = 'success'; END IF; END //
On clicking Go, we get a success message if the syntax is correct. If it is not, well it's time to revise our typing abilities or debug our syntax. Unfortunately, MySQL does not come with a procedure debugger.
Again, in the query box, we test our procedure by entering the following statements. Here, we are using a SQL variable, @message
, which will receive the contents of the OUT
parameter param_message:
call add_page('1-234567-22-0', 4, @message); SELECT @message;
If all went well, we should see that the @message variable contains success.
We can then verify whether the page count for this book has increased. We also need to test the problematic case:
call add_page('1-234567-22-0', 101, @message); SELECT @message;
This procedure is now available for calling (for example) from your PHP scripts using the mysqli
extension, which is the one recommended to access all the functionalities provided by MySQL 4.1 and above.
A procedure is stored inside a database, and is not tied to a specific table. Therefore, the interface for manipulating procedures and functions can be found at the database level, on the Structure page under the Routines slider, which appears if at least one routine is already defined.
The first icon brings this procedure's text into a query box for editing. The second icon would be used to delete this procedure. When editing the procedure, we notice that the text has been somewhat modified.
DROP PROCEDURE `add_page`// CREATE DEFINER=`marc`@`%` PROCEDURE `add_page`(IN param_isbn VARCHAR(25), IN param_pages INT, OUT param_message VARCHAR(100)) BEGIN IF param_pages > 100 THEN SET param_message = 'the number of pages is too big'; ELSE UPDATE book SET page_count = page_count + param_pages WHERE isbn=param_isbn; SET param_message = 'success'; END IF; END
First, a DROP PROCEDURE
statement appears. This is normal because MySQL does not offer a statement that would permit changing the body of a procedure. Therefore, we have to delete a procedure every time we want to change it. It's true that the ALTER PROCEDURE
statement exists, but it can only change the procedure's characteristics, for example, by adding a comment. Then, a DEFINER
clause is shown. It was generated at creation time, and indicates who created this procedure.
At this point, we make any changes we need to the code, and click on Go to save this procedure.
It might be tempting to open the book
table on its Structure page and look for a list of procedures that manipulate this table, such as our add_page()
procedure. However, all procedures are stored at the database level, and there is no direct link between the code itself (UPDATE book)
and the place where the procedure is stored.
Functions are similar to stored procedures. However, a function may return just one value, whereas a stored procedure can have more than one OUT
parameter. On the other hand, using a stored function from within a SELECT
statement may seem more natural as it avoids the need for an intermediate SQL variable to hold the value of an OUT
parameter.
What is the goal of functions? As an example, a function can be used to calculate the total cost of an order, including tax and shipping. Putting this logic inside the database instead of at the application level helps to document the application-database interface. It also avoids duplicating business logic in every application that needs to deal with this logic.
We should not confuse MySQL 5.0 functions with UDF (User-Defined Functions), which existed prior to MySQL 5.0. A UDF consists of code written in C or C++, compiled into a shared object, and referenced with a CREATE FUNCTION
statement and the SONAME
keyword.
phpMyAdmin's treatment of functions is, in many ways, similar to what we have covered in procedures:
Let us define a function that retrieves the country name, based on its code. I prefer to use a param_
prefix to clearly identify the parameters inside the function's definition and a var_
prefix for local variables. We will use our trusty SQL query box to enter the function's code, again indicating to this box to use //
as the delimiter.
CREATE FUNCTION get_country_name(param_country_code CHAR(2)) RETURNS VARCHAR(50) READS SQL DATA BEGIN DECLARE var_country_name VARCHAR(50) DEFAULT 'not found'; SELECT description FROM country WHERE code = param_country_code INTO var_country_name; RETURN var_country_name; END //
We should note that our newly-created function can be seen on the database's Structure page, along with its friend, the add_page
procedure:
To test the function we just created, enter the following query in a query box (refer to Chapter 11):
SELECT CONCAT('ca->', get_country_name('ca'), ', zz->', get_country_name('zz')) as test;
This will produce the following result:
ca->Canada, zz->not found
When exporting a database, procedures and functions appear in an SQL export. This is because the Add CREATE PROCEDURE / FUNCTION / EVENT checkbox is selected by default in the Object creation options dialog of the Export page (it can be seen in the Custom export mode). Here is the part of the export file related to procedures and functions:
DELIMITER $$ -- -- Procedures -- CREATE DEFINER=`marc`@`%` PROCEDURE `add_page`(IN param_isbn VARCHAR(25), IN param_pages INT, OUT param_message VARCHAR(100)) BEGIN IF param_pages > 100 THEN SET param_message = 'the number of pages is too big'; ELSE UPDATE book SET page_count = page_count + param_pages WHERE isbn=param_isbn; SET param_message = 'success'; END IF; END$$ -- -- Functions -- CREATE DEFINER=`marc`@`%` FUNCTION `get_country_name` (param_country_code CHAR(2)) RETURNS varchar(50) CHARSET latin1 READS SQL DATA BEGIN DECLARE var_country_name VARCHAR(50) DEFAULT 'not found'; SELECT description into var_country_name FROM country WHERE code = param_country_code; RETURN var_country_name; END$$ DELIMITER ;
3.135.182.107