《Oracle PL/SQL开发指南》学习笔记28——源码调试——PL/SQL基础知识（第七部分）

最新推荐文章于 2018-12-01 20:47:05 发布

预见未来to50

最新推荐文章于 2018-12-01 20:47:05 发布

阅读量217

点赞数

CC 4.0 BY-SA版权

分类专栏：数据库（DB）

本文链接：https://blog.youkuaiyun.com/hpdlzu80100/article/details/84558653

数据库（DB）专栏收录该内容

368 篇文章

订阅专栏

本文详细探讨了Oracle中游标的多种使用方式，包括显式静态游标、显式动态游标、WHERE CURRENT OF子句以及Oracle推荐的关联更新方法。通过具体示例，展示了如何在PL/SQL中操作游标，进行数据检索和批量更新。

1. 显式静态游标

SQL> edit
Wrote file afiedt.buf

  1  -- An explicit static cursor for-loop statement.
  2  DECLARE
  3    CURSOR c IS
  4      SELECT distinct item_title FROM item order by 1;
  5  BEGIN
  6    FOR i IN c LOOP
  7      dbms_output.put_line(i.item_title);
  8    END LOOP;
  9* END;
SQL> /
Around the World in 80 Days
Brave Heart
Camelot
Casino Royale
Christmas Carol
Chronicles of Narnia - The Lion, the Witch and the Wardrobe
Clear and Present Danger
Die Another Day
Golden Eye
Harry Potter and the Chamber of Secrets
Harry Potter and the Goblet of Fire
Harry Potter and the Order of the Phoenix
Harry Potter and the Prisoner of Azkaban
Harry Potter and the Sorcer's Stone
Harry Potter: Goblet of Fire
Indiana Jones and the Last Crusade
Indiana Jones and the Raiders of the Lost Ark
Indiana Jones and the Temple of Doom
King Arthur
King Arthur - The Director's Cut
Pirates of the Caribbean
Pirates of the Caribbean - At World's End
Pirates of the Caribbean - Dead Man's Chest
Pirates of the Caribbean - The Curse of the Black Pearl
Scrooge
Spider-Man
Spider-Man 2
Spider-Man 3
Star Wars - Episode I
Star Wars - Episode II
Star Wars - Episode III
Star Wars - Episode IV
Star Wars - Episode V
Star Wars - Episode VI
The Hunt for Red October
The Lord of the Rings - Fellowship of the Ring
The Lord of the Rings - The Return of the King
The Lord of the Rings - Two Towers
The Patriot
The Patriot Games
The Sum of All Fears
The World Is Not Enough
Tomorrow Never Dies
We Were Soldiers

PL/SQL procedure successfully completed.

2. 显式动态游标的使用

SQL> edit
Wrote file afiedt.buf

  1  -- An explicit dynamic cursor for-loop statement.
  2  DECLARE
  3    lv_search_string VARCHAR2(60);
  4    CURSOR c (cv_search VARCHAR2) IS
  5      SELECT distinct item_title
  6      FROM item
  7      WHERE REGEXP_LIKE(item_title,'^'||cv_search||'*+');
  8  BEGIN
  9    FOR i IN c ('&input') LOOP
 10      dbms_output.put_line(i.item_title);
 11    END LOOP;
 12* END;
SQL> /
Enter value for input: Harry
old   9:   FOR i IN c ('&input') LOOP
new   9:   FOR i IN c ('Harry') LOOP
Harry Potter: Goblet of Fire
Harry Potter and the Chamber of Secrets
Harry Potter and the Prisoner of Azkaban
Harry Potter and the Goblet of Fire
Harry Potter and the Order of the Phoenix
Harry Potter and the Sorcer's Stone

PL/SQL procedure successfully completed.

3. WHERE CURRENT OF子句（不推荐使用）

SQL> edit
Wrote file afiedt.buf

  1  -- A static bulk cursor for an anonymous block.
  2  DECLARE
  3    TYPE update_record IS RECORD
  4    ( last_updated_by NUMBER
  5    , last_update_date DATE );
  6    TYPE update_table IS TABLE OF UPDATE_RECORD;
  7    updates UPDATE_TABLE;
  8    CURSOR c IS
  9      SELECT last_updated_by, last_update_date
 10      FROM item
 11      WHERE item_id BETWEEN 1031 AND 1040
 12      FOR UPDATE;
 13  BEGIN
 14    OPEN c;
 15    LOOP
 16      FETCH c BULK COLLECT INTO updates LIMIT 5;
 17      EXIT WHEN updates.COUNT = 0;
 18      FORALL i IN updates.FIRST..updates.LAST
 19      UPDATE item
 20      SET last_updated_by = updates(i).last_updated_by
 21      , last_update_date = updates(i).last_update_date
 22      WHERE CURRENT OF c;
 23    END LOOP;
 24* END;
SQL> /

PL/SQL procedure successfully completed.

4. Oracle推荐使用以下方式（关联更新）：

SQL> ed
Wrote file afiedt.buf

  1  -- A correlated update statement.
  2  UPDATE item i1
  3  SET last_updated_by = 3
  4  , last_update_date = SYSDATE
  5  WHERE EXISTS (SELECT NULL FROM item i2
  6                WHERE item_id BETWEEN 1031 AND 1040
  7*               AND i1.ROWID = i2.ROWID)
SQL> /

10 rows updated.