• Choose a category
• All
• Classic-Fiction

• You might not have the security access needed to create stored procedures. Many database administrators restrict stored procedure creation rights, allowing users to execute them but not necessarily create them.

Nonetheless, stored procedures are useful and should be used whenever possible.

* * *

Note: Can’t Write Them? You Can Still Use Them

MariaDB distinguishes the security and access needed to write stored procedures from the security and access needed to execute them. This is a good thing; even if you can’t (or don’t want to) write your own stored procedures, you can still execute them when appropriate.

* * *

Using Stored Procedures

Using stored procedures requires knowing how to execute (run) them. Stored procedures are executed far more often than they are written, so we start there. And then we look at creating and working with stored procedures.

Executing Stored Procedures

MariaDB refers to stored procedure execution as calling, and so the MariaDB statement to execute a stored procedure is simply CALL. CALL takes the name of the stored procedure and any parameters that need to be passed to it. Take a look at this example:

Input

CALL productpricing(@pricelow,

@pricehigh,

@priceaverage);

Analysis

Here a stored procedure named productpricing is executed; it calculates and returns the lowest, highest, and average product prices. Of course, you can’t run this example yet, as stored procedure productpricing does not exist. (Well, you could try to run it, but you’ll just see a MariaDB error message.)

Stored procedures might or might not display results, as you see shortly.

Creating Stored Procedures

As already explained, writing a stored procedure is not trivial. To give you a taste for what is involved, let’s look at a simple example—a stored procedure that returns the average product price. Here is the code:

Input

CREATE PROCEDURE productpricing()

BEGIN

SELECT Avg(prod_price) AS priceaverage

FROM products;

END;

Analysis

The stored procedure is named productpricing and is thus defined with the statement CREATE PROCEDURE productpricing(). Had the stored procedure accepted parameters, these would have been enumerated between the ( and ). This stored procuedure has no parameters, but the trailing () is still required. BEGIN and END statements are used to delimit the stored procedure body, and the body itself is just a simple SELECT statement (using the Avg() function learned back in Chapter 12, “Summarizing Data”).

When MariaDB processes this code it creates a new stored procedure named productpricing. No data is returned because the code does not call the stored procedure, it simply creates it for future use.

* * *

Note: mysql Command Line Client Delimiters

If you are using the mysql command line utility, pay careful attention to this note.

The default MariaDB statement delimiter is ; (as you have seen in all the SQL statements used thus far). However, the mysql command line utility also uses ; as a delimiter. If the command line utility were to interpret the ; characters inside of the stored procedure itself, those would not end up becoming part of the stored procedure, and that would make the SQL in the stored procedure syntactically invalid.

The solution is to temporarily change the command line utility delimiter, as seen here:

DELIMITER //

CREATE PROCEDURE productpricing()

BEGIN

SELECT Avg(prod_price) AS priceaverage

FROM products;

END //

DELIMITER ;

Here, DELIMITER // tells the command line utility to use // as the new end of statement delimiter, and you will notice that the END that closes the stored procedure is defined as END // instead of the expected END;. This way the ; within the stored procedure body remains intact and is correctly passed to the database engine. And then, to restore things back to how they were initially, the statement closes with a DELIMITER ;.

Any character may be used as the delimiter except for \.