zhouweifeng | forall在10g新功能

In Oracle Database 10g, PL/SQL now offers two new clauses in the FORALL statement, INDICES OF and VALUES OF, which allow you to choose very selectively which rows from the driving array should be processed by the extended DML statement.

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

zhouwf0726 发表于:2007.03.09 11:14 ::分类: ( oracle开发 ) ::阅读:(773次) :: 评论 (0) :: 引用 (0)