--分步插入
create or replace procedure inserttab1(rowc NUMBER,colc NUMBER)
is
buf varchar(24000):='insert into tcol'||to_char(colc)||' select level,';
begin
if rowc<=1000 then
for i in 1..colc-1
  loop
   buf:=buf||'mod(level*DBMS_RANDOM.RANDOM,1e10),';
  end loop;
buf:=buf||'mod(level*DBMS_RANDOM.RANDOM,1e10) from dual connect by level<='||to_char(rowc);
execute immediate buf;
commit;
else
for j in 1..rowc/1000+1
loop
buf :='insert into tcol'||to_char(colc)||' select level+'||(j-1)*1000||',';
for i in 1..colc-1
  loop
   buf:=buf||'mod(level*DBMS_RANDOM.RANDOM,1e10),';
  end loop;
if j<rowc/1000 then
buf:=buf||'mod(level*DBMS_RANDOM.RANDOM,1e10) from dual connect by level<='||to_char(1000);
else
buf:=buf||'mod(level*DBMS_RANDOM.RANDOM,1e10) from dual connect by level<='||to_char(rowc-j*1000);
end if;
execute immediate buf;
commit;
end loop;
end if;
end;
想用来代替
-- inserttab插入一个含有指定数值型列的表，插入的行数可以指定，数值是随机的
create or replace procedure inserttab(rowc NUMBER,colc NUMBER)
is
buf varchar(24000):='insert into tcol'||to_char(colc)||' select level,';
begin
for i in 1..colc-1
  loop
   buf:=buf||'mod(level*DBMS_RANDOM.RANDOM,1e10),';
  end loop;
buf:=buf||'mod(level*DBMS_RANDOM.RANDOM,1e10) from dual connect by level<='||to_char(rowc);
execute immediate buf;
end;

但是插入的行数不对
SQL> exec inserttab1(20000,50);

PL/SQL procedure successfully completed.

Elapsed: 00:00:13.07
SQL> select count(*) from tcol50;

  COUNT(*)
----------
     19002

Elapsed: 00:00:00.03
用
create or replace procedure createtab(colc IN NUMBER,PP IN NUMBER:=15,SS IN NUMBER:=2)
is
buf varchar(24000):='create table tcol'||to_char(colc)||'(x1 varchar(10),';
begin
for i in 1..colc-1
  loop
   buf:=buf||'v'||to_char(i)||' number('||to_char(PP)||','||to_char(SS)||'),';
  end loop;
buf:=buf||'v'||to_char(colc)||' number('||to_char(PP)||','||to_char(SS)||'))';
execute immediate buf;
end;
建立表

create or replace procedure inserttab1(rowc NUMBER,colc NUMBER)
is
buf varchar(24000):='insert into tcol'||to_char(colc)||' select level,';
b number:=floor(rowc/1000);
begin
if rowc<=1000 then
for i in 1..colc-1
  loop
   buf:=buf||'mod(level*DBMS_RANDOM.RANDOM,1e10),';
  end loop;
buf:=buf||'mod(level*DBMS_RANDOM.RANDOM,1e10) from dual connect by level<='||to_char(rowc);
execute immediate buf;
commit;
else
for j in 1..b
loop
buf :='insert into tcol'||to_char(colc)||' select level+'||(j-1)*1000||',';
for i in 1..colc-1
  loop
   buf:=buf||'mod(level*DBMS_RANDOM.RANDOM,1e10),';
  end loop;
buf:=buf||'mod(level*DBMS_RANDOM.RANDOM,1e10) from dual connect by level<='||to_char(1000);
execute immediate buf;
commit;
end loop;
if rowc-b*1000>0 then
buf :='insert into tcol'||to_char(colc)||' select level+'||(b)*1000||',';
for i in 1..colc-1
  loop
   buf:=buf||'mod(level*DBMS_RANDOM.RANDOM,1e10),';
  end loop;
buf:=buf||'mod(level*DBMS_RANDOM.RANDOM,1e10) from dual connect by level<='||to_char(rowc-b*1000);
execute immediate buf;
commit;
end if;
end if;
end;

create or replace procedure inserttab1(rowc NUMBER,colc NUMBER)
is
bufl varchar(24000):='insert into tcol'||to_char(colc)||' select level,';
buf varchar(24000):='';
bufr varchar(24000):='';
b number:=floor(rowc/1000);
begin
for i in 1..colc-1
  loop
   buf:=buf||'mod(level*DBMS_RANDOM.RANDOM,1e10),';
  end loop;
if rowc<=1000 then
bufr:='mod(level*DBMS_RANDOM.RANDOM,1e10) from dual connect by level<='||to_char(rowc);
execute immediate bufl||buf||bufr;
commit;
else
for j in 1..b
loop
bufl:='insert into tcol'||to_char(colc)||' select level+'||(j-1)*1000||',';
bufr:='mod(level*DBMS_RANDOM.RANDOM,1e10) from dual connect by level<='||to_char(1000);
execute immediate bufl||buf||bufr;
commit;
end loop;
if rowc-b*1000>0 then
bufl :='insert into tcol'||to_char(colc)||' select level+'||(b)*1000||',';
bufr:='mod(level*DBMS_RANDOM.RANDOM,1e10) from dual connect by level<='||to_char(rowc-b*1000);
execute immediate bufl||buf||bufr;
commit;
end if;
end if;
end;

报什么错?

呵,版主已经改好了呀

教训
insert into a select level from dual connect vy level<=-1也会插入

QUOTE:

---

原帖由 jack198409 于 2008-9-17 16:27 发表
报什么错?

---

2# 说已经改好了, 俺就不看了

select level from dual connect vy level<=-1
其实这样的查询我不太喜欢用, 只是最近看大家用得多了, 我偶尔也会用用
不过这样的insert 也能插入记录, 是比较奇怪, 该死的 Oracle

QUOTE:

---

原帖由 hotiice 于 2008-9-17 16:41 发表
教训
insert into a select level from dual connect vy level

---

这个不容易注意。

QUOTE:

---

原帖由 nyfor 于 2008-9-17 16:45 发表
select level from dual connect vy level

---

不奇怪也不该死

start with 限制第一层；
connect by 从第二层开始起作用；
where 对整个结果集过滤。