Paging / SQL Server / Oracle
.pbcode {font-size: 10pt;
Background-color: #eeeeee;
Margin: 10px 30px 10px 30px;
Padding: 10px 10px 10px 10px}
Although the DataGrid control belongs yourself with a paging processing mechanism, it is read in memory in accordance with all records that meet the query criteria, and then displayed. As the number of requirements, the number of operational efficiency will occur, or at least the utilization rate of resources is lowered.
The following code examples are subject to the following table structure:
Articles Table SQL Server Type Oracle Type PKIDINT Number (9) (inserted in Current Maximum) Authornvarchar (10) NVARCHAR2 (10) TitleNVarchar (50) NVARCHAR2 (50) PubtimedateTimeDate
In Microsoft products such as SQL Server / Access, we have two ideas in our usual custom paging:
One is represented by ASP.NET Forum, "Temporary Table" method: that is, a temporary table is created during the stored procedure, which contains a serial number field (1, 2, 3, ....) and table Primary key (other fields that can uniquely determine a line record) field. The stored procedure may be as follows: (No. SS1)
Create Procedure getAllarticles_paged
(
@PageIndex Int,
@PageSize Int,
@TotalRecords out,
@Totalpages Out Int
)
AS
Declare @PagelowerBound Int
Declare @PAGEUPPERBOUND INT
- set the page bounds
Set @PagelowerBound = @PageSize * @PageIndex
Set @PageUpperBound = @pagelowerbound @PageSize 1
- Create a Temp Table to Store The Select Results
Create Table #TMP
(
Recno int ITENTITY (1, 1) Not null,
ArticleID Int
)
INSERT INTO #TMP
SELECT [ID]
From articles
Order by Pubtime DESC
SELECT A. *
From articles a (nolock), #tmp t
Where a.id = t.ArticleID and
T.Recno> @pagelowerbound and
T.Recno <@PageUpperBound
Order by T.Recno
Go
Another possibility more suitable for "Patchwork" SQL statement in the program: use two TOP commands to get what we want, for example: (number SS2)
SELECT * FROM
(
SELECT TOP
PageSize * from from
(
SELECT TOP
PageSize *
PageIndex) *
From articles
Order by Pubtime DESC
)
ORDER by Pubtime ASC
)
Order by Pubtime DESC
This idea is "hunch to tail", there are many ways to page, here is not listed. For Oracle databases, there are several different severity to prevent the implementation of the above methods. For example, Oracle does not support TOP keywords: but this seems not very serious, because it provides this implicit cursor, which can be similar to TOP The function, such as:
SELECT TOP 10 ... from where ...
Have to write
Select ... from ... where ... and rownum <= 10
Rownum is a record serial number (1, 2, 3 ...), but there is a more troublesome thing: if there is ordery by ... in the SQL statement, ROWNUM is actually first "labeled" first "label"! In this way, this serial number does not use the needs of usage if it is not handled.
As for the temporary table, Oracle's temporary table and SQL Server have a great difference, I haven't worried about this thing yet, I don't add it.
Introducing Oracle Pieces in Domestic Website, I found a FAQ of a foreign site (www.faqts.com), according to the introduction of this article, can be paging as follows: (No. ORA1)
SELECT * FROM
(
SELECT A. *, ROWNUM R
From
(
Select * from articles Order by Pubtime DESC
) A
WHERE ROWNUM <= PageUpperBound
) B
WHERE R> PagelowerBound;
The blue part can be changed to any, the required SQL SELECT statement, this is very convenient.
Interested friends can then talk about your ideas and algorithms, look forward to ... (For convenience, the above has been added. You can also add numbers to this order when you post algorithm, convenient to discuss.)
