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

sunny05296

已于 2024-03-29 07:37:28 修改

阅读量4.7k

点赞数 1

分类专栏：数据库常见问题文章标签：数据库 oracle dba

于 2017-12-20 11:53:15 首次发布

本文链接：https://blog.csdn.net/sunny05296/article/details/78851590

版权

常见问题同时被 2 个专栏收录

149 篇文章 4 订阅

订阅专栏

数据库

136 篇文章 5 订阅

订阅专栏

1.MySQL批量插入数据

/* 
利用MySQL存储过程批量创建MySQL的表：
test_1
test_2
test_...
*/

-- 如果存储过程已存在，先删除
DROP PROCEDURE IF EXISTS `createTables`;

-- 创建存储过程
-- 先自定义分隔符代付默认的分号分隔符、再创建存储过程，然后复默认分号分隔符，最后查询或执行存储过程
-- 注意：
-- 1.存储过程中包含多行、存在分号与默认的分隔符冲突，因此使用 DELIMITER 定义一个特殊的分隔符，遇到特殊分隔符才算语句的结束，才开始SQL解析和执行
-- 2.如果存储过程中的对象命名存在关键字冲突，则需要特殊符号(反引号)括起来
-- 3.MySQL中不要使用 CREATE OR REPLACE PROCEDURE，必须使用 CREATE PROCEDURE，Oracle中才支持 CREATE OR REPLACE PROCEDURE 语法
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 ;  -- 重新定义（恢复）默认的分号分隔符


-- 查询存储过程
SHOW CREATE PROCEDURE `createTables`;

-- 调用存储过程创建表
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();

2.Oarcle批量插入数据

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


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


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

-- 注意：
-- 上面内容如果是直接在命令行终端执行、或者navicat都可以执行，但DBeaver无法执行。
-- 如果要在DBeaver中执行，必须创建存储过程、执行存储过程的方式，参考如下：

-- eg03
CREATE OR REPLACE PROCEDURE WRITEBATCH
IS
BEGIN
FOR i in 1 .. 1000000
  loop 
    INSERT INTO "USER01"."TABLE01" ("RYNBID", "COL02", "COL03", "COL04") VALUES (i, 'aaa', 'bbb', 'ccc');
    IF MOD(i,50000) = 0 THEN
      commit;
    END IF;
  END loop;
  commit;
END;
/

CALL WRITEBATCH();
-- 或者
BEGIN 
    WRITEBATCH();
END;


DROP PROCEDURE WRITEBATCH;



-- eg04
-- Oracle 批量生成随机内容测试数据（数据内容为随机值），FOR LOOP
-- CREATE OR REPLACE PROCEDURE MYPROC
-- IS
BEGIN
    FOR i in 1 .. 1000000
        LOOP
            INSERT INTO "TEST"."TB_TEST01" ("ID", "STR01", "STR02", "STR03", "STR04", "STR05", "STR06", "STR07", "STR08", "STR09") VALUES (i, (select DBMS_RANDOM.STRING('x',230) FROM dual), (select DBMS_RANDOM.STRING('x',230) FROM dual), (select DBMS_RANDOM.STRING('x',230) FROM dual), (select DBMS_RANDOM.STRING('x',230) FROM dual), (select DBMS_RANDOM.STRING('x',230) FROM dual), (select DBMS_RANDOM.STRING('x',230) FROM dual), (select DBMS_RANDOM.STRING('x',230) FROM dual), (select DBMS_RANDOM.STRING('x',230) FROM dual), (select DBMS_RANDOM.STRING('x',208) FROM dual));
            IF MOD(i,50000) = 0 THEN
                COMMIT;
            END IF;
        END LOOP;
    COMMIT;
 END;
/

-- CALL MYPROC();
-- DROP PROCEDURE MYPROC;




-- eg05
-- Oracle 批量生成随机内容测试数据（数据内容为随机值，CNT为100~200范围内的随机值），FOR LOOP，并打印构造数据执行耗时时长
-- DBMS_UTILITY.GET_TIME 精确到1/00秒，例如: start_time: 481297262, end_time: 481301199, 执行耗时(s): 39.37
-- SET SERVEROUTPUT ON;
DECLARE
    start_time NUMBER(38,0);
    end_time NUMBER(38,0);
    seconds NUMBER(38,2);
