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

Source: Internet
Author: User
Tags mysql tutorial

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
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.