一、存储过程基础语法

1.1、设置参数

• 命令开启：允许创建函数设置

# 不加global只是当前窗口有效
SET GLOBAL log_bin_trust_function_creators = 1;

1.2、随机产生字符串

DELIMITER $
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
    DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
    DECLARE return_str VARCHAR(255) DEFAULT '';
    DECLARE i INT DEFAULT 0;
    WHILE i < n
        DO
            SET return_str = CONCAT(return_str, SUBSTRING(chars_str, FLOOR(1 + RAND() * 52), 1));
            SET i = i + 1;
        END WHILE;
    RETURN return_str;
END $
DELIMITER ;

1.3、用于随机产生多少到多少的编号

DELIMITER $
CREATE FUNCTION rand_num(from_num INT, to_num INT) RETURNS INT(11)
BEGIN
    DECLARE i INT DEFAULT 0;
    SET i = FLOOR(from_num + RAND() * (to_num - from_num + 1));
    RETURN i;
END $
DELIMITER ;

1.4、创建往stu表中插入数据的存储过程

DELIMITER $
CREATE PROCEDURE insert_stu(start INT, max_num INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    SET autocommit = 0; #  设置手动提交事务
    REPEAT
        #循环
        SET i = i + 1; #  赋值
        INSERT INTO student (stuno, name, age, classid)
        VALUES ((start + i), rand_string(6), rand_num(1, 50), rand_num(1, 1000));
    UNTIL i = max_num
        END REPEAT;
    COMMIT; #  提交事务
END $
DELIMITER ;

1.5、调用存储过程

#  执行存储过程，往stu表添加50万条数据
CALL insert_stu(100000, 500000);

1.6、销毁索引的存储过程

DELIMITER $
CREATE PROCEDURE `proc_drop_index`(dbname VARCHAR(200), tablename VARCHAR(200))
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE ct INT DEFAULT 0;
    DECLARE _index VARCHAR(200) DEFAULT '';
    DECLARE _cur CURSOR FOR SELECT index_name
                            FROM information_schema.statistics
                            WHERE table_schema = dbname
                              AND table_name = tablename
                              AND seq_in_index = 1
                              AND index_name <> 'PRIMARY';
#   每个游标必须使用不同的declare continue handler for not found set done=1来控制游标的结束
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 2;
#   若没有数据返回,程序继续,并将变量done设为2
    OPEN _cur;
    FETCH _cur INTO _index;
    WHILE _index <> ''
        DO
            SET @str = CONCAT("drop index ", _index, " on ", tablename);
            PREPARE sql_str FROM @str;
            EXECUTE sql_str;
            DEALLOCATE PREPARE sql_str;
            SET _index = '';
            FETCH _cur INTO _index;
        END WHILE;
    CLOSE _cur;
END $
DELIMITER ;

• 调用销毁索引的存储过程

CALL proc_drop_index("dbname","tablename");