Article catalog

• Create / call stored procedure
  • Stored procedure body
  • Label statement blocks
  • parameter list
• Advantages and disadvantages of stored procedures
  • Advantages of stored procedures
  • Disadvantages of stored procedures

Stored procedures and functions can be understood as a collection of sql. They are compiled in advance and stored in the database.

Create / call stored procedure

create procedure Stored procedure name(parameter list)
begin
 Stored procedure body
end

For example:

delimiter $$　　#Change the closing symbol of the statement from semicolon; Temporarily changed to two $$(can be customized)

create procedure test()
begin
select * from test_table;
end;

delimiter ;　　#Returns the closing symbol of the statement to a semicolon

call user_data();

Stored procedure body

The stored procedure body contains the statements that must be executed during procedure call, such as dml, ddl statements, if then else and while do statements, declare statements that declare variables, etc.

Procedure body format: start with begin and end with end (can be nested):

BEGIN
　　BEGIN
　　　　BEGIN
　　　　　　statements; 
　　　　END
　　END
END

Label statement blocks

[begin_label:] BEGIN
　　[statement_list]
END [end_label]

For example, when you want to use a loop.

mysql> create procedure pro10()
    -> begin
    -> declare i int;
    -> set i=0;
    -> while i<5 do
    ->     insert into t1(filed) values(i);
    ->     set i=i+1;
    -> end while;
    -> end;//

mysql> create procedure pro12()
    -> begin
    -> declare i int default 0;
    -> loop_label: loop
    ->     insert into t1(filed) values(i);
    ->     set i=i+1;
    ->     if i>=5 then
    ->         leave loop_label;
    ->     end if;
    -> end loop;
    -> end;//

parameter list

　　IN Input parameter: indicates that the caller passes in a value to the procedure (the passed in value can be literal or variable)

　　OUT Output parameter: indicates that the procedure sends out a value to the caller(Multiple values can be returned)(Outgoing value can only be variable)

　　INOUT Input / output parameters: both the value passed in by the caller to the procedure and the value passed out by the procedure to the caller (values can only be variables)

Let's use two examples to understand what is not difficult:

mysql> delimiter $$
mysql> create procedure in_param(in p_in int)
    -> begin
    -> 　　select p_in;
    -> 　　set p_in=2;
    ->    select P_in;
    -> end$$
mysql> delimiter ;

mysql> set @p_in=1;

mysql> call in_param(@p_in);
+------+
| p_in |
+------+
|    1 |
+------+

+------+
| P_in |
+------+
|    2 |
+------+

mysql> select @p_in;
+-------+
| @p_in |
+-------+
|     1 |
+-------+

mysql> delimiter //
mysql> create procedure out_param(out p_out int)
    ->   begin
    ->     select p_out;
    ->     set p_out=2;
    ->     select p_out;
    ->   end
    -> //
mysql> delimiter ;

mysql> set @p_out=1;

mysql> call out_param(@p_out);
+-------+
| p_out |
+-------+
|  NULL |
+-------+
　　#Because out outputs parameters to the caller and does not receive input parameters, P in the stored procedure_ Out is null
+-------+
| p_out |
+-------+
|     2 |
+-------+

mysql> select @p_out;
+--------+
| @p_out |
+--------+
|      2 |
+--------+
　　#Out called_ Param stored procedure, output parameters, changed P_ The value of the out variable

　　>Input value use in Parameters;

　　>Return value usage out Parameters;

　　>inout Use as few parameters as possible.

Advantages and disadvantages of stored procedures

Advantages of stored procedures

1. The first advantage is that the execution speed is fast, because each sql statement needs to be compiled and run, but the stored procedures can be run directly after being compiled 2. The second advantage is to reduce network traffic. The overhead of transmitting a stored procedure is much less than that of transmitting a large number of sql statements 3. The third point is to improve system security, because stored procedures can use permission control, and parameterized stored procedures can effectively place slq injection attacks to ensure their security 4. The fourth point is to reduce the coupling. When our table structure changes, we can modify the corresponding stored procedures, and our application needs to change less to a certain extent 5. The fifth point is strong reusability. Because after we have written a stored procedure, we only need a name to call it again, that is, we can write it once and call it everywhere, and the use of stored procedures can also strengthen the modularity of the program

Disadvantages of stored procedures

1. Poor portability. Because stored procedures are bound to the database, if we change the database and other operations, many places may need to be changed. 2. Inconvenient modification. Because we can't debug the stored procedure very effectively, some of its bugs may be found later, which increases the risk of the application. 3. No obvious advantages and redundant functions. For small web applications, if we use statement caching, we find that the cost of compiling sql is not large, but the use of stored procedures lacks the cost of checking permissions, and these redundant functions will also drag down the performance to a certain extent