这个问题是我上一个问题的延续。
我有使用ORACLE数据库的jQuery表分页。
代码是这样的：
$cur_page = $page;
$page -= 1;
$per_page = 14;
$previous_btn = true;
$next_btn = true;
$first_btn = true;
$last_btn = true;
$start = $page * $per_page;
$query_pag_data = "
SELECT * FROM
(
SELECT x.*, ROWNUM as r FROM
(
SELECT P.PRODUCTION_STATUS, P.DATE_ADDED, P.FORM_NO, P.QTY_PLAN, ROWNUM, M.MODEL_NO, M.MODEL_NAME
FROM SEIAPPS_PRODUCTION_STATUS P, SEIAPPS_MODEL M
WHERE P.MODEL_NO = M.MODEL_NO
ORDER BY P.DATE_ADDED DESC, P.TIME
) x
)
WHERE r >= $start AND r <= $per_page
";
我每页限制是 14 行。第一页的结果还可以，没问题。但是当我尝试打开下一页时，它没有加载数据。而我在表中有更多的 20 行数据。
任何人请帮忙。
谢谢。
试试这样，
SELECT * FROM
(
SELECT x.* FROM
(
SELECT p.production_status, p.date_added, p.form_no, p.qty_plan, ROWNUM r, m.model_no, m.model_name
FROM seiapps_production_status p, seiapps_model m
WHERE p.model_no = m.model_no
ORDER BY p.date_added DESC, p.TIME
) x
)
WHERE r >= $start AND r <= $per_page
;
如果要获得包含页码和每页行的结果，请尝试以下过程。
CREATE OR REPLACE PROCEDURE pagination_proc(
i_page_num IN NUMBER,
i_lines_per_page IN NUMBER,
o_result OUT sys_refcursor)
AS
l_max_row NUMBER(4) := i_page_num * i_lines_per_page;
l_rec_count NUMBER(4);
BEGIN
IF i_page_num = 1 THEN
SELECT count(*)
INTO l_rec_count
FROM seiapps_production_status p, seiapps_model m
WHERE p.model_no = m.model_no;
END IF;
IF l_rec_count > 0 OR i_page_num >1 THEN
OPEN o_result FOR
SELECT production_status, date_added, form_no, qty_plan, model_no, model_name
FROM
(
SELECT production_status, date_added, form_no, qty_plan, model_no, model_name, ROWNUM rn
FROM
(
SELECT p.production_status, p.date_added, p.form_no, p.qty_plan, m.model_no, m.model_name
FROM seiapps_production_status p, seiapps_model m
WHERE p.model_no = m.model_no
)
WHERE ROWNUM <= l_max_row
)
WHERE rn > l_max_row - i_lines_per_page;
END IF;
END;