MYSQL stored procedure is the summary of common logic knowledge points.

Mysql stored procedure

1. Create stored procedure syntax (format)

DELIMITER $CREATE PROCEDURE stored procedure name A (IN passes parameter name an INT,IN passes parameter name b VARCHAR (20), OUT returns parameter name c INT) BEGIN content. END $

Parsing:

IN represents the passed parameter, defines the passed parameter name, and is followed by the passed parameter type (INT,VARCHAR,DOUBLE,.) OUT represents the data returned by the stored procedure after execution, defines the parameter name, and followed by the parameter type (INT,VARCHAR,DOUBLE,.) INOUT indicates that it can be passed in or returned, defining the parameter name Followed by the parameter type (INT,VARCHAR,DOUBLE,.)

two。 Specific syntax and logic in stored procedures

a. Define variable syntax:

DECLARE variable name a parameter type (INT,VARCHAR (20), BOOLEAN,.) [DEFAULT NULL]

Note: the initial value can be set by adding DEFAULT NULL; after the parameter type.

b. Variable assignment:

Method 1 (assign variables directly):

The variable defined by SET a = NEW ()

Method 2 (the result of the sql query is directly assigned to the variable):

The variable a FROM `student` WHERE defined by SELECT `student`.age INTO.

Mode 3 (the result of the sql query is directly assigned to multiple variables):

SELECT `student`.name AS defined variable a, `student`.age AS defined variable b INTO defined variable a, defined variable b FROM `student`.

c. Logical judgment:

# IF judgment: IF conditional statement (3 > 5) execute when THEN condition is TRUE; END IF; # IF ELSE judgment: IF condition (a > 0) THEN condition is (a > 0) execute. ELSE IF condition (a)