truncate_insert.txt

来自「orale培训教材包括了所有的sql说明和实例」· 文本 代码 · 共 78 行

--select sysdate from dual;
--插入 100000条纪录
declare
s_time date;
--s_time varchar2;
e_time date;
v_name varchar2(13);
begin
  --select to_char(sysdate,'yyyy/mm/dd hh:mi:ss) into s_time from dual ;
  select sysdate into s_time from dual ;
  select name into v_name from person where name='person4';
  select sysdate into e_time from dual ;
  --dbms_output.put_line('ues time' || to_char((e_time-s_time),'ss'));
dbms_output.put_line(to_char((e_time),'hh:mi:ss'));
dbms_output.put_line(to_char((s_time),'hh:mi:ss'));

end;

-------------------------------------------------
--使用delete ,commit
declare
s_time date;
--s_time varchar2;
e_time date;
v_name varchar2(13);
begin
  --select to_char(sysdate,'yyyy/mm/dd hh:mi:ss) into s_time from dual ;
  select sysdate into s_time from dual ;
  delete from person;
  commit;
  select sysdate into e_time from dual ;
  
dbms_output.put_line(to_char((e_time),'hh:mi:ss'));
dbms_output.put_line(to_char((s_time),'hh:mi:ss'));

end;

-------------------------------------------------
--data insert
declare
n_loop number;
s_time date;
--s_time varchar2;
e_time date;
begin
  select sysdate into s_time from dual ;
  for n_loop in 1..100000 loop
	insert into person values(n_loop,'name'||n_loop);
	commit;
  end loop;
  select sysdate into e_time from dual ;
  dbms_output.put_line(to_char((e_time),'hh:mi:ss'));
  dbms_output.put_line(to_char((s_time),'hh:mi:ss'));
end;

--truncate;
declare
s_time date;
--s_time varchar2;
e_time date;
v_name varchar2(13);
begin
  --select to_char(sysdate,'yyyy/mm/dd hh:mi:ss) into s_time from dual ;
  select sysdate into s_time from dual ;
  --erroe:truncate不可用在plsql
  truncate table person;
  select sysdate into e_time from dual ;
  --dbms_output.put_line('ues time' || to_char((e_time-s_time),'ss'));
dbms_output.put_line(to_char((e_time),'hh:mi:ss'));
dbms_output.put_line(to_char((s_time),'hh:mi:ss'));

end;

--建立表
create table person (id number(10) primary key,name varchar2(20));


create table person (id number(10),name varchar2(20));