How to create (CREATE PROCEDURE) and call (CALL) a MySQL stored procedure and how to create (DECLARE) and assign (SET) a variable

This article uses examples to describe how to create (CREATE PROCEDURE) and call (CALL) MySQL stored procedures and how to create (DECLARE) and assign (SET) variables. Share with you for your reference, the details are as follows:

Stored procedure creation (CREATE PROCEDURE) and call (CALL)

We won’t talk about any definitions or terms here, just look at the examples and slowly get familiar with it. Now let's create a simple stored procedure called GetAllProducts(). This GetAllProducts() stored procedure is mainly used to select all products from the products table. Let's start the mysql client tool and type the following command:

DELIMITER //
 CREATE PROCEDURE GetAllProducts()
  BEGIN
  SELECT * FROM products;
  END //
DELIMITER ;

Let's take a closer look at the above SQL:

• The first command is DELIMITER //, which has nothing to do with stored procedure syntax. The DELIMITER statement changes the standard delimiter - the semicolon (;) - to ://. In this case, the delimiter is changed from a semicolon (;) to double slashes //. Why do we have to change the delimiter? Because we want to pass the stored procedure as a whole to the server, rather than having the mysql tool interpret each statement one at a time. After the END keyword, use the delimiter // to indicate the end of the stored procedure. The last command (DELIMITER;) changes the delimiter back to a semicolon (;).
• Use the CREATE PROCEDURE statement to create a new stored procedure. Specify the name of the stored procedure after the CREATE PROCEDURE statement. In this example, the name of the stored procedure is: GetAllProducts, and the parentheses are placed after the name of the stored procedure.
• The part between BEGIN and END is called the body of the stored procedure. Place declarative SQL statements in the body to handle business logic. In this stored procedure, we use a simple select query to query the data in the products table.

Through the above SQL, we have created a stored procedure. When we are done, we will call the stored procedure. Let's take a look at the calling syntax:

CALL STORED_PROCEDURE_NAME();

Let's take a look at the specific SQL that calls the GetAllProducts() stored procedure:

CALL GetAllProducts();

Run the above SQL and you will see the results of running the SQL in the stored procedure.

Variable creation (DECLARE) and assignment (SET)

As we all know, a variable is a named data object whose value can be changed during the execution of a stored procedure. Next, we will try to use variables in the stored procedure to save the direct/indirect results. These variables are local variables of the stored procedure, but we must note that the variable must be declared before it can be used. If we want to declare a variable in a stored procedure, we can use the DECLARE statement. Let's take a look at the SQL syntax:

DECLARE variable_name datatype(size) DEFAULT default_value;

Let's take a look at what the above sql means:

• First, specify the variable name after the DECLARE keyword. Variable names must follow the naming conventions for MySQL table column names.
• Next, specify the data type of the variable and its size. The variable can be of any mysql data type like INT, VARCHAR, DATETIME etc.
• Finally, when a variable is declared, its initial value is NULL. But you can assign a default value to a variable using the DEFAULT keyword.

Next, we will declare a variable called total_sale, with a data type of INT and a default value of 0. Let's look at the sql:

DECLARE total_sale INT DEFAULT 0;

Among them, MySQL allows the use of a single DECLARE statement to declare two or more variables that share the same data type. Let's take a look at the sql:

DECLARE x, y INT DEFAULT 0;

In the above sql, we declared two integer variables x and y and set their default values ​​to 0. Now that the variable has been set, it is time to assign a value. If we want to assign a value to a variable, we can use the SET statement. Let's take a look at an example:

DECLARE total_count INT DEFAULT 0;
SET total_count = 10;

In the above sql statement, we assign the value 10 to the total_count variable. In addition to the SET statement, you can also use the SELECT INTO statement to assign the result of a query to a variable. Let's look at an example:

DECLARE total_products INT DEFAULT 0
 
SELECT COUNT(*) INTO total_products
FROM products

In the above sql, we should have the following understanding:

• First, declare a variable called total_products and initialize its value to 0.
• Then, use the SELECT INTO statement to assign the value to the total_products variable, selecting the number of products from the products table in the database.

We know that a variable has its own scope, which is used to define its life cycle. But if you declare a variable inside a stored procedure, it will go out of scope when the stored procedure's END statement is reached, and therefore cannot be accessed in other code blocks.

We can understand that if we declare a variable inside a BEGIN END block, then it will go out of scope if END is reached. We can also declare two or more variables with the same name in different scopes because a variable is valid only within its own scope. However, declaring variables with the same name in different scopes is not a good programming practice. Among them, variables starting with the @ symbol are session variables, which are available and accessible until the session ends.

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL stored procedure skills", "MySQL common function summary", "MySQL log operation skills", "MySQL transaction operation skills summary" and "MySQL database lock related skills summary"

I hope this article will be helpful to everyone's MySQL database design.