BEGIN
    start_time := DBMS_UTILITY.GET_TIME;
    FOR i in 1 .. 1000000
        LOOP
            INSERT INTO "TEST"."TB_TEST02" ("ID", "INFO", "CNT") VALUES (i, (select DBMS_RANDOM.STRING('x',50) FROM dual), (select DBMS_RANDOM.VALUE(100,200) FROM dual));
            IF MOD(i,50000) = 0 THEN
                COMMIT;
            END IF;
        END LOOP;
    COMMIT;
    end_time := DBMS_UTILITY.GET_TIME;
    seconds := (end_time - start_time)/100;
    DBMS_OUTPUT.PUT_LINE('  ==>  start_time: ' || start_time || ', end_time: ' || end_time || ', 执行耗时(s): ' || seconds);
 END;
/





-- eg06
-- Oracle 批量生成随机测试数据（数据内容为随机值，CNT为100~200范围内的随机值），LOOP & EXIT WHEN，并打印构造数据执行耗时时长
-- DBMS_UTILITY.GET_TIME 精确到1/00秒，例如: start_time: 481297262, end_time: 481301199, 执行耗时(s): 39.37
-- SET SERVEROUTPUT ON;
DECLARE
    start_time NUMBER(38,0);
    end_time NUMBER(38,0);
    seconds NUMBER(38,2);
    i NUMBER(38,0) := 1;
    i_max NUMBER(38,0) := 1000000;
BEGIN
    start_time := DBMS_UTILITY.GET_TIME;
    LOOP
        INSERT INTO "TEST"."TB_TEST02" ("ID", "INFO", "CNT") VALUES (i, (select DBMS_RANDOM.STRING('x',50) FROM dual), (select DBMS_RANDOM.VALUE(100,200) FROM dual));
        IF MOD(i,50000) = 0 THEN
            COMMIT;
        END IF;
        i := i + 1;
        EXIT WHEN i > i_max;    
    END LOOP;
    COMMIT;
    end_time := DBMS_UTILITY.GET_TIME;
    seconds := (end_time - start_time)/100;
    DBMS_OUTPUT.PUT_LINE('  ==>  start_time: ' || start_time || ', end_time: ' || end_time || ', 执行耗时(s): ' || seconds);
 END;
/

3.Oracle中批量删除多表

SELECT 'DROP TABLE ' || table_name || ';' FROM user_tab_comments WHERE table_name LIKE '%TESTJOB%';

4.PostgreSQL批量插入数据

1）PostgreSQL 批量插入数据（批量造数据、批量生成测试数据）

drop table test02;
create table test02(id int,info varchar(255),primary key(id));
insert into test02(id,info) select i,'test'||i from generate_series(1,100) as i;
select COUNT(*) from test02;
select * from test02 limit 10;

2）表复制(方式创建表)和数据复制，基于已有的表复制

-- 基于已有的表tableA创建相同表结构的表tableB(仅复制表结构、不复制数据)
drop table if exists tableB;
create table tableB like tableA;
-- 或者也可以下面这种方式：
drop table if exists tableB;
create table tableB select * from tableA where false;


-- 基于已有的表tableA创建相同表结构的表tableB(复制表结构、同时复制所有数据)
drop table if exists tableB;
create table tableB select * from tableA;

-- 基于已有的表tableA创建相同表结构的表tableB(复制表结构、同时复制部分数据)
drop table if exists tableB;
create table tableB select * from tableA where id<10;

-- A、B相同结构的两表已经存在时，将一张表的数据插入到另外一张表（B表中的数据插入A表）
insert into tableB select * from tableA where id<10;

-- SELECT INTO 将查询结果集插入到目的表（该方式貌似MySQL不支持）：
SELECT id,info,cnt INTO test81_dst_d_tmp FROM test81_dst;

-- SELECT INTO 将查询结果集插入到目的表（MySQL能支持的方式）
DROP TABLE IF EXISTS test81_dst_d_tmp;
CREATE TABLE test81_dst_d_tmp
(
SELECT a.id,a.info,a.cnt,a.idu FROM test81 a,(
SELECT
id, MAX(ctimestamp)AS time
FROM test81
GROUP BY id
ORDER BY COUNT(*)DESC)b WHERE a.id=b.id AND a.ctimestamp=b.time AND a.idu='D'
);

sunny05296

关注

1
点赞
踩
11

收藏

觉得还不错? 一键收藏
1
评论
利用数据库的存储过程批量建表和批量插入数据

==============MySQL====================/* 利用MySQL存储过程批量创建MySQL的表：test_1test_2test_...*/-- 如果存储过程已存在，先删除DROP PROCEDURE IF EXISTS `create_tables`;-- 创建存储过程DELIMITER $$CREATE PROCEDURE `cr.........
复制链接

扫一扫