MySQL's stored procedure

Definition of a stored procedure

A stored procedure is a lump of declarative SQL statements stored in a database directory.

Applications such as java,python,php can call stored procedures.

Since the MySQL 5.0 release, stored procedures, stored functions, triggers and events have been added to the MySQL database engine.

Second, the advantages of stored procedures

1. Typically, stored procedures help improve the performance of your application. When created, the stored procedure is compiled and stored in the database. However, the storage of MySQL implementations has been slightly different. MySQL stored procedures are compiled on demand. After compiling the stored procedure, MySQL puts it into the cache. MySQL maintains its own stored procedure cache for each connection. If your application uses stored procedures more than once in a single connection, the compiled version is used, or the stored procedure works like a query.

2. Stored procedures help reduce traffic between the application and the database server, because the application does not have to send multiple lengthy SQL statements, but only the name and parameters of the stored procedure.

3. Stored procedures are reusable and transparent to any application. Stored procedures expose the database interface to all applications so that developers do not have to develop features that are already supported in stored procedures.

4, the stored procedures are safe. The database administrator can grant the appropriate permissions to the application that accesses the stored procedures in the database without giving any permissions to the underlying database.

Third, the shortcomings of the stored procedure

1. If you use a large number of stored procedures, the memory usage of each connection using these stored procedures will be greatly increased. In addition, if you overuse a large number of logical operations in a stored procedure, the CPU usage also increases because the database server is poorly designed for logical operations.

2. The construction of stored procedures makes it more difficult to develop stored procedures with complex business logic.

3, it is difficult to debug stored procedures. Only a handful of database management systems allow you to debug stored procedures. Unfortunately, MySQL does not provide the ability to debug stored procedures.

4. Developing and maintaining stored procedures is not easy. Developing and maintaining stored procedures often requires a professional skill that is not owned by all application developers. This can lead to problems in the application development and maintenance phases.

四、一个 simple MySQL Stored procedure example

// Create procedure B1 ()    begin   Select *   from blog;    End // delimiter;

Explain:

1. The first imperative delimiter//, which is independent of the stored procedure syntax. The Delimter statement changes the standard delimiter-semicolon (;) to://. In this case, the delimiter is changed from a semicolon (;) to a double slash//. Why do we have to change the delimiter? Instead of having the MySQL tool explain each statement at once, we want to pass the store over to the server as a whole. After the end keyword, use the delimiter//to indicate the result of the stored procedure. The last command (delimter;) changes the delimiter back to a semicolon (;).

2. Create a new stored procedure using the creation procedure statement. Specifies the name of the stored procedure after the CREATE PROCEDURE statement. In this example, the name of the stored procedure is: B1, and the parentheses are placed after the name of the stored procedure.

3. 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 statement to query the data in the blog table.

# Calling stored procedure in MySQL call B1 (); #在python中基于pymysql调用 cursor. Callproc ('b1'print(cursor. Fetchall ())

V. Declaring variables

To declare a variable in a stored procedure, you can use the Delclare statement, as follows:

DECLARE DEFAULT default_value;

Here's a more detailed explanation of the above statement:

First, you specify the variable name after the DECLARE keyword. Variable names must follow the naming conventions 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.

Thirdly, when a variable is declared, its initial value is null. However, you can assign a default value to a variable by using the default keyword.

Realize:

Delimiter// Create procedureB2 ()begin   DECLARENint DEFAULT 1; SetN= 5; Select *   fromBlogwhereId=N; End //delimiter; # Calling stored procedure in MySQL call B2 ();

Vi. Stored Procedure Transfer parameters

In real-world applications, the development of stored procedures requires almost all parameters. These parameters make stored procedures more flexible and useful. In MySQL, there are three modes of parameters: In,out or inout.

　　 in-is the default mode. When you define an in parameter in a stored procedure, the calling program must pass parameters to the stored procedure. In addition, the value of the in parameter is protected. This means that even if the value of the in parameter is changed in the stored procedure, its original value remains at the end of the stored procedure. In other words, the stored procedure uses only a copy of the in parameter.

　　 out-You can change the value of the out parameter in the stored procedure and pass it back to the calling program after the new value is changed. Note that the stored procedure cannot access the initial value of the out parameter at startup.

　　The INOUT -INOUT parameter is a combination of in and out parameters. This means that the calling program can pass parameters, and the stored procedure can modify the InOut parameter and pass the new value back to the calling program.

The syntax for defining parameters in a stored procedure is as follows:

MODE param_name Param_type (param_size)

Depending on the purpose of the parameters in the stored procedure, mode can be either In,out or inout.

Param_name is the name of the parameter. The name of the parameter must follow the naming rules for the column names in MySQL.

After the parameter name is its data type and size. As with variables, the data type of the parameter can be any valid MySQL data type

PS: If the stored procedure has more than one parameter, each parameter is separated by a comma (,).

# 1.indelimiter//CREATE PROCEDURE B3 (in     blogname varchar ())   begin   SELECT * from  blog where NAME = bl Ogname;   End//delimiter; #mysql中调用存储过程call B3 (' 5th '); #python中调用存储过程cursor. Callproc (' B3 ', args = (' 5th ')); # 2.outdelimiter//CREATE PROCEDURE B4 (     in year int, out     count  int)   begin       SELECT count (1) into count From  blogging GROUP by Date_format (sub_time, '%Y ') has Max (Date_format (sub_time, '%Y ')) = year;       Set count = 6;   End//delimiter; call B4 (@count); select @count;  #out只能当返回值 # 3.inoutdelimiter//CREATE PROCEDURE B5 (     inout N1 int)   begin    SELECT * from blog where ID > N1;   End//delimiter; #mysql中调用set @n = 3;call b5 (@n); select @n; #在python中基于pymysql调用cursor. Callproc (' B5 ', (4)) print ( Cursor.fetchall ()) #查询select的查询结果cursor. Execute (' select @n1 ') print (Cursor.fetchall ()) # InOut: can be passed in and returned

　　

MySQL's stored procedure