An introductory tutorial on MySQL database stored procedures (1/6)

Introduction to Stored Procedures

A stored procedure is a set of SQL statements that are compiled and stored in a database in order to complete a specific function. The user executes it by specifying the name of the stored procedure and giving the parameter (if the stored procedure has parameters). Stored procedures can be executed by an application through a call and allow the user to declare variables. At the same time, a stored procedure can receive and output parameters, return state values for executing stored procedures, or nest calls.

Advantages of stored Procedures

As a stored procedure, you have the following advantages:

(1) Reduce the amount of network traffic. Calling a stored procedure with a low number of rows may not be very different from the network traffic that invokes the SQL statement directly, but if the stored procedure contains hundreds of rows of SQL statements, its performance is definitely much higher than that of a single calling SQL statement.

(2) Faster execution speed. When a stored procedure is created, the database has been parsed and optimized. Second, once the stored procedure is executed, it retains a copy of the stored procedure in memory so that it can be read directly from memory the next time the same stored procedure is executed.

(3) Stronger security. Stored procedures, by granting permissions to users, rather than on tables, can provide access to specific data and improve code security, such as preventing SQL injection.

(4) Business logic can encapsulate stored procedures, which is not only easy to maintain, but also high execution efficiency

Of course, there are some drawbacks to the stored procedure, such as:

1 Portability: When migrating from one database to another, a number of stored procedures are written in a partial modification.

2 stored procedures need to spend a certain amount of learning time to learn, such as learning their grammar.

Stored Procedure Learning Notes

Variables variable

The instruction to declare a variable in a compound statement is declare.

(1) Example with two DECLARE statements

Two examples of DECLARE statements

The code is as follows	Copy Code
CREATE PROCEDURE P8 () BEGIN DECLARE a INT; DECLARE b INT; SET a = 5; SET B = 5; INSERT into T VALUES (a); SELECT S1 * A from T WHERE S1 >= b; End; /* I won ' t call this */

The variables defined in the process are not really defined, you just define them within the begin/end block (i.e., formal parameters).

Note that these variables and session variables are not the same, you cannot use modifiers @ You must clearly declare variables and their types in begin/end blocks.

Once a variable is declared, you can use it anywhere you can use a session variable, text, or column name.

(2) Example with no DEFAULT clause and SET statement

Examples of no default clauses and SET statements

The code is as follows	Copy Code
CREATE PROCEDURE P9 () BEGIN DECLARE a INT/* There is no DEFAULT clause * *; DECLARE b INT/* There is no DEFAULT clause * *; SET a = 5; /* There is a SET statement * * SET B = 5; /* There is a SET statement * * INSERT into T VALUES (a); SELECT S1 * A from T WHERE S1 >= b; End; /* I won ' t call this */

There are many ways to initialize variables. If there is no default clause, then the initial value of the variable is null. You can assign a value to a variable at any time using the SET statement.

(3) Example with DEFAULT clause

Example with DEFAULT clause

The code is as follows	Copy Code
CREATE PROCEDURE P10 () BEGIN DECLARE A, b INT DEFAULT 5; INSERT into T VALUES (a); SELECT S1 * A from T WHERE S1 >= b; End; //

We've made some changes here, but the results are the same. The default clause is used here to set the initial value, which does not need to separate the implementation of the declare and SET statements.

(4) Example of call

Examples of Calls

The code is as follows	Copy Code
Mysql> call P10 ()// +--------+ | S1 * a | +--------+ | 25 | | 25 | +--------+ 2 rows in Set (0.00 sec) Query OK, 0 rows Affected (0.00 sec)

The results show that the process works.

(5) Scope

Scope

The code is as follows	Copy Code
CREATE PROCEDURE P11 () BEGIN DECLARE x1 CHAR (5) DEFAULT ' outer '; BEGIN DECLARE x1 CHAR (5) DEFAULT ' inner '; SELECT X1; End; SELECT X1; End; //

Home 1 2 3 4 5 6 last