向DB表中插入100W条记录

有机会出笔试题的时候，下面是备用方案。
题：向数据库中插入100W条记录，表自定义，数据可重复。

delimiter $$
drop procedure if exists insertRecords $$
create procedure insertRecords(num int)
begin
 declare mi tinyint default 0;
 drop table if exists recursive;
 create table recursive(id int auto_increment, name varchar(20),primary key(id));
 insert into recursive(name) values('anna');
 while(power(2,mi)<num) do
  set mi=mi+1;
  insert into recursive(name) select name from recursive;
 end while;
 delete from recursive where id > num;
end
$$ delimiter ;

MySQL 存储过程是从 MySQL 5.0 开始增加的新功能。使用delimiter $$ 来定义语句结束符，创建完成后，还原；因为MySQL默认以";"为分隔符，过程体的每一句都被MySQL以存储过程编译，则编译过程会报错。drop procedure if exists...在此已经执行了。
上述代码的第13行,可以使用带参数的limit来改进，如下,

delimiter $$
drop procedure if exists insertRecords $$
create procedure insertRecords(num int)
begin
 declare mi tinyint default 1;
 declare more int default 0;
 drop table if exists recursive;
 create table recursive(id int auto_increment, name varchar(20),primary key(id));
 insert into recursive(name) values('anna');
 while(power(2,mi)<num) do
  set mi=mi+1;
  insert into recursive(name) select name from recursive;
 end while;
 set more=num-power(2,(mi-1));
 set @stmt='insert into recursive(name) select name from recursive limit ?';
 prepare newSql from @stmt;
 set @arg=more;
 execute newSql using @arg;
 deallocate prepare newSql;
end
$$ delimiter ;

To deallocate a prepared statement produced with PREPARE, use a DEALLOCATE PREPARE statement that refers to the prepared statement name. Attempting to execute a prepared statement after deallocating it results in an error.看得懂，不会翻译...执行存储过程后才开始关于表的DDL&DML。

call insertRecords(1000000);

最后查看记录条数。

select count(id) num from recursive;

--------下面给出Oracle版-------------------------------
先创建序列用于自增，如下。

create sequence auto_increment;

然后创建表，blabla...

create table recursive(id number(10) primary key, name varchar2(20));

接着整个触发器，作用：向表中插入数据时实现自动自增功能。

create or replace trigger insert_recursive_trigger
before insert on recursive 
for each row
begin 
 select auto_increment.nextval into :new.id  from dual; 
end;
/

插入一条记录，以备后用。

insert into recursive(name) values('anna');

后面写个存储过程，用于插入数据，下面13行后面的 :1是占位符 。

create or replace procedure insertRecords(num number)
is 
 mi number(2) default 1;
 more number(10) default 0;
 dml_insert varchar2(100);
begin
 dml_insert :='insert into recursive(name) select name from recursive';
 while(power(2,mi)<num) loop
  mi :=mi+1;
  execute immediate dml_insert ;
 end loop;
 more :=num-power(2,(mi-1));
 dml_insert :='insert into recursive(name) select name from recursive where  rownum<=:1';
 execute immediate dml_insert using more;
end ;
/

最后测试下，只用了10w条，有点慢，是由于用了触发器的缘故。

exec insertRecords(100000); 
select count(id) num from recursive;
select count(distinct(id)) from recursive;

之前也是把所有Orable语句集成到insertRecords存储过程里，发现有关于execute immediate的权限问题，没深入探索，就拆开了。感兴趣的童鞋给加个MS SqlServer(刚卸载不久，暂不想再装)版的。

1.若总分为10分，写出 insert into .. select ..即可得5分。
2.若之用for循环100w次插入数据，得3分。

[注]：程序实用性不大，主要考察能力。
上述代码通过了MySql5.0、Oracle10g_r1编译和运行。