MySQL stored procedure syntax creation and viewing (1/5)

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

Home 1 2 3 4 5 last