Oracle efficient paging storage process

Oracle efficient paging storage process:

1. Create Or ReplacePackage JT_P_pageIs
2. Type type_curIsRefCursor;-- Defines the cursor variable used to return the record set
3. ProcedurePagination (PindexInNumber,-- Index of the number of pages to be displayed, starting from 0
4. PsqlInVarchar2,-- Query statement that generates paging data
5. PsizeInNumber,-- Number of records displayed per page
6. PcountOutNumber,-- Number of returned pages
7. ProwcountOutNumber,-- Number of returned records
8. V_curOutType_cur-- Returns the cursor of the paging data.
9. );
10. EndJT_P_page;
11. Commit;
12. -- Define the package subject
13. Create Or ReplacePackage body JT_P_pageIs
14. ProcedurePagination (PindexInNumber,-- Index of the number of pages to be displayed, starting from 0
15. PsqlInVarchar2,-- Query statement that generates paging data
16. PsizeInNumber,-- Number of records displayed per page
17. PcountOutNumber,-- Number of returned pages
18. ProwcountOutNumber,-- Number of returned records
19. V_curOutType_cur-- Returns the cursor of the paging data.
20. )AS
21. V_ SQL VARCHAR2 (1000 );
22. V_Pbegin number;
23. V_Pend number;
24. Begin
25. V_ SQL: ='Select count (*) from ('| Psql |')';
26. ExecuteImmediate v_ SQLIntoProwcount;-- Calculate the total number of records
27. Pcount: = ceil (Prowcount/Psize );-- Calculate the total number of pages
28. -- Display any page content
29. V_Pend: = Pindex * Psize + Psize;
30. V_Pbegin: = v_Pend-Psize + 1;
31. V_ SQL: ='Select * from (select rownum rn, t. * from ('| Psql |') T) where rn'| V_Pbegin |And'| V_Pend;
32. OpenV_curForV_ SQL;
33. EndPagination;
34. EndJT_P_page;
35. Commit;
37. **************************************** *********************
38. OracleParameter [] param = new OracleParameter [] {new OracleParameter ("Pindex", OracleType. Number), new OracleParameter ("Psql", OracleType.VarChar), New OracleParameter ("Psize", OracleType. Number), new OracleParameter ("Pcount", OracleType. Number), new OracleParameter ("Prowcount", OracleType. Number), new OracleParameter ("V_cur", OracleType.Cursor)};
39. Param [0]. Value =Index;
40. Param [1]. Value = SQL;
41. Param [2]. Value = pageSize;
43. Param [0]. Direction = ParameterDirection. Input;
44. Param [1]. Direction = ParameterDirection. Input;
45. Param [2]. Direction = ParameterDirection. Input;
46. Param [3]. Direction = ParameterDirection.Output;
47. Param [4]. Direction = ParameterDirection.Output;
48. Param [5]. Direction = ParameterDirection.Output;
50. DataSet ds = OracleHelper. ExecuteDataset (Form1.OracleConnString, CommandType. StoredProcedure,"JT_P_page.Pagination", Param );
53. IntPcount =Int. Parse (param [3]. Value. ToString ());
54. IntRecord =Int. Parse (param [4]. Value. ToString ());
56. Lb_pcount.Text = pcount. ToString ();
58. DataGridView1.DataSource = ds. Tables [0];