oracle10g的forall功能加強(qiáng)[@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新增加訂單(狀態(tài)1)；orders_archive無效訂單(狀態(tài)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/，如需轉(zhuǎn)載，請注明出處，否則將追究法律責(zé)任。

總結(jié)

以上是生活随笔為你收集整理的oracle中forall in,oracle10g的forall功能加强的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。