MySQL stored procedure writing guide

introduction

Because of too many labels, the structure of label table has been changed in the actual application process.

From the past, we can choose labels at will. Now we need to classify labels to simplify the difficulty of selection. After adding account classification, you need to add account information to the labels that have been used in history to migrate data.

During data migration, stored procedures are used, and many problems are encountered. Hereby record and share the pit climbing process.

stored procedure

Stored Procedure is a kind of database object that stores complex programs in the database for external programs to call.

A stored procedure is a set of SQL statements to complete a specific function. It is compiled, created and saved in the database. Users can call and execute it by specifying the name of the stored procedure and giving parameters (when needed).

Generally speaking, a stored procedure is a compiled SQL function in a database. Like ordinary functions, it has a function body, a sequence structure, a condition structure, a loop structure, parameters, and inputs and outputs.

reference

Please read the following two articles first. On this basis, some details are supplemented.

How MYSQL can automatically upgrade data -- take the update operation if a data table exists and a field does not exist as an example - mengyunzhi software development team of Hebei University of Technology

Database programming practice - jingdongxiang

Identifier Replacement

When defining stored procedures, statements will be executed when semicolons are encountered, which needs to be escaped.

-- Identifier Replacement 
DELIMITER $$

-- Stored procedure definition

-- Redefining identifiers
DELIMITER ;

Defining stored procedures

-- stored procedure
CREATE PROCEDURE procedure_name(IN param BIGINT)
BEGIN

  -- Specific implementation of stored procedure

END$$

Cursor example

Please pay attention to the order problem in the following example code. First declare variables, then declare cursors, and then handle them. Otherwise, MySQL will throw errors.

-- Declare variables
DECLARE tag_id BIGINT;
DECLARE done INT DEFAULT FALSE;
-- declare cursor 
DECLARE cur CURSOR FOR SELECT `id` FROM `tag`;
-- statement not found handle
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur;
FETCH cur INTO tag_id;

WHILE NOT done DO
  -- Traverse label table to migrate data one by one
  CALL migrate_tag_info_by_id(tag_id);
  FETCH cur INTO tag_id;
END WHILE;

HANDLER

The following codes need to be highlighted:

-- statement not found handle
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

To check related blogs, the processor declaring NOT FOUND is the most commonly used cursor traversal method.

The DECLARE ... HANDLER statement specifies a handler that deals with one or more conditions. If one of these conditions occurs, the specified statement executes. statement can be a simple statement such as SET var_name = value , or a compound statement written using BEGIN and END.

The declare... Handler statement specifies a processor that processes single or multiple conditions, and if the conditions are met, the specified statement is executed. Statements can be simple statements like SET, or bggin... End blocks.

So in the complex business scenario, the nesting of cursors will cause problems. Here I choose the call of multiple stored procedures to solve the problem.

When nesting is needed, the inner cursor can be placed in a new stored procedure and called with CALL.

WHILE NOT done DO
  -- Traverse label table to migrate data one by one
  CALL migrate_tag_info_by_id(tag_id);
  FETCH cur INTO tag_id;
END WHILE;

Debugging method

The most difficult thing to write a stored procedure is to write a stored procedure with dozens of lines of code. When executing, the error is not reported by lines, but a kind of general error, which is very difficult to debug.

After consulting the relevant data, MySQL debugging stored procedures need to print the logs manually.

The ideas are as follows:

Create the logging table and write a stored procedure named debug. The function is to write parameters to the logging table. Determine the execution of the stored procedure by logging.

I have encountered the problem that I need to query the middle value of the SELECT statement. The solution is as follows:

Establish a temporary table according to the relevant data structure, and track the relevant intermediate values during the execution of the stored procedure by means of INSERT INTO SELECT.

Complete example

The complete example code is as follows:

-- Identifier Replacement 
DELIMITER $$

-- Stored procedure according to label id Transfer reference data of test questions to labels
CREATE PROCEDURE migrate_subject_tag_ref_by_id(IN tag_id BIGINT)
BEGIN

  DECLARE tag_name VARCHAR(255);
  DECLARE subject_id BIGINT;
  DECLARE course_id BIGINT;
  DECLARE tag_ref_id BIGINT;
  DECLARE done INT DEFAULT FALSE;

  DECLARE cur CURSOR FOR
  SELECT `subject`.`id`, `subject`.`course_id`
  FROM `subject_tags`
  INNER JOIN `subject`
  ON `subject_tags`.`subjects_id` = `subject`.`id`
  WHERE `subject_tags`.`tags_id` = tag_id;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  SELECT `name` INTO tag_name FROM `tag` WHERE `id` = tag_id;

  OPEN cur;
  FETCH cur INTO subject_id, course_id;

  WHILE NOT done DO

    SELECT `id` INTO tag_ref_id FROM `tag`
    WHERE `tag`.`name` = tag_name AND `tag`.`course_id` = course_id;

    UPDATE `subject_tags` SET `tags_id` = tag_ref_id
    WHERE `subjects_id` = subject_id AND `tags_id` = tag_id;

    FETCH cur INTO subject_id, course_id;
  END WHILE;

END$$

-- Stored procedure according to label id Migrate new label data
CREATE PROCEDURE migrate_tag_info_by_id(IN tag_id BIGINT)
BEGIN

  DECLARE tag_name VARCHAR(255);
  DECLARE course_id BIGINT;
  DECLARE done INT DEFAULT FALSE;

  DECLARE cur CURSOR FOR
  SELECT `subject`.`course_id`
  FROM `subject_tags`
  INNER JOIN `subject`
  ON `subject_tags`.`subjects_id` = `subject`.`id`
  WHERE `subject_tags`.`tags_id` = tag_id
  GROUP BY `subject`.`course_id`;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  SELECT `name` INTO tag_name FROM `tag` WHERE `id` = tag_id;

  OPEN cur;
  FETCH cur INTO course_id;

  WHILE NOT done DO
    -- Create a new label
    INSERT INTO `tag`(`name`, `course_id`) VALUES(tag_name, course_id);
    FETCH cur INTO course_id;
  END WHILE;

  -- Migrating references to historical Tags
  CALL migrate_subject_tag_ref_by_id(tag_id);
  -- Delete history label
  DELETE FROM `tag` WHERE `tag`.`id` = tag_id;

END$$

-- Stored procedures migrate all label data
CREATE PROCEDURE migrate_all_tag_info()
BEGIN

  DECLARE tag_id BIGINT;
  DECLARE done INT DEFAULT FALSE;
  DECLARE cur CURSOR FOR SELECT `id` FROM `tag`;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur;
  FETCH cur INTO tag_id;

  WHILE NOT done DO
    -- Traverse label table to migrate data one by one
    CALL migrate_tag_info_by_id(tag_id);
    FETCH cur INTO tag_id;
  END WHILE;

END$$
-- Redefining identifiers
DELIMITER ;

CALL migrate_all_tag_info();

Copyright notice

Author: Mengyunzhi development team of Hebei University of Technology - Zhang xishuo

Tags: MySQL Stored Procedure Database SQL

Posted on Sat, 06 Jun 2020 00:25:42 -0400 by malec