--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;
Oracle 存储过程
最新推荐文章于 2024-06-05 16:17:52 发布