批处理倒是挺有意思,以后再工作中可以尝试使用一下。
批处理和大容量数据处理时,BULK COLLECT批处理是默认方案。
1. 使用BULK COLLECT子句实现批处理
SQL> edi
Wrote file afiedt.buf
1 -- Show bulk collections in an anonymous block.
2 DECLARE
3 TYPE title_record IS RECORD
4 ( title VARCHAR2(60)
5 , subtitle VARCHAR2(60));
6 TYPE title_collection IS TABLE OF TITLE_RECORD;
7 lv_title_collection TITLE_COLLECTION;
8 CURSOR c IS
9 SELECT item_title, item_subtitle
10 FROM item;
11 lv_counter integer :=0;
12 BEGIN
13 OPEN c;
14 LOOP
15 FETCH c BULK COLLECT INTO lv_title_collection LIMIT 20;
16 EXIT WHEN lv_title_collection.COUNT = 0;
17 FOR i IN 1..lv_title_collection.COUNT LOOP
18 dbms_output.put_line('['||lv_title_collection(i).title||']'||'['||lv_title_collection(i).subtitle||']');
19 END LOOP;
20 lv_counter := lv_counter + 1;
21 dbms_output.put_line('No. '||lv_counter||' bulk collection done');
22 dbms_output.put_line('--------------------------------------------');
23 END LOOP;
24 CLOSE c;
25* END;
SQL> /
[Pirates of the Caribbean - The Curse of the Black Pearl][]
[Pirates of the Caribbean - Dead Man's Chest][]
[Pirates of the Caribbean - Dead Man's Chest][Two-Disc Collector's Edition]
[Pirates of the Caribbean - At World's End][]
[Pirates of the Caribbean - At World's End][Two-Disc Collector's Edition]
[Indiana Jones and the Raiders of the Lost Ark][Widescreen Edition]
[Indiana Jones and the Raiders of the Lost Ark][Fullscreen Edition]
[Indiana Jones and the Temple of Doom][Widescreen Edition]
[Indiana Jones and the Temple of Doom][Fullscreen Edition]
[Indiana Jones and the Last Crusade][Widescreen Edition]
[Indiana Jones and the Last Crusade][Fullscreen Edition]
[Spider-Man][Widescreen Special Edition]
[Spider-Man][Fullscreen Special Edition]
[Spider-Man 2][Widescreen Special Edition]
[Spider-Man 2][Fullscreen Special Edition]
[Spider-Man 3][Widescreen Special Edition]
[Spider-Man 3][Fullscreen Special Edition]
[Star Wars - Episode I][The Phantom Menace]
[Star Wars - Episode II][Attack of the Clones]
[Star Wars - Episode II][Attack of the Clones]
No. 1 bulk collection done
--------------------------------------------
[Star Wars - Episode III][Revenge of the Sith]
[Star Wars - Episode III][Revenge of the Sith]
[Star Wars - Episode IV][A New Hope]
[Star Wars - Episode IV][A New Hope]
[Star Wars - Episode V][The Empire Strikes Back]
[Star Wars - Episode V][The Empire Strikes Back]
[Star Wars - Episode VI][Return of the Jedi]
[Star Wars - Episode VI][Return of the Jedi]
[The Sum of All Fears][Special Collector's Edition]
[The Patriot][Special Edition]
[The Patriot][Unrated Extended Cut]
[We Were Soldiers][]
[Chronicles of Narnia - The Lion, the Witch and the Wardrobe][Widescreen Edition]
[Chronicles of Narnia - The Lion, the Witch and the Wardrobe][Fullscreen Edition]
[Chronicles of Narnia - The Lion, the Witch and the Wardrobe][]
[Chronicles of Narnia - The Lion, the Witch and the Wardrobe][]
[Chronicles of Narnia - The Lion, the Witch and the Wardrobe][]
[Harry Potter: Goblet of Fire][]
[Harry Potter: Goblet of Fire][]
[Harry Potter: Goblet of Fire][]
No. 2 bulk collection done
--------------------------------------------
2. 使用FORALL语句实现批量更新操作
SQL> ed
Wrote file afiedt.buf
1 /* Formatted on 2018/12/1 19:43:25 (QP5 v5.256.13226.35538) */
2 --Show bulk collections in an anonymous block.
3 DECLARE
4 TYPE title_record IS RECORD
5 (
6 title VARCHAR2 (60),
7 subtitle VARCHAR2 (60),
8 id NUMBER
9 );
10 TYPE title_collection IS TABLE OF TITLE_RECORD;
11 lv_title_collection TITLE_COLLECTION;
12 CURSOR c
13 IS
14 SELECT item_title, item_subtitle, item_id FROM item;
15 lv_counter INTEGER := 0;
16 BEGIN
17 OPEN c;
18 LOOP
19 FETCH c BULK COLLECT INTO lv_title_collection LIMIT 20;
20 EXIT WHEN lv_title_collection.COUNT = 0;
21 FORALL i IN lv_title_collection.FIRST .. lv_title_collection.LAST
22 UPDATE item_temp
23 SET item_title = lv_title_collection (i).title||'_upd',
24 item_subtitle = lv_title_collection (i).subtitle||'_upd'
25 WHERE item_id = lv_title_collection (i).id;
26 END LOOP;
27 CLOSE c;
28* END;
29 /
PL/SQL procedure successfully completed.
SQL> /
ITEM_TITLE ITEM_SUBTITLE
------------------------------------------------------------ ----------------------------------------
Pirates of the Caribbean - The Curse of the Black Pearl_upd _upd
Indiana Jones and the Last Crusade_upd Widescreen Edition_upd
Spider-Man 2_upd Fullscreen Special Edition_upd
Pirates of the Caribbean - At World's End_upd Two-Disc Collector's Edition_upd
Spider-Man 3_upd Widescreen Special Edition_upd
Star Wars - Episode II_upd Attack of the Clones_upd
Pirates of the Caribbean - Dead Man's Chest_upd Two-Disc Collector's Edition_upd
Spider-Man 3_upd Fullscreen Special Edition_upd
Indiana Jones and the Raiders of the Lost Ark_upd Widescreen Edition_upd
Indiana Jones and the Temple of Doom_upd Widescreen Edition_upd
Indiana Jones and the Last Crusade_upd Fullscreen Edition_upd
Spider-Man_upd Widescreen Special Edition_upd
Pirates of the Caribbean - Dead Man's Chest_upd _upd
Spider-Man_upd Fullscreen Special Edition_upd
Spider-Man 2_upd Widescreen Special Edition_upd
Star Wars - Episode I_upd The Phantom Menace_upd
Pirates of the Caribbean - At World's End_upd _upd
Indiana Jones and the Raiders of the Lost Ark_upd Fullscreen Edition_upd
Indiana Jones and the Temple of Doom_upd Fullscreen Edition_upd
19 rows selected.