2

I am trying to learn MySQL and after doing some database, I am having the following questions.

When declaring a variable inside a procedure or in the function I can do it like this:

  1. using @ sign, like this: SET @varname = varcontent
  2. using DECLARE keyword, like this: DECLARE varname vartype
  3. using directly SET without DECLARE, like this SET varname = varcontent

My doubts are:

  • what type of syntax is correct, using DECLARE, @ or SET?
  • what would be the correct form of doing it?

Also, I would like if you could recommend some good practices with MySQL.

informatik01
  • 16,038
  • 10
  • 74
  • 104
JcAm
  • 59
  • 1
  • 9

1 Answers1

2

varname and @varname are two DIFFERENT variables!

@varname - user-defined variable.

Exists always. Even when it was not initialized and/or used, in such case it has NULL value. I.e. it does not need in declaration.

Have no datatype (or it has dynamic datatype). Datatype may be easily changed by assigning the value of another datatype.

This variable has a scope of a connection. I.e. it exists until the connection exists, and its value is not altered until it is altered explicitly. Each connection has its own variavle with the same name, they do not interfere.

For example, you may set it to some value, then use/alter this value in called stored procedure, then use the value altered in the procedure in outer code after the procedure finished.

varname - local (declared) variable

Not exists in anonymous code, exists only within compound (BEGIN-END) code block. Must be declared explicitly at the beginning of the block. Is destroyed at the end of the block. Special type of local variable is function/procedure parameter - it is declared in function/procedure header and exists within the function/procedure code block.

Has definite datatype. Cannot be re-declared.

Has a scope of a block where it is defined.

If a variable and a column with the same name exists in some scope, then the variable has priority and masks the column, so if you need to access the column you must specify table alias.

In most constructions any variable type may be used. But sometimes only one of types may be used - consult User Manual.

1.using @ like set @varname= varcontent

This statement initializes user-defined variable with some value.

2.using declare like declare varname vartype

This statement declares local variable. May posess at the beginning of BEGIN-END block. Does not set a value to the variable (it is NULL).

3.using directly set varname=varcontent without declare

Causes an error.

Akina
  • 39,301
  • 5
  • 14
  • 25