存储过程显示行数、%rowcount，显示sql语句，提前定义好，然后输出变量

Posted 2023-03-07 by wukaiqiang; tagged with none

declare   
    ---define Cursor 
    cur_policy_sql  VARCHAR2(32767) := 'select * from  (select object_id,object_name 
     from t1111 order by object_id )  
     where rownum < 10' ;
    Cursor cur_policy is  
     select * from  (select object_id,object_name 
     from t1111 order by object_id )  
     where rownum < 10 ;  
    curPolicyInfo cur_policy%rowtype;---定义游标变量  
Begin  
   Dbms_Output.put_line(cur_policy_sql);
   open cur_policy; ---open cursor  
   Loop   
     --deal with extraction data from DB  
     Fetch cur_policy into curPolicyInfo;  
     Exit when cur_policy%notfound;  
        
     Dbms_Output.put_line(curPolicyInfo.object_id); 
     Dbms_Output.put_line(cur_policy%ROWCOUNT); 
   end loop;  
   
   Exception   
     when others then  
         close cur_policy;  
         Dbms_Output.put_line(Sqlerrm);  
           
   if cur_policy%isopen then    
    --close cursor   
      close cur_policy;  
   end if;  
end;