--all_orders所有订单；new_records新增加订单（状态1）；orders_archive无效订单（状态0）
create table all_orders(id number(18) primary key,mc varchar2(60),order_status number(2));
create table new_orders(id number(18) primary key,mc varchar2(60),order_status number(2));
create table orders_archive(id number(18) primary key,mc varchar2(60),order_status number(2));

insert into all_orders values(1,'mc1',0);
insert into all_orders values(2,'mc2',2);
insert into all_orders values(3,'mc3',2);
insert into all_orders values(4,'mc4',2);
insert into all_orders values(5,'mc5',1);
insert into all_orders values(6,'mc6',0);
insert into all_orders values(7,'mc7',1);
insert into all_orders values(8,'mc8',1);
insert into all_orders values(9,'mc9',1);
insert into all_orders values(10,'mc10',1);
insert into all_orders values(11,'mc11',0);
insert into all_orders values(12,'mc12',1);
insert into all_orders values(13,'mc13',1);
insert into all_orders values(14,'mc14',1);
insert into all_orders values(15,'mc15',1);
insert into all_orders values(16,'mc16',1);
insert into all_orders values(17,'mc17',0);
insert into all_orders values(18,'mc18',1);
insert into all_orders values(19,'mc19',1);
insert into all_orders values(20,'mc20',1);
commit;

SET SERVEROUTPUT ON

DECLARE
TYPE orders_type IS TABLE OF all_orders%ROWTYPE;
TYPE orders_index_type IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER ;

orders_archive orders_type;
new_order orders_type;
all_order orders_type;
orders_archive_index orders_index_type;

order_status INTEGER;
new_orders_count INTEGER := 0;
old_orders_count INTEGER := 0;

BEGIN
SELECT * BULK COLLECT INTO all_order FROM all_orders;

new_order := all_order;

FOR i IN all_order.FIRST .. all_order.LAST LOOP
order_status := all_order(i).order_status ;
IF ( order_status = 2 ) THEN
new_order.DELETE(i);
ELSE
new_orders_count := new_orders_count+1;
END IF;

IF order_status = 0 THEN
orders_archive_index(old_orders_count) := i;
old_orders_count := old_orders_count+1;
END IF;

END LOOP;

DBMS_OUTPUT.PUT_LINE('Inserting '|| TO_CHAR(new_orders_count) || ' new ORDERS into NEW_ORDERS table');
DBMS_OUTPUT.PUT_LINE(' ');

FORALL indx IN INDICES OF new_order
INSERT INTO new_orders VALUES all_order(indx);

DBMS_OUTPUT.PUT_LINE('Inserting ' ||TO_CHAR(old_orders_count) ||' old ORDERS data into ORDERS_ARCHIVE table');

FORALL indx IN VALUES OF orders_archive_index
INSERT INTO orders_archive VALUES all_order(indx);

commit;
END;
/