--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;