MySQL存储过程---变量的应用

存储过程中变量的应用

DELIMITER $$

USE `world`$$

DROP PROCEDURE IF EXISTS `t1`$$

CREATE DEFINER=`root`@`10.0.0.%` PROCEDURE `t1`(IN num INT)
BEGIN
DECLARE v_uname VARCHAR(25);
DECLARE v_pass VARCHAR(20);
DECLARE str,pre_str VARCHAR(64);
DECLARE str_1 VARCHAR(64) DEFAULT 'abcdefghijklmnopqrstuvwxyz';
SELECT SUBSTR(REPLACE(UUID(),'-',''),1+FLOOR(RAND()*21),6) INTO v_uname;
SELECT REPLACE(UUID(),'-','') INTO pre_str;
SELECT SUBSTR(pre_str,FLOOR(RAND()*22),11) INTO str;
SELECT CONCAT(SUBSTR(UPPER(str_1),FLOOR(RAND()*27),1),str) INTO v_pass;
INSERT INTO t1 VALUES(v_uname,v_pass);
END$$

DELIMITER ;

调用

CALL t1()

SELECT * FROM t1;

 练习：

向表中插入随机值，要求：

uname：6位随机字符

passwd：12位随机字符密码。首字母大些，其余为随机数字、字母组合

u_birth：随机出生日期，如：1996-10-11

u_age：根据u_birth算出

u_tel：11位随机手机号码

建表

mysql>  create table  t3 (id int(10) not null primary key auto_increment,
    ->  uname varchar(64) not null,
    -> passwd varchar(20) not null,
    -> u_birth datetime not null,
    -> u_age tinyint,
    -> u_tel char(11)
    -> );

创建存储过程

DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    PROCEDURE `world`.`test3`()
    /*LANGUAGE SQL
    | [NOT] DETERMINISTIC
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }
    | COMMENT 'string'*/
    BEGIN
　　-- 声明变量：存储过程中声明变量时，变量数据类型最好和原表中的数据类型一致。
    DECLARE u_u,u_p,u_b,u_a,u_t VARCHAR(64); 
    DECLARE def_str VARCHAR(64) DEFAULT 'abcdefghijklmnopqrstuvwxyz';
    DECLARE str_11 VARCHAR(20) ;
    DECLARE rnd_20 INT(20);
    DECLARE rnd_char_uid VARCHAR(64);
　　-- 赋值变量：
    SELECT REPLACE(UUID(),'-','') INTO rnd_char_uid;
    SELECT FLOOR(RAND()*21) INTO rnd_20;
    SELECT SUBSTR(rnd_char_uid,rnd_20,6) INTO u_u;
    SELECT SUBSTR(rnd_char_uid,rnd_20,11) INTO str_11;
    SELECT CONCAT(SUBSTR(UPPER(def_str),FLOOR(RAND()*27),1),str_11) INTO u_p;
    SELECT DATE(FROM_UNIXTIME(CEIL(UNIX_TIMESTAMP('2020-06-12')* RAND()+1))) INTO u_b;
    SELECT YEAR(NOW())-YEAR(u_b) INTO u_a;
    SELECT CONCAT('1',30+LPAD(RAND()*70,2,0),CEIL(RPAD(RAND()*100000000,8,0))) INTO u_t;
　　-- 调用变量：
    INSERT INTO t3(uname,passwd,u_birth,u_age,u_tel) VALUES(u_u,u_p,u_b,u_a,u_t);

    END$$

DELIMITER ;

调用

CALL test3()
SELECT * FROM t3;
DELETE FROM t3;