oracle存储过程中进行分页
create or replace procedure APP_BUSSINESS_CARD_LIST(p_in_str in varchar2,p_out_str out varchar2) is /*******************************************************************************
out varchar2我本地oracle显示最长不能超过2000个字符 -- 函数名称 APP_BUSSINESS_CARD_LIST -- 功能描述 商通卡列表 -- 2019年8月7日10:40:09 ********************************************************************************/ -- Private exception declarations scanner_exception exception; pragma exception_init(scanner_exception, -20100); parser_exception exception; pragma exception_init(parser_exception, -20101); -- Private variable declarations
-- 变量定义最长长度为4000（和oracle版本有关系，可以用str :=rpad("*",4000,"*")；
DBMS_OUTPUT.put_line('str: ' ||length(str));
进行测试） type card_no_tab is table of varchar2(4000) index by BINARY_INTEGER; v_card_no_tab_info card_no_tab; v_single_info varchar2(4000);-- 存放整个分页list j_param json; iCount number; vv_count number; v_count number; v_user_id number; v_page number; v_page_size number; v_total_count number; v_total_page number; v_begin_rownum number; v_end_rownum number; jl_card json_list;-- 返回结果的list集合 j_return json; v_ret varchar2(2); v_des varchar2(32); begin -- 创建json对象 j_param := json(p_in_str); -- 获取用户ID v_user_id := json_ext.get_number(j_param, 'userId'); -- 获取页码 v_page := json_ext.get_number(j_param, 'page'); if (v_page is null) then p_out_str := '{"resultCode":"31", "resultMsg":"page类型错误"}'; return; end if; -- 校验页码数据是否有效(页码不能小于1) if (v_page < 1) then p_out_str := '{"resultCode":"36", "resultMsg":"page数据无效"}'; return; end if; -- 获取页大小 v_page_size := json_ext.get_number(j_param, 'pageSize'); if (v_page_size is null) then p_out_str := '{"resultCode":"31", "resultMsg":"pageSize类型错误"}'; return; end if; -- 校验页大小数据是否有效(页大小不能小于1并且不能大于7,可能会超出out长度) if (v_page_size < 1 or v_page_size > 7) then p_out_str := '{"resultCode":"38", "resultMsg":"pageSize数据无效"}'; return; end if; -- 校验用户ID是否存在 select count(1) into v_count from web_user_info where user_id = v_user_id; if (v_count <> 1) then p_out_str := '{"resultCode":"32", "resultMsg":"userId数据无效"}'; return; end if; -- 获取卡列表总数量 select count(1) into v_total_count from web_user_bussiness_card_view where userid = v_user_id; -- 计算总页数 if (mod(v_total_count, v_page_size) = 0) then v_total_page := trunc(v_total_count / v_page_size); else v_total_page := trunc(v_total_count / v_page_size) + 1; end if; -- 如果总页数是0, 则直接返回结果; 否则查询交易记录并返回结果. if (v_total_page = 0) then -- 返回的josn数据 j_return := json('{"resultCode":"00", "resultMsg":"成功"}'); j_return.put('page', v_page); j_return.put('pageSize', v_page_size); j_return.put('totalPage', v_total_page); j_return.put('totalCount', v_total_count); j_return.put('cardList', jl_card); -- 返回结果 p_out_str := j_return.to_char(false); return; end if; -- 如果当前页数大于总页数, 那么当前页数赋值为总页数. if (v_page > v_total_page) then v_page := v_total_page; end if; -- 计算开始行数和结束行数 v_begin_rownum := (v_page - 1) * v_page_size; v_end_rownum := v_begin_rownum + v_page_size; iCount := 0; vv_count := 0; for r in (select * from web_user_bussiness_card_view where userid = v_user_id order by ismain desc, bindId asc) loop -- 修改条数限制位置 iCount := iCount + 1; if iCount <= v_begin_rownum then --当不是我们需要的那页的数据，则取下一条数据 continue ; elsif iCount > v_end_rownum then exit; else null; end if; vv_count := vv_count + 1; v_card_no_tab_info(vv_count) := '{"cardNo":"' || r.cardNo || '","background":"' ||r.background || '","publicType":' || r.publicType || ',"cardAreaType":"' || r.cardAreaType || '","isInMainCard":' || r.isInMainCard || ',"isSetMainCard":"' || r.isSetMainCard || '","isTrade":"' || r.isTrade || '","isSend":"' || r.isSend || '","facePrice":"' || r.facePrice || '","isActive":"' || r.isActive || '","isMain":"' || r.isMain || '"}'; end loop; --组合jl_card -- "cardList":" v_single_info := '"cardList":['; for k in 1 .. vv_count loop if k < vv_count then v_single_info := v_single_info || v_card_no_tab_info(k) || ','; else v_single_info := v_single_info || v_card_no_tab_info(k); end if; end loop; v_single_info := v_single_info || ']'; v_ret :='00'; v_des :='成功'; DBMS_OUTPUT.put_line('v_single_info : ' || LENGTH(v_single_info)); p_out_str := '{"resultCode":"' || v_ret || '", "resultMsg":"' || v_des || '","page":"' || v_page || '","pageSize":"' || v_page_size || '","totalPage":"' ||v_total_page || '","totalCount":"' ||v_total_count || '",' || v_single_info || '}'; return; exception when scanner_exception then p_out_str := '{"resultCode":"94", "resultMsg":"Json扫描器异常"}'; when parser_exception then p_out_str := '{"resultCode":"95", "resultMsg":"Json解析器异常"}'; when others then DBMS_OUTPUT.put_line('sqlcode : ' ||sqlcode); DBMS_OUTPUT.put_line('sqlerrm : ' ||sqlerrm); p_out_str := '{"resultCode":"96", "resultMsg":"运行时异常"}'; end APP_BUSSINESS_CARD_LIST;
另外一种分页方法：
-- SQL语句
v_sqltext := 'select tradeMonth as "tradeMonth",
debitAmount as "debitAmount",
loanAmount as "loanAmount"
from (select d.*, rownum as rn
from (select to_char(tradedate, ''yyyy.mm'') as tradeMonth,
sum(debitamount) as debitAmount,
sum(loanamount) as loanAmount
from xsh_trade_view
where cardno = ''' || v_card_no || '''
group by to_char(tradedate, ''yyyy.mm'')
order by to_char(tradedate, ''yyyy.mm'') desc) d
where rownum <= ' || v_end_rownum || ')
where rn > ' || v_begin_rownum;
end if;
-- 执行SQL语句并返回josn_list数据
jl_trade := json_dyn.executeList(v_sqltext);
