Other ways to assign values to variables

You can use the SELECT statement to fetch a value from the database and assign it directly to a variable, as the following figure shows.

Figure 1. Fetch a value from the database and assign it directly to a variable.

SELECT fname, lname INTO a, b FROM customer
   WHERE customer_num = 101

Use the CALL or EXECUTE PROCEDURE statements to assign values returned by an SPL function or an external function to one or more SPL variables. You might use either of the statements in the following figure to return the full name and address from the SPL function read_address into the specified SPL variables.

Figure 2. Return the full name and address from the SPL function.

EXECUTE FUNCTION read_address('Smith')
   INTO p_fname, p_lname, p_add, p_city, p_state,
        p_zip;

CALL read_address('Smith')
   RETURNING p_fname, p_lname, p_add, p_city,
             p_state, p_zip;