Setting Variables

The SET statement is used to set the value of a declared stored routine variable. Its prototype looks like this:

SET variable_name = value [, variable_name = value]

The following example illustrates the process of declaring and setting a variable titled inv:

DECLARE inv INT; SET inv = 155;

It's also possible to set variables using a SELECT...INTO statement. For example, the inv variable can also be set like this:

DECLARE inv INT;

SELECT inventory INTO inv FROM product WHERE productid="MZC38373";

Of course, this variable is local in scope to the BEGIN/END block from within which it was declared. If you want to use this variable from outside of the routine, you need to pass it in as an OUT variable, like so:

mysql>DELIMITER //

mysql>CREATE PROCEDURE get_inventory(OUT inv INT)

->SELECT 45 INTO inv;

Query OK, 0 rows affected (0.08 sec) mysql>DELIMITER ; mysql>CALL get_inventory(@inv); mysql>SELECT @inv;

This returns the following:

You may be wondering about the DELIMITER statement, though. By default, MySQL uses the semicolon to determine when a statement has concluded. However, when creating a multistatement stored routine, you need to write several statements, but you don't want MySQL to do anything until you've finished writing the stored routine. Therefore, you must change the delimiter to another character string. It doesn't have to be //. You can choose whatever you please, ||| or AA, for instance.

Was this article helpful?

+1 0

Post a comment