mysql 存储过程 字符串函数吗_mysql-存储函数、存储过程

在测试数据的过程中，通过存储函数及存储过程构造数据：

# 生成随机字符串DELIMITER $$

CREATE FUNCTION rand_string (n INT) RETURNS VARCHAR(255)

BEGIN

DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwsyzABCDEFGHIJKLMNOPQRSTUVWXYZ';

DECLARE return_str VARCHAR(255) DEFAULT '';

DECLARE i INT DEFAULT 0;

WHILE i < n DO

SET return_str = CONCAT(return_str,SUBSTRING(chars_str FROM FLOOR(1+RAND()*52) FOR 1));

SET i = i+1;

END WHILE;

RETURN return_str;

END $$

# 生成随机数DELIMITER $$

CREATE  FUNCTION `rand_num`() RETURNS int(5)

BEGIN

DECLARE i INT DEFAULT 0;

set i = FLOOR(100+RAND()*10);

RETURN i;

END $$

# 创建存储过程

## 生成dept的数据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 dept (deptno, dname, loc) values ((start + i), rand_string(10), rand_string(8));

until i = max_num

end repeat;

commit;

END $$

## 生成emp的数据CREATE  PROCEDURE `insert_emp`(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 emp (empno, ename, job, mgr,hiredate, sal, comm, deptno) VALUES ((START+i), rand_string(6), 'salesman', 0001, CURDATE(), 2000,  400, rand_num());

UNTIL i = max_num

end REPEAT;

COMMIT;

END

## 生成数据briefdelimiter $$

CREATE DEFINER = CURRENT_USER PROCEDURE `insert_brief`(in start bigint(20), in max_num bigint(20))

BEGIN

declare i int default 0;

set autocommit = 0;

repeat

set i = i + 1;

insert into

brief

(`name`, `brief`, `address`) values (rand_string(5), rand_string(10), rand_string(8));

until i = max_num

end repeat;

commit;

END $$

# 调用：

call insert_dept(1, 10);

# 删除函数与存储过程：

drop function/procedure insert_emp