多种数据库存储过程数据插入方法总结

多种数据库存储过程数据插入方法总结

ORACLE-DM-OSCAR

CREATE TABLE zhaobsh1m ( ID varchar(1024),NAME1 varchar(1024),NAME2 varchar2(1024),NAME3 varchar2(1024));

select segment_name --表名
,bytes/1024/1024/1024 as 空间大小GB
from user_segments
where segment_type = 'TABLE'  AND  segment_name LIKE 'ZHAOBSH%'
order by  bytes/1024/1024/1024 desc ;

begin
  for i in 1 .. 1000000 loop
    INSERT INTO  zhaobsh1m ("ID", "NAME1", "NAME2", "NAME3")
    VALUES ('Test测试用长度限制' || i, i, 'Test测试用长度限制' || i, 'Test测试用长度限制' || i);
  end loop;
end;
/

---

PG

CREATE TABLE zhaobsh1m ( "ID" varchar(1024),"NAME1" varchar(1024),"NAME2" varchar(1024),"NAME3" varchar(1024));

  begin
  FOR i IN 0..1000000 LOOP
    INSERT INTO  zhaobsh1m ("ID", "NAME1", "NAME2", "NAME3")
    VALUES ('Test测试用长度限制' || i, i, 'Test测试用长度限制' || i, 'Test测试用长度限制' || i);
  end loop;
  return true;
  end;
/

---

SQLServer

CREATE TABLE zhaobsh1m ( "ID" varchar(1024),"NAME1" varchar(1024),"NAME2" varchar(1024),"NAME3" varchar(1024));

declare @i int
set @i=1
while @i<1000001
begin
    INSERT INTO  zhaobsh1m ("ID", "NAME1", "NAME2", "NAME3")
    VALUES ('Test测试用长度限制' + cast(@i as varchar),cast(@i as varchar), 'Test测试用长度限制' +cast(@i as varchar), 'Test测试用长度限制' + cast(@i as varchar));
set @i=@i+1
end

---

MySQL数据库

drop table zhaobsh1m ;
CREATE TABLE zhaobsh1m ( ID varchar(1024),NAME1 varchar(1024),NAME2 varchar(1024),NAME3 varchar(1024));

DROP PROCEDURE IF EXISTS `insertdata`;
DELIMITER $$
CREATE PROCEDURE `insertdata`()
BEGIN
    DECLARE v_i int unsigned DEFAULT 0;
    WHILE v_i < 1000000 DO
    INSERT INTO  zhaobsh1m VALUES (concat ('Test测试用长度限制' , v_i ), v_i, concat ('Test测试用长度限制' , v_i ) , concat ('Test测试用长度限制' , v_i ) );
        SET v_i = v_i+1;
    END WHILE;
END $$
DELIMITER ;
call insertdata();