How to return query results between specified rows?

zhaozj2021-02-11  167

How to return query results between specified rows?

How to return the results of the query between the specified row, to implement the web record paging, there are many ways in Oracle, here only list 4, I hope everyone can help everyone, you can choose the following Script according to different needs.

1) SELECT ... WHERE ROWNUM <50 Minus Select ... WHERE ROWNUM <30

This method is affected by the MINUS operator, so speed will be affected.

2)

Select Results. * From FROM

(SELECT T2. *, ROWNUM ROWNUMBER FROM

(Select T. * From mv_table t where order by col1) T2) Results

WHERE RESULTS.ROWNUMBER BETWEEN 30 And 50 ORDER BY Col1

This method is seen from a forum, no personally tested

3)

Define Cursor X, 2.Fetch X A, B, C; Loop ... end loop;

Among them, two loop variables and a FLAG variable, respectively, the current record number, which belongs to the first page, and the first page.

PS;

J: = TO_NUMBER (Kafyf);

i: = 1;

Open CX;

LOOP FETCH CX INTO COL1, COL2, COL3, COL4, COL5, COL6;

IF CX% Notfound Then EXIT; END IF;

IF i> = j Then

htp.tableerowopen;

Htp.tableData (col1);

htp.tabledata (col2);

htp.tabledata (col4);

htp.tabledata (col5);

Htp.tableData (col6);

htp.tabledata (col3);

htp.tablerowclose;

i: = i 1;

IF i = J 10 THEN L: = 1; EXIT; END IF;

ELSE I: = i 1;

END IF;

End loop;

Close x;

The method is Script written by netizens named '淼', he uses Owa_Util Package in Oracle Web2kit.

4) How Can One Page Forward and Backwards THROUGH A TABLE?

Externalize Rownum by Implementing Queries Like this:

SELECT ...

From (SELECT ROWNUM RNUM, ... from ...)

WHERE RNUM BETWEEN: low and: high and rownum <(: high: low 1);

WHERE: Low and: High Are Dynamically Generated Values ​​Depending On Which Result Page The User

Is Viewing. Typically, They Are Used To Show "Next 15 Matches", "Previous 15 Matches" LINKS AT THE

Bottom of Each Page.

转载请注明原文地址:https://www.9cbs.com/read-5659.html

New Post(0)