利用数据库的存储过程批量建表和批量插入数据

//利用MySQL存储过程批量创建表

drop procedure if EXISTS `createtables`;

DELIMITER $$
create procedure `createtables`()
BEGIN 
        DECLARE `@i` int(11);     
        DECLARE `@createSql` VARCHAR(2560); 
        DECLARE `@createIndexSql1` VARCHAR(2560);     
        DECLARE `@createIndexSql2` VARCHAR(2560);
        DECLARE `@createIndexSql3` VARCHAR(2560);




        set `@i`=0; 
        WHILE  `@i`< 64 DO                 
    
                            -- `M_ID` bigint AUTO_INCREMENT PRIMARY KEY NOT NULL,
                            -- 创建表        
                            SET @createSql = CONCAT('CREATE TABLE IF NOT EXISTS test_',`@i`,'(
                                id INT(11) NOT NULL COMMENT \'用户id\',
name VARCHAR(255)  COMMENT \'用户名\',
info VARCHAR(255)  COMMENT \'信息\',
cnt INT(11) DEFAULT 0 COMMENT \'计数\',
PRIMARY KEY (id)  
                                ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin'
                            ); 
                            prepare stmt from @createSql; 
                            execute stmt;
SET `@i`= `@i`+1; 
            END WHILE;
END$$
DELIMITER;

call createtables();

//MySQL批量插入数据，举例1
DROP PROCEDURE test;
DELIMITER $$
CREATE PROCEDURE `test`()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE (i <= 10) DO
INSERT INTO `testuser`.`tableA`  (`ID`, `NAME`) VALUES (i, 'testaaaa');
SET i = i + 1; 
END WHILE;
END$$
DELIMITER ;
CALL test();


//MySQL批量插入数据，举例2
DROP PROCEDURE test;
DELIMITER $$
CREATE PROCEDURE `test`()
BEGIN
DECLARE i INT;
SET i = 10001;
WHILE (i <= 20000) DO
INSERT INTO `testuser`.`tableA`  (`ID`, `NAME`) VALUES (i, 'testaaaa');
SET i = i + 1; 
END WHILE;
END$$
DELIMITER ;
CALL test();

======

Oracle批量插入数据

# sys用户登录，grant execute on dbms_xxxx to xxx 来赋下权限才可以
# grant execute on dbms_output to USER01
# grant execute on dbms_lock to USER01


//举例1
BEGIN
FOR i in 1 .. 2000
  loop 
    INSERT INTO "USER01"."TABLE01" ("RYNBID", "COL02", "COL03", "COL04") VALUES (i, 'aaa', 'bbb', 'ccc');
  END loop;
  commit;
END;
/


//举例2
BEGIN
FOR i in 1 .. 2000
  loop 
    INSERT INTO "USER01"."TABLE01" ("RYNBID", "COL02", "COL03", "COL04") VALUES (i, 'aaa', 'bbb', 'ccc');
    IF MOD(i,50) = 0 THEN
      commit;
      dbms_output.put_line('to commit, i = '||i||''); 
      dbms_lock.sleep(1);
    END IF;
  END loop;
  commit;
END;
/