what is mysql stored procedure

The stored procedure in MySQL refers to a collection of SQL statements stored in the database. When the stored procedure is created and the required parameters are provided at runtime, the stored procedure can be used in the way specified by the code. Parameter execution and return value

provides the ability to create stored procedures in MySQL. Stored procedures are a powerful feature in MySQL and other database management systems. Next, in this article, we will introduce the stored procedures in MySQL in detail, which will serve as a reference and I hope it will be helpful to you.

[Recommended course: MySQL Tutorial]

Stored procedure:

Stored procedures refer to a collection of SQL statements stored in the database. Stored procedures can contain business logic, which is one of the keys to distinguishing stored procedures from views. In addition, stored procedures can also accept parameters. We can set variables, write statements, etc. in the stored procedure.

How stored procedures work

First create a stored procedure and then run it. You need to provide the parameters it needs when running the process, and then the stored procedure will be executed using the parameters in any way specified by the code. For example, write a stored procedure that accepts a FruitId parameter. The stored procedure can then get that parameter and use it to check the inventory of that particular fruit. So we can call the stored procedure i.e. every time with a different fruit ID it will return a value and show how many fruits are in that stock.

Create a stored procedure

We can create a stored procedure through the CREATE PROCEDURE statement

CREATE PROCEDURE demo_name(p_1 INT)
BEGIN
  ...code goes here...
END;

demo_name refers to the name of the stored procedure, and parentheses are required , if it does not contain any parameters, the brackets can be empty

The body of the stored procedure is between the BEGIN and END keywords. These keywords are used to write compound statements. A compound statement can contain multiple statements, which can be nested if necessary.

Example: Create a stored procedure named FruitStock:

DELIMITER //

CREATE PROCEDURE FruitStock(thisFruit SMALLINT)
BEGIN
	SELECT 
		Fruit.FruitName, 
		Fruit.Inventory, 
		Units.UnitName
	FROM 
		Fruit INNER JOIN Units ON
		Fruit.UnitId = Units.UnitId
	WHERE 
		Fruit.FruitId = thisFruit;
END //

DELIMITER ;

After creating a stored procedure, this stored procedure will be called next.

Call an ID with a parameter of 1

CALL FruitStock(1);

Delete stored procedures

You can use the DROP PROCEDURE statement to delete stored procedures.

DROP PROCEDURE FruitStock;

Change the stored procedure

In the stored procedure, you can change the stored procedure ALTER PROCEDURE through the following statements.

It should be noted that when you want to change the body of a stored procedure or any of its parameters, you need to delete the procedure and create it again

Example: Add the list to be returned by Fruit.FruitId

DROP PROCEDURE IF EXISTS FruitStock;
DELIMITER //
CREATE PROCEDURE FruitStock(thisFruit SMALLINT)
BEGIN
	SELECT 
		Fruit.FruitId, 
		Fruit.FruitName, 
		Fruit.Inventory, 
		Units.UnitName
	FROM 
		Fruit INNER JOIN Units ON
		Fruit.UnitId = Units.UnitId
	WHERE 
		Fruit.FruitId = thisFruit;
END //

DELIMITER ;

Summary: The above is the entire content of this article, I hope it will be helpful to everyone.

The above is the detailed content of what is mysql stored procedure. For more information, please follow other related articles on the PHP Chinese website!