• 8.游标


       oracle 在执行sql 语句时,总是需要创建一快内存区域,在这块内存区域称为上下文区域。早上下文区域中包含了出路语句的信息,这些信息包含当前语句已经处理了多少行,指向被分析语句的指针和查询语句返回的数据行集。当在pl/sql 中执行select 和dml 语句时,如果只查询单行数据,比如使用select into 语句或执行dml 语句时,oracle 会分配隐含的游标。吐过吃药处理select 语句返回的多行记录,必须要显式地定义游标。

    1. 在plsql 中使用游标
    在显式使用游标时,总是要先在declare 区对游标进行定义,然后打开游标,从游标中提取数据,在所有的操作完成之后关闭游标。通过灵活地对游标进行控制,可以比隐式游标具有更多的编程能力,也可以具有更高的效率。
    declare
       empow emp%rowtype;    -- 定义保存游标检索结果行的记录变量
       cursor emp_cur
       is
          select *
            from emp
           where deptno is not null;
    begin
       open emp_cur;        -- 打开游标
       loop                 -- 循环检索游标
          fetch emp_cur
           into emprow;     -- 提取游标内容
       -- 输出检索到的游标行的信息
       dbms_output.put_line(  '员工编号:'
                         || emprow.empno
                         ||''
                         ||'员工名称:'
                         ||emprow.ename
       );
       exit when emp_cur%notfound;   --当游标数据检索完成后退出循环
     end loop;
     close emp_cur;                  -- 关闭游标
    end;
    2.隐式游标
    begin
       update emp
          set comm = comm* 1.12
        where empno = 7369;   -- 更新员工编号魏7369的员工信息
        -- 使用隐式游标属性判断已更新的行数
        dbms_output.put_line(sql%rowcount||'行被更新');
        -- 如果没有任何更新
        if sql%notfound
        then
        --- 显示未更新的信息
        dbms_output.put_line('不能更新员工号为7369的员工!');
      end if;
      -- 向数据库提交更改
      commit;
    exception
       when others
       then
          dbms_output.put_line(sqlerrm);   -- 如果出现异常,显示异常信息
    end;
       
    3.简单的游标定义语句
    declare
       cursor emp_cursor   --定义一个查询emp表中部门编号为20的游标
       is
          select *
            from emp
           where deptno =20;
    begin
        null;
    end;
    4.根据变量查询emp表
    declare
       v_deptno number;
       cursor emp_cursor      -- 定义一个查询emp表中部门编号为20的游标
       is
          select *
            from emp
           where deptno =v_deptno;
    begin
       v_deptno :=20;
       open emp_cursor;  --打开游标
       if emp_cursor%isopen then
          dbms_output.put_line('游标已经被打开');
       end if;
    end;
    5. 声明游标参数
    declare
       cursor emp_cursor(p_deptno in number)   -- 定义游标并执行游标参数
       is
          select *
            from emp
           where deptno =p_pdeno;
    begin
       open emp_cursor(20);
    end;
    6. 指定游标的返回类型
    declare
       -- 声明游标并指定游标返回值类型
       cursor emp_cursor(p_deptno in number) return emp%rowtype
       is
          select *
            from emp
           where deptno= p_deptno;
    begin
       open emp_cursor(20);  -- 打开游标
    end;
    7.%isopen 游标属性使用示例
    declare
       cursor emp_cursor (p_deptno in number)  -- 定义游标并执行游标参数
       is
          select *
            from emp
           where deptno=p_deptno;
    begin
       if not emp_cursor%isopen then    -- 如果游标还没有被打开
          open emp_cursor(20);         -- 打开游标
       end if;
       if emp_cursor%isopen then    -- 判断游标状态,显示状态信息
          dbms_output.put_line('游标已经被打开');
       else
          dbms_output.put_line('游标还没有被打开');
       end if;
       close emp_cursor;
    end;
    8.%found 游标属性使用示例
    declare
       emp_row emp%rowtype;      --定义游标值存储变量
       cursor emp_cursor(p_deptno in number) -- 定义游标并指定游标参数
       is
          select *
            from emp
           where deptno =p_deptno;
    begin
       if not emp_cursor%isopen;
       then
          open emp_cursor(20);   --打开游标
       end if;
       if emp_cursor%found is null   --在使用fetch提取游标数据之前,值为null
       then
          dbms_output.put_line('%found 属性为null');    --- 输出提示信息
       end if;      
       loop                     -- 循环提取游标数据
          fetch emp_cursor
           into emp_row;        -- 使用fetch 语句提取游标数据
       -- 每循环一次判断%found 属性值,如果该值为false,表示提取完成,将退出循环
       exit when not emp_cursor%found;
      end loop;
      close emp_cursor;
    end;
    9.%notfound 游标属性使用示例
    declare
       emp_row emp%rowtype;     -- 定义游标值存储变量
       cursor emp_cursor(p_deptno in number)  --定义游标并指定游标参数
       is
          select *
            from emp
           where deptno =p_deptno;
    begin
       open emp_cursor(20);       -- 打开游标
       if emp_cursor%notfound is null  -- 在使用fetch提取游标数据之前,值为null
       then
          dbms_output.put_line('%notfound属性为null');  --输出提示信息
       end if;
       loop                           -- 循环提取游标数据
          fetch emp_cursor
           into emp_row;      --使用fetch 语句提取游标数据
           --每循环一次判断%found 属性值,如果该值为false,表示提取完成,将退出循环
           exit when emp_cursor%notfound;
       end loop;
       close emp_cursor;
    end;
    10 %rowtype 游标属性使用示例
    declare
       emp_row emp%rowtype;     --定义游标值存储变量
       cursor emp_cursor(p_deptno in number) -- 定义游标并指定游标参数
       is
          select *
            from emp
           where deptno =p_deptno;
    begin
       open emp_cursor(20);    -- 打开游标
       loop                    -- 循环提取游标数据
          fetch emp_cursor
           into emp_row;       -- 使用fetch 语句提取游标数据
           -- 每循环一次判断%found 属性值,如果该值为false,表示提取完成,将退出循环
         exit when emp_cursor%notfound;
         dbms_output.put_line('当前已提取的行数为'||emp_cursor%rowcount||'行!');
       end loop;
       close emp_cursor;
    end;
    11.使用fetch语句提取游标数据
    declare
      deptno dept.deptno%type;   --定义保存游标数据的变量
      dname dept.dname%type;
      loc   dept.loc%type;
      dept_row dept%rowtype;   --定义记录变量
      cursor dept_cur is select * from dept; -- 定义游标
    begin
       open dept_cur;    --打开游标
       loop
          if dept_cur%rowcount<=4 then  -- 判断如何当前提取的游标小于等于4行
          fetch dept_cur into depty_row;  --提取游标数据到记录类型
          if dept_cur%found then   -- 如果fetch 到数据,则进行显示
          dbms_output.put_line(dept_row.deptno||''||dept_row.dname||''||dept_row.loc);
        end if;
        else
        fetch dept_cur into deptno,dname,loc;   --否则提取记录到变量列表中
        if dept_cur%found then                  -- 如果提取到数据进行显示
        dbms_output.put_line(deptno||''||dname||''||loc);
        end if;
       end if;
       exit when dept_cur%notfound;             -- 判断是提取完成
      end loop;
      close dept_cur
    end;
    12 使用bulkcollect 语句批量提取游标数据
    declare
       type depttab_type is table of dept%rowtype;  --定义dept行类型的嵌套表类型
       depttab depttab_type;                        --定义嵌套表变量
       cursor deptcur is select * from dept;        --定义游标
    begin
        open deptcur;
        fetch deptcur bulk collect into depttab; -- 使用bulk collect into 子句批次插入
        close deptcur;                           -- 关闭游标
        for i in 1 .. depttab.count              -- 循环嵌套表变量中的数据
        loop
           dbms_output.put_line( depttab(i).deptno
                             || ''
                             ||depttab(i).dname
                             ||''
                             ||depttab(i).loc
              
                            );
         end loop;
         close deptcur;            --关闭游标
    end;
    13 使用 bulk collect limit 语句批量提取游标数据
    declare
       type dept_type is varray(4) of dept%rowtype;   --定义变长数组类型
       depttab dept_type ;                            -- 定义变长数组变量
       cursor dept_cursor 
       is
          select *
            from dept;                             
       v_rows int  :=4;                              --使用limit 限制的行数
       v_count int :=0;                              --保存游标提取过的数据
    begin
       open dept_cursor;                             --打开游标
       loop                                          --循环提取游标
          --每次提取4行数据到边长数组中
       fetch dept_cursor bulk collect into depttab limit v_rows;
       exit when dept_cursor%notfound;              -- 没有游标数据时退出
       dbms_output.put('部门名称:');                -- 输出部门名称
       --循环提取变长数组数据,因为边长数组只能存放4个元素,因此不能越界读取
       for i in 1 .. (dept_cursor%rowcount - v_count)
       loop
          dbms_output.put_line(depttab(i).dname||'');   -- 输出部门名称
       end loop;
       close dept_cursor;
    end;
    
                             
       
       
             
           
    14.基本的loop循环
    declare
       dept_row dept%rowtype;       --定义游标结果记录类型
       cursor dept_cursor is select * form dept; -- 定义游标变量
    begin
       open dept_cursor;          -- 打开游标
       loop
          fetch dpet_cursor into dept_row;  -- 提取游标数据
          exit when dept_cursor%notfound;   -- 退出循环的控制语句
          dbms_output.put_line('部门名称:'||dept_row.dname);
       end loop;
       close dept_cursor;--关闭游标
     end;
    15 使用while 循环检索游标数据
    declare
       dept dept_row dept%rowtype;     -- 定义游标结果记录变量
       cursor dept_cursor is select * from dept; -- 定义游标变量
    begin
       open dept_cursor;          -- 卡开游标
       fetch dept_cursor into dept_row;-- 提取游标数据
       while dept_cursor%found loop
          dbms_output.put_line('部门名称:'||dept_row.dname);
          fetch dept_cursor into dept_row; --提取游标数据
       end loop;
       close dept_cursor;
    end;
    16.使用游标for 循环检索数据
    declare
       cursor dept_cursor is select * from dept;  -- 定义游标变量
    begin
       for dept_row in dept_cursor loop
          dbms_output.put_line('部门名称:'||dept_row.dname);
       end loop;
    end;
    17 游标for 循环子查询语句
    begin
       for dept_row in (select * from dept) loop   -- 在游标for 循环中检索数据
          dbms_output.put_line('部门名称:'||dept_row.dname);
       end loop;
    end;
    18.使用游标哦更新数据
    declare
       cursor emp_cursor(p_deptno in number)
       is
          select * from emp
                  where deptno=p_deptno 
          for update;                         -- 使用for update 子句添加互斥锁
    begin
       for emp_row in emp_cursor(20)          -- 使用游标for循环检索游标
       loop
          update emp
             set comm =comm *1.12
           where current of emp_cursor;       -- 使用where current of 更新游标数据
       end loop;
       commit;                                -- 提交更改
    end;
    19.使用游标删除数据
    declare
       cursor emp_cursor(p_empno in number)
       is
          select *
               from emp
              where emp=p_empno
          for update;                       -- 使用for update 子句添加互斥锁
    begin
       for emp_row in emp_cursor(7369)      -- 使用游标for 循环 检索游标
       loop
          delete from emp
             where current of emp_cursor;  -- 使用where current of 删除游标数据
          end loop;
    end;
    20 游标变量使用示例
    declare
       type emp_type is ref cursor return emp%rowtype;   --定义游标变量类型
       emp_cur emp_type;                                 -- 声明游标变量
       emp_row emp%rowtype;                              --定义游标结果值变量
    begin
       open emp_cur for select * from emp;               -- 打开游标
       loop
          fetch emp_cur into emp_row;                    -- 循环提取游标变量
          exit when emp_cur%notfound;                    -- 循环退出检测
          dbms_output.put_line('员工名称:'||emp_row.ename);
       end loop;
        
    end;
    21. 定义游标变量
    declare
       type emp_type is ref cursor return emp%rowtype;   -- 定义游标类型
       type gen_type is ref cursor;
       emp_cur tmp_type;
       gen_cur gen_type;
    begin
       open mep_cur for select * from emp where deptno=20;
    end;
    22.打开游标变量
    declare
       type emp_curtype is ref cursor;    -- 定义游标类型
       emp_cur emp_curtype;               -- 声明游标类型的变量
    begin
       open emp_cur for select * from emp;    -- 打开游标,查询emp 所有列
       open emp_cur for select empno from emp;--打开游标,查询emp表empno 列
       open emp_cur for select deptno from dept;-- 打开游标,查询dept表deptno 列
    end; 
    23.使用fetch 语句提取游标变量数据
    declare
       type emp_type is ref cursor return emp%rowtype;   -- 定义游标类型
       emp_cur emp_type;                                 -- 声明游标变量
       emp_row emp%rowtype;    
    begin
       if not emp_cur%isopen then
       open emp_cur for select * from emp where deptno=20; -- 打开游标变量
       end if;
       loop
          fetch emp_cur into emp_row;                      -- 提取游标变量
          exit when emp_cur%notfound;                      -- 如果提取完成则退出循环
          dbms_ourput.put_line('员工名称:'++emp_row.ename||'员工职位:'||emp_row.job);  --输出员工信息
       end loop;
    end;
    24. 使用colse语句关闭游标数据
    declare
       type emp_type is ref cursor return emp%rowtype;  --定义游标类型
       emp_cur emp_type;     -- 声明游标变量
       emp_row emp%rowtype; 
    begin
       open emp_cur for select * from emp where deptno=20; -- 打开游标
       fetch emp_cur into emp_row;                         -- 提取游标
       while emp_cur%found loop
          dbms_output.put_line('员工名称:'||emp_row.ename);
          fetch emp_cur into emp_row;
      end loop;
      close emp_cur;                                --关闭游标
      
       
    end;
    25.处理invalid_cursor异常
    declare
       type emp_curtype is ref cursor;     --定义游标类型
       emp_cur1 emp_curtype;               -- 声明游标类型的变量
       emp_cur2 emp_curtype;  
       emp_row emp%rowtype;               -- 定义保存游标数据的记录类型
    begin
       open emp_cur1 for select * from emp where deptno=20; -- 打开第一个游标
       fetch emp_cur1 into emp_row;      -- 提取并显示游标信息
       dbms_output.put_line('员工名称:'||emp_row.ename||'部门编号:'||emp_row.deptno);
       fetch emp_cur2 into emp_row;      -- 提取第二个游标变量讲引发异常
       exception
       when invalid_cursor then          -- 异常处理
       emp_cur2:emp_cur1;                -- 将emp_cur1 指向的查询区域赋给emp_cur2
       fetch emp_cur2 into emp_row;      -- 现在emp_cur1 与emp_cur2 指向相同的查询
       dbms_output.put_line('员工名称:'emp_row.ename||'部门编号'||emp_row.deptno);  -- 重新打开emp_cur2 游标变量,利用相同的查询区域
       open emp_cur2 for select * from emp where deptno=30;
       fetch emp_cur1 into emp_row;-- 由于emp_cur1与emp_cur2 共享相同的查询区域,因此结果相同
       dbms_output.put_line('员工名称:'||emp_row.ename||'部门编号:'||emp_row.deptno);
    end;
    26 处理rowtype_mismatch 异常
    declare
       type emp_curtype is ref cursor;    -- 定义游标类型吧
       emp_cur emp_curtype;               -- 声明游标类型的变量
       rmp_row emp%rowtype;               -- 声明游标数据结果类型
       dept_row dept%rowtype; 
    begin
       open emp_cur for select * from emp where deptno=20; -- 打开游标变量
       fetch emp_cur into dept_row;                        -- 提取到一个不匹配的类型中
    exception
       when rowtype_mismatch then                          -- 处理rowtype_mismatch异常
       fetch emp_cur into emp_row;                         -- 再次提取游标变量数据,输出结果
       dbms-output.put_line('员工名称:'||emp_row.ename||emp_ename||'部门编号'||emp_row.deptno);
       
    end;
    27 使用sys_refcursor类型
    declare
       emp_cur sys_refcursor;    --定义弱类型游标变量
       emp_row emp%rowtype;
       dept_row dept%rowtype;
    begin
       open emp_cur for select * from emp where deptno=20; -- 打开游标数据
       fetch emp_cur into dept_row;
       exception
          when rowtype_mismatch then   --处理rowtype_mismatch 异常
          fetch emp_cur into emp_row; -- 重新提取并输出异常结果
          dbms_output.put_line('员工名称:'||emp_row.ename||'部门编号'||emp_row.deptno);
    end;
    28.在包中使用游标变量
    -- 创建包规范
    create or replace package emp_data_action as
       type emp_type is ref cursor return emp%rowtype;  --定义强类型游标类型
       --定义使用游标变量的子程序
       procedure getempbydeptno(emp_cur in out emp_type,p_deptno number);
     end emp_data_action;
     -- 实现包体
     create or replace package body emp_data_action as
     -- 创建在包规范定义的过程
     procedure getempbydeptno(emp_cur in out emp_type,p_deptno number) is emp_row emp%rowtype;
     begin
           open emp_cur for select * from emp where deptno=p_deptno;
           loop
              fetch emp_cur into emp_row;   --提取数据
              exit when emp_cur%notfound;
              --输出游标数据
              dbms_output.put_line('员工名称:'||emp_row.ename||'部门编号:'||emp_row.deptno);
           end loop;
           ccose emp_cur;
         end;
    end emp-data_action;
  • 相关阅读:
    [sql]在case语句中不同情况下then的数据的数据类型不一致ORA-00932: inconsistent datatypes: expected NUMBER got CHAR
    环境迁移 小记
    linux下安装oracle遇到的问题
    正向代理与反向代理
    文件夹与SVN脱离关系
    shell 脚本中$$,$#,$?
    在MySQL中单列索引和联合索引的区别
    Java中Map、HashMap、LinkedHashMap、TreeMap的区别
    Error、Exception与RuntimeException的区别
    设计模式--单例模式
  • 原文地址:https://www.cnblogs.com/etllearn/p/15147737.html
Copyright © 2020-2023  润新知