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


Part I: Stored Programming Fundamentals

Introduction to MySQL Stored Programs

MySQL Stored Programming Tutorial

Language Fundamentals

Blocks, Conditional Statements, and Iterative Programming

Using SQL in Stored Programming

Error Handling

Part II: Stored Program Construction

Creating and Maintaining Stored Programs

Transaction Management

MySQL Built-in Functions

Stored Functions

Triggers

Part III: Using MySQL Stored Programs in Applications

Using MySQL Stored Programs in Applications

Using MySQL Stored Programs with PHP

Using MySQL Stored Programs with Java

Using MySQL Stored Programs with Perl

Using MySQL Stored Programs with Python

Using MySQL Stored Programs with .NET

Part IV: Optimizing Stored Programs

Stored Program Security

Tuning Stored Programs and Their SQL

Basic SQL Tuning

Advanced SQL Tuning

Optimizing Stored Program Code

Best Practices in MySQL Stored Program Development



MySQL Stored Procedure Programming
MySQL Stored Procedure Programming
ISBN: 0596100892
EAN: 2147483647
Year: 2004
Pages: 208

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net