create or replace procedure
t1358(vtab varchar(100),num int)
as
--定义游标和相关类型
type my_type is ref cursor ;
cur2 my_type;
cur1 my_type;
--定义存储执行sql的类型
vsql varchar(1000);
tcn varchar(100);
tct varchar(100);
tctl int;
tva varchar(100);
i integer;
begin
i:=1;
--对数据量做循环,引用了输入变量
while i < num loop
vsql:='insert into '||vtab||'(';
open cur1 for select A.colname from syscolumns A left join syscolumnsext b on a.colname=b.colname and a.tabid=b.tabid where a.tabid=(select tabid from systables where tabname=vtab);
loop
fetch cur1 into tcn;
exit when cur1%notfound;
vsql:=vsql||tcn||',';
end loop;
vsql:=(select left(vsql,length(vsql)-1)||') VALUES(' from dual);
close cur1;
open cur2 for select COLTYPENAME2,COLLENGTH from systables A ,syscolumnsext B where a.tabid=b.tabid and a.tabname=vtab;
loop
fetch cur2 into tct,tctl;
exit when cur2%notfound;
case when tct like 'VARCHAR%' then tva:=(SELECT DBMS_RANDOM.STRING('a', tctl) FROM DUAL);
when tct in ('int','INTEGER') then tva:=(select DBMS_RANDOM.value(1,100000)::int from dual);
when tct like 'CHAR%' then tva:=(select DBMS_RANDOM.STRING('a', tctl) from dual);
else tva:='3';
end case;
vsql:=vsql||''''||tva||''',';
end loop;
vsql:=(select left(vsql,length(vsql)-1)||');' from dual);
EXECUTE IMMEDIATE vsql;
i:=i+1;
end loop;
end;/