Oracle中游标Cursor的用法详解

什么是游标Cursor

在Oracle数据库中，利用游标可以对查询结果集进行遍历、选择、修改等操作。游标是一个数据缓存区，用于存储一个查询结果集，可以在程序中对其进行一次或多次遍历，也可以对其进行 select ... into ... 多行赋值操作。

游标的基本语法

游标的基本语法如下：

DECLARE
  --定义游标
  CURSOR cursor_name IS select_statement;
BEGIN
  --打开游标
  OPEN cursor_name;
  --依次获取游标中每个结果行
  LOOP
    FETCH cursor_name INTO variable1, variable2, ..., variablen;
    EXIT WHEN cursor_name%NOTFOUND;
    --处理获取到的数据
  END LOOP;

  --关闭游标
  CLOSE cursor_name;
END;

其中，CURSOR关键字用于定义游标，且游标名必须唯一；OPEN语句用于打开游标，FETCH语句用于获取游标中的一行数据，需要定义一些变量来存储获取到的数据；%NOTFOUND关键字用于判断游标是否已经到达结果集的末尾；LOOP语句用于循环获取游标中的每一行数据，直到结果集的末尾；CLOSE语句用于关闭游标，释放相应的资源。

游标的示例应用

以下是两个游标的使用示例：

示例一：通过游标统计图书销售量

假设我们有以下两个表：

• books：存储书籍信息
• sales：存储销售记录

CREATE TABLE books (
  book_id NUMBER(10) PRIMARY KEY,
  book_name VARCHAR2(50),
  author VARCHAR2(50),
  price NUMBER(10, 2),
  pub_date DATE
);

CREATE TABLE sales (
  sale_id NUMBER(10) PRIMARY KEY,
  book_id NUMBER(10),
  sale_date DATE,
  quantity NUMBER(5),
  FOREIGN KEY (book_id) REFERENCES books(book_id)
);

INSERT INTO books (book_id, book_name, author, price, pub_date) VALUES (1, 'Java编程思想', 'Bruce Eckel', 99.00, DATE '2006-09-01');
INSERT INTO books (book_id, book_name, author, price, pub_date) VALUES (2, 'Head First 设计模式', 'Freeman等', 79.00, DATE '2004-10-01');
INSERT INTO sales (sale_id, book_id, sale_date, quantity) VALUES (1, 1, DATE '2018-01-01', 10);
INSERT INTO sales (sale_id, book_id, sale_date, quantity) VALUES (2, 1, DATE '2018-01-05', 20);
INSERT INTO sales (sale_id, book_id, sale_date, quantity) VALUES (3, 2, DATE '2018-01-01', 30);
INSERT INTO sales (sale_id, book_id, sale_date, quantity) VALUES (4, 2, DATE '2018-01-06', 40);

我们需要统计所有书籍的销售总量，并按照销售量从高到低排序输出。可以使用以下游标代码：

DECLARE
  --定义游标
  CURSOR c_sale_count IS 
    SELECT b.book_id, b.book_name, SUM(s.quantity) AS sales_count
    FROM books b
    JOIN sales s ON b.book_id = s.book_id
    GROUP BY b.book_id, b.book_name
    ORDER BY sales_count DESC;

  --定义变量
  v_book_id books.book_id%TYPE;
  v_book_name books.book_name%TYPE;
  v_sales_count NUMBER;
BEGIN
  --打开游标
  OPEN c_sale_count;
  --依次获取游标中每个结果行
  LOOP
    FETCH c_sale_count INTO v_book_id, v_book_name, v_sales_count;
    EXIT WHEN c_sale_count%NOTFOUND;
    --处理获取到的数据
    DBMS_OUTPUT.PUT_LINE(v_book_name || '：' || v_sales_count);
  END LOOP;

  --关闭游标
  CLOSE c_sale_count;
END;

这段代码定义了一个游标，然后使用循环遍历游标中的每一行数据，将数据输出到控制台上。执行该代码后，控制台会输出以下结果：

Head First 设计模式：70
Java编程思想：30

示例二：通过游标修改图书价格

假设我们需要将书籍的价格打8折，并更新到数据库中，可以使用以下游标代码：

DECLARE
  --定义游标
  CURSOR c_books IS SELECT * FROM books;

  --定义变量
  v_book_id books.book_id%TYPE;
  v_book_name books.book_name%TYPE;
  v_author books.author%TYPE;
  v_price books.price%TYPE;
  v_pub_date books.pub_date%TYPE;
BEGIN
  --打开游标
  OPEN c_books;
  --依次获取游标中每个结果行
  LOOP
    FETCH c_books INTO v_book_id, v_book_name, v_author, v_price, v_pub_date;
    EXIT WHEN c_books%NOTFOUND;
    --处理获取到的数据
    UPDATE books SET price = v_price * 0.8 WHERE book_id = v_book_id;
  END LOOP;

  --关闭游标
  CLOSE c_books;
  COMMIT;
END;

这段代码定义了一个游标，然后使用循环遍历游标中的每一行数据，将价格打8折后更新到数据库中。注意，在修改完数据后需要执行 COMMIT 语句提交事务。