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.