Variables in Stored Routines

There are 3 kinds of variables used:

1. System Variables (system global scope)
2. User Defined Variables (connection global scope)
3. Local variables (block scope)

System variables has the name of kind @@var_name. Note, that system variable names are not case sensitive. There are read-only variables. Trying to assign something to such variable you will get an exception. Currently there are 3 variables:

1. @@LastErrorNumber
2. @@LastErrorMessage
3. @@LastErrorQuery

The first two variables are filled with last exception data which is raised inside some SQL statement. @@LastErrorQuery contains the query which produced the exception. So you can do something like this:

CREATE TABLE t1 ( f1 LONG UNIQUE );
 
--
CREATE OR REPLACE PROCEDURE errHandler()
BEGIN
    PRINT @@LastErrorQuery || HEX(@@LastErrorNumber) || @@LastErrorMessage;
END;
 
--
CREATE OR REPLACE PROCEDURE sp1()
BEGIN
...
    INSERT INTO t1 VALUES( 1 );
    INSERT INTO t1 VALUES( 1 );
...
EXCEPTION
    WHEN ERR_CONSTRAINT_UNIQUE_VIOLATION THEN
        CALL errHandler();
END;

You can store a value in a user-defined variable and then refer to it later. This enables you to pass values from one statement to another. User-defined variables are connection-specific. That is, a user variable defined by one client cannot be seen or used by other clients. All variables for a given client connection are automatically freed when that client exits.

You can use user variables in LOCAL mode, i.e. when you not using Valentina Server. It will behave like a global variable. Such variable is available all the time between ValentinaInit() and ValentinaShutdown() calls.

User variables has the name of kind @var_name. Note, that user variable names are not case sensitive.

To define a user variable you can use SET statement. The expr assigned to each variable can evaluate to an integer, real, string, or NULL value.

SET @var_name = expr [, @var_name = expr] ...

Example:

SET @x = 5, @y = 7, @z = 'abcd';

If you use user variable without initializing it, then its value is NULL. The only legal way to use not initialized user variables is passing them into OUT or INOUT parameters of a stored procedure, so procedure will assign them values.

Example:

CREATE PROCEDURE p (IN inX INT, INOUT ioY INT)
BEGIN
  SET ioY = inX;
END;
 
SET @x = 10;
CALL proc(@x, @y);   -- @y now has value 10. 

User variable can be used in context where an expression is allowed.

NOTE: User Variables can be used without stored routines at all.

This statement is used to declare local variables.

DECLARE var_name[,...] TYPE [DEFAULT VALUE]

To provide a default value for the variable, include a DEFAULT clause. The value can be specified as an expression; it need not be a constant. If the DEFAULT clause is missing, the initial value is NULL.

The scope of a local variable is within the BEGIN … END block where it is declared. The variable can be referred to in blocks nested within the declaring block, except those blocks that declare a variable with the same name.

SET var_name = expr [, var_name = expr] ...

Example:

SET x = 5, y = 7, z = 'abcd';

The SET statement in stored routines is an extended version of the general SET statement. Referenced variables may be ones declared inside a routine, or global system variables.

The SET statement in stored routines is implemented as part of the pre-existing SET syntax. This allows an extended syntax of SET a=x, b=y, … where different variable types (locally declared variables and global and session server variables) can be mixed.

SET var_name[,...] = SELECT col_name[,...] table_expr ...

or (mySQL compatible syntax)

SELECT col_name[,...] INTO var_name[,...] table_expr ...

This SELECT syntax stores selected columns directly into variables. Therefore, only a single row may be retrieved.

Example:

SET x,y = SELECT id,DATA FROM test.t1 LIMIT 1;

or

SELECT id,DATA INTO x,y FROM test.t1 LIMIT 1;