Oralce 游标知识点总结

想吃桔子zz

已于 2022-05-18 10:34:21 修改

阅读量390

点赞数

文章标签： oracle

于 2022-05-09 17:48:35 首次发布

本文链接：https://blog.csdn.net/weixin_42481104/article/details/124666196

版权

隐式游标(DML,SELECT INTO)

隐式游标属性：

隐式游标案例

动态游标(REF)

游标 CURSOR

游标类似 C 语言中的指针，游标可以指向结果集中任意位置。当腰对结果集进行逐行单独处理时，必须声明一个指向该结果集的游标变量。默认指向结果集的首记录。

游标分类：

静态游标：显式游标/隐性游标
动态游标(REF)

显式游标(DQL)声明与操作过程

有明确的游标声明和操作过程。操作过程包括打开游标，取值，关闭游标。

定义游标：使用查询语句来定义游标的列和行。在指定数据类型时，不能使用长度约束，如 NUMBER(4)
```
CURSOR cursorname [(parameter_name datatype,...)]  IS select_statement;
```
打开游标，使用 PL/SQL 命令 OPEN 来打开一个声明的游标
```
OPEN cursor_name;
```
提取数据，从游标中重复提取每条记录到数据结构中，直到数据集合被提空
```
FETCH cursor_name INTO Record_name;
```
关闭游标，使用完游标之后将其关闭
```
CLOSE cursor_name;
```

显式游标属性：

%ISOPEN: 判断游标属性是否打开，打开返回 true，否则 false

%FOUND: 用于检查行数据是否有效，有效返回 true，否则 fals

%NOTFOUND: 与%FOUND相反，如果没有提取数据则返回 true，否则 fals

%ROWCOUNT: 表示累计到当前为止使用FETCH提取数据的行数。若OPEN游标，但没有进行 FETCH ，则 ROWCOUNT 值为0.

显式游标案例

CREATE TABLE fruits
(
f_id    varchar2(10)     	NOT NULL,
s_id    number(6)        	NOT NULL,
f_name  varchar(255)  	NOT NULL,
f_price number (8,2)  	NOT NULL
);
INSERT INTO fruits (f_id, s_id, f_name, f_price) VALUES  ('a1', 101,'apple',5.2);
INSERT INTO fruits (f_id, s_id, f_name, f_price) VALUES ('b1',101,'blackberry', 10.2);
INSERT INTO fruits (f_id, s_id, f_name, f_price) VALUES ('bs1',102,'orange', 11.2);
INSERT INTO fruits (f_id, s_id, f_name, f_price) VALUES('bs2',105,'melon',8.2);
INSERT INTO fruits (f_id, s_id, f_name, f_price) VALUES ('t1',102,'banana', 10.3);
INSERT INTO fruits (f_id, s_id, f_name, f_price) VALUES ('t2',102,'grape', 5.3);
INSERT INTO fruits (f_id, s_id, f_name, f_price) VALUES ('o2',103,'coconut', 9.2);
INSERT INTO fruits (f_id, s_id, f_name, f_price) VALUES  ('c0',101,'cherry', 3.2);
INSERT INTO fruits (f_id, s_id, f_name, f_price) VALUES  ('a2',103, 'apricot',2.2);
INSERT INTO fruits (f_id, s_id, f_name, f_price) VALUES('l2',104,'lemon', 6.4);
INSERT INTO fruits (f_id, s_id, f_name, f_price) VALUES ('b2',104,'berry', 7.6);
INSERT INTO fruits (f_id, s_id, f_name, f_price) VALUES ('m1',106,'mango', 15.6);
INSERT INTO fruits (f_id, s_id, f_name, f_price) VALUES  ('m2',105,'xbabay', 2.6);
INSERT INTO fruits (f_id, s_id, f_name, f_price) VALUES  ('t4',107,'xbababa', 3.6);
INSERT INTO fruits (f_id, s_id, f_name, f_price) VALUES  ('m3',105,'xxtt', 11.6);
INSERT INTO fruits (f_id, s_id, f_name, f_price) VALUES('b5',107,'xxxx', 3.6);

读取游标首条数据

set serveroutput on; -- 打开oracle自带的输出方法dbms_output
DECLARE 
    CURSOR frt_cur -- 声明游标
    IS SELECT f_id,f_name FROM fruits ; -- 游标关联的查询
    cur_fruits  frt_cur%ROWTYPE; -- 定义一个游标变量
BEGIN
OPEN  frt_cur; -- 打开游标 
    FETCH frt_cur INTO cur_fruits; -- 利用FETCH语句从结果集中提取指针指向的当前行记录(1次1条)
    dbms_output.put_line(cur_fruits.f_id||'.'||cur_fruits.f_name); -- 输出 
    CLOSE  frt_cur; --关闭游标，释放资源
END;

结果：a1.apple

使用LOOP语句

注意：FETCH 每次会提取一条数据，所以循环。

set serveroutput on;
DECLARE 
    CURSOR frt_loop_cur
    IS SELECT f_id,f_name,f_price FROM fruits WHERE f_price>10;

    cur_id  fruits.f_id%TYPE; -- 类型同表fruits的对应的字段类型一致
    cur_name  fruits.f_name%TYPE;
    cur_price  fruits.f_name%TYPE;

