mysql存储过程

操作如下： 

      CREATE TABLE tstudent4 (
        studentID int(15) NOT NULL,
        Sname varchar(64) DEFAULT NULL,
        sex char(1) DEFAULT NULL,
        cardID varchar(20) DEFAULT NULL,
        Birthday date DEFAULT NULL,
        Email varchar(40) DEFAULT NULL,
        Class varchar(20) DEFAULT NULL,
        enterTime datetime DEFAULT NULL,
        remarks mediumtext,
        cmos_modify_time datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
        PRIMARY KEY (studentID),
        KEY pri_stuid (studentID),
        KEY idx_cmos_modify_time (cmos_modify_time)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8

      ----------------------------------------------------------------------
      DELIMITER $$

      CREATE PROCEDURE addStudent4( IN iCount INT)
      BEGIN
              DECLARE i INT;
              DECLARE    inum INT;
              SET i =1;
              SELECT COUNT(0) INTO inum FROM tstudent4;
              IF inum > 1
                  THEN SELECT MAX(studentID) INTO inum FROM tstudent4;
              END IF;

              start transaction;
              WHILE i <= iCount DO
              INSERT tstudent4 VALUES (
                  inum+i,
                  CONCAT('阿兰',inum+i),
                  IF(CEIL(RAND()*10)%2=0,'男','女'),
                  RPAD(CONVERT(CEIL(RAND()*1000000000000000000),CHAR(18)),18,'0'),
                  CONCAT(CONVERT(CEIL(RAND()*10)+1980,CHAR(4)),'-',LPAD(CONVERT(CEIL(RAND()*12),
                  CHAR(2)),2,'0'),'-',LPAD(CONVERT(CEIL(RAND()*28),CHAR(2)),2,'0')),
                  CONCAT(CONCAT('alan',inum+i),'@hotmail.com'),
                  CASE CEIL(RAND()*3) WHEN 1 THEN '网络与网站开发' WHEN 2 THEN '计算机科学技术' ELSE '汇编语言初入门' END,
                  NOW(),'河南省郑州市金水区第一实验小学一年级一班',
                  CURRENT_TIMESTAMP(3));
              SET i = i + 1;
              END WHILE;
              commit;
          END$$

      DELIMITER ;

      -------------------------------------------------------------------
      DELIMITER $$
      CREATE PROCEDURE upStudent4(IN iCount INT)
      BEGIN
              DECLARE    iid INT;
              DECLARE    i INT;
              SET i =CEIL(RAND()*iCount)+100;

              SELECT studentID INTO iid FROM tstudent4 ORDER BY RAND() LIMIT 1; 
              UPDATE tstudent4 SET cardID=RPAD(CONVERT(CEIL(RAND()*1000000000000000000),CHAR(18)),18,'0') WHERE studentID between iid and  iid+i;

          END$$

      DELIMITER ;

      -----------------------------------------------------------------------
      DELIMITER $$

      CREATE  PROCEDURE teststudent4()
      BEGIN
              DECLARE    iid INT;
              DECLARE    i INT;
              SET i =CEIL(RAND()*200)+100;

              CALL addStudent4(5000);
              CALL upStudent4(200);

              SELECT min(studentID) INTO iid FROM tstudent4;
              DELETE FROM  tstudent4 WHERE studentID between iid and  iid+i;

          END$$

      DELIMITER ;

      ------------------------------------------------------------------------
      CREATE  EVENT example_event2 
      ON SCHEDULE EVERY 10 SECOND STARTS CURRENT_TIMESTAMP 
      ON COMPLETION NOT PRESERVE ENABLE 
      DO call teststudent4();

      ---------------批量建表----------------------------------

      DELIMITER $$

      CREATE  PROCEDURE createTables( IN table_pre VARCHAR(20),IN iCount INT)
      BEGIN
              DECLARE i INT;
              DECLARE table_name VARCHAR(20);  
              DECLARE sql_text VARCHAR(2000); 
              SET i=0;
              SET table_name='';
              SET sql_text='';

              WHILE i<iCount DO

                  #IF i<10 THEN SET table_name=CONCAT(table_pre,i);
                      #    ELSE SET table_name=CONCAT(table_pre,i);
                      #END IF;
                  SET table_name=CONCAT(table_pre,i);

                  SET sql_text=CONCAT('CREATE TABLE ', table_name, '(
                   id INT(11) NOT NULL COMMENT '用户id' AUTO_INCREMENT,
                   userName VARCHAR(32)  COMMENT '用户名',
                   service INT(11) DEFAULT 0 COMMENT '服务',
                   passportUserName VARCHAR(32)  COMMENT 'y',
                   email  VARCHAR(32) COMMENT 'email',
                   phone  VARCHAR(15) COMMENT '电话',
                       trueName  VARCHAR(12) COMMENT '真实姓名',
                       idNumber  VARCHAR(18) COMMENT '身份证',
                       nickName  VARCHAR(32) COMMENT '昵称',
                       maxMsgId  INT(18) COMMENT '消息id',
                       gameIds  VARCHAR(32)DEFAULT null COMMENT '昵称？',
                       crmVip  INT(11) COMMENT '消息id',
                       status  INT(11) COMMENT '状态',
                       updateTime  Date COMMENT '更新时间',
                  bigHead  VARCHAR(200)DEFAULT null COMMENT 'bigHead',
                  smallHead  VARCHAR(200)DEFAULT null COMMENT 'smallHead',
                  cmos_modify_time datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
                  PRIMARY KEY (id),
                  KEY idx_cmos_modify_time (cmos_modify_time)  
                  ) ENGINE=INNODB DEFAULT CHARSET=utf8' );

                  SELECT sql_text; 
                  SET @sql_text=sql_text;
                  PREPARE stmt FROM @sql_text;
                  EXECUTE stmt;
                  DEALLOCATE PREPARE stmt;  
                  SET i=i+1;

              END WHILE;
          END$$

      DELIMITER ;

      ---------------批量删表----------------------------------
      DELIMITER $$

      CREATE  PROCEDURE delTables( IN table_pre VARCHAR(20),IN iCount INT)
      BEGIN
              DECLARE i INT;
              DECLARE table_name VARCHAR(50);  
              DECLARE sql_text VARCHAR(2000); 
              SET i=0;
              SET table_name='';
              SET sql_text='';

              WHILE i<iCount DO

                  SET table_name=CONCAT(table_pre,i);

                  SET sql_text=CONCAT('DROP TABLE IF EXISTS ', table_name, ';' );

                  SELECT sql_text; 
                  SET @sql_text=sql_text;
                  PREPARE stmt FROM @sql_text;
                  EXECUTE stmt;
                  DEALLOCATE PREPARE stmt;  
                  SET i=i+1;

              END WHILE;
          END$$

      DELIMITER ;