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:
SELECTexpression1
[,expression2
....] INTOvariable1
[,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.
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_firstnameINTO
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
Get MySQL Stored Procedure Programming now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.