BEGIN
   OPEN  frt_loop_cur;
       LOOP
          FETCH frt_loop_cur INTO cur_id,cur_name,cur_price;
          EXIT WHEN frt_loop_cur%NOTFOUND; -- 没有记录时退出循环
          dbms_output.put_line('第'|| frt_loop_cur%ROWCOUNT || '行--'||cur_id||'.'||cur_name||'.'||cur_price);
        END LOOP;
    CLOSE  frt_loop_cur;
END;

使用 BULK COLLECT 和 FOR 语句(批量提取数据)

DECLARE 
CURSOR frt_collect_cur
IS SELECT * FROM fruits
WHERE f_price>10;
TYPE FRT_TAB IS TABLE OF FRUITS%ROWTYPE;
frt_rd FRT_TAB;

BEGIN
   OPEN  frt_collect_cur;
       LOOP
          FETCH frt_collect_cur BULK COLLECT INTO frt_rd LIMIT 2; --每次提取两条数据
          FOR i in 1..frt_rd.count LOOP
          dbms_output.put_line(frt_rd(i).f_id||'.'||frt_rd(i).f_name
                          ||'.'||frt_rd(i).f_price);
        END LOOP;
        EXIT WHEN frt_collect_cur%NOTFOUND;
      END LOOP;
    CLOSE  frt_collect_cur;
END;

定义和表fruits行对象一致的集合类型frt_rd，该变量用于存放批量得到的数据。之所以这样创建：后面需要对 frt_rd 内进行循环(frt_rd(i))，如果用 frt_rd FRUITS%ROWTYPE 进行定义，是没有办法进行内循环的。

TYPE FRT_TAB IS TABLE OF FRUITS%ROWTYPE;
frt_rd FRT_TAB;

使用 CURSOR FOR LOOP 语句的游标

DECLARE 
CURSOR frt1 IS SELECT * FROM fruits WHERE f_price<10;
BEGIN
   FOR curfrt1 IN frt1
       LOOP
         dbms_output.put_line(curfrt1.f_id||'.'|| curfrt1.f_name
                          ||'.'|| curfrt1.f_price);
       END LOOP;
END;

游标声明带参数

--CURSOR FOR LOOP
DECLARE 
CURSOR frt1(V_PRICE NUMBER) IS SELECT * FROM fruits WHERE f_price< V_PRICE;
BEGIN
   FOR curfrt1 IN frt1(5)
       LOOP
         dbms_output.put_line(curfrt1.f_id||'.'|| curfrt1.f_name
                          ||'.'|| curfrt1.f_price);
       END LOOP;
END;


-- LOOP
DECLARE 
    CURSOR frt_loop_cur(V_PRICE NUMBER)
    IS SELECT f_id,f_name,f_price FROM fruits WHERE f_price<V_PRICE;
    
    cur_id  fruits.f_id%TYPE; -- 类型同表fruits的对应的字段类型一致
    cur_name  fruits.f_name%TYPE;
    cur_price  fruits.f_name%TYPE;

BEGIN
   OPEN  frt_loop_cur(5);
       LOOP
          FETCH frt_loop_cur INTO cur_id,cur_name,cur_price;
          EXIT WHEN frt_loop_cur%NOTFOUND; -- 没有记录时退出循环
          dbms_output.put_line('第'|| frt_loop_cur%ROWCOUNT || '行--'||cur_id||'.'||cur_name||'.'||cur_price);
        END LOOP;
    CLOSE  frt_loop_cur;
END;

隐式游标(DML,SELECT INTO)

没有明确的游标声明和操作过程。

隐式游标属性：

%FOUND：最近的SQL语句是否影响了一行以上的数据

%NOTFOUND：当最近的一次读记录(FETCH)成功取到值，则FALSE

%ISOPEN：永远为FALSE

%ROWCOUNT：受最近的SQL语句影响的行数

隐式游标案例

隐式游标是由数据库自动创建和管理的游标，默认名称为SQL，也成为SQL游标。每当运行 SELECT 语句时，系统会自动打开一个隐式游标，用户不能控制隐式游标，但是可以使用隐式游标。注意：使用的是 SQL%... 来判断游标的异常处理

必须保证只有一条记录符合，因为 SELECT INTO 只能返回一条记录，如果返回多条，则会提示报错。
DECLARE 
cur_id  fruits.f_id%TYPE;
cur_name  fruits.f_name%TYPE;
cur_price  fruits.f_name%TYPE;
BEGIN
    SELECT f_id,f_name,f_price INTO cur_id,cur_name,cur_price
    FROM fruits
    WHERE f_price=5.3;
    IF SQL%FOUND THEN
        dbms_output.put_line(cur_id||'.'||cur_name||'.'||cur_price);
    END IF;
END;

动态游标(REF)

动态游标：显式游标+动态SQL

DECLARE
  TYPE REFCUR IS REF CURSOR;
  V_CUR    REFCUR;
  V_FRUITS FRUITS%ROWTYPE;
BEGIN
  OPEN V_CUR FOR 'SELECT * FROM FRUITS WHERE f_price < :price' USING 5;
  LOOP
    FETCH V_CUR INTO V_FRUITS;
    EXIT WHEN V_CUR%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(V_FRUITS.F_ID || '.' || V_FRUITS.F_NAME || '.' || V_FRUITS.F_PRICE);
  END LOOP;

  CLOSE V_CUR;
END;

动态游标还有强类型以及弱类型，具体可以参考以下文章：

oracle动态(参考)游标_尚云峰的博客-CSDN博客_oracle 动态游标