MySQL Stored Procedure syntax Learning

Introduction:A stored procedure includes a name, a list of parameters, and a set of SQL statements that can contain many SQL statements.

A stored procedure includes a name, a list of parameters, and a set of SQL statements that can contain many SQL statements.

Create a 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 (); # Even if no parameter is required, the brackets () after the stored procedure name are required.

To delete a stored procedure, follow these steps:

Drop procudure productpricing;

Create a stored procedure with parameters:

Create procudure productpricing ( Out P1 decimal (8, 2 ), Out pH decimal (8, 2 ), Out pa decimal (8, 2) ) Begin Select min (prod_price) into Pl from products; Select max (prod_price) into pH from products; Select AVG (prod_price) into PA from products; End; # Decimal: Specifies the Data Type of a parameter. # Out indicates that this value is output from the stored procedure. # MySQL supports out, in, and inout

Call a stored procedure with parameters:

Call productpricing (@ pricelow, @ Pricehigh, @ Priceaverage ); # All parameters must start @ # To obtain the value of @ priceaverage, use the following statement Select @ priceaverage; # Use the following statement to obtain three values: Select @ pricehigh, @ pricelow, @ priceaverage;

Another stored procedure with In and out parameters:

Create procedure ordertotal ( In onumber int, Out ototal decimal (8, 2) ) Begin Select sum (item_price * quantity) From orderitems Where order_num = onumber Into ototal; End; Call ordertotal (20005, @ total ); Select @ total;

Add a complete example: (this is a stored procedure for custom paging)

Delimiter $

Drop procedure if exists 'dbcall'. 'Get _ page' $

Create definer = 'root' @ 'localhost' procedure 'get _ page '(
/** // * Table name */
Tablename varchar (100 ),
/** // * Fileds to display */
Fieldsnames varchar (100 ),
/** // * Page index */
Pageindex int,
/** // * Page size */
Pagesize int,
/** // * Field to sort */
Sortname varchar (500 ),
/** // * Condition */
Strwhere varchar (500)
)
Begin
Declare fieldlist varchar (200 );
If fieldsnames = ''| fieldsnames = NULL then
Set fieldlist = '*';
Else
Set fieldlist = fieldsnames;
End if;

If strwhere = ''| strwhere = NULL then
If sortname = ''| sortname = NULL then
Set @ strsql = Concat ('select', fieldlist, 'from', tablename, 'limit', (pageIndex-1) * pagesize, ',', pagesize );
Else
Set @ strsql = Concat ('select', fieldlist, 'from', tablename, 'ORDER BY', sortname, 'limit', (pageIndex-1) * pagesize ,',', pagesize );
End if;
Else
If sortname = ''| sortname = NULL then
Set @ strsql = Concat ('select', fieldlist, 'from', tablename, 'where', strwhere, 'limit ', (pageIndex-1) * pagesize ,',', pagesize );
Else
Set @ strsql = Concat ('select', fieldlist, 'from', tablename, 'where', strwhere, 'ORDER BY', sortname, 'limit', (pageIndex-1) * pagesize, ',', pagesize );
End if;
End if;
Prepare stmt1 from @ strsql;
Execute stmt1;
Deallocate prepare stmt1;
End $

Delimiter;