Paging process for pl/SQL writing, paging for plsql writing

Paging process for pl/SQL writing, paging for plsql writing
-- Develop a package
-- Create a package. In this package, the test_cursor type is defined as a cursor. As follows:
SQL code

1. Create or replace package testpackage
2. TYPE test_cursor is ref cursor;
3. End testpackage;

-- Start the paging Process
Create or replace procedure fenye
(Table_name in varchar2,
Page_size in number, -- number of records displayed on each page
Page_now in number, -- current page
Order_by in varchar2, -- Additional -- sorting Condition

Total_rows out number, -- total number of records
Total_pages out number, -- total number of pages
P_cursor out testpackage. test_cursor -- returned record set
) Is
-- Definition
-- Define the SQL statement string
V_ SQL varchar2 (500 );
V_start number: = (page_now-1) * page_size + 1;
V_end number: = page_now * page_size;
Begin
-- Execution part
V_ SQL: = 'select * from (select t1. *, rownum rn from (select * from' | table_name | 'ORDER BY' | order_by | ') t1 where rownum <= '| v_end |') where rn> = '| v_start;
-- Associate a cursor with an SQL statement
Open p_cursor for v_ SQL;
-- Calculate the total number of records and total number of pages
-- Organize another SQL statement
V_ SQL: = 'select count (*) from' | table_name;
-- Execute the preceding SQL statement and pay the returned value to total_rows;
Execute immediate v_ SQL into total_rows;
-- Calculate total_pages (-- total number of pages)
If mod (total_rows, page_size) = 0 then
Total_pages: = total_rows/page_size;
Else
Total_pages: = total_rows/page_size + 1;
End if;
-- The cursor cannot be closed here. Otherwise, when called in JAVA, the cursor has been closed exception, and the result set cannot be obtained.
--- Close p_cursor;
End;
/
Testing Using java
// Test page
Package oracle. plsql. test. fenye;


Import java. SQL .*;


/**
* // Call a paging Stored Procedure
Create or replace procedure fenye
(Table_name in varchar2, -- table name
Page_size in number, -- number of records displayed on each page
Page_now in number, -- current page
Order_by in varchar2, -- sorting Condition


Total_rows out number, -- total number of records
Total_pages out number, -- total number of pages
P_cursor out testpackage. test_cursor -- returned record set
)
*/
Public class TestPlSqlFenye {
Public static void main (String [] args ){
Connection ct = null;
CallableStatement cs = null;
Try {
// 1. Load the driver
Class. forName ("oracle. jdbc. driver. OracleDriver ");
// 2. Get the connection
Ct = DriverManager. getConnection ("jdbc: oracle: thin: @ 127.0.0.1: 1521: BJSXT", "scott", "tiger ");
// See how to call the process with returned values
// 3. Create CallableStatement
Cs = ct. prepareCall ("{call fenye (?,?,?,?, ?,?,?)} ");

// 4. To the first 4? Assignment
Cs. setString (1, "emp ");
Cs. setInt (2, 6); // 6 entries per page
Cs. setInt (3, 1); // page 1st is displayed.
Cs. setString (4, "sal ");
// Give the last three? Register Parameters
Cs. registerOutParameter (5, oracle. jdbc. OracleTypes. INTEGER); // pay attention to this type.
Cs. registerOutParameter (6, oracle. jdbc. OracleTypes. INTEGER); // pay attention to this type.
Cs. registerOutParameter (7, oracle. jdbc. OracleTypes. CURSOR); // pay attention to this type.

// 5. Run
Cs.exe cute ();
System. out. println (cs. getInt (5 ));
System. out. println (cs. getInt (6 ));
// Obtain the result set. The result set is obtained here.
ResultSet rs = (ResultSet) cs. getObject (7 );

While (rs. next ()){
System. out. println (rs. getInt (1) + "" + rs. getString (2) + "" + rs. getFloat ("SAL "));
}


} Catch (Exception e ){
E. printStackTrace ();
} Finally {
// 6. Close open resources
Try {
Cs. close ();
Ct. close ();
} Catch (SQLException e ){
E. printStackTrace ();
}
}
}
}

The console displays the following results: 14
3
7369 SMITH 671.17
7900 JAMES 950.71
7876 ADAMS 1100.0
7521 WARD 1250.0
7654 MARTIN 1250.0
7934 MILLER 1300.0



How to call the paging process of oracle input/output in pl/SQL?

DECLARE
Xhname VARCHAR2 (4000 );
BEGIN
Xh_pro8( 7521, xhname );
END;

Run this command to replace 7521 with your own ID number.

Oracle can give me a general paging stored procedure that supports multi-Table query and put it in PL/SQL to directly run the editing test.

Watching!