精华内容
下载资源
问答
  • Oracle 游标详解(cursor)

    万次阅读 多人点赞 2019-01-08 22:27:49
    文章目录1 概述1.1 思维导图2 语法2.1 基本写法(4步)2.2 游标4大属性3 分类3.1 静态游标3.1.1 隐式游标 dml3.1.2 显式游标 cursor3.2 动态游标3.2.1 自定义类型 ref cursor3.2.2 系统类型 sys_refcursor4 扩展4.1 ...

    1 概述

    1. 游标是什么?
       用来存储多条查询数据的一种数据结构('结果集'),
       它有一个 '指针',从上往下移动('fetch'),从而能够 '遍历每条记录'
       
    2. 优缺点
       (1) 提高 sql '执行效率'
       (2) 牺牲 '内存'
    

    游标概念图:
    在这里插入图片描述

    1.1 思维导图

    Oracle 游标详解

    2 语法

    2.1 基本写法(4步)

    -- 测试基础数据
    create table stu_info (
      id   number(3),
      name varchar2(30),
      sex  varchar2(2)
    );
    
    insert into stu_info(id, name, sex) values (1, '小游子', '女');
    insert into stu_info(id, name, sex) values (2, '小优子', '男');
    commit;
    

    游标语法:4 个步骤,获取 学生信息表(stu_info)的记录

    declare
      -- 1 声明游标
      cursor cur_stu_info is
        select * from stu_info;
      v_stu_info cur_stu_info%rowtype;
    begin
      -- 2 开启游标
      open cur_stu_info;
    
      -- 3 获取数据(一次获取一行)
      fetch cur_stu_info
        into v_stu_info;
      dbms_output.put_line(v_stu_info.id || ' : ' || v_stu_info.name);
    
      -- 4 关闭游标
      close cur_stu_info;
    
    end;
    

    执行截图:

    1 : 小游子
    

    2.2 游标4大属性

    属性            返回值类型		作用
    sql%isopen      布尔型			判断游标是否 '开启'
    sql%found		布尔型			判断游标是否 '获取' 到值
    sql%notfound	布尔型		    判断游标是否 '没有获取' 到值(常用于 "退出循环"sql%rowcount	整型			    '当前' 成功执行的数据行数(非 "总记录数"

    特别说明:sql%notfound

    Oracle 官方文档解释:Before the first fetch%NOTFOUND returns NULL. If fetch never executes susscessfully. the loop is never exited, because then EXIT WHEN statement executes only if it’s WHEN condition is true. To be safe. you might want to use the following EXIT statement instead:
    EXIT WHEN SQL%NOTFOUND OR SQL%NOTFOUND IS NULL;

    简单说,退出循环的必要条件:fetch 语句执行成功 + 第一次 fetch 的值返回 null

    -- **************************************************************
    -- 功能:循环读取数据
    -- 核心:先后顺序 = 先 fetch ... 再 exit when *%notfound
    -- ************************************************************** 
    declare
      cursor cur_stu_info is
        select * from stu_info t where t.id = 9;
      v_stu_info cur_stu_info%rowtype;
    begin
    
      open cur_stu_info;
    
      loop
        fetch cur_stu_info
          into v_stu_info; -- 可测试,这段 fetch 放在 %notfound 后面
      
        exit when cur_stu_info%notfound;
      
        dbms_output.put_line('该语句不会执行,因为没有 id = 9 的记录');
      
      end loop;
    
      close cur_stu_info;
    end;
    

    3 分类

    3.1 静态游标

    3.1.1 隐式游标 dml

    1. 自动创建
       (1) DML
       (2) select into
       
    2. 自动管理
       (1) 无需人为干预(自动声明、打开、关闭)
       (2) 默认游标名:'SQL'
    

    演示:

    declare
      v_count number;
    begin
      insert into stu_info (id, name, sex) values (3, '瑶瑶', '女');
      if sql%found then
        dbms_output.put_line('插入成功!');
      end if;
    
      update stu_info t set t.name = '悠悠' where t.id = 3;
      if sql%found then
        dbms_output.put_line('更新成功!');
      end if;
    
      delete from stu_info t where t.id = 3;
      if sql%found then
        dbms_output.put_line('删除成功!');
      end if;
    
      select count(1) into v_count from stu_info t;
      if sql%found then
        dbms_output.put_line('总记录为: ' || v_count);
      end if;
    
      if sql%isopen then
        dbms_output.put_line('不可能的,永远不可能走这一步');
      else
        dbms_output.put_line('系统已自动关闭游标');
      end if;
    end;
    

    测试结果:

    插入成功!
    更新成功!
    删除成功!
    总记录为: 2
    系统已自动关闭游标
    

    3.1.2 显式游标 cursor

    由关键字 cursor 声明,可带参数,也可不带参数
    

    情况1:不带参数:同上 -> 游标语法:(4 个步骤)

    情况2:带参数(声明参数值和类型):

    declare
      v_stu_info stu_info%rowtype;
    
      -- 步骤1: 声明游标
      cursor cur_stu_info(v_id stu_info.id%type) is
        select * from stu_info t where t.id = v_id;
    begin
      -- 步骤2: 打开游标
      open cur_stu_info(1);
    
      -- 步骤3: 提取数据
      fetch cur_stu_info
        into v_stu_info;
      dbms_output.put_line(v_stu_info.id || ' : ' || v_stu_info.name);
    
      -- 步骤4: 关闭游标
      close cur_stu_info;
    
    end;
    

    测试结果(都一样,因为都是取得 id=1 的记录):

    1 : 小游子
    

    3.2 动态游标

    3.2.1 自定义类型 ref cursor

    有两种使用情况
    1. 弱类型,无 return(常用)
    2. 强类型,有 return(游标的类型必须和 return 的类型完全一致)
       (1) '字段数量、字段类型、字段顺序' 均完全同 return 一致
       (2) 此时 open ... for 后只能用 sql 语句,而不能是 '字符串'
    

    情况1:弱类型,无 return(常用)

    declare
      v_sql_select varchar(200);
      v_id         scott.stu_info.id%type;
      v_name       scott.stu_info.name%type;
      type cur_stu_type is ref cursor; -- 无 return
      cur_stu_info cur_stu_type;
    
    begin
      -- 测试
      v_id := 1;
    
      v_sql_select := 'SELECT t.id, t.name 
                         FROM stu_info t 
                        WHERE t.id <= :b1';
    
      open cur_stu_info for v_sql_select
        using v_id; -- 绑定变量(大数据处理常用优化手段)
    
      fetch cur_stu_info
        into v_id, v_name;
    
      dbms_output.put_line(v_id || ' :' || v_name);
    
      close cur_stu_info;
    
    end;
    

    输出结果:

    1 :小游子
    

    情况2:强类型,有 return

    declare
      v_sql_select    varchar(200);
      v_stu_info_rows scott.stu_info%rowtype;
      type cur_stu_type is ref cursor return scott.stu_info%rowtype; -- 有 return 
      cur_stu_info cur_stu_type;
    
    begin
      -- 测试
      /*  v_id := 1;
      
      v_sql_select := 'SELECT t.id, t.name 
                         FROM stu_info t 
                        WHERE t.id <= :b1';*/
    
      open cur_stu_info for
        select t.id, t.name, t.sex from stu_info t;
    
      fetch cur_stu_info
        into v_stu_info_rows;
    
      dbms_output.put_line(v_stu_info_rows.id || ' :' || v_stu_info_rows.name);
    
      close cur_stu_info;
    
    end;
    

    输出结果:(同弱类型一样)

    1 :小游子
    

    3.2.2 系统类型 sys_refcursor

    常用,省去了手动定义动态游标的步骤,以下效果等同:

    declare
       -- type cur_stu_type is ref cursor; -- 手动定义动态游标
       -- cur_stu_info cur_stu_type;
       
       -- 声明动态游标, 这一个步骤等于上面两个步骤
       cur_stu_info sys_refcursor;  
    begin
    end;
    

    4 扩展

    4.1 三种游标循环效率对比

    结论:一般来说 '批量处理' 的速度要最好,'隐式游标' 的次之,'单条处理' 的最差
    说明:若有兴趣,可以在数据量多的表里面,分别尝试下列三种写法,并打印时间,用作比较
    
    1. 批量处理
       open 游标;
       loop
          fetch 游标 bulk collect  
           into 集合变量(也就是 table 类型哦) limit 数值; -- 一般 500 左右
           
           exit when 条件; -- cursor.count = 0
           逻辑处理;
        end loop;
        close 游标;
    
    2. 隐式游标
       for x in (sql 语句) loop
          逻辑处理;
       end loop;
    
    3. 单条处理
       open  游标;
       loop
          fetch 游标 
           into 变量;
         
           exit when 条件;
           逻辑处理;
       end loop;
       close 游标;
    

    4.2 实例:实际开发中,游标遍历数据

    -- *************************************************
    -- 功能:读取表 stu_info 数据
    -- *************************************************
    declare
      v_sql_select varchar(300);
      cur_stu_info sys_refcursor;
      type record_stu_info is record(
        v_id   scott.stu_info.id%type,
        v_name scott.stu_info.name%type);
      type table_stu_info is table of record_stu_info;
      v_stu_info_table table_stu_info;
    
    begin
      v_sql_select := 'SELECT t.id, t.name 
                         FROM stu_info t 
                        WHERE t.id <= :b1';
    
      open cur_stu_info for v_sql_select
        using 3; -- 绑定变量,此处 3,仅用作测试
    
      loop
        fetch cur_stu_info bulk collect
          into v_stu_info_table limit 1; -- 数据量太少,仅当前测试使用哦,实际开发 建议 500 左右
        -- 此时 %notfound 不合适,count 适用
        -- 因为 可能找到数据了(found 非空值),但是小于 limit n
        exit when v_stu_info_table.count = 0; -- 退出条件!
      
        for i in v_stu_info_table.first .. v_stu_info_table.last loop
          dbms_output.put_line('序号:' || v_stu_info_table(i).v_id || ' , ' ||
                               '姓名:' || v_stu_info_table(i).v_name);
        end loop;
      end loop;
    
      close cur_stu_info;
    
    exception
      when others then
        -- 异常时,仍要 关闭游标
        if cur_stu_info%isopen then
          close cur_stu_info;
        end if;
        dbms_output.put_line(sqlcode || ' : ' || sqlerrm);
        dbms_output.put_line(dbms_utility.format_error_backtrace);
    end;
    

    测试结果:(建议在 ‘测试窗口’ debug 看看执行步骤)

    序号:1 , 姓名:小游子
    序号:2 , 姓名:小优子
    

    如果对 table 类型、record 类型有疑问,
    请点击 %type、%rowtype、record,varry、table 的使用详解

    展开全文
  • 游标 CURSOR 一、游标概述 游标(cursor)是数据库系统在内存中开设的一个数据缓冲区,存放SQL语句的执行结果。 每个游标都有一个名字,用户可以用SQL语句逐一从游标中获取记录,并赋给变量做进一步处理。 作用:...

    转载自:https://www.2cto.com/database/201801/715697.html 学习备忘

    游标 CURSOR

    一、游标概述

    游标(cursor)是数据库系统在内存中开设的一个数据缓冲区,存放SQL语句的执行结果。

    每个游标都有一个名字,用户可以用SQL语句逐一从游标中获取记录,并赋给变量做进一步处理。

    作用:用于定位结果集的行 和 遍历结果集。

    游标产生时间:当执行DML SQL语句时;

    游标用于存放:结果集

    游标有名字吗:有,SQL或用户给他取名

    游标如何操作:用FETCH语句逐一从游标中提取记录,并赋给变量进一步处理

    同时可打开几个游标:多个,具体数量由数据库初始化参数OPEN_CURSOR决定。

    二、游标分类

    游标分为:静态游标(隐式和显式)和REF游标(+游标变量)

    1. 游标的属性

    %FOUND SQL语句影响了一行或多行时为true;

    %NOTFOUND SQL语句没有影响任何行时为true(常用,没找到为T,就退出)

    %ROWCOUNT SQL语句影响的行数;

    %ISOPEN 游标是否打开,始终为false。

    1. 静态游标

    静态游标是指结果集已经确实(静态定义)的游标。

    静态游标分:隐式游标 和 显示游标。

    2.1 隐式游标

    在PL/SQL中隐式游标在执行DML SQL(Insert/Delete/Update/Select)语句时自动创建、自动声明、打开和关闭,其名SQL(注:所有的隐式游标名都叫“SQL”);

    示例:

    使用游标的属性。

    BEGIN

    UPDATE emp SET sal=5000 WHERE empno=7369;

    IF SQL%FOUND THEN

    DBMS_OUTPUT.PUT_LINE( ‘表已更新’ );

    END IF;

    END;

    提示:

    在Java中,如对表进行了增删改操作,结果会返回n,表示在数据库中多少行受影响。

    我们通过 n>0 或 n=0 来判断SQL代码是否执行成功。

    这个n 即SQL%ROWCOUNT属性。

    2.2 显示游标

    显示游标用于处理SELECT时返回多行的查询;

    增删改时不会用显示游标;

    显示游标需要手动的去做声明、打开、提取、关闭操作。

    示例:

    l 显示游标操作

    DECLARE

    –声明游标:划分存储区域,注意此时并没有执行Select语句。

    CURSOR c_name IS

    SELECT sal FROM emp WHERE empno=7369;

    my_sal emp.sal%TYPE;

    BEGIN

    –打开游标:执行select语句,获得结果集存到游标中,此时游标指向结果集头,而不是第一条记录。

    OPEN c_name;

    –提取记录:移动游标取一条记录

    FETCH c_name INTO my_sal;

    DBMS_OUTPUT.PUT_LINE(my_sal);

    –关闭游标:将游标放入缓冲池中,没有完全释放资源,可重新打开。

    CLOSE c_name;

    END;

    l 带参数的显示游标

    DECLARE

    CURSOR c_name( dno NUMBER ) IS

    SELECT * FROM emp WHERE deptno=dno;

    my_a emp%ROWTYPE;

    BEGIN

    OPEN c_name(10); --打开游标,并传值

    LOOP

    FETCH c_name INTO my_a;

    EXIT LOOP c_name%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE( ‘名字:’||my_a.ename );

    END LOOP;

    CLOSE c_name;

    END;

    l For循环游标

    作用:简化游标处理代码(简化打开、提取、关闭)。

    基本语法:

    FOR r_index IN cursor_name LOOP

    ……

    END LOOP;

    示例:

    For循环游标操作

    DECLARE

    CURSOR c_name IS

    SELECT * FROM emp;

    BEGIN

    FOR i IN c_name LOOP

    DBMS_OUTPUT.PUT_LINE( i.ename||’ '||i.job );

    END LOOP;

    END;

    注释:如同foreach语句一样,i默认为行类型(%rowtype),自动打开、提取、关闭。

    l 带参数的For循环游标

    DECLARE

    CURSOR c_name( dno number ) IS

    SELECT * FROM emp WHERE deptno=dno;

    BEGIN

    FOR i IN c_name(30) LOOP

    DBMS_OUTPUT.PUT_LINE( i.ename||’ '||i.job );

    END LOOP;

    END;

    l 游标的嵌套问题

    固名思义,就是在游标中嵌套一个游标。

    示例:

    列出所有部门的人员信息。

    要求结果如下:

    部门号:10 部门名称:****

    编号:** 姓名:**

    编号:** 姓名:**

    ……

    部门号:20 部门名称:****

    编号:** 姓名:**

    编号:** 姓名:**

    ……

    分析:

    1.创建部门游标,先将部门信息列出来;

    2.在循环取部门信息时,再创建打开员工表游标,进行读取。

    代码:

    DECLARE

    CURSOR mydc IS

    SELECT * FROM dept;

    mydr dept%ROWTYPE;–声明dept部门表对象

    CURSOR myec(dno NUMBER) IS

    SELECT * FROM emp WHERE empno=dno;

    myer emp%ROWTYPE;–声明emp员工表对象

    BEGIN

    –先打开部门结果集游标

    OPEN mydc;

    LOOP

    FETCH mydc INTO mydr;

    EXIT LOOP mydc%NOTFOUND;–提取完退出LOOP

    DBMS_OUTPUT.PUT_LINE( ‘部门号:’||mydr.deptno||’ 部门名称:’||mydr.dname );

    –再打开员工结果集游标,入部门编号

    OPEN myec(mydr.deptno);

    LOOP

    FETCH myec INTO myer;

    EXIT LOOP myec%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(’ 编号:’||myer.empno||’ 姓名:’||myer.ename);

    END LOOP;

    CLOSE myec;

    END LOOP;

    CLOSE mydc;

    END;

    –使用For循环简化

    DECLARE

    CURSOR mydc IS

    SELECT * FROM dept;

    CURSOR myec(dno number) IS

    SELECT * FROM emp WHERE empno=dno;

    BEGIN

    –遍历部门结果集游标

    FOR i INT mydc LOOP

    DBMS_OUTPUT.PUT_LINE( ‘部门号’||i.deptno||’ 部门名称:’||i.dname );

    –遍历员工结果集游标,传下部门编号

    FOR j INT myec(i.detpno) LOOP

    DBMS_OUTPUT.PUT_LINE( ’ 编号’||j.empno||’ 姓名:’||j.ename );

    END LOOP;

    END LOOP;

    END;

    1. REF游标

    REF游标也叫动态游标,动态SQL执行时产生。

    REF游标更应该被称之为游标类型,而游标变量则为该类型的游标。

    3.1 创建REF游标+游标变量

    基本语法:

    –创建REF游标

    TYPE ref_cursor_name IS REF CURSOR

    [RETURN 返回类型];

    –声明REF游标变量

    游标变量 ref_cursor_name;

    示例:

    创建个REF游标类型

    TYPE ref_name IS REF CURSOR; --定义弱游标类型,名字叫ref_name

    c_name ref_name;

    3.2 REF游标分类

    强类型:带RETURN返回类型

    弱类型:不带RETURN返回类型

    相对来说,弱类型更为灵活。

    示例:

    声明强类型REF游标+游标变量

    DECLARE

    TYPE c_type IS REF CURSOR

    RETURN emp%ROWTYPE;

    c_name c_type;

    3.3 打开游标变量

    OPEN cursor_name FOR 查询结果集;

    示例:

    用REF游标+游标变量显示数据

    DECLARE

    TYPE my_t IS REF CURSOR

    RETURN emp%ROWTYPE; --声明一个游标类型

    myc my_t; --定义一个REF类型的游标变量

    myr emp%ROWTYPE;

    BEGIN

    OPEN myc FOR

    SELECT * FROM emp;

    LOOP

    FETCH myc INTO myr;

    EXIT WHEN myc%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(myr.ename);

    END LOOP;

    CLOSE myc;

    END;

    1. 标变量的优点和限制

    4.1 游标变量功能强大,可以简化数据处理;

    4.2 游标变量优点:

    a.可从不同的select语句中提取结果集;

    b.可以作为过程的参数进行传递;

    c.可以引用游标的所有属性;

    d.可以进行赋值运算;

    4.3 游标变量的限制:

    a.不能在程序包中声明游标变量;

    b.for update子句不能与游标变量一起使用;

    c.不能使用比较运算符。

    三、动态语句拼接 :n

    设定一个游标变量来执行动态的sql语句。

    数据库管理员、设计师常用的知识点。

    基本语法:

    OPEN 游标名 FOR ‘SELECT … >:1 …’;

    USEING 变量名;

    示例1:

    OPEN cursor_name FOR

    ‘SELECT * FROM emp WHERE sal>:1 ORDER BY sal DESC’

    USEING p_sal;

    解释:

    –设置占位符:sal>:1动态拼接(相当于JDBC中的?占位符)

    –填充占位符:将变量p_sal的值代入SELECT中“:1”位置处

    –如有多个动态拼接处,请设置:2,:3,……,同时using 后面变量用逗号分隔。

    示例2:

    DECLARE

    r_emp emp%ROWTYPE;

    –定义REF游标及游标变量

    TYPE c_type IS REF CURSOR;

    cur c_type;

    p_salary NUMBER;

    BEGIN

    p_salary := 2500;

    –打开游标

    OPEN cur FOR

    ‘select * from emp where sal>:1 order by sal desc’

    –填充占位符

    USING p_salary;

    DBMS_OUTPUT.PUT_LINE(‘薪水大于’|| p_salary ||‘的员工有:’);

    LOOP

    –提取游标中的数据到变量r_emp

    FETCH cur INTO r_emp;

    EXIT WHEN cur%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(‘编号:’|| r_emp.empno||‘姓名:’||r_emp.ename||‘薪水:’||r_emp.sal);

    END LOOP;

    –关闭游标

    CLOSE cur;

    END;

    展开全文
  • Oracle游标详解

    2021-03-15 16:21:16
    游标(CURSOR):游标是把从数据表中提取出来的数据,以临时表的形式存放在内存中,在游标中有一个数据指针,在初始状态下指向的是首记录,利用fetch语句可以移动该指针,从而对游标中的数据进行各种操作。 2、游标的...

    1、游标的概念

    游标(CURSOR):游标是把从数据表中提取出来的数据,以临时表的形式存放在内存中,在游标中有一个数据指针,在初始状态下指向的是首记录,利用fetch语句可以移动该指针,从而对游标中的数据进行各种操作。

    2、游标的作用

    游标是用来处理使用SELECT语句从数据库中检索到的多行记录的工具。借助于游标的功能,数据库应用程序可以对一组记录逐条进行处理,每次处理一行。

    3、游标的类型

    • 显式游标(Explicit Cursor):显式游标需要定义声明,在使用前要打开和获取,使用完毕后要关闭。多用于返回多行的SELECT语句
    • 隐式游标(Implicit Cursor):在执行一个SQL语句时,服务器将自动创建一个隐式游标,该游标是内存中的工作区,存储了执行SQL语句的结果,可通过游标的属性获得SQL的执行结果及状态信息。多用于只返回一行的SQL语句

    4、隐式游标

    (ORACLE在创建隐式游标时,默认的游标名为SQL)

      1)游标的主要属性(显示游标、隐式游标)

    • %FOUND 布尔型属性,当SQL语句至少影响一行时为TRUE,否则为FALSE
    • %NOTFOUND 布尔型属性,当SQL语句没有影响的行时为TRUE,否则为FALSE
    • %ISOPEN 布尔型属性,当游标已打开时返回TRUE,否则为FALSE(对用户而言,隐式游标永远是false)
    • %ROWCOUNT 数 字型属性,返回受到SQL影响的行数

    注意:
    属性名与游标名之间没有空格。
    游标的属性只能在PL/SQL块中使用,而不能在SQL语句中使用
    例1 将PRODUCTS表中类型为1的所有产品的单价打9折,并显示该更新所影响的行数.

    BEGIN
       UPDATE products
       SET  unitprice=unitprice*0.9
       WHERE categoryid=1;
       IF SQL%FOUND THEN
          dbms_output.put_line(‘更新了’||SQL%ROWCOUNT||’条记录’);
      ELSE
          dbms_output.put_line(‘没有更新记录’);
      END IF;
    END;

    5.显式游标

     1)定义游标     

         在使用显示游标之前,必须先在声明部分定义游标,其定义语法如下: CURSOR cursor_name[(parameter,…)]         IS select_statement; 说明: 参数parameter形式如下:para_name [IN] data_type [:=|DEFAULT value]

    (2)打开游标       

      当打开游标时,ORACLE会执行游标所对应的SELECT语句,并将结果存放到结果集,其定义语法如下: OPEN cursor_name[(parameter,…)];

    (3)提取数据语法如下:  

       FETCH  cursor_name INTO variable[,…];
    说明:

    • 对游标第一次执行FETCH语句时,它将工作区中的第一条记录赋给赋给变量,并使工作区内的指针指向下一条记录。
    • 工作区中的游标指针只能向下移动,不能回退。
    • 在使用FETCH语句之前,必须先打开游标,才能保证工作区内有数据。
    • INTO子句中的变量,顺序、类型必须与工作区中每行记录的字段数、顺序以及数据类型一一对应

    (4)关闭游标       

      关闭游标可释放其结果集,语法如下:  CLOSE  cursor_name;说明:关闭游标,就是使游标所对应的内存工作区变为无效,并释放与游标相关的系统资源

    显式游标——无参游标

    例2:用显式游标显示输出products表中供应商编号为6 的产品的信息。

    DECLARE
       CURSOR  prod_cursor  IS
         select *from products where
         supplierid=6;
      prod_record products%rowtype;
    BEGIN
      OPEN prod_cursor;
      LOOP
       FETCH prod_cursor INTO prod_record;
    EXIT WHEN
        prod_cursor%NOTFOUND;
    dbms_output.put_line(‘产品编号’||prod_record.productid);
    dbms_output.put_line(‘产品名称’||prod_record.productname);
    dbms_output.put_line(‘供应商编号’||prod_record.supplierid);
    END LOOP;
    CLOSE prod_cursor;
    END;

    显式游标——参数游标

    例3:用显式参数游标显示输出products表中供应商编号为XX 的产品的信息。

    DECLARE
       CURSOR  prod_cursor (suppID in number DEFAULT 1)  IS
         select * from products where
         supplierid=suppId;
      prod_record products%rowtype;
    BEGIN
      OPEN prod_cursor(2);
      LOOP
       FETCH prod_cursor INTO prod_record;
    EXIT WHEN
        prod_cursor%notfound;
    dbms_output.put_line(‘产品编号’||prod_record.productid);
    dbms_output.put_line(‘产品名称’||prod_record.productname);
    dbms_output.put_line(‘供应商编号’||prod_record.supplierid);
    END LOOP;
    CLOSE prod_cursor;
    END;

    注意:在为参数游标定义参数的数据类型时,不能使用长度约束

    显式游标——游标FOR循环

    语法格式: FOR 循环变量 IN 游标类型名    LOOP         循环语句    END LOOP;
    注意:

    • 循环变量不需要定义,系统隐含地定义其数据类型为%ROWTYPE的变量
    • 使用游标FOR循环时,不能使用OPEN、FETCH、CLOSE语句。

    例4:用显式参数游标显示输出products表中供应商编号为XX 的产品的信息。

    DECLARE
       CURSOR  prod_cursor (suppID in number DEFAULT 1)  IS
         select *from products where    supplierid=suppId;
    BEGIN
    FOR v_pr IN prod_cursor(3) LOOP
    dbms_output.put_line(‘产品编号’||v_pr.productid);
    dbms_output.put_line(‘产品名称’||v_pr.productname);
    dbms_output.put_line(‘供应商编号’||v_pr.supplierid);
    END LOOP;
    END;

    6.显示游标与隐式游标比较

     7.游标变量

    游标包括显示游标和隐式游标,在定义时与特定的查询绑定,即在声明中定义查询,其结构是不变的,因此又称静态变量。

    游标变量是一个指向多行查询结果集的指针,不与特定的查询绑定,可以在打开游标变量时定义查询,可以返回不同结构的结果集。

    在PL/SQL 中,使用游标变量包括定定义游标引用类型(REF CURSOR),声明游标变量,打开游标变量、检索游标变量、关闭游标变量等几个基本步骤。     

    游标变量基本步骤:

    (1) 定义游标CURSOR类型的指针TYPE type_name IS REF CURSOR [RETURN return_type];
    type_name 是新引用类型的名字
    而return_type表示该游标变量返回的记录类型。
    (2) 声明REF CURSOR类型的变量
        游标变量   type_name;

    (3) 打开游标变量的语法格式如下所示:
       OPEN  游标变量名  FOR SELECT 语句;

    (4) 检索游标变量

     检索游标变量的方法与检索静态游标相似,使用 FETCH.... INTO 语句循环检索游标变量的
    结果集中的记录。语法为:

    LOOP
    FETCH cursor _ variable INTO variablel,variable2........;
    EXIT WHEN Cursor_ variables%NOTFOUND;
    ........
    END LOOP;
    检索游标变量时只能使用简单循环或 WHILE 循环,不能采用 FOR 循环.

    (5)关闭游标变量

    检索并处理完游标变量所对应的结果集后,可以关闭游标变量,释放存储空间。语法为;

    close 游标变量名;

    游标变量的不同定义方式举例

    DECLARE
      1、-- 用 %ROWTYPE类型定义游标变量的返回值.
           TYPE t_productsRef IS REF CURSOR
            RETURN products%ROWTYPE;
      2、 -- 定义一个新的记录类型
           TYPE t_prodRecord IS RECORD (
             prodid  products.productid%TYPE,
             prodname   products.productname%TYPE);
            --定义游标变量的返回值为记录类型变量
           TYPE t_prodRef IS REF CURSOR
             RETURN  t_prodRecord;
    

    例5:使用游标变量查询出产品表中类别为1的产品的信息,并输出产品名称、类别编号、单价。

    DECLARE
          TYPE prod_record IS RECORD
          ( pname products.productname%type,
            cid products.categoryid%type,
            uprice products.unitprice%type);
          TYPE prod_cursor IS REF CURSOR RETURN prod_reord;
          v_prodcur prod_cursor ;
          v_prodrec  prod_record;
    BEGIN
       OPEN v_prodcur FOR select productname,categoryid,unitprice from products where categoryid=1;
      LOOP
    FETCH v_prodcur INTO v_prodrec ;
     EXIT WHEN v_prodcur%notfound;
     dbms_output.put_line (‘产名’||v_prodrec.pname);
    dbms_output.put_line(‘产品类别’||v_prodrec.cid);
    dbms_output.put_line (‘单价’||v_prodrec.uprice);
      End  LOOP;
    Close v_prodcur;
    End; 
    使用同一游标变量打开多个查询
    例6:使用游标变量查询出产品表中类别为1的产品的信息,然后查询输出产品单价小于20的产品信息。
    DECLARE
    TYPE prod_cursor IS REF CURSOR;
          v_cur prod_cursor;
          v_rec products%rowtype;
    BEGIN
       OPEN v_cur FOR select *  from products where categoryid=1;
      LOOP
         FETCH v_cur INTO v_rec;
         EXIT WHEN v_cur%notfound;
         dbms_output.put_line('产品名' || v_rec.productname);
         dbms_output.put_line('产品类别' || v_rec.categoryid);
         dbms_output.put_line('单价' || v_rec.unitprice);
      End  LOOP;
    OPEN v_cur FOR select *  from products where unitprice<20;
      LOOP
         FETCH v_cur INTO v_rec;
     EXIT WHEN v_cur%notfound;
        dbms_output.put_line('产品名' || v_rec.productname);
        dbms_output.put_line('产品类别' || v_rec.categoryid);
         dbms_output.put_line('单价' || v_rec.unitprice);
      End  LOOP;
    Close v_cur;
    End;
    
    展开全文
  • oracle游标详解

    2019-07-08 10:23:36
    隐式游标由PL/SQL自动定义、自动打开、自动关闭、不需要用户的参与。 隐式游标属性: %FOUND,结果为BOOLEAN型,用于判断DML语句是否改变了行,或者判断SELECT INTO是否返回了一行或者多行数据。 %NOTFOUND,跟SQL%...

    隐式游标由PL/SQL自动定义、自动打开、自动关闭、不需要用户的参与。

    隐式游标属性:

    %FOUND,结果为BOOLEAN型,用于判断DML语句是否改变了行,或者判断SELECT INTO是否返回了一行或者多行数据。
    %NOTFOUND,跟SQL%FOUND相反。
    %ISOPEN,判断游标是否打开,对隐式游标来说,当SQL语句执行完成后,游标被自动关闭,因此SQL%ISOPEN的值永远是False
    %ROWCOUNT,用于判断DML语句影响了多少行,或者SELECT INTO返回了多少行,结果为整形。

    举例:

    begin
    	delete from tra;
    	if sql%found then
    		commit;
    	else
    		null;
    	end if;
    end;
    
    SQL> begin
    delete from tra;
    dbms_output.put_line(to_char(sql%rowcount));
    end;
    /
    

    显示游标

    显示游标用cursor … is 命令定义
    如:

    declare
    cursor ct is select * from ttc;
    begin
    

    打开游标

    open ct;
    

    关闭游标

    close ct;
    

    使用完游标之后,要关闭游标,未关闭的游标占用内存,耗费系统资源。如果数据库中存在很多未关闭的游标,还可能导致数据库死机。

    从游标中取数据
    使用fetch命令从游标中提取数据,每提取一次,游标都指向结果集的下一行。

    显示游标的属性跟隐式游标属性意思不一样
    %FOUND,结果为BOOLEAN型,当最后一次读记录成功,返回值为TRUE.
    %NOTFOUND,跟%FOUND相反。
    %ISOPEN,判断游标是否打开,当游标已打开时返回true
    %ROWCOUNT,返回已从游标中读取的记录数(到目前为止),结果为整形。

    举例:

    declare
    cursor c1 is select employee_id from t_bonus where bonus <500;
    v_employee_id varchar2(20);
    begin
    	open c1;
    	loop
    		fetch c1 into v_employee_id;
    		exit when c1%notfound or c1%notfound is null;
    		-- .....
    	end loop;
    	--....
    	close c1;
    end;
    

    带参数的游标,参考 https://blog.csdn.net/huangbaokang/article/details/79570896

    游标变量

    游标变量其实就是指针,这个指针可以指向不同的查询工作区。
    游标变量的定义:

    定义cursor类型的指针。

    type ref_type_name is ref cursor [return return_type]
    

    ref_type_name是新类型的名字,return_type是可选的,它必须是一个记录类型或行类型,我们称有返回类型的为“强ref cursor”,称没有返回类型的为“弱ref cursor”。
    区别:弱ref cursor允许把游标变量与任何查询进行关联。
    强ref cursor只允许把游标变量与特定查询进行关联。

    强ref cursor

    type strong_type is ref cursor return employees%rowtype;
    

    弱ref cursor

    type weak_type is ref cursor;
    

    声明游标变量

    strong_type v2
    

    定义行类型:

    v_rec employees%rowtype;-- 定义行类型
    
    展开全文
  • ORACLE游标详解

    2019-01-10 15:15:29
    啥是游标变量? 在定义游标时,不绑定具体的查询,而是动态地打开指定类型的查询; 使用游标变量,首先应像集合那样定义一种新的游标变量类型,语法: CURSOR 游标变量类型名称 IS REF CURSOR [RETURN 数据类型]; ...
  • Oracle游标的使用实例详解

    千次阅读 2020-05-24 09:24:33
    什么是游标? ①从表中检索出结果集,从中每次指向一条记录进行交互的机制。 ②关系数据库中的操作是在完整的行集合上执行的。 由 SELECT 语句返回的行集合包括满足该语句的 WHERE 子句所列条件的所有行。由该...
  • Oracle游标的基本用法详解

    千次阅读 2019-03-29 09:35:02
    -- 游标(光标cursor) --在写Java程序中有集合的概念, 那么在pl/sql中也会用到多条记录, 这时候我们就要用到游标游标可以存储查询返回的多条数据。 -- 语法:cursor 游标名(参数 数据类型,参数 数据类型) is ...
  • ORACLE 游标详解

    2018-12-26 11:40:19
    ORACLE中一般游标和ref游标 https://wenku.baidu.com/view/c37c0073852458fb770b56e3.html?pn=1   游标标是构建在PL/SQL中,用来查询数据,获取记录集的指针。它让开发者 一次访问结果集中一行记录。 在oracle中...
  • oracle游标详解与应用

    千次阅读 2015-05-19 08:37:58
    1,什么是游标?  ①从表中检索出结果集,从中每次指向一条记录进行交互的机制。    ②关系数据库中的操作是在完整的行集合上执行的。  由 SELECT 语句返回的行集合包括满足该语句的 WHERE 子句所列条件的...
  • oracle 游标详解

    2016-04-08 21:51:17
    游标是映射在结果集中一行数据上的位置实体,有了游标,用户就可以访问结果集中的任意一行数据。将游标放置到某行后,即可对该行数据进行操作,例如提取当前行的数据,当前行的某些字段等。在大多数时候我们在设计...
  • oracle游标 详解 精析 示例 真正能把游标讲透、说全、调理清晰的讲义。 游标犹如C语言的指针:灵活、实用、高效。 游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。 游标是一个通过定义...
  • oracle游标的使用

    2018-05-22 16:09:09
    声明游标 declare cursor cur is select t.login_name, t.fk_user_id from t_user_info t; v_id t_user_info.fk_user_id%TYPE; v_name t_user_info.login_name%TYPE; v_count number; begin --2.使用l...
  • 主要介绍了详解Oracle游标的简易用法,本文通过实例代码给大家介绍的非常详细,具有一定的参考借鉴价值,需要的朋友可以参考下
  • 数据库oracle游标详解

    2018-12-28 14:24:03
    @feifeifan //oracle游标详解 游标基础:游标有一种最简单的形式,我们可以把它想象成一个指针,指向一个查询结果,这个查询可以指向一个或多个数据库表。 比如声明一个游标: cursor staff_cursor is select * from ...
  • Oracle 游标详解

    2015-01-08 11:25:03
    Oracle游标循环 第一种使用loop 循环  open c_postype;  0. loop  fetch c_postype into v_postype,v_description ; exit when c_postype%notfound; …… end loop colse c_p
  • ORACLE游标使用详解

    2018-07-10 11:16:55
    游标使用详解

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 4,749
精华内容 1,899
关键字:

oracle游标详解