贴个带分页的存储过程以备使用-优快云博客

本文链接：https://blog.youkuaiyun.com/wangyingda415/article/details/6552252

本文介绍了一个PL/SQL过程，用于从用户帖子表中获取主题列表，并实现分页功能。该过程接收用户ID、页面大小、当前页数等参数，并返回总记录数、总页数及查询结果集。通过对帖子表进行分页查询，可以有效地展示用户发布的帖子列表。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

--用户帖子表
PROCEDURE get_topic_list(userIdS   IN VARCHAR2,
                           pageSize IN INTEGER,
                           pageNum   IN INTEGER,
                           total     OUT INTEGER,
                           totalPage OUT INTEGER,
                           mycursor OUT SYS_REFCURSOR) IS
    minNum       INTEGER;
    maxNum       INTEGER;
    tem_pageSize INTEGER;
    tem_pageNum INTEGER;
BEGIN
    SELECT decode(pageSize, 0, 10, pageSize),
           decode(pageNum, 0, 1, pageNum)
      INTO tem_pageSize, tem_pageNum
      FROM dual;
    SELECT COUNT(*)
      INTO total
      FROM (SELECT T.TITLE, rownum RN
              FROM bbscs_forum t
             WHERE t.userid = userIdS
               AND t.parentid IS NULL);
    SELECT ceil(total / tem_pageSize) INTO totalPage FROM dual;
    minNum := (tem_pageNum - 1) * tem_pageSize;
    maxNum := minNum + tem_pageSize;
    OPEN mycursor FOR
      SELECT *
        FROM (SELECT T.Id idS,
                     t.title nameS,
                     t.click,
                     t.boardid,
                     t.posttime times,
                     rownum RN,
                     '' toUserNameS,
                     '' ToUserIdS,
                     '' ResonS,
                     '' ContentS,
                     '' FromUserIdS,
                     '' FromUserNameS
                FROM bbscs_forum t
               WHERE t.userid = userIdS
                 AND t.parentid IS NULL
               ORDER BY t.posttime DESC)
       WHERE rn > minNum
         AND rn <= maxNum;
END get_topic_list;