Mysql Stored Procedure syntax

Mysql tutorial Stored Procedure syntax

Variable definition:

Declare variable_name [, variable_name...] datatype [default value];

Datatype is the data type of mysql, such as int, float, date, varchar (length)

Example:

Declare l_int int unsigned default 4000000; declare l_numeric numeric (9.95) default 1999; declare l_date default '2017-12-31 '; declare l_datetime default '2017-12-31 23:59:59 '; declare l_varchar varchar (255) default 'this will not be padded ';

Variable assignment

Set variable name = expression value [, variable_name = expression...]
 

Parameters

Mysql stored procedure parameters are used in the definition of stored procedures. There are three parameter types: in, out, And inout.

Create procedure | function ([[in | out | inout] Parameter Name Data class...])

 

In input parameters

Indicates that the value of this parameter must be specified when the stored procedure is called. Modifying the value of this parameter in the stored procedure cannot be returned, which is the default value.

Out output parameters

This value can be changed within the stored procedure and can be returned

Inout Input and Output Parameters

Can be changed and returned.

 

Stored Procedure:

 

Syntax:

Create procedure p ()

Begin

/* Body of the stored procedure */

End
Create procedure productpricing ()

Begin

Select avg (pro_price) as priceaverage

From products;

End;

# Begin... End is the subject definition of the stored procedure.

# The mysql Delimiter is a semicolon (;)

The method to call a stored procedure is:

 

# Add the process name and brackets to the call.

# For example, call the stored procedure defined above

Call productpricing ();