mysql 存储过程和函数

创建表：

CREATE TABLE `mhbtest`.`dept`  (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `deptno` int(11) NULL DEFAULT NULL,
  `dname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `loc` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4183527 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

创建函数：

DELIMITER $$   // 以$$结尾算作执行
CREATE FUNCTION rand_string( n INT ) RETURNS VARCHAR ( 255 ) BEGIN
DECLARE
	chars_str VARCHAR ( 100 ) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
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 $$ 

创建存储过程： 存储过程中调用 函数 rand_string

DELIMITER $$
CREATE PROCEDURE insert_dept(IN START INT(10), IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
set autocommit = 0;
REPEAT
set i = i +1;
insert into mhbtest.dept(deptno, dname, loc) VALUES ((START+i), rand_string(10), rand_string(8));
UNTIL i = max_num
END REPEAT;
COMMIT;
END $$

执行函数和存储过程：

CALL insert_dept(200,20);

查询，删除：

show procedure status;
show FUNCTION status;

drop procedure if exists insert_dept;
drop function if exists rand_string;