Shulou(Shulou.com)06/01 Report--

This paper gives an example of how to create (CREATE PROCEDURE) and invoke (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)

Let's not talk about definitions, terms, etc., just look at the examples and get familiar with it. When we're done, 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 sql above, ha:

The first command is DELIMITER / /, which has nothing to do with stored procedure syntax. The DELIMITER statement changes the standard delimiter-semicolon (;) to: / /. In this case, the delimiter changes from a semicolon (;) to a double slash / /. Why do we have to change the separator? Because we want to pass the stored procedure to the server as a whole, rather than let the mysql tool interpret each statement at once. After the END keyword, use the delimiter / / to indicate the end of the stored procedure. The last command (DELIMITER;) changes the delimiter back to the semicolon (;). Create a new stored procedure using the CREATE PROCEDURE statement. Specify the name of the stored procedure after the CREATE PROCEDURE statement. In this example, the name of the stored procedure is: GetAllProducts, with parentheses 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 the business logic. In this stored procedure, we use a simple select query to query the data in the products table.

With the sql above, we have created a stored procedure. When we are done, let's call the stored procedure and 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 sql above and you will see the results of your sql in the stored procedure.

Variable creation (DECLARE) and assignment (SET)

As we all know, a variable is a named data object, and its value can be changed during the execution of the stored procedure. We then try to use variables in the stored procedure to hold the direct / indirect results. These variables are local to the stored procedure, but we have to 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 to take a look at the sql syntax:

DECLARE variable_name datatype (size) DEFAULT default_value

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

First, specify the variable name after the DECLARE keyword. Variable names must follow the naming convention for MySQL table column names. Second, specify the data type of the variable and its size. Variables can have any mysql data type, such as INT,VARCHAR,DATETIME, and so on. Finally, when a variable is declared, its initial value is NULL. However, you can use the DEFAULT keyword to assign default values to variables.

Let's next declare a variable named total_sale with a data type of INT and a default value of 0. Take a look at sql:

DECLARE total_sale INT DEFAULT 0

Where mysql allows you to use a single DECLARE statement to declare two or more variables that share the same data type to look at a 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. When we're done, now that the variable is set, it's time to assign a value. If we want to assign a value to the variable, we can use the set statement to look at an example:

DECLARE total_count INT DEFAULT 0 * * set total_count = 10

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

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

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

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

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

We can understand that if we declare a variable in a BEGIN END block, then if END is reached, it will be out of scope. We can also declare two or more variables with the same name in different scopes because variables are only valid in their own scope. However, it is not a good programming habit to declare variables with the same name in different scopes. Where the variable that begins with the @ symbol is the session variable, which is available and accessible until the end of the session.

More readers who are interested in MySQL-related content can check out this site topic: "MySQL stored procedure skills Collection", "MySQL Common function Summary", "MySQL Log Operation skills Collection", "MySQL transaction Operation skills Summary" and "MySQL Database Lock related skills Summary".

It is hoped that what is described in this article will be helpful to everyone's MySQL database design.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.