Variables

This lesson sheds light on how to declare and use variables in a stored procedure and also discusses the scope of variables.

We'll cover the following

Variables

A variable is nothing but a data object with a name associated to it. Variables help store a user-defined, temporary value which can be referred to in subsequent statements.

A variable must be declared before it can be used in the code. The DECLARE keyword is used to declare variables by providing the data type and an optional default value. If DEFAULT is not used at the time of declaration, then the variable will have NULL value.

Assignment of a value to a variable is done using the SET keyword. Another way to assign values to a variable is to use it in a query with a SELECT INTO statement.

The scope of a variable defines its lifetime after which it becomes unavailable. The scope of a variable created in a stored procedure is local meaning that it will not be accessible after the END statement of the stored procedure. Inside the stored procedure, the scope of a variable depends on where it is declared. This means we can have multiple variables of the same name in a stored procedure as long as they have different scopes.

Syntax

DECLARE VarName DataType (VarLength) [DEFAULT DefaultValue];

SET VarName = value;

SELECT ColName

INTO VarName

FROM TableName;

Connect to the terminal below by clicking in the widget. Once connected, the command line prompt will show up. Enter or copy and paste the command ./DataJek/Lessons/52lesson.sh and wait for the MySQL prompt to start-up.

Level up your interview prep. Join Educative to access 70+ hands-on prep courses.