mysql存储过程和函数实例

 

drop procedure if exists sp_auto_insert_proc;
delimiter $$
create procedure sp_auto_insert_proc
(
    out ios_out     integer     ,
    out str_out     varchar(1024)    
)
begin
    declare     iRand     integer;
    declare     strCode   char(50);
    declare     strname   varchar(255);
    declare     strAddr   varchar(255);
   
    declare     imathscr     int;
    declare     ichinscr     int;
    declare     iengscr      int;
    declare     iphyscr      int;
    declare     itotalscr    int;
    declare     iage         int;
   
    declare     indexpos  int;
   
    set indexpos = 0;
    set strCode = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
   
    drop table if exists tbl_student;
    create table tbl_student
    (
        id      integer primary key AUTO_INCREMENT,
        name    varchar(255),
        age     integer,
        Addr    varchar(255),
        mathscr int,
        chinscr int,
        engscr  int,
        physcr  int,
        score   int
    );
   
    while indexpos < 10000000 do
        set imathscr = round(rand() * 60) + 40;
        set ichinscr = round(rand() * 60) + 40;
        set iengscr = round(rand() * 60) + 40;
        set iphyscr = round(rand() * 60) + 40;
        set itotalscr = imathscr + ichinscr + iengscr + iphyscr;
        set iage = round(rand() * 6) + 18;
       
        set strname = getRandstr();
        set strAddr = getRandstr();
        insert into tbl_student(
                        name,
                        age,
                        Addr,
                        mathscr,
                        chinscr,
                        engscr,
                        physcr,
                        score
                        )
                        values(
                        strname,
                        iage,
                        strAddr,                       
                        imathscr,
                        ichinscr,
                        iengscr,
                        iphyscr,
                        itotalscr
                        );
        set indexpos = indexpos + 1;
       
    end while;
end;
$$
delimiter ;

 

 

 

 

drop function if exists getRandstr;
delimiter $$
create function getRandstr()
RETURNS VARCHAR(21)
begin
    declare     strCode   char(60);
    declare     strRanstr varchar(21) default '';
    declare     iranpos   int; 
   
    declare     indexpos  int;
   
    set indexpos = 1;
    set strCode = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
   
    while indexpos < 20 do
       
        set iranpos = round(rand() * 52);
        set strRanstr = concat(strRanstr, substr(strCode, iranpos + 1, 1));
        set indexpos = indexpos + 1;
    end while;
   
    return strRanstr;
end;
$$
delimiter ;