Oracle 存储过程

  --1 存储过程迁移数据 
  CREATE OR REPLACE PROCEDURE P_TB_TEST IS
  --功能：插入任务到任务日志表 
  V_TASK_START_DATE DATE;
  V_TASK_END_DATE   DATE;
  V_SQL_CODE        NUMBER;
  V_SQL_MSG         VARCHAR2(4000) := '';
  --SQL错误信息 
  V_TEST  tablespace_test.TB_TEST%ROWTYPE;
  V_COUNT NUMBER;
  cursor CUR_TEST is
    select * FROM tablespace_test.TB_TEST a;
begin
  --该存储过程执行开始时间和结束时间 
  SELECT SYSDATE INTO v_task_start_date FROM dual;
  SELECT SYSDATE INTO v_task_end_date FROM dual;
  v_sql_msg := 'step 1: 抽取数据到目的表TB_TEST';
  open CUR_TEST;
  V_COUNT := 0;
  loop
    fetch CUR_TEST
      into V_TEST;
    exit when CUR_TEST %NOTFOUND;
    INSERT INTO TB_TEST (Field1) values (V_TEST.Filed1);
    if (V_COUNT = 2000) then
      commit;
      V_COUNT := 0;
    else
      V_COUNT := V_COUNT + 1;
    end if;
  end loop;
  close CUR_TEST;
  commit;
  SELECT SYSDATE INTO v_task_end_date FROM dual;
  INSERT INTO LOAD_HIS_LOG
    (SYS, JOBNAME, START_DATE, END_DATE, RUN_DATE, SQL_CODE, SQL_STATE)
  VALUES
    ('tablespace_test',
     'P_TB_TEST_LOG',
     v_task_start_date,
     v_task_end_date,
     to_char((v_task_end_date - v_task_start_date) * 86400),
     v_sql_code,
     v_sql_msg);
  COMMIT;
end P_TB_TEST;
--2 存储过程插入10000条数据 Insert a million records with the DEMO procedure 
  create or replace procedure demo_p(total in integer) AS
  uuid number := 1; uuser varchar2(50) := 'user'; upassword varchar2(50) := 'password';
begin
  loop insert into demo(uuid, uuser, upassword) values(uuid, uuser || uuid, upassword || uuid); uuid := uuid + 1; exit when uuid >= total;
end loop;
end;
---Clear table then call procedure to insert a million records 
  truncate table demo; call demo_p(1000); select * from demo;