@a variables are 'user variables'.
1a) User variables are created implicitly, simply be referencing one. They can appear inside as well as outside procedures:
set @my_variable := 1
;
select @my_variable
;
(see:
http://dev.mysql.com/doc/refman/5.0/en/example-user-variables.html
and:
http://dev.mysql.com/doc/refman/5.0/en/variables.html)
1b) 'Normal', 'declared' or 'local' variables (3 different terms for the same thing) can appear inside procedures, triggers and functions only. They must be declared before they can be referenced:
begin
DECLARE my_variable1 varchar(64) default 'bla'
;
DECLARE my_variable2 int
;
DECLARE my_variable3, my_variable4 date
;
end
the DECLARE statement declares one or several variables. Declare is always inside a BEGIN..END block.
(see:
http://dev.mysql.com/doc/refman/5.0/en/declare-local-variables.html)
2) As you can see, the local variables are declared with a datatype - user variables have a type that is determined from the context. So, in the previous example, my_variable2 will always remain of the int type, because it was declared that way. Whereas:
set @my_variable := 1
;
here, @my_variable is of the int type, and here:
set @my_variable := 'bla'
;
@my_variable is some string type: char, or varchar.
3) User variables allow for a special syntax not available to local variables:
select @my_variable:=column1
;
selects the value of column1 into @my_variable. For a local variable, the equialent would be:
select column1
into my_variable
;
The SELECT..INTO syntax can also be used for user variables.
4) User variables have session scope. Once they are created, they are accessible up to the moment the client terminates the session.
Local variables are visible only within the BEGIN..END blocks (and it's inner blocks). consider this:
OUTER_BLOCK: begin
declare my_var int;
declare my_other_var char;
set my_var := 1; --this my_var, (lets call this: OUTER_BLOCK.my_var), is now 1.
set my_other_var := 'a';
set @my_var := 1; --@my_var is now 1.
INNER_BLOCK: begin
declare my_var int;
set my_var := 5; --this my_var (lets call this: INNER_BLOCK.my_var), is now 5.
set @my_var := 5; --@my_var is now 5. this is the same @my_var as before
select my_other_var; -- my_other_var is visible here, it's still in scope of the OUTER_BLOCK
end INNER_BLOCK;
select my_var; --what do you think, is this my_var 1 or 5? Well, it's 1. It is distinct from the my_var in the inner block
select @my_var; --and this, 1 or 5? Well, it's 5. There's only one @my_var!
end OUTER_BLOCK;
So, User variables have session scope. THat means that they are visible (accessible) from the moment you make your connection up to the moment you disconnect. The @my_var in the example will still be 5, even after the proc ends. This is not at all true for my_var.
Normally, you should use only local variables inside your stored procedures. There must be a good reason to not do that. It's a bit quirky, but the PREPARE syntax often makes it neccessary to introduce user variables (see:
http://dev.mysql.com/doc/refman/5.0/en/sqlps.html).
And, some developers that are used to MS SQL tend to write the @ because that is the syntax for local variables in MS SQL's Transact SQL dialect. Most of the time you see someone writing the @ inside a proc, they are probably not aware of the MySQL syntax regarding these different types of variables.
BTW:
- Procedure and function parameters to a procedure are special local variables that can be used to pass values from and to the calling environment, (see:
http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html)
- Inside a trigger, there are some special local variables that represent the fields in the current record:(http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html)
Edited 1 time(s). Last edit at 01/04/2006 08:48PM by Roland Bouman.