PLSQL 游标的使用

最新推荐文章于 2025-05-25 18:26:05 发布

原创最新推荐文章于 2025-05-25 18:26:05 发布 · 5.9k 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

本文详细介绍了PL/SQL中游标的使用方法，包括游标的声明、打开、读取和关闭等基本操作，并通过具体示例展示了如何利用游标进行数据处理。此外，还涉及了静态显示游标、游标FOR循环以及带参数的游标等多种应用场景。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1.游标特性
%FOUND
%OPEN
%NOTFOUND
%ROWCOUNT

2.静态显示游标
DECLARE
id item.item_id%TYPE;
title varchar2(60);
CURSOR c IS
    SELECT item_id,item_title
    FROM item
BEGIN
OPEN c;
LOOP
    FETCH c INTO id,title;
    EXIT WHEN c%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('id||'   '||title);
END LOOP;
CLOSE c;
END;

游标FOR循环
1.
DECLARE
CURSOR c IS
    SELECT item_id AS id,item_title AS title
    FROM item
BEGIN
　FOR i IN c LOOP
    DBMS_OUTPUT.PUT_LINE(i.id||' '||i.title);
END LOOP;
END;

2.简单游标循环，需显式打开和关闭游标
DECLARE
TYPE item_record IS RECORD (
   id NUMBER,
   title VARCAHR2(60));
item ITERM_RECORD;
CURSOR c IS
   SELECT item_id,item_title
   FROM item;

BEGIN
   OPEN c;
   LOOP
     FETCH c INTO item;
     EXIT WHEN c%NOTFOUND;
     DBMS_OUTPUT.PUT_LINE(item.itemid||'   '||item.item_title);
   END LOOP;
   CLOSE c;
END;

3.
DECLARE
TYPE item_record IS RECORD (
   id NUMBER,
   title VARCAHR2(60));
item ITERM_RECORD;
CURSOR c IS
   SELECT item_id,item_title
   FROM item;
BEGIN
FOR i IN c LOOP
    item := i;
    DBMS_OUTPUT.PUT_LINE(item.itemid||'   '||item.item_title);
END LOOP;
END;

4.带参数的游标(简单循环）
DECLARE
   lowend NUMBER;
   highend NUMBER;
   item_id NUMBER := 1012;

   TYPE item_record IS RECORD(
     id　NUMBER,
     title VARCHAR2(60));
   item ITEM_RECORD;
   CURSOR c(low_id NUMBER,high_id NUMBER) IS
     SELECT item_id,item_title
     FROM item
     WHERE item_id BETWEEN low_id AND high_id;

BEGIN
   lowend := 1005;
   highend := 1021;
   OPEN c(lowend,highend);
   LOOP
      FETCH c INTO item;
      EXIT WHEN c%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(item.id || ' '|| item.title);
   END LOOP;
   CLOSE c;
END;

5.带参数的游标(FOR 循环）
DECLARE
   lowend NUMBER;
   highend NUMBER;
   item_id NUMBER := 1012;
   TYPE item_record IS RECORD(
     id　NUMBER,
     title VARCHAR2(60));
   item ITEM_RECORD;
   CURSOR c(low_id NUMBER,high_id NUMBER) IS
     SELECT item_id,item_title
     FROM item
     WHERE item_id BETWEEN low_id AND high_id;
BEGIN
   lowend := 1005;
   highend := 1021;
   FOR i IN c(lowend,highend) LOOP
     item := i;
     DBMS_OUTPUT.PUT_LINE(item.id || ' '|| item.title);
END;