社区首页 >专栏 >PLSQL-游标

PLSQL-游标

字母哥博客

发布于 2020-09-23 03:17:51

91800

代码可运行

文章被收录于专栏：写字母的代码哥写字母的代码哥

运行总次数：0

代码可运行

游标（Cursor）：用来查询数据库，获取记录集合（结果集）的指针，可以让开发者一次访问一行结果集，在每条结果集上作操作。

游标可分为： 1.静态游标：分为显式（explicit）游标和隐式（implicit）游标。 2.REF游标(动态游标)：是一种引用类型，类似于指针。
显式和隐式游标的区别：尽量使用隐式游标，避免编写附加的游标控制代码（声明，打开，获取，关闭），也不需要声明变量来保存从游标中获取的数据。
REF游标和静态游标的区别 1）静态游标不能返回到客户端。ref游标能够被返回到客户端，是从Oracle的存储过程返回结果集的方式。 2）不能在包说明或包体中的过程或函数之外定义ref游标。只能在定义ref游标的过程中处理它，或返回到客户端应用程序。 3）ref游标可以从子例程传递到子例程，而游标则不能。为了共享静态游标，必须在包说明或包体中把它定义为全局游标。 4）使用静态游标--通过静态SQL（但不用ref游标）--比使用ref游标效率高，

静态游标

显式游标

显式游标的使用方法：第一步：声明游标第二步：打开游标第三步：使用游标进行循环操作第四步：关闭游标

create or replace procedure TEST is
  cursor c1(inname in varchar2) is
    select tname from tab where tname like inname;
  pname varchar2(32);
begin
  open c1('T%');
  loop
    fetch c1 into pname;
    exit when c1%notfound;
    dbms_output.put_line(pname);
  end loop;
  close c1;
end TEST;

DML隐式游标

在PL/SQL中使用DML语言，使用ORACLE提供的名为“SQL”的隐示游标。

declare
begin
  update departments set department_name = department_name;
  dbms_output.put_line('update ' || sql%rowcount || ' records');
end;

CURSOR FOR IN LOOP隐式游标

//例子1：无参数，使用循环，无须打开关闭游标
create or replace procedure TEST is
  cursor c1 is select tname from tab;
begin
  for rr in c1 loop
    dbms_output.put_line(rr.tname);
  end loop;
end TEST;
//例子2：有参数，使用循环，无须打开关闭游标
create or replace procedure TEST is
    cursor c1(inname in varchar2) is select tname from tab where tname like inname;
begin
  for rr in c1('T%') loop
    dbms_output.put_line(rr.tname);
  end loop;
end TEST;

游标的常用属性

%FOUND：变量最后从游标中获取记录的时候，在结果集中找到了记录。
%NOTFOUND：变量最后从游标中获取记录的时候，在结果集中没有找到记录。
%ROWCOUNT：当前时刻已经从游标中获取的记录数量。
%ISOPEN：是否打开。

declare  /* /* 定义静态游标 */ */
  Cursor emps is
    Select * from employees where rownum < 6 order by 1;
  emp employees%rowtype;
  row number := 1;
begin
  Open emps; /* ´打开静态游标 */
  fetch emps into emp; /*  读取游标当前行  */
  loop
    if emps%found then
      dbms_output.put_line('Looping over record ' || row || ' of ' || emps%rowcount);
      fetch emps into emp;
      row := row + 1;
    elsif emps%notfound then
      exit;
    end if;
  End loop;

  If emps%isopen then
    close emps; /*  关闭游标  */
  End if;
End;

游标的更新与删除

UPDATE或DELETE语句中的WHERE CURRENT OF子串专门处理要执行UPDATE或DELETE操作的表中取出的最近的数据。要使用这个方法，在声明游标时必须使用FOR UPDATE子串.
当对话使用FOR UPDATE子串打开一个游标时，所有返回集中的数据行都将处于行级（ROW-LEVEL）独占式锁定，其他对象只能查询这些数据行，不能进行UPDATE、DELETE或SELECT...FOR UPDATE操作。
在多表查询中，使用OF子句来锁定特定的表,如果忽略了OF子句，那么所有表中选择的数据行都将被锁定。如果这些数据行已经被其他会话锁定，那么正常情况下ORACLE将等待，直到数据行解锁。
在UPDATE和DELETE中使用WHERE CURRENT OF子串的语法如下： WHERE{CURRENT OF cursor_name|search_condition}

create or replace procedure pc_SetVersionValid(PFlowsID in integer) is
  Cursor c1 is
    select * from wf_flows
    where flowname in
           (select flowname from wf_flows where flowsid = PFlowsID)
    for update;

  r c1%rowtype;
  v integer;
begin
  open c1;
  fetch c1 into r;
  while c1%found loop
    if r.flowsid = PFlowsID then
      v := 1;
    else
      v := 0;
    end if;
    UPDATE wf_flows SET isValid = v WHERE CURRENT OF c1;
    fetch c1 into r;
  end loop;
  close c1;
  commit;
end;

引用游标ref cursor

弱类型引用游标，就是不指定游标将要提取的数据行的类型

declare
       type my_cur_type is ref cursor;
       mycur my_cur_type;--声明引用游标变量

       which varchar2(10);
       deptrow dept%rowtype;
       emprow emp%rowtype;
begin

     which := '&请选择dept还是emp';
     if (which = 'dept') then
        open mycur for select * from dept;      --引用游标可以动态sql
        loop
            fetch mycur into deptrow;
            exit when (mycur%notfound);
            dbms_output.put_line(deptrow.deptno || '  ' || deptrow.dname);
        end loop;
     elsif (which = 'emp') then
        open mycur for select * from emp;       --引用游标可以动态sql
        loop
            fetch mycur into emprow;
            exit when (mycur%notfound);
            dbms_output.put_line(emprow.empno || '  ' || emprow.ename);
        end loop;

     end if;

     close mycur;
end;

强类型引用游标

就是指定游标将要提取的数据行的类型，只能是record或%rowtype类型.比如：return number是错的，return emp.ename%type也是错的

declare
       type mycurtype is ref cursor return emp%rowtype;
       mycur mycurtype;--声明变量

       emprow emp%rowtype;
begin
        open mycur for select * from emp;
        loop
            fetch mycur into emprow;
            exit when mycur%notfound;
            dbms_output.put_line(emprow.empno || '  ' || emprow.ename);
        end loop;

        close mycur;
end;