MySQL stored procedure creation Solution

The following article describes the actual operation analysis of the MySQL stored procedure. We all know that a MySQL stored procedure mainly includes the name and parameter list, there are also a lot of SQL statements and SQL statement sets. The following describes how to create a MySQL stored procedure: Syntax: CREATEPROCEDUREp () BEGIN * Body of the stored procedure *

The following article describes the actual operation analysis of the MySQL stored procedure. We all know that a MySQL stored procedure mainly includes the name and parameter list, there are also a lot of SQL statements and SQL statement sets. The following describes how to create a MySQL stored procedure: Syntax: CREATEPROCEDUREp () BEGIN/* Body of the stored procedure *

The following article describes the actual operation analysis of the MySQL stored procedure. We all know that a MySQL stored procedure mainly includes the name and parameter list, there are also a lot of SQL statements and SQL statement sets. The following describes the specific content,

Create a MySQL 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 MySQL 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 is used to specify the Data Type of a parameter.

OUT indicates that this value is used to output from the stored procedure.

MySQL supports OUT, IN, and INOUT

Call the MySQL 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 custom paging MySQL Stored Procedure)

 
 

  
  
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 ; 
 
 

The above content is an introduction to the MySQL stored procedure. I hope you will get some benefits.