学习记录
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;