Mysql stored procedures, Mysql stored procedures

Mysql stored procedures, Mysql stored procedures
1. What is stored procedure

Simply put, it is a set of SQL statements with powerful functions. It can implement complicated logic functions, similar to the methods in JAVA;

Ps: the stored procedure is similar to the trigger, and it is a set of SQL sets. However, the stored procedure is called proactively and has more powerful functions than the trigger. The trigger is automatically called after a certain event is triggered;

Ii. Features

There are input and output parameters, variables can be declared, and control statements such as if/else, case, and while can be implemented by compiling stored procedures;

Common Features of functions: modularization, encapsulation, and code reuse;

High speed. Only the first execution requires compilation and optimization steps. You can directly execute the subsequent calls without the above steps;

3. Create a simple Stored Procedure

The proc_adder function of the stored procedure is very simple. Two integer input parameters a and B, and one integer output Parameter sum, calculate the results of input parameters a and B and assign values to the output parameter sum;

Notes:

DELIMITER;: As mentioned earlier, replace the default input Terminator ;;.

DEFINER: Creator;

-- ------------------------------ Procedure structure for `proc_adder`-- ----------------------------DROP PROCEDURE IF EXISTS `proc_adder`;DELIMITER ;;CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_adder`(IN a int, IN b int, OUT sum int)BEGIN    #Routine body goes here...    DECLARE c int;    if a is null then set a = 0;     end if;      if b is null then set b = 0;    end if;
    set sum  = a + b;END;;DELIMITER ;

Run the above storage result to verify whether the result is correct. For example, the result is OK:

set @b=5;call proc_adder(2,@b,@s);select @s as sum;

Iv. control statements in Stored Procedures

IF statement:

-- ------------------------------ Procedure structure for `proc_if`-- ----------------------------DROP PROCEDURE IF EXISTS `proc_if`;DELIMITER ;;CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_if`(IN type int)BEGIN    #Routine body goes here...    DECLARE c varchar(500);    IF type = 0 THEN        set c = 'param is 0';    ELSEIF type = 1 THEN        set c = 'param is 1';    ELSE        set c = 'param is others, not 0 or 1';    END IF;    select c;END;;DELIMITER ;

CASE statement:

-- ------------------------------ Procedure structure for `proc_case`-- ----------------------------DROP PROCEDURE IF EXISTS `proc_case`;DELIMITER ;;CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_case`(IN type int)BEGIN    #Routine body goes here...    DECLARE c varchar(500);    CASE type    WHEN 0 THEN        set c = 'param is 0';    WHEN 1 THEN        set c = 'param is 1';    ELSE        set c = 'param is others, not 0 or 1';    END CASE;    select c;END;;DELIMITER ;

Loop while statement:

-- ------------------------------ Procedure structure for `proc_while`-- ----------------------------DROP PROCEDURE IF EXISTS `proc_while`;DELIMITER ;;CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_while`(IN n int)BEGIN    #Routine body goes here...    DECLARE i int;    DECLARE s int;    SET i = 0;    SET s = 0;    WHILE i <= n DO        set s = s + i;        set i = i + 1;    END WHILE;    SELECT s;END;;DELIMITER ;

Others: omitted ~

5. disadvantages of the stored procedure:

Different databases have different syntaxes and are difficult to transplant. If you change the database, you need to rewrite it;

It is difficult to manage and write too much business logic in the stored procedure, which is not conducive to hierarchical management and confusion. Generally, stored procedures are suitable for businesses with high performance requirements, and other needs are not very great;

...