Oracle Cursor 游标总结（简单样例附带表结构）

Oracle Cursor 游标总结

一，隐式游标
隐式游标的属性 返回值类型 意 义
SQL%ROWCOUNT 整型 代表DML语句成功执行的数据行数
SQL%FOUND 布尔型 值为TRUE代表插入、删除、更新或单行查询操作成功
SQL%NOTFOUND 布尔型 与SQL%FOUND属性返回值相反
SQL%ISOPEN 布尔型 DML执行过程中为真，结束后为假
二，显示游标
游标的属性 返回值类型 意义
%ROWCOUNT 整型 获得FETCH语句返回的数据行数
%FOUND 布尔型 最近的FETCH语句返回一行数据则为真，否则为假
%NOTFOUND 布尔型 与%FOUND属性返回值相反
%ISOPEN 布尔型 游标已经打开时值为真，否则为假

三，测试表

create table t_raynor(
       id number(5),
       name varchar(20),
       age number(5)
       
);
insert into t_raynor values(1,'ray',22);
insert into t_raynor values(2,'sha',22);
insert into t_raynor values(3,'ray',null);
insert into t_raynor values(4,'sha',null);
insert into t_raynor values(5,'ray',null);

隐式游标

declare
 cot number ;
begin
       update t_raynor t set t.age = '' where t.id in (3,4,5) and t.age is not null;
       cot := sql%rowcount;
       dbms_output.put_line('num:'||cot);     
end;

显示游标

–显示游标(提取ID=1 的数据)

 DECLARE
   cid number;
   CURSOR my_cursor is
     select id from t_raynor where id = 1;
 BEGIN
   open my_cursor;--打开
   fetch my_cursor INTO cid;--获取数据
   dbms_output.put_line('num:' || cid);
   close my_cursor; --关闭
 END;

–显示游标(提取ID=1,2,3 的数据)

 DECLARE
   cid number;
   CURSOR my_cursor is
     select id from t_raynor where id in(1,2,3);
 BEGIN
   open my_cursor;--打开
   for i in 1..3 loop
     fetch my_cursor INTO cid;--获取数据
     dbms_output.put_line('id:' || cid);
   end loop;
   close my_cursor; --关闭
 END;

–显示游标(提取ID=1,2,3 的数据)

DECLARE
   CURSOR my_cursor IS
     select id from t_raynor where id in(1,2,3);
 BEGIN
   FOR aa IN my_cursor LOOP
      dbms_output.put_line('id:' || aa.id);  
  END LOOP;
 END;

–显示游标(提取ID=1,2,3 的数据) :快速游标

DECLARE
   
 BEGIN
   FOR aa IN (select id from t_raynor where id in(1,2,3)) LOOP
      dbms_output.put_line('id:' || aa.id);         
   END LOOP;
 END;

–显示游标的属性 1

 DECLARE
       CURSOR my_cursor IS
         select id from t_raynor where id in(1,2,3);
     BEGIN
       open my_cursor;
       if my_cursor%isopen THEN
         dbms_output.put_line('OPEN' );
       end if;  
     END;

– 显示游标的属性 2

 DECLARE
   v_id number;
   CURSOR my_cursor(p_id number) IS
     select id from t_raynor where id in(p_id,2,3);--定义游标的参数可以作为SELECT语句的条件
 BEGIN
   open my_cursor(1);--OPEN的时候需要 写入参数值
   LOOP
     FETCH my_cursor INTO v_id;
     EXIT WHEN  my_cursor%NOTFOUND;--当游标循环完成 退出
     DBMS_OUTPUT.put_line(v_id);
   END LOOP;  
 END;

– 显示游标的属性 2

 DECLARE
   v_id number :=1;--通过变量给游标传值
   CURSOR my_cursor IS
     select id from t_raynor where id in(v_id,2,3);
 BEGIN
   open my_cursor;
   LOOP
     FETCH my_cursor INTO v_id;
     EXIT WHEN  my_cursor%NOTFOUND;
     DBMS_OUTPUT.put_line(v_id);
   END LOOP;  
 END;

–动态SEL

 DECLARE
   v_id number;
   str varchar2(200);  
 BEGIN
   str:='select id from t_raynor where id =1';
   execute immediate str into v_id;--SELECT...INTO...语句存放在STR字符串中，通过EXECUTE语句执行。
   dbms_output.put_line(v_id);  
 END; 

–

 DECLARE
   TYPE cur_type  is ref cursor;
   my_cur cur_type;
   my_rec t_raynor%rowtype;
   str varchar2(200);
   letter char:= 'a'; 
 BEGIN   
   loop
       str:='select name from t_raynor where name like ''%'||letter||'%'' ';
       open my_cur for str;
       dbms_output.put_line('包含字母'||letter||'的名字：');   
       
       loop
            fetch my_cur into my_rec.name;  
            exit when my_cur%notfound;  
            dbms_output.put_line(my_rec.name);  
       end loop;
       
       exit when letter='z';   
       letter:=chr(ascii(letter)+1);   
   end loop;  
 END; 

–复杂更新

 declare
           cot number ;
           scot number;
    begin
      scot := 0;
      for aa in (SELECT t.age, t.name FROM t_raynor t where t.age is not null) loop
         
        update t_raynor tr
           set tr.age = aa.age
         where tr.name = aa.name
           and tr.age is null;
           cot := sql%rowcount;
           dbms_output.put_line('cot:'||cot);
           scot := scot + cot;
            
      end loop;
          dbms_output.put_line('影响的总行数:'||scot);
    end;

declare
begin
  
update t_raynor t
   set t.age =
       (select t1.age
          from t_raynor t1
         where t1.name = t.name
         and t1.age is not null
           and rownum = 1)
 where exists (SELECT 1 from t_raynor t2 where t2.name = t.name);
  dbms_output.put_line('影响的总行数:'||sql%rowcount);

end;

-------------------- ref cursor

DECLARE
  rz1 sys_refcursor;
  ray t_raynor%rowtype; 
BEGIN
  open rz1 for
    SELECT * FROM t_raynor;     
  fetch rz1
    into ray;
  loop
    dbms_output.put_line(ray.id);
    fetch rz1
      into ray;
    exit when rz1%notfound;
  end loop;
END;

快速游标

DECLARE

BEGIN
 for  aa  in(
    SELECT t.id FROM  t_raynor t where t.id ='1') 
  loop
    insert into t_raynor values(aa.id);  
  end loop;
END;