Mysql Stored Procedure syntax creation and viewing (1/5)

This article provides a mysql Stored Procedure syntax for creating and viewing. For a stored procedure, including the name, parameter list, and a set of SQL statements that can contain many SQL statements, next let's take a look at creating a stored procedure and viewing a stored procedure.

This article provides a mysql tutorial on creating and viewing Stored Procedure syntax. For a stored procedure, including the name, parameter list, and a set of SQL statements that can contain many SQL statements, next let's take a look at creating a stored procedure and viewing a stored procedure.

Create a stored procedure:

Query the stored procedure in the database tutorial

 

Method 1:

Select 'name' from mysql. proc where db = 'your _ db_name 'and 'type' = 'Procedure'

 

Method 2:

Show procedure status;

 

 

View the code for creating a stored procedure or function

 

Show create procedure proc_name;
Show create function func_name;

 

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

1 2 3 4 5