• Choose a category
• All
• Classic-Fiction

* * *

So how would you use this stored procedure? Like this:

Input

CALL productpricing();

Output

+--------------+

| priceaverage |

+--------------+

| 16.133571 |

+--------------+

Analysis

CALL productpricing(); executes the just created stored procedure and displays the returned result. As a stored procedure is actually a type of function, () characters are required after the stored procedure name (even when no parameters are being passed).

Dropping Stored Procedures

After they are created, stored procedures remain on the server, ready for use, until dropped. The drop command (similar to the statement seen Chapter 21, “Creating and Manipulating Tables”) removes the stored procedure from the server.

To remove the stored procedure we just created, use the following statement:

Input

DROP PROCEDURE productpricing;

Analysis

This removes the just-created stored procedure. Notice that the trailing () is not used; here just the stored procedure name is specified.

* * *

Tip: Drop Only If It Exists

DROP PROCEDURE throws an error if the named procedure does not actually exist. To delete a procedure if it exists (and not throw an error if it does not), use DROP PROCEDURE IF EXISTS.

* * *

Working with Parameters

productpricing is a really simple stored procedure—it simply displays the results of a SELECT statement. Typically stored procedures do not display results; rather, they return them into variables that you specify.

* * *

New Term: Variable

A named location in memory, used for temporary storage of data.

* * *

Here is an updated version of productpricing (you’ll not be able to create the stored procedure again if you did not previously drop it):

Input

CREATE PROCEDURE productpricing(

OUT pl DECIMAL(8,2),

OUT ph DECIMAL(8,2),

OUT pa DECIMAL(8,2)

)

BEGIN

SELECT Min(prod_price)

INTO pl

FROM products;

SELECT Max(prod_price)

INTO ph

FROM products;

SELECT Avg(prod_price)

INTO pa

FROM products;

END;

Analysis

This stored procedure accepts three parameters named pl to store the lowest product price, ph to store the highest product price, and pa to store the average product price (and thus the variable names). Each parameter must have its type specified; here a decimal value is used. The keyword OUT is used to specify that this parameter is used to send a value out of the stored procedure (back to the caller). MariaDB supports parameters of types IN (those passed to stored procedures), OUT (those passed from stored procedures, as used here), and INOUT (those used to pass parameters to and from stored procedures). The stored procedure code itself is enclosed within BEGIN and END statements as seen before, and a series of SELECT statements are performed to retrieve the values that are then saved into the appropriate variables (by specifying the INTO keyword).

* * *

Note: Parameter Datatypes

The datatypes allowed in stored procedure parameters are the same as those used in tables. Appendix C, “MariaDB Datatypes,” lists these types.

Note that a recordset is not an allowed type, and so multiple rows and columns could not be returned via a parameter. This is why three parameters (and three SELECT statements) are used in the previous example.

* * *

To call this updated stored procedure, three variable names must be specified, as seen here:

Input

CALL productpricing(@pricelow,

@pricehigh,

@priceaverage);

Analysis

As the stored procedure expects three parameters, exactly three parameters must be passed, no more and no less. Therefore, three parameters are passed to this CALL statement. These are the names of the three variables that the stored procedure will store the results in.

* * *

Note: Variable Names

All MariaDB variable names must begin with @.

* * *

When called, this statement does not actually display any data. Rather, it returns variables that can then be displayed (or used in other processing).

To display the retrieved average