Oracle Stored Procedure paging code

Welcome to the Oracle community forum and interact with 2 million technical staff & gt; how to implement the paging code in the oracle stored procedure, let's take a look at the Chinese IT lab editor's solutions for you! * ****** Stored Procedure paging code ********** -- Baotou createorreplacepackagepck_myistypec_myisrefcursor; pr

Welcome to the Oracle community forum and interact with 2 million technical staff> How to Implement the paging code in the oracle stored procedure? Let's take a look at the methods compiled by the Chinese IT experiment editor for you! /******** Stored Procedure paging code **********/-- Baotou create or replace package pck_my is type c_my is ref cursor; pr

Welcome to the Oracle community forum and interact with 2 million technical staff> enter

How does the paging code of the oracle stored procedure be implemented? Let's take a look at the methods for organizing the IT experiment in China!

/******* Stored Procedure paging code **********/

-- Baotou

Create or replace package pck_my is

Type c_my is ref cursor;

Procedure page_moed (

V_table in varchar2, -- table name

Current_page in out number, -- current page

PageSize in out number, -- number of page rows

Total out number, -- total number of rows

CountPage out number, -- total number of pages

C_cursor out pck_my.c_my -- cursor

);

End pck_my;

-- Body

Create or replace package body pck_my

Procedure page_moed (

V_table in varchar2,

Current_page in out number,

PageSize in out number,

Total out number,

CountPage out number,

C_cursor out pck_my.c_my

) Is

V_ SQL varchar2 (1000 );

V_max number;

V_min number;

E_table exception;

Begin

-- Judge Parameters

If v_table is null then

Raise e_table;

-- Return;

End if;

If current_page is null then

Current_page: = 1;

End if;

If pageSize <= 0 then

PageSize: = 5;

End if;

-- Calculate the smallest row of the largest row

V_max: = (current_page + 1) * pageSize;

V_min: = current_page * pageSize;

-- Get Data

V_ SQL: = 'select *

From (select filminfo. *, rownum as t from '| v_table | 'where rownum <=' | v_max | ')

Where t> '| v_min;

Open c_cursor for v_ SQL;

-- Calculate the total number of rows

V_ SQL: = 'select count (*) from' | v_table;

Execute immediate v_ SQL into total;

-- Calculate the total number of pages

If mod (total, pageSize) = 0 then

CountPage: = total/pageSize;

Else

CountPage: = total/pageSize + 1;

End if;

-- Exception

Exception

When e_table then

Dbms_output.put_line ('table name cannot be blank ');

End;

End pck_my;

-- Exet

Select * from filminfo

[1] [2]