《Oracle PL/SQL开发指南》学习笔记28——源码调试——PL/SQL基础知识(第十部分——批量操作)

批处理倒是挺有意思,以后再工作中可以尝试使用一下。

批处理和大容量数据处理时,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.

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值