A variable is a named data object whose value can change during the stored procedure execution. We typically use the variables in stored procedures to hold the immediate results. These variables are local to the stored procedure.

In addition to allowing you to create, access, and manipulate session variables from within a stored procedure, MySQL offers the DECLARE keyword, which can be used to declare variables that are “local” to a given routine.

CREATE PROCEDURE decl()
BEGIN
DECLARE count INT;
END

A DECLARE statement must be followed by the variable name and its data type. The same rules that govern user-defined variable names also apply to variables in stored routines. Multiple variables of the same type can be initialized in a single DECLARE statement by separating the variable names with commas.

CREATE PROCEDURE decl()
BEGIN
DECLARE count, retval, x INT;
END

The DECLARE statement also supports an optional DEFAULT keyword, which can be used to assign a default value to a variable.

CREATE PROCEDURE decl()
BEGIN
DECLARE count INT DEFAULT 0;
END

Once defined, these variables can be assigned values using either SET or SELECT INTO statements, and can be accessed by name from other statements within the routine. Note that when accessing a local variable defined with DECLARE, there is no need to prefix the variable name with the @ symbol.

CREATE PROCEDURE add_one()
BEGIN
DECLARE count INT DEFAULT 99;
SELECT (count+1);
END

CALL add_one();

 

JSC0d3's Logo
About JSC0d3

JSC0d3 is an entrepreneur, online marketer, and an employee of an IT company. When not building websites, creating content, or helping customers improve their online business, spend time with their wife and two beautiful children. Although he still feels new in WordPress, he enjoys sharing what he has learned with all of you! If you want to get in touch with him, you can do this through this website.

On the same idea

Posted by | July 8, 2014

MySQL also supports loops in stored routines, thus enabling routines that repeat a series of actions until a prespecified condition is fulfilled
...

Posted by | July 4, 2014

In addition to storing and retrieving values in variables, MySQL lets programmers evaluate different conditions that occur during routine execution...

Posted by | July 1, 2014

This MySQL article explains how to create and drop functions in MySQL with syntax and examples Stored functions are defined in a similar manner to...

Previous PostBackNext Post

Leave here an impression