26. Oracle PL/SQL Paging

One, no return value of the stored procedure

Ancient cloud: Make haste, in order to let the big guy easier to accept the paging process to write, I still from simple to complex, gradually to everyone to explain. The first is to master the simplest stored procedure, without the return value of the stored procedure. Case: The existing table book, the table structure is as follows: ISBN, title, publishing house.

CREATE TABLE Book (
   ID Number (4),
   Book_name VARCHAR2 (30),
   Publishing VARCHAR2 (30)
);

Please write a procedure that adds a book to the books table and requires that the procedure be called through a Java program.

--note that:in-> indicates that this is an input parameter, and the default is in--out-> to indicate an output parameter
CREATE OR REPLACE PROCEDURE add_book (ID         in number,
                                     NAME       in VARCHAR2,
                                     Publishing in VARCHAR2) is
BEGIN
  INSERT into book VALUES (ID, NAME, publishing);
  COMMIT;
END;
/

Code that the Java program calls the stored procedure

Package junit.test;

Import java.sql.CallableStatement;
Import java.sql.Connection;
Import Java.sql.DriverManager;

/**
* Call a stored procedure with no return value
*
* @author Jiqinlin
*
*/
public class Proceduretest {

public static void Main (string[] args) {

try {
1. Load Driver
Class.forName ("Oracle.jdbc.driver.OracleDriver");
2. Get Connected
Connection ct = drivermanager.getconnection (
"Jdbc:oracle:thin:@127.0.0.1:1521:orcl", "Scott", "Oracle");
3. Create CallableStatement
CallableStatement cs = Ct.preparecall ("Call Add_book (?,?,?)");
Assign a value to.
Cs.setint (1, 1);
Cs.setstring (2, "Java");
Cs.setstring (3, "Java Publishing House");
4. Implementation
Cs.execute ();
5. Close
Cs.close ();
Ct.close ();
} catch (Exception e) {
E.printstacktrace ();
}
}
}

Ii. stored procedures with return values (non-list)
Case: Write a stored procedure that can enter the employee's number and return the employee's name.

--Stored procedures for input and output
CREATE OR REPLACE PROCEDURE sp_proc (spno in number, spname out VARCHAR2) is
BEGIN
  SELECT ename to Spname from EMP WHERE EMPNO = spno;
END;
/

Code that the Java program calls the stored procedure

Package junit.test;

Import java.sql.CallableStatement;
Import java.sql.Connection;
Import Java.sql.DriverManager;

/**
* Call a stored procedure with no return value
*
* @author Jiqinlin
*
*/
public class Proceduretest {

public static void Main (string[] args) {

try {
1. Load Driver
Class.forName ("Oracle.jdbc.driver.OracleDriver");
2. Get Connected
Connection ct = drivermanager.getconnection (
"Jdbc:oracle:thin:@127.0.0.1:1521:orcl", "Scott", "Oracle");
3. Create CallableStatement
CallableStatement cs = Ct.preparecall ("{Call Sp_proc (?,?)}");
Assign a value to the first?
Cs.setint (1,7788);
To the second one? Assign a value
Cs.registeroutparameter (2,oracle.jdbc.oracletypes.varchar);
4. Implementation
Cs.execute ();
Take out the return value. The order
String name=cs.getstring (2);
System.out.println ("Name of number 7788:" +name);
5. Close
Cs.close ();
Ct.close ();
} catch (Exception e) {
E.printstacktrace ();
}
}
}

Case expansion: Write a procedure that allows you to enter the employee's number and return the employee's name, salary, and position.

--Stored procedures for input and output
CREATE OR REPLACE PROCEDURE sp_proc (spno   in number,
                                    Spname out VARCHAR2,
                                    Spsal out number  ,
                                    Spjob out  VARCHAR2) is
BEGIN
  SELECT ename, SAL, JOB into Spname, Spsal, spjob from EMP WHERE EMPNO = spno;
END;
/

Code that the Java program calls the stored procedure

Package junit.test;

Import java.sql.CallableStatement;
Import java.sql.Connection;
Import Java.sql.DriverManager;

/**
* Call a stored procedure with no return value
*
* @author Jiqinlin
*
*/
public class Proceduretest {

public static void Main (string[] args) {

try {
1. Load Driver
Class.forName ("Oracle.jdbc.driver.OracleDriver");
2. Get Connected
Connection ct = drivermanager.getconnection (
"Jdbc:oracle:thin:@127.0.0.1:1521:orcl", "Scott", "Oracle");
3. Create CallableStatement
CallableStatement cs = Ct.preparecall ("{Call Sp_proc (?,?,?,?)}");
Assign a value to the first?
Cs.setint (1,7788);
To the second one? Assign a value
Cs.registeroutparameter (2,oracle.jdbc.oracletypes.varchar);
To the third one? Assign value
Cs.registeroutparameter (3,oracle.jdbc.oracletypes.double);
To the fourth one? Assign value
Cs.registeroutparameter (4,oracle.jdbc.oracletypes.varchar);
4. Implementation
Cs.execute ();
Take out the return value. The order
String name=cs.getstring (2);
Double sal=cs.getdouble (3);
String job=cs.getstring (4);
System.out.println ("No. 7788 Name:" +name+ ", Position:" +job+ ", Salary:" +sal+ ");
5. Close
Cs.close ();
Ct.close ();
} catch (Exception e) {
E.printstacktrace ();
}
}
}

Third, stored procedure with return value (list [result set])
Case: Write a stored procedure, enter the department number, and return all employee information for that department.
The analysis of the problem is as follows: Because the Oracle stored procedure does not return a value, all its return values are replaced by out parameters, the list is no exception, but because it is a collection, it is not possible to use the general parameters, you must use Pagkage. So it has to be divided into two parts:
1), create a package in which we define the type Test_cursor, which is a cursor.

CREATE OR REPLACE Package Testpackage as
  TYPE Test_cursor is REF CURSOR;
END Testpackage;
/

2), set up the stored procedure.

CREATE OR REPLACE PROCEDURE sp_proc (spno     in number,
                                    P_cursor out Testpackage. Test_cursor) is
BEGIN
  OPEN P_cursor for
    SELECT * from EMP WHERE DEPTNO = spno;
END Sp_proc;
/

3), how to invoke the procedure in a Java program

Package junit.test;

Import java.sql.CallableStatement;
Import java.sql.Connection;
Import Java.sql.DriverManager;
Import Java.sql.ResultSet;

/**
* Call a stored procedure with no return value
*
* @author Jiqinlin
*
*/
public class Proceduretest {

public static void Main (string[] args) {

try {
1. Load Driver
Class.forName ("Oracle.jdbc.driver.OracleDriver");
2. Get Connected
Connection ct = drivermanager.getconnection (
"Jdbc:oracle:thin:@127.0.0.1:1521:orcl", "Scott", "Oracle");
3. Create CallableStatement
CallableStatement cs = Ct.preparecall ("{Call Sp_proc (?,?)}");
Assign a value to the first?
Cs.setint (1,10);
To the second one? Assign a value
Cs.registeroutparameter (2,oracle.jdbc.oracletypes.cursor);
4. Implementation
Cs.execute ();
Get the result set
ResultSet rs = (ResultSet) cs.getobject (2);
while (Rs.next ()) {
System.out.println (Rs.getint (1) + "" + rs.getstring (2));
}
5. Close
Rs.close ();
Cs.close ();
Ct.close ();
} catch (Exception e) {
E.printstacktrace ();
}
}
}

Iv. writing the paging process
With the above basis, I believe you can complete the paging stored procedures.
requirements, please write a stored procedure that requires you to enter the table name, the number of records displayed per page, and the current page. Returns the total number of records, total pages, and returned result sets.

--rownum usage
Select O.*, ROWNUM RN from (SELECT * from EMP) o WHERE ROWNUM <= 10;
----Oracle Paging SQL statements, you can use the following SQL statement as a template when paging
SELECT *
From (select O.*, ROWNUM RN from (SELECT * from EMP) o WHERE ROWNUM <= 10)
WHERE RN >= 6;

1), develop a package
Create a package that defines a cursor of type test_cursor in the package.

--Build a package
CREATE OR REPLACE Package Testpackage as
TYPE Test_cursor is REF CURSOR;
END Testpackage;
/

--Begin the process of writing pagination
CREATE OR REPLACE PROCEDURE fenye (TABLENAME in VARCHAR2,
PAGESIZE in number,--Display records per page
Pagenow in number,--Number of pages
Myrows out number,--Total records
Mypagecount out number,--Total pages
P_cursor out Testpackage. Test_cursor) is--the recordset returned
                                 
--Definition section
--Define the SQL statement string
V_sql VARCHAR2 (1000);
--Definition of two integers
V_begin Number: = (PAGENOW-1) * PAGESIZE + 1;
V_end Number: = Pagenow * PAGESIZE;
BEGIN
--Executive Section
V_sql: = ' select * FROM (select t1.*, rownum rn from (SELECT * from ' | | TABLENAME | | ') t1 where rownum<= ' | | V_end | | ') where rn>= ' | | V_begin;
--Associating cursors with SQL
OPEN p_cursor for V_sql;
--Calculate myrows and Mypagecount
--Organize an SQL statement
V_sql: = ' SELECT count (*) from ' | | TABLENAME;
--Executes the SQL and assigns the returned value to the myrows;
EXECUTE ImMEDIATE v_sql into myrows; -it parses and immediately executes a dynamic SQL statement or a PL/SQL block that is not created at runtime. Dynamically creating and executing SQL statements performance ahead of time,
The goal of--execute immediate is to reduce enterprise costs and achieve high performance, which is quite easy to code than it used to be.
--although Dbms_sql is still available, execute IMMEDIATE is recommended because it gains revenue on the package.
--Calculation Mypagecount
--if Myrows%pagesize=0 then it's wrong to write
IF MOD (myrows, PAGESIZE) = 0 Then
Mypagecount: = myrows/pagesize;
ELSE
Mypagecount: = myrows/pagesize + 1;
END IF;
--Close cursor
--close P_cursor; --do not close, otherwise Java calls the stored procedure will error
END;
/

Java Calling paging code

Package junit.test;

Import java.sql.CallableStatement;
Import java.sql.Connection;
Import Java.sql.DriverManager;
Import Java.sql.ResultSet;

/**
* Call a stored procedure with no return value
*
* @author Jiqinlin
*
*/
public class Proceduretest {

public static void Main (string[] args) {

try {
1. Load Driver
Class.forName ("Oracle.jdbc.driver.OracleDriver");
2. Get Connected
Connection ct = drivermanager.getconnection (
"Jdbc:oracle:thin:@127.0.0.1:1521:orcl", "Scott", "Oracle");
3. Create CallableStatement
CallableStatement cs = Ct.preparecall ("{Call Fenye (?,?,?,?,?,?)}");
Cs.setstring (1, "EMP"); Table name
Cs.setint (2, 5); Shows the number of records per page
Cs.setint (3, 1);//pages
Total number of registered records
Cs.registeroutparameter (4, Oracle.jdbc.OracleTypes.INTEGER); Total Record Count
Total pages Registered
Cs.registeroutparameter (5, Oracle.jdbc.OracleTypes.INTEGER); Total pages
Registering the returned result set
Cs.registeroutparameter (6, Oracle.jdbc.OracleTypes.CURSOR); The recordset returned
4. Implementation
Cs.execute ();
Get the result set
Total records taken out/note here, GetInt (4) in 4, is determined by the position of the parameter
int rowNum = Cs.getint (4);

int pagecount = Cs.getint (5);
ResultSet rs = (ResultSet) cs.getobject (6);
Show it, see if it's right
System.out.println ("rownum=" + rowNum);
System.out.println ("Total pages =" + PageCount);

while (Rs.next ()) {
System.out.println ("No.:" + rs.getint (1) +
"First Name:" + rs.getstring (2) +
"Salary:" + rs.getfloat (6));
}
5. Close
Rs.close ();
Cs.close ();
Ct.close ();
} catch (Exception e) {
E.printstacktrace ();
}
}
}

26. Oracle PL/SQL Paging