oracle cursor使用的几个实例（笔记）

CREATE OR REPLACE PROCEDURE p_c_csp_update_staffnoteva
is
   
            cur    SYS_REFCURSOR;
            v_receivers t_msg_noteinfo.receivers%type;
            v_receiver t_msg_notereceive.receiver%type default '';
            v_NoteId t_msg_notereceive.noteid%type;
            v_sendtime   DATE;


BEGIN


   update   t_c_sqm_staffnoteva t set  t.iseva='1',t.removereason='免检到期' where t.endday <sysdate and t.iseva='0' ;
  
   commit;
   update t_c_sqm_staffnoteva t  set  t.iseva='1',t.endday=sysdate,t.removereason='服务质量差错'
    where t.iseva='0' and t.staffid in(select t1.staffid from v_c_sqm_newworksituation t1 where
     t1.countday>sysdate-2  and(t1.error>0 or t1.bigerror>0 or t1.morebigerror>0 
      or t1.declarationofservice>0 or t1.badreviewproblem1>0 ));
      commit;
      
      OPEN cur FOR
      SELECT T.STAFFID
        FROM t_c_sqm_staffnoteva T
       WHERE T.iseva='1' and t.removereason='服务质量差错' and t.endday>sysdate-1/24;
       
        p_generate_sequence('',6,'0',v_NoteId);
        v_sendtime:=sysdate;
        
       LOOP
       FETCH cur
        INTO v_receiver;
        exit when cur%NOTFOUND;
        
        
        insert into t_msg_notereceive (HOSTEDCCID, NOTEID, RECEIVER, READFLAG, 
        STATUS, TITLE, SENDTIME, SENDER, GRADE, AFFIXFLAG, ANONYMOUSFLAG, READTIME,
              AFFIXPATH, PARTIONID, TYPEID, TYPENAME, ISSCROLL, ISPOPUP, NEWAFFIXPATH, 
               ISPOP, POPVALIDDATE, ISSHOWCOUNT, ISFLOAT, ISSATISFY)
               values ('', v_NoteId, v_receiver, 'N', '7', 'ceshi123', 
               v_sendtime, '9800139', '1', 
               'N', 'N', null, '', '68', '203', '团队建设', 'N', 'N', '', 'N', null, 'N', 'N', '');
        
        if v_receivers is null
        then 
            v_receivers:=v_receiver;
        else 
                v_receivers:=v_receivers||','||v_receiver;
        end if;
        
              
        end loop;
        
if v_receivers!=''
then
        
        insert into t_msg_noteinfo (HOSTEDCCID, NOTEID, TITLE, CONTENT, 
SENDTIME, STATUS, GRADE, RETURNFLAG, ISEMAIL, EMAILADDRESS, ANONYMOUSFLAG, SENDMODULE,
 AFFIXPATH, SENDER, RECEIVERS, OTHERID, UPID, ORIGINID, DELETETIME, TYPEID,
  TYPENAME, ISSCROLL, ISPOPUP, NEWAFFIXPATH, ISPOP, POPVALIDDATE, ISSHOWCOUNT, ISFLOAT)
values ('', v_NoteId, 'ceshi123',
 '您在' ||to_char(v_sendtime,'mm')|| '月' || to_char(v_sendtime,'dd')|| '日，出现话务录音质评差错（或服务申告），即将退出免检名单，若有疑义，可在5个工作日内提出申诉，逾期未申诉视为无疑义', 
v_sendtime, '2', '1', '',
 'N', '', 'N', 'memo', '', '9800139', v_receivers, '', '', '', 
 null, '203', '团队建设', 'N', 'N', '', 'N', null, 'N', 'N');
      
commit;

end if;


 EXCEPTION  
 WHEN OTHERS THEN
  
  ROLLBACK;



    
END p_c_csp_update_staffnoteva;

declare 
  TYPE curname is ref CURSOR  --return test_yf%rowtype
    ;
  i number:=0 ;
  openflag varchar2(20);
  
  cur curname;
  cursor cur1(tid in number)  is select id,name from test_yf where id=tid or id=i;
  rowty test_yf%rowtype;
  rowtyp cur1%rowtype;
  
begin


-- cur begin


--loop 1
open cur for select * from test_yf where id <0;
  loop 
  fetch cur into rowty ;
  exit when cur%notfound;
  
  dbms_output.put_line(cur%rowcount||':loop1  '||rowty.id);
   
end loop;


if cur%isopen then
openflag:='true';
else openflag:='false';
end if;
dbms_output.put_line('cur openstatus='||openflag||'  '||cur%rowcount);


 if(cur is not null  and cur%isopen) then
   close cur;
   end if;
   
   
 
 --loop 2  
 open cur for select * from test_yf where id <0;  
 for i in 1..3 loop 
 fetch cur into rowty ;
 dbms_output.put_line(cur%rowcount||':loop2  '||rowty.id  );
 end loop;
 dbms_output.put_line(cur%rowcount);--return value is relate to count of effective fetch operation
  close cur;
   
 --cur end
  
  
 --cur1 begin
 
-- loop 3
 
 
 for auto_record in cur1(5) loop
 
 dbms_output.put_line('loop3  '||auto_record.id||'  '||auto_record.name);
 end loop;
--this kind of loop will open,fetch and  close cursor automatically,
--and auto define 'auto_record' as type of cur1%rowtype ,
-- should not call 'close cursor'  after loop; 








-- normal fetch
    open cur1(5);
  
  fetch cur1 into rowtyp;
   dbms_output.put_line(rowtyp.id||'  '||rowtyp.name);
  
if cur1%isopen then
openflag:='true';
else openflag:='false';
end if;
dbms_output.put_line('cur1 openstatus='||openflag||'  '||cur1%rowcount);


 if( cur1%isopen)then 
 close cur1;
 end if;
  --cur1 end
 
 
 --exception
  exception
    when others then
    null;
    if(cur is not null and cur%isopen) 
    then close cur; end if;
    
    if( cur1%isopen)then 
     close cur1;
       end if;
       
    dbms_output.put_line(SQLCODE());
    dbms_output.put_line(sqlerrm());
  
end;

declare   
cursor cur(tid  in number) --return test_yf%rowtype 
is  select * from test_yf where id=tid ;


rowty test_yf%rowtype;

begin
open cur(111);
loop 
fetch cur into rowty;
exit when cur%notfound;
dbms_output.put_line(rowty.id||'  '||rowty.name);
end loop;
close cur;




open cur(3);
loop 
fetch cur into rowty;
exit when cur%notfound;
dbms_output.put_line(rowty.id||'  '||rowty.name);
end loop;
close cur;




open cur(-2);
loop 
fetch cur into rowty;
exit when cur%notfound;
dbms_output.put_line(rowty.id||'  '||rowty.name);
end loop;
close cur;




exception  
    when others then  
    null;  
   
    dbms_output.put_line(SQLCODE());  
    dbms_output.put_line(sqlerrm());  
    
end;  

-----'DML'  and  'select .. into.. from ...' operation always create an implicit cursor by DB，
----use keyword  'sql' to manipulate current implicit cursor; 

declare 
  -- Local variables here
  i integer;
  foundflag varchar2(20);
  openflag varchar2(20);
  
begin
 update test_yf set name='ccc' where id>0;
 if sql%found then
 foundflag:='true';
 else foundflag:='false';
 end if;
 
 if sql%isopen then 
 openflag:='true';
 else openflag:='false';
 end if;
 
 dbms_output.put_line(sql%rowcount||'  '||foundflag||'  '||openflag);--n  true  false

 commit;
 
  if sql%found then
 foundflag:='true';
 else foundflag:='false';
 end if;
 
 if sql%isopen then 
 openflag:='true';
 else openflag:='false';
 end if;
 
dbms_output.put_line(sql%rowcount||'  '||foundflag||'  '||openflag);--0 false false
end;

1. implicit游标的属性 返回值类型   意    义  
2. SQL%ROWCOUNT    整型  代表DML语句成功执行的数据行数  
3. SQL%FOUND   布尔型 值为TRUE代表插入、删除、更新或单行查询操作成功  
4. SQL%NOTFOUND    布尔型 与SQL%FOUND属性返回值相反  
5. SQL%ISOPEN  布尔型 DML执行过程中为真，结束后为假  

1. explicit游标的属性   返回值类型   意    义  
2. %ROWCOUNT   整型  获得FETCH语句返回的数据行数  
3. %FOUND  布尔型 最近的FETCH语句返回一行数据则为真，否则为假  
4. %NOTFOUND   布尔型 与%FOUND属性返回值相反  
5. %ISOPEN 布尔型 游标已经打开时值为真，否则为假  

oracle中预定义的异常 
Oracle的系统异常很多，但只有一部分常见错误在标准包中予以定义。定义的错误可以在EXCEPTION部分通过标准的错误名来进行判断，并进行异常处理。常见的系统预定义异常如下所示。 

Sql代码  

1. 错 误 名 称 错误代码    错 误 含 义  
2. CURSOR_ALREADY_OPEN ORA_06511   试图打开已经打开的游标  
3. INVALID_CURSOR  ORA_01001   试图使用没有打开的游标  
4. DUP_VAL_ON_INDEX    ORA_00001   保存重复值到惟一索引约束的列中  
5. ZERO_DIVIDE ORA_01476   发生除数为零的除法错误  
6. INVALID_NUMBER  ORA_01722   试图对无效字符进行数值转换  
7. ROWTYPE_MISMATCH    ORA_06504   主变量和游标的类型不兼容  
8. VALUE_ERROR ORA_06502   转换、截断或算术运算发生错误  
9. TOO_MANY_ROWS   ORA_01422   SELECT…INTO…语句返回多于一行的数据  
10. NO_DATA_FOUND   ORA_01403   SELECT…INTO…语句没有数据返回  
11. TIMEOUT_ON_RESOURCE ORA_00051   等待资源时发生超时错误  
12. TRANSACTION_BACKED_OUT  ORA_00060   由于死锁，提交失败  
13. STORAGE_ERROR   ORA_06500   发生内存错误  
14. PROGRAM_ERROR   ORA_06501   发生PL/SQL内部错误  
15. NOT_LOGGED_ON   ORA_01012   试图操作未连接的数据库  
16. LOGIN_DENIED    ORA_01017   在连接时提供了无效用户名或口令  

-- dynamic query and dynamic cursor
declare 
  -- Local variables here
  type mycurtype is ref cursor;
  i integer;
  mycur mycurtype;
  str varchar2(200);
  trecord test_yf%rowtype;
  letter char default 'A';
  flag boolean;
  val varchar2(20);
  
  --user defined exception
  not_null_constraint_violation exception;
  pragma exception_init(not_null_constraint_violation,-1400);
  divisor_zero_error exception;
  pragma exception_init(divisor_zero_error,-1476);
  user_defined_exception exception;
  
begin
--dynamic query
 i:=1;
 str:='select * from test_yf where id='||i;
 execute  immediate str into trecord;
 dbms_output.put_line(trecord.id||'  '||trecord.name);
 
 
 --dynamic cursor 1
  i:=3;
  open mycur for 'select * from test_yf where id='||i;
  fetch mycur into trecord;
  close   mycur;
  dbms_output.put_line(trecord.id||'  '||trecord.name);
 
 
 --dynamic cursor 2
 
 while (letter>='z')loop
 str:='select * from test_yf where name like ''%'||letter||'%''';
 open mycur for str;

 dbms_output.put_line(letter||' :');
  loop 
  fetch mycur into trecord;
  exit when (mycur%notfound);
  dbms_output.put_line(mycur%rowcount||'= '||trecord.id||' '||trecord.name);
 end loop;
 
 close mycur;

 
 if(letter='Z') then 
 letter:=chr(ascii(letter)+7);
 else
  letter:=chr(ascii(letter)+1);
  end if;
 end loop;
 
 
 -----make exceptions
 
 RAISE_APPLICATION_ERROR(-20001,'an exception was throwed by raise_application_error');
 
 raise zero_divide;
 raise not_null_constraint_violation;
 raise NO_DATA_FOUND;
 
insert into test_yf(when) values(null);--not_null_constraint_violation
 
 i:=i/0;--divisor_zero_error
 
 select name into val from test_yf where id=11;
 dbms_output.put_line(val);--NO_DATA_FOUND
 
 ----------------------
 
 exception 
 
 when NO_DATA_FOUND  then 
  dbms_output.put_line('data not found'); 
  DBMS_OUTPUT.PUT('errcode：'|| SQLCODE( )||'  ');  
 dbms_output.put_line(sqlerrm());
 
  when not_null_constraint_violation then 
  dbms_output.put_line('not_null_constraint_violation');
   DBMS_OUTPUT.PUT('errcode：'|| SQLCODE( )||'  ');  
 dbms_output.put_line(sqlerrm());
 
 when divisor_zero_error then
  dbms_output.put_line('divisor_zero_error'); 
  DBMS_OUTPUT.PUT('errcode：'|| SQLCODE( )||'  ');  
 dbms_output.put_line(sqlerrm());
 
 when user_defined_exception then
  dbms_output.put_line('user_defined_exception'); 
  DBMS_OUTPUT.PUT('errcode：'|| SQLCODE( )||'  ');  
 dbms_output.put_line(sqlerrm());
 
   when others then
   dbms_output.put_line('others exception'); 
 DBMS_OUTPUT.PUT('errcode：'|| SQLCODE( )||'  ');  
 dbms_output.put_line(sqlerrm());

 
end;