Using OUT Parameters

As I mentioned earlier in the chapter, stored procedures can have IN parameters (as you've seen) as well as OUT and INOUT parameters. IN parameters work just like those in stored functions. OUT parameters allow you to assign a value to a variable that exists outside of the stored procedure. INOUT can serve both purposes. MySQL assumes that all parameters are IN unless you say otherwise, by indicating such before the argument's name:

CREATE PROCEDURE myproc (OUT x INT, INOUT y CHAR)...

When calling these procedures, you'll normally involve user-defined variables in the mysql client as the OUT argument (Figure 11.18):

DELIMITER $$
CREATE PROCEDURE assign (OUT n INT)
BEGIN
SET n = 10;
END $$
DELIMITER ;
CALL assign (@num);
SELECT @num;

The CALL line invokes the procedure, providing the user variable @num as its argument. In the procedure, this variable is given a value of 10. To see the variable's value, it must then be selected.

As an example of this concept, the next stored procedure will return the client ID if a user successfully enters the proper login name and password. This procedure also makes use of the SELECT...INTO query, which is introduced in the sidebar.

SELECT...INTO Queries In this series of steps I use a special kind of SELECT query, referred to as SELECT...INTO. Such a query selects values and immediately assigns those values to variables. The general syntax is SELECT col1, col2 INTO var1, var2 FROM tablename You can have as many variables as you want, as long as they match the number of columns selected. You can also add WHERE and other clauses as needed.

To use OUT parameters:

1.	Log in to the mysql client as a user permitted to create stored functions in the accounting database, if you have not already.
2.	Select the accounting database, if it is not already selected. USE accounting;
3.	Change the delimiter. DELIMITER $$
4.	Begin defining the stored procedure. CREATE PROCEDURE check_login (name VARCHAR(20), pass VARCHAR(20), OUT cid INT) BEGIN This procedure takes three arguments. The first two are of type IN and are both strings. The third is an OUT parameter of type integer.
5.	Retrieve the client ID. SELECT client_id INTO cid FROM logins WHERE login_name=AES_ENCRYPT(name, 'w1cKet') AND login_pass=SHA1(pass); This is a simple login query that returns the client ID associated with the password and username, assuming that the correct values were entered. The selected value, assuming there is one, will be immediately assigned to cid. Once again, the routine's argumentsname and pass, specificallyare not put within quotation marks even though they are strings. Cursors If you need to fetch and handle a lot of data in a stored procedure (more than the single value returned by check_login), you'll need to make use of cursors. I don't discuss this topic in detail in the book, because: They aren't often necessary. Some people suggest that their use implies bad programming form (i.e., there's probably a better way to do what you're doing). Still, if you're curious or think you need cursors, check out the MySQL manual. You'll also probably need to learn about declaring handlers, as cursors and handlers are often used together.
6.	Complete the procedure and change the delimiter back to the semicolon (Figure 11.19). END $$ DELIMITER ; Figure 11.19. This procedure runs a SELECT...INTO query, assigning the returned value to an OUT argument. [View full size image]
7.	Test the procedure using both valid and invalid access information (Figure 11.20). CALL check_login ('username', 'password', @id); SELECT @id; CALL check_login ('bad', 'bad', @id); SELECT @id;

Figure 11.20. The user variable @id is used when calling the stored procedure. In the procedure, @id is assigned the client's ID, if the proper username and password combination is submitted.

[View full size image]