The article provides a mysql tutorial stored procedure syntax creation and viewing Oh, about a stored procedure that includes a name, a list of arguments, and a set of SQL statements that can include many SQL statements, let's look at creating a stored procedure and viewing a stored procedure.
To create a stored procedure:
Querying the stored procedures in the database tutorial
Method One:
Select ' Name ' from Mysql.proc where db = ' your_db_name ' and ' type ' = ' procedure '
Method Two:
Show
procedure status;
To view the creation code of a stored procedure or function
Show
CREATE PROCEDURE proc_name;
Show Create function Func_name;
Grammar:
CREATE Procedure P ()
Begin
/* The body of this stored procedure * *
End
CREATE PROCEDURE productpricing ()
Begin
Select AVG (pro_price) as Priceaverage
From Products;
End
# begin...end is the body definition of the stored procedure
# MySQL's delimiters are semicolons (;)
The method to invoke the stored procedure is:
# Call Plus procedure name and a bracket
# For example, call the stored procedure defined above
Call Productpricing ();
# Even without passing arguments, the parentheses "()" Behind the stored procedure name are also required
The method for deleting a stored procedure is:
Drop Procudure productpricing;
To 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-products;
Select Max (prod_price) into the ph from products;
Select AVG (prod_price) into PA out products;
End
# decimal is used to specify the data type of the parameter
# Out is used to indicate that this value is used to output from a stored procedure.
# MySQL support out, in, inout