oracle中forall in,oracle10g的forall功能加强

oracle10g的forall功能加强[@more@]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;

/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/272588/viewspace-909708/，如需转载，请注明出处，否则将追究法律责任。