MYSQL 系列 -存储过程

之前的个人整理、记录

查看数据库中所有的存储过程：

SHOW PROCEDURE STATUS WHERE Db='db_name'

1：创建一个最简单的存储过程

CREATE PROCEDURE pro1()
BEGIN
SELECT * FROM person_copy;
END

调用：

CALL pro1();

2：带输入参数和输出参数的存储过程

CREATE  PROCEDURE pro2
(
 in _id INT,
 in _name VARCHAR(10),
OUT _age INT
)
BEGIN
SELECT age FROM person_copy WHERE id=_id AND `name`=_name;
END

调用

CALL pro2(1,'yzm',@age);

3：存贮过程中的循环体 whlie… do

CREATE PROCEDURE pro3
(
 _name VARCHAR(10),
_age INT
)
BEGIN
DECLARE i INT;
SET i=0;
WHILE i<3
DO
INSERT INTO person_copy VALUES (NULL,_name,_age);
SET i=i+1;
END WHILE;
END

调用就会执行SQL语句插入数据。
执行后，插入了3条数据。

4：存贮过程中的循环体 LOOP……END LOOP

CREATE PROCEDURE pro4
(
 _name VARCHAR(10),
_age INT
)
BEGIN
DECLARE i INT DEFAULT 0;
looplabel:LOOP
INSERT INTO person_copy VALUES(NULL,_name,_age);
SET i=i+1;
IF i>2 THEN
LEAVE looplabel;
END IF;
END LOOP;
END

执行后，插入了3条数据。

5：复杂存储过程带游标

CREATE PROCEDURE pro5
(
 _name VARCHAR(10)
)
BEGIN
DECLARE i INT DEFAULT 0;
looplabel:LOOP
IF i=3 THEN
SET i=i+1;
ITERATE looplabel;
END IF;
INSERT INTO person_copy VALUES (NULL,_name,i);
SET i=i+1;
IF i>=5 THEN
LEAVE looplabel;
END IF;
END LOOP;
END

让我们来看看这个存储过程的过程。

iterate语句类似于continue。结束当前循环进行下一次循环。
首先i的值为0，条件判断语句if i=3 then判断为假，跳过if语段，执行插入语句。然后继续往下走，执行i+1，if i>=5 then判断也为假，也跳过,继续循环,同理1和2再继续插入两次。在i=3的时候，条件判断语句if i=3 then判断为真，执行i=i+1，i值为4，然后执行迭代 ITERATE looplabel，然后此时语句跳到if i=3 then判断语句，执行判断，这个时候由于i=4，if i=3 then判断为假，跳过IF语段，执行插入语句。继续往下走，将i+1，i变为5，条件判断if i>=5 then判断为真，执行LEAVE looplabel跳出loop循环，然后执行end;结束整个存储过程。

执行调用

CALL pro5('youbiao')

看看结果

6:进阶存储过程运用：清理表数据条数。

CREATE PROCEDURE  pro6()
BEGIN
SET @count=(SELECT COUNT(*) FROM person_copy);
IF (@count>10)
THEN
SET @delete_id=(SELECT id FROM person_copy ORDER BY id DESC LIMIT 10,1);
    DELETE FROM person_copy WHERE id<=@delete_id;
END IF;
END

查询表条数，如果表条数大于10条，就清理，只保留最新的十条数据。