Example Analysis of mysql Stored Procedure Usage

  • 2021-10-16 05:14:44
  • OfStack

This article illustrates the use of mysql stored procedures. Share it for your reference, as follows:

Overview:

To put it simply, it is a group of SQL statement sets, which are powerful and can realize some complex logical functions, similar to the methods in JAVA language;

Stored procedures are somewhat similar to triggers, both of which are a set of SQL sets, but stored procedures are actively called and more powerful than triggers, and triggers are automatically called after something is triggered;

Example


DELIMITER //
CREATE PROCEDURE proc (IN num INT)
BEGIN
SELECT * FROM v9_qd_account limit num;
END //
DELIMITER ;

Execution:


SET @p_in=5;
CALL proc(@p_in);

Or


CALL proc(5);

Cycle


DECLARE num int default 5;
SET num = 1;
SET num = num + 1;


DELIMITER //
CREATE PROCEDURE proc(IN sname VARCHAR(20),IN pwd VARCHAR(5),IN qd INT,IN start INT,IN end INT)
  begin
  DECLARE var INT;
  DECLARE myname VARCHAR(30);
  SET var=start;
  while var<end do
    SET myname = CONCAT(sname,LPAD(var,3,'0'));
    insert into v9_qd_account (storename,password,qudao,regdate) value(myname,md5(pwd),qd,UNIX_TIMESTAMP());
    SET var=var+1;
  end while;
  end //
DELIMITER ;

Call


CALL proc('test','123456',1,1,21);

View


SHOW PROCEDURE STATUS LIKE 'C%' \G;  View in letters C Stored procedure at the beginning 
SHOW CREATE PROCEDURE proc \G;  View status and create statements 
information_schema.Routines  View in 

Delete


DROP PROCEDURE IF EXISTS proc;
DROP FUNCTION mytest;

Dial

If there is Chinese in the parameter, it can be like this


CREATE PROCEDURE useInfo(IN u_name VARCHAR(50) character set gbk,OUT u_age INT) ......

More readers interested in MySQL can check out the topics on this site: "MySQL Stored Procedure Skills Encyclopedia", "MySQL Common Function Summary", "MySQL Log Operation Skills Encyclopedia", "MySQL Transaction Operation Skills Summary" and "MySQL Database Lock Related Skills Summary"

I hope this article is helpful to everyone's MySQL database.


Related articles: