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

The following content mainly brings you the definition and analysis of Mysql stored procedures, the knowledge here is slightly different from books, are summed up by professional and technical personnel in the process of contact with users, have a certain experience sharing value, hope to bring help to the majority of readers.

What is a mysql stored procedure?

In a database system, a set of SQL statements is compiled and stored in the database in order to complete a specific function. The user executes it by setting the name of the stored procedure and giving parameters (if the stored procedure has parameters).

A stored procedure is a compiled set of SQL statements. Save a lot of performance after compilation.

Second, why use stored procedures?

1. The stored procedure is compiled only when it is created, and does not need to be recompiled every time the stored procedure is executed, while the SQL statement is usually compiled every time it is executed, so the stored procedure can improve the execution speed of the database.

2. When performing complex operations on the database (such as UPDATE,INSERT,QUERY,DELETE multiple tables), the complex operation can be encapsulated with stored procedures and used in combination with the transaction processing provided by the database. If these operations are done with programs, they will become SQL statements, which may require multiple links to the database. Instead of stored procedures, you only need to link to the database once.

3. Stored procedures can be reused, which can reduce the workload of database developers.

4. High security can be set that only some users have the right to use the specified stored procedure.

III. Shortcomings of stored procedures

1. Poor portability

2. For simple SQL statements, stored procedures have no advantage.

3. Network transmission may not be reduced in stored procedures.

4. If a user uses a database, stored procedures have no impact on security.

5. team development needs a unified standard, otherwise it will be troublesome to maintain in the later stage.

6. In the case of large concurrent visits, it is not appropriate to write too many stored procedures involving operations.

7. when the business logic is complex, especially when it comes to operating on large tables, it is better to simplify the business logic at the front end.

Fourth, the difference between stored procedures, functions and triggers

1. The trigger is used to complete the actions triggered by some trigger conditions, and the execution of the trigger is automatic.

2. Custom functions can only return a single value or table object through the return statement, while stored procedures cannot call the declare statement, but can return multiple values through the out parameter. Functions can be combined with SQL statements, functions cannot use temporary tables, only table variables can be used, and some system functions are not available

3. Stored procedures are used to complete a series of SQL operations and complete database operations in batches, which are called and executed by users.

Fifth, the creation of stored procedures

Parsing:

1. To write a mysql stored procedure, first change the statement Terminator to / /, because the stored procedure generally contains multiple SQL statements, and if you use the default Terminator, the operation of SQL may be interrupted.

2. Mysql > the fixed statement of the CREATE PROCEDURE stored procedure, followed by the name of the stored procedure, () contains (the parameter name of the stored procedure data type)

3. BEGIN....END is the beginning and end symbol of a stored procedure, with the contents of the stored procedure in the middle

4. / / ends, indicating that the statement of the entire stored procedure has been written

5. Change the Terminator of mysql back to

6. CALL plus the name and parameters of the stored procedure to call the stored procedure

Parameters of the stored procedure

Format:

CREATE PROCEDURE stored procedure name ([[IN | OUT | INOUT] parameter name data class.])

The characteristics of parameter IN: when the stored procedure is running, the value of the external variable is read. After the stored procedure is completed, the value of the external variable does not change, and it is still the value of the external variable set by the stored procedure.

The characteristic of the parameter OUT: the value of the external variable is not read. After the stored procedure is run, the value of the external variable is updated to the value inside the stored procedure.

The characteristics of the parameter INOUT: when the stored procedure is run, the value of the external variable is read. After the stored procedure is run, the value of the external variable is updated to the value inside the stored procedure.

For the above definition and analysis of Mysql stored procedures, if you need to know more, you can continue to pay attention to the innovation of our industry. If you need professional solutions, you can contact the pre-sales and after-sales on the official website. I hope this article can bring you some knowledge updates.

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.