Using SELECT Statements with an INTO Clause
If you have a SELECT statement that returns only a single row, you can return that row into stored program variables by using the INTO statement within the SELECT statement. The format for such a SELECT is:
SELECT expression1 [, expression2 ....] INTO variable1 [, variable2 ...] other SELECT statement clauses
Example 5-2 shows how we can retrieve details from a single customer. The customer ID is passed in as a parameter.
Example 5-2. Using a SELECT-INTO statement
|
CREATE PROCEDURE get_customer_details(in_customer_id INT) BEGIN DECLARE l_customer_name VARCHAR(30); DECLARE l_contact_surname VARCHAR(30); DECLARE l_contact_firstname VARCHAR(30); SELECT customer_name, contact_surname,contact_firstname INTO l_customer_name,l_contact_surname,l_contact_firstname FROM customers WHERE customer_id=in_customer_id; /* Do something with the customer record */ END; |
If the SQL statement returns more than one row, a runtime error will result. For instance, if we omitted the WHERE clause in Example 5-2, the following error would result when we tried to run the stored procedure:
mysql> CALL get_customer_details(2) ; ERROR 1172 (42000): Result consisted of more than one row