declare i number:=1;begin loop if i>4 then exit; end if;
insert into SUP_WB_河道站防洪指标 values(i,to_char(i,'xxx'),'1',1.0,1.0,1.0,1.0,1.0,1.0,1.0,1.0,sysdate,1.0,sysdate,1.0,sysdate,1.0,sysdate,1.0,sysdate,1.0,sysdate,1.0,sysdate,1.0,sysdate,'1','1',sysdate,1,1);
i:=i+1; end loop; end;这条语句针对上面那个表可以,但换个表字段就会变了,还是很麻烦。
for c in (select * from user_tab_cols t where t.TABLE_NAME = str_l_tabname) loop str_l_sql_1 := str_l_sql_1 || c.column_name || ','; select str_l_sql_2 || decode(c.data_type, 'DATE', 'sysdate,', '1,') into str_l_sql_2 from dual; end loop;
i number:=1;begin
loop
if i>4 then
exit;
end if;
insert into SUP_WB_河道站防洪指标 values(i,to_char(i,'xxx'),'1',1.0,1.0,1.0,1.0,1.0,1.0,1.0,1.0,sysdate,1.0,sysdate,1.0,sysdate,1.0,sysdate,1.0,sysdate,1.0,sysdate,1.0,sysdate,1.0,sysdate,'1','1',sysdate,1,1);
i:=i+1;
end loop;
end;这条语句针对上面那个表可以,但换个表字段就会变了,还是很麻烦。
通过类型判断,插入指定类型的数据。动态SQL
2、执行下面的脚本(需替换注释部分);
declare
str_l_tabname varchar2(30) := 'TABLENAME'; -- 表名
i_l_rows pls_integer := 3; -- 生成的数据条数
i_l_count pls_integer;
str_l_sql_1 varchar2(4000);
str_l_sql_2 varchar2(4000);
str_l_sql varchar2(4000);
begin
str_l_sql_1 := 'insert into ' || str_l_tabname || ' (';
str_l_sql_2 := ' values (';
for c in (select * from user_tab_cols t where t.TABLE_NAME = str_l_tabname) loop
str_l_sql_1 := str_l_sql_1 || c.column_name || ','; select str_l_sql_2 || decode(c.data_type, 'DATE', 'sysdate,', '1,')
into str_l_sql_2
from dual;
end loop;
str_l_sql := substr(str_l_sql_1, 1, length(str_l_sql_1) - 1) || ') ' || substr(str_l_sql_2, 1, length(str_l_sql_2) - 1) || ');';
dbms_output.put_line(str_l_sql);
for i in 1 .. i_l_rows loop
execute immediate str_l_sql;
end loop;
commit;
exception
when others then
rollback;
dbms_output.put_line(sqlerrm);
end;
3、批量更新表的主键,再启用表的主键约束。
disable constraint 主键名;