plsql存储过程，分页查询

学习记录 

  procedure PQ_PS_OrderList
  (
   I_PageSize       In Number, --每页记录数
   I_PageNO         In Number, --当前页数
   O_OutRecordCount Out Number, --记录总数
   O_OutCursor      Out t_cursor, --返回游标
   O_Result         Out Number,
   O_Message        Out Varchar2 
  )
  is
  v_sql       Varchar2(2000) := '';
  v_HeiRownum Number;
  v_LowRownum Number;

  begin
    O_Result  := 0;
    O_Message := '查询成功';
  
   v_sql:='select ACCOUNT_ID,USER_ID,DEVICE_ID from 
           USER_DRAW_ALIPAY where STATUS=0';
   --记录总数
   execute immediate 'select count(1) from ('||v_sql||')' into O_OutRecordCount;
   	
   --执行分页查询
   v_HeiRownum:=I_PageNO * I_PageSize;
   v_LowRownum:=v_HeiRownum - I_PageSize+1;
   v_sql:='select ACCOUNT_ID,USER_ID,DEVICE_ID,rn
                  from(
                  select ACCOUNT_ID,USER_ID,DEVICE_ID,rownum rn 
                  from('||v_sql||') A 
                  where rownum<='||
                  to_char(v_HeiRownum) ||'
                  ) B
                  where rn >='|| to_char(v_LowRownum);             
   open O_OutCursor for v_sql;
   
   commit;
   return;
                             
    exception
    when others then
      rollback;
      O_Result  := -9;
      O_Message := '获取失败 错误编码：' || sqlcode || '  错误信息 ：' || sqlerrm;
      return;

  end PQ_PS_OrderList;

  
end P_USER_DRAW_ALIPAY;