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

Source: Internet
Author: User

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!

Related Article

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.