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

小鱼儿 2022-06-03 05:56 268阅读 0赞

==============MySQL====================

/* 
    利用MySQL存储过程批量创建MySQL的表：
    test_1
    test_2
    test_...
    */
    
    -- 如果存储过程已存在，先删除
    DROP PROCEDURE IF EXISTS `create_tables`;
    
    -- 创建存储过程
    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`=1;
        WHILE `@i`<=4 DO 
             -- `M_ID` bigint AUTO_INCREMENT PRIMARY KEY NOT NULL,
             -- createTable     
             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批量插入数据，举例 */
    
    -- 如果存储过程已存在，先删除
    DROP PROCEDURE test;
    
    DELIMITER $$
    CREATE PROCEDURE `test`()
    BEGIN
        DECLARE i INT DEFAULT 1;
        WHILE (i <= 10) DO
        INSERT INTO `testdb`.`table01`(`id`,`name`) VALUES (i,'testaaaa');
        SET i = i + 1;
        END WHILE;
    END$$
    DELIMITER ;
    
    -- 调用存储过程
    CALL test();

/* MySQL批量插入数据，举例 */
    
    -- 如果存储过程已存在，先删除
    DROP PROCEDURE test;
    
    DELIMITER $$
    CREATE PROCEDURE test()
    BEGIN 
       DECLARE i INT  DEFAULT 1;
       WHILE (i<=10) DO
         INSERT INTO test03.test_2(id,name,info) VALUES(i,'aaa','bbb');
         SET i=i+1;
       END WHILE;
    COMMIT;
    END $$
    DELIMITER;
    
    -- 调用存储过程
    CALL test();

==============Oracle=====================

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;  
/

\#Oracle中批量删除多表

SELECT 'DROP TABLE '  || table\_name || ';' FROM user\_tab\_comments WHERE  table\_name LIKE '%TESTJOB%';