MySQL-存储过程基本示例

举例1:
mysql> CREATE TABLE `t` ( `id` int(11) NOT NULL, `c` int(11) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;

delimiter ;;

create procedure idata()
begin 
    declare i int; 
    set i=1; 
    while(i<=100000) 
     do 
     insert into t values(i,i); 
     set i=i+1; 
    end while;
  end;;
delimiter ;

call idata();


举例2:
mysql> CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `city` varchar(20) DEFAULT NULL,
  `tm` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

delimiter ;;
create procedure idata()
begin
declare i int;
set i=1;
while i<=10000000
do
insert into t values(i,substr('ABCDEFGHIJKLMNOPQRSTUVWXYZ',1+floor(rand()*20),5),10+floor(rand()*89),substr('ABCDEFGHIJKLMNOPQRSTUVWXYZ',1+floor(rand()*20),8),date(from_unixtime(floor(rand()*unix_timestamp('2022-06-28')))));
set i=i+1;
end while;
end;;
delimiter ;

call idata();

/*存储过程存在information_schema.ROUTINES表中，调用时需要在相应的库内。*/