Local Variables






Local Variables

Within a stored procedure, local variables can be declared. They can be used to keep temporary intermediate results. If we need a local variable within a stored procedure, we must introduce it first with a DECLARE statement. So, SQL is different from similar languages such as PHP, in which a variable, if it is used, is declared implicitly.

With a declaration, the data type of the variable is determined and an initial value can be specified. The data types that are supported are the ones that may be used in CREATE TABLE statements; see Section 15.3, in Chapter 15, "Creating Tables."

<declare variable statement> ::=
   DECLARE <variable list> <data type> [
      DEFAULT <expression> ]

<variable list> ::=
   <variable> [ { , <variable> }... ]


3. Declare a numeric and an alphanumeric variable.

DECLARE NUM1 DECIMAL(7,2);
DECLARE ALPHA1 VARCHAR(20);

Multiple variables carrying the same data type can be declared with one DECLARE statement.

4. Declare two integer variables.

DECLARE NUMBER1, NUMBER2 INTEGER;

By adding a default expression, variables get an initial value.

5. Create a stored procedure in which an initial value is assigned to a local variable. Next, call this stored procedure.

CREATE PROCEDURE TEST
   (OUT NUMBER1 INTEGER)
BEGIN
   DECLARE NUMBER2 INTEGER DEFAULT 100;
   SET NUMBER1 = NUMBER2;
END

CALL TEST (@NUMBER)

SELECT @NUMBER

The result is:

@NUMBER
-------
    100

Explanation If DECLARE statements are used, they must be included as the first statements of a begin-end block. @NUMBER is a user variable.

The expression for the default value is not limited to literals but may consist of compound expressions, including scalar subqueries.

Figure. Create a stored procedure in which a local variable is initiated with the number of players in the PLAYERS table.

CREATE PROCEDURE TEST
   (OUT NUMBER1 INTEGER)
BEGIN
   DECLARE NUMBER2 INTEGER
      DEFAULT (SELECT COUNT(*) FROM PLAYERS);
   SET NUMBER1 = NUMBER2;
END

Local variables can be declared within each begin-end block. After the declaration, the variables can be used in the relevant block, including all subblocks of that block. Those variables are unknown in the other blocks. In the following construct, the variable V1 may be used in all blocks. V2, on the other hand, can be used only in the first subblock, called B2. In the second subblock B3, this variable is unknown, so the SET statement will not be accepted. The last SET statement will also not be accepted.

B1 : BEGIN
   DECLARE V1 INTEGER;
   B2 : BEGIN
      DECLARE V2 INTEGER;
      SET V2 = 1;
      SET V1 = V2;
   END B2;
   B3 : BEGIN
      SET V1 = V2;
   END B3;
   SET V2 = 100;
END B1

Do not confuse local variables with user variables. The first difference is that, in front of local variables, no @ symbol is placed. Another difference is that user variables exist during the entire session. Local variables disappear immediately after the processing of the begin-end block in which they have been declared is finished. User variables can be used within and outside a stored procedure, whereas local variables have no meaning outside a procedure.

For your information, SQL does not support arrays as local variables.



 Python   SQL   Java   php   Perl 
 game development   web development   internet   *nix   graphics   hardware 
 telecommunications   C++ 
 Flash   Active Directory   Windows