mysql stored procedure

Posted by andco on Wed, 09 Feb 2022 13:21:08 +0100

1, Overview

Stored procedures can be understood as a collection of SQL statements (equivalent to a function method in PHP to implement business logic). They are compiled in advance and stored in the database.

Calling stored procedures has the same effect as directly executing SQL statements, but one advantage of stored procedures is that the processing logic is encapsulated in the database.

When we call a stored procedure, we do not need to understand the processing logic. Once the processing logic changes, we only need to modify the stored procedure, which has no impact on the program calling it.

Calling stored procedures and functions can simplify a lot of work of application developers, reduce the transmission of data between database and application server, reduce the interaction with script language and bandwidth, and improve the efficiency of data processing.


2, Stored procedure structure

create procedure [Stored procedure name(parameter list)]
    [Stored procedure body]
call Stored procedure name(parameter list)

3, Use example

Example 1. Create a new data table and add 1 million records to this data table.

(1) New data table

CREATE TABLE `test_table` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `loop` int(10) unsigned NOT NULL DEFAULT '0',
  `name` varchar(256) NOT NULL DEFAULT '',
  `pen_name` varchar(256) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)

(2) New stored procedure

DROP PROCEDURE IF EXISTS insert_many_rows;

CREATE PROCEDURE insert_many_rows (IN loopTime INT)
    DECLARE executedTime INT ;
    SET executedTime = loopTime;
    while executedTime > 0 DO
        INSERT INTO test_table(NULL, 0, 'sss', 'kkk');
        SET executedTime = executedTime - 1;

(3) Call stored procedure

CALL insert_many_rows(1000000);

The result should be that there are 1 million records in the new data table.

Example 2: create 10 data tables through stored procedures, namely test_ table_ 0 ~ test_ table_ nine

(1) Create stored procedure

DROP PROCEDURE IF EXISTS create_test_tables;
CREATE PROCEDURE `create_test_tables`()
    DECLARE tableName VARCHAR(30);
    DECLARE sqlText text;

    SET i = 0;

    WHILE i < 10 DO
      SET tableName = CONCAT('test_table_' , i);
      SET sqlText = CONCAT('CREATE TABLE ', tableName , '(
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `loop` int(10) unsigned NOT NULL DEFAULT ''0'',
  `name` varchar(256) NOT NULL DEFAULT '''',
  `pen_name` varchar(256) NOT NULL DEFAULT '''',
  PRIMARY KEY (`id`)
      SET @sqlText = sqlText;

      PREPARE stmt
      EXECUTE stmt;
      SET i = i + 1;

(2) Call stored procedure

CALL create_test_tables();

4, Analysis

1. Parameter type of stored procedure:

(1) IN means only for input.

(2) OUT means only for output.

(3) INOUT can be used as input or output.

2. Variable declaration in stored procedure

DECLARE a local variable through DECLARE. The scope of the variable is only begin End block.

Default values can be added to the declaration of variables, such as:


3. Process control statement syntax

if The syntax format is:
if Conditional expression then sentence
    [elseif Conditional expression then sentence] ....
    [else sentence]
end if

case Syntax format of
 The first is the first way to write:
case expression
    when value then sentence
    when value then sentence
    [else sentence]
end case
 Then there is the second way:
    when expression then sentence
    when expression then sentence
    [else sentence]
end case

loop The syntax format of the loop is:
[grade:] loop
    Circular statement
end loop [grade]

while grammar
while a>100 do
 Circular statement
End while

Repeat        //cursor
  SQL Statement 1
  UNTIL Conditional expression
END Repeat;

  SQL sentence
  All conditional judgments and jumps need to be realized by themselves
End loop

leave Statement is used to exit from the annotated process construct. It is usually associated with begin...end Or cycle together
leave grade;

Declaration statement terminator, which can be customized:
DELIMITER [accord with]
delimiter $$


4. Data types in stored procedures

Value type: Int, float, double, decimal

Date type: timestamp, date, year

String: char, varchar, text

5, Advantages and disadvantages of stored procedures

1. Advantages:

(1) Fast execution speed. Because every SQL statement needs to be compiled and then run. However, after the stored procedures are compiled directly, they can be run directly.

(2) 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) Improve system security, because stored procedures can use permission control, and parameterized stored procedures can effectively prevent SQL injection attacks. Ensure its safety.

(4) Coupling is reduced. When our table structure is adjusted or changed, we can modify the corresponding stored procedure, and our application needs to change less to a certain extent.

(5) Reusability is strong, because after we write a stored procedure, we only need a name to call it again, that is, "write once, call anywhere", and the use of stored procedures can also strengthen the modularity of the program.

2. Disadvantages:

(1) Poor portability. Because the stored procedure is bound to the database, if we want to replace the database and other operations, many places may need to be changed.

(2) Inconvenient to modify. Because for stored procedures, we can't debug them particularly effectively. Some of its bugs may be found later, which increases the risk of 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 using stored procedures requires the cost of checking permissions. These redundant functions will also drag down the performance to a certain extent.

6, Using stored procedures in PHP

Stored procedures can also be used in PHP, and the use of stored procedures is also very simple. Just execute the create statement and call statement of the stored procedure separately.

The simplest pdo call method is used here. If you are in the framework, in order to maintain the beauty of the code, please use the query execution statement provided by the framework.

declare(strict_types = 1);

// Note: the create stored procedure and call stored procedure should be executed separately. After creating the stored procedure, comment out the part of the create stored procedure, and then open the call stored procedure for code execution

// pdo connection
$dsn = "mysql:dbname=test;host=";
$pdo = new PDO($dsn,'root','123456');

# -------------------------------Create stored procedure--------------------------
// Create stored procedure statement assignment to variable
$sql = 'DROP PROCEDURE IF EXISTS insert_many_rows_2;
CREATE PROCEDURE insert_many_rows_2 (IN loopTime INT)
    DECLARE executedTime INT ;
    SET executedTime = loopTime;
    while executedTime > 0 DO
        INSERT INTO test_table(NULL, 0, \'sss\', \'kkk\');
        SET executedTime = executedTime - 1;

// implement
$stmt = $pdo->query($sql);

#----------------------call stored procedure--------------------------

/*$callSql = 'CALL insert_many_rows(1000000);';
$stmt = $pdo->query($callSql);

7, Summary

It's enough to know about stored procedures. In fact, many companies prohibit the use of stored procedures, mainly because once stored procedures are used, it will be very difficult for newcomers to take over, debug and expand, and there is no portability.

Moreover, the problems that can be solved by stored procedures can also be solved by general program code. Therefore, when it is not necessary, it is better to use code instead of considering using stored procedures.

Topics: Database MySQL