Recently, it was found that the gender field in a user information table was not correctly maintained in the database due to previous problems. Now it needs to be maintained. I wanted to write a program directly. It should be interesting to think about using Mysql. Therefore, I decided to use stored procedures to complete the maintenance of the field. By the way, I reviewed the use of stored procedures

What is a stored procedure

Stored Procedure is a set of SQL statements to complete specific functions

It is mainly used to complete CURD operations, in which process control statements can be used to complete complex judgments and complex operations

After compilation, it can be stored in the database, and users can call it by calling the stored procedure (passing in relevant parameters [not necessary])

Differences between stored procedures and functions

1. The function must have a return value, but the stored procedure does not have this restriction
2. A function can only return one variable, while a stored procedure can return multiple variables
3. Only in class parameters of the function are output parameters, while the stored procedure can have in out input parameters
4. The global database state cannot be modified in the function, but the stored procedure can
5. Functions can be used as part of query statements, but stored procedures cannot

Create stored procedure

Let's start with the last example

DELIMITER // 
CREATE PROCEDURE proc1(int s varchar(20),out ss int,input sss double)
BEGIN
  SELECT COUNT(*) INTO s FROM user;
END //
DELIMITER ;

Explanation:

(1) It should be noted here that DELIMITER / / and DELIMITER; are two sentences. DELIMITER means DELIMITER, because MySQL uses ";" as the DELIMITER by default. If we do not declare the DELIMITER, the compiler will treat the stored procedure as an SQL statement, and the compilation process of the stored procedure will report an error. Therefore, it is necessary to declare the current segment DELIMITER with the DELIMITER keyword in advance SQL will be ';' As the code in the stored procedure, the code will not be executed. After it is used up, the separator should be restored.
(2) The stored procedure may have input, output and input / output parameters as required. There are three parameters here. The first parameter s is the input parameter varchar type, the second parameter ss is the output parameter, and the third parameter sss type is the input / output parameter. You can define the parameters you need freely. Do not follow the above order. The above example parameters are not required

Call of stored procedure

call procedure_name(parame1,parame2,parame3);

The stored procedure completes the maintenance of table information fields

Back to the opening question, use the stored procedure to maintain the gender field in the employee table
It includes mysql circular condition judgment, string function interception, etc

# Delete stored procedure
drop procedure if exists emp_change;

delimiter //
# Create a stored procedure
create procedure emp_change()

begin
    # Current record id
    declare this_id int;
    # Declare the ID card value to be judged
    declare curren_check int;
    # Current gender
    declare this_gender tinyint;
    # Current ID number
    declare this_card_no varchar(20);
    # Declare whether an execution is completed
    declare done int default 0;
    # Set the query results to c1. First, simply take 2 pieces of data to test
    declare c1 cursor for select id,card_no from employees where gender = 0;
    # Declare an exception. If the result set is traversed, set the done ID to 1 to end
    declare continue handler for sqlstate '02000' set done = 1;

    open c1;
    # Open cycle
    repeat
    # Set a default value first
    set this_gender = 0;
    set curren_check = 0;
    # Take out a result set
    fetch c1 into this_id, this_card_no;
    # If done ID can continue
    if not done then
        # If it is the 15 ID number
        if CHAR_LENGTH(this_card_no) = 15 then
            # Intercept the 15th bit value
            set curren_check = right(this_card_no, 1);
            if mod(curren_check,2) = 0 then
                # If you divide it, it's female
                set this_gender = 2;
            else
                set this_gender = 1;
            end if;
        # If it is the 18 ID number
        elseif CHAR_LENGTH(this_card_no) = 18 then
            # Intercept the 17th bit
            set curren_check = substring(this_card_no, -2, 1);
            if mod(curren_check,2) = 0 then
                # If you divide it, it's female
                set this_gender = 2;
            else
                set this_gender = 1;
            end if;
        end if;
        # Set the gender and start to change the sql
        update employees set gender = this_gender where id = this_id;
    end if;
    until done end repeat;
    # close
    close c1;

end //

delimiter ;

# implement
# call emp_change();

This is basically how to use stored procedures

Reference documents:

www.jianshu.com/p/7b2d74701ccd
www.jianshu.com/p/53a12af46836
www.cnblogs.com/chenmh/p/5201473.ht...