Oracle BASICS (5) Advanced pl/SQL (paging process)

Oracle BASICS (5) Advanced pl/SQL (paging process)
Compile the paging process to implement the paging process through pl/SQL, and then step by step from simple to difficult. The purpose is to familiarize yourself with the various stored procedures, packages, and cursors of pl/SQL through this case, learning how to call and other content in java.

1. Stored Procedure without return values

Example 1: You can add a book to the book table,

--- Create a table

Create table book (bookId number, bookName varchar2 (50), publishHouse varchar2 (50 ));

-- In indicates that the variable is the input value. If this parameter is not set, the input value is used by default instead of the output variable. out indicates the output value.

Create or replace procedure sp_pro7 (spBookId in number, spbookName in varchar2, sppublishHouse in varchar2) is

Begin

Insert into book valuse (spBookId, spbookName, sppublishHouse );

End

--- How to call in java

 

Package com. sp; Importjava. SQL. *; Publicclass Test1 {public static void main (string [] args) {try {// 1 load the driver Class. forName ("Oracle. jdbc. driver. oracleDriver "); Connetionct = DriverManger. getConnection ("jdbc: oracle: thin: @ 127.0.0.1: 1521: myora1", "scott", "123"); // 2 create callablestatement Callablestatement cs = ct. preparecall ("{call sp_pro7 (?,?,?)} "); // Give? Assign a value to Cs. setInt (1, 10); Cs. setstring (2, "swordsman"); Cs. setstring (3, "People's Publishing House"); // execute Cs.exe cute () ;;} Catch (exception e) {e. printstacktrace ();} Finally {close each link }}}

 

2. Stored Procedures with returned values

For example, enter the Book Number and return the book name

Create or replace procedure sp_pro8 (spno in number, spkName out varchar2, spsal out varchar2) is

Begin

Select ename, spsal, into spName, spsal form emp where empno = spno;

End

How to call

 

Package com. sp; Importjava. SQL. *; Publicclass Test1 {public static void main (string [] args) {try {// 1 load the driver Class. forName ("Oracle. jdbc. driver. oracleDriver "); Connetionct = DriverManger. getConnection ("jdbc: oracle: thin: @ 127.0.0.1: 1521: myora1", "scott", "123"); // 2 create callablestatement Callablestatement cs = ct. preparecall ("{call sp_pro8 (?,?)} "); // Give? Assign a value to Cs. setInt (1,7788); Cs. registerOutParemeter (2, oracle. jdbc. oracleTyps. varchar) // execute Cs. registerOutParemeter (3, oracle. jdbc. oracleTyps. double) // run // oracle. jdbc. oracleTyps. varchar indicates the type of execution Cs.exe cute (); // retrieve the returned value. Do you need to note? Sequential String name = SC. getstring (2); String job = cs. getstring (3); System. out. println ("7788 name" + name + "7788 salary" + sal);} Catch (exception e) {e. printstacktrace ();} Finally {close each link }}}

3. Stored Procedures with returned values are returned in the form of a list result set.

 

Oracle stored procedures are replaced by the out parameter when they are not returned. A package is required for a collection.

-- Create a package and define the test_cursor type.

Create or replace packagetestpackage

Typetest_cursur is ref cursor;

End testpackage

Create a stored procedure

Create or repalce procedure sp_pro9 (spNo in number, P_cursor out tespackage, test_cursor) is

Begin

Open p_cursor for select * from emp where depto-spNo;

End;

-- How to call

 

Package com. sp; Importjava. SQL. *; Publicclass Test1 {public static void main (string [] args) {try {// 1 load the driver Class. forName ("Oracle. jdbc. driver. oracleDriver "); Connetionct = DriverManger. getConnection ("jdbc: oracle: thin: @ 127.0.0.1: 1521: myora1", "scott", "123"); // 2 create callablestatement Callablestatement cs = ct. preparecall ("{call sp_pro9 (?,?)} "); // Give? Assign Cs. setInt (); Cs. registerOutParemeter (2, oracle. jdbc. oracleTypes. cursor)

// Execute Cs.exe cute (); // retrieve the returned value. What should I do? Sequential ResultSet rs = (ResultSet) cs. getObject (2); While (rs. next () {system. out. println (rs. getInt (1) + "" + rs. getString (2) ;}} Catch (exception e) {e. printstacktrace ();} Finally {close each link }}}

4. ---- oracle paging rule Introduction

 

SQL> select t1. *, rownum rn from (select * from emp) t1;

SQL> select t1. *, rownum rn from (select * from emp) t1 where rownum <= 10;

-- During paging, you can

Select * from (

SQL> select t1. *, rownum rn from (select * from emp) t1where rownum <= 10;) where rn> = 6;

-- Develop a package

Create or replace package testpackage

Typetest_cursur is ref cursor;

End testpackage

5. -- compile the write splitting process by PAGE and sort by salary

Create or replace procedure fenye

(TableName in varchar2,

Pagesize in number,

Pagenow in number,

Myrows out number, -- total number of records

MypageCount 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 (1000 );

-Define two integers

V_begin number: = (Pagenow-1) * Pagesize + 1;

V_endnumber: = Pagenow * Pagesize;

Begin

-- Execution part

V_ SQL: = 'select * from (

SQL> select t1. *, rownum rn from (select * from '| tableName | 'order by sal) t1 where rownum <=' | v-end | ';) where rn> = '| v_begin | ';'

--Enable the association between the cursor and SQL

Open p_cursor for v_ SQL;

-- Calculate Myrows and mypageCount

-- Organize an SQL statement

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

-- Execute the SQL statement and assign the returned value to myrows;

Executeimmediate v_ SQL into myrows;

-Calculate mypagecount

If mod (myrows, pagesize) = 0 then

Mypagecount: = myrows/pagesize;

Else

Mypagecount: = myrows/pagesize + 1;

End if;

-- Close the cursor

Close p_cursor;

End;

-- Test with java

 

Package com. sp; Importjava. SQL. *; Public class Test1 {public static void main (string [] args) {try {// 1 load the driver Class. forName ("Oracle. jdbc. driver. oracleDriver "); Connetionct = DriverManger. getConnection ("jdbc: oracle: thin: @ 127.0.0.1: 1521: myora1", "scott", "123"); // 2 create callablestatement Callablestatement cs = ct. preparecall ("{call fenye (?,?,?,?,?,?)} "); // Give? Assign a value to Cs. setString (1, "emp"); table Cs. setInt (2, 5); The size of each page Cs. setInt (2nd); Page // total number of accepted records Cs. registerOutParemeter (4, oracle. jdbc. oracleTypes. integer) // run // The total number of registered pages Cs. registerOutParemeter (5, oracle. jdbc. oracleTypes. integer) // execute // register the returned result set Cs. registerOutParemeter (6, oracle. jdbc. oracleTypes. cursor) // execute Cs.exe cute (); // retrieve the total number of records. Note that in getint (4), 4 is determined by the position of this parameter. Int rowNum = cs. getInt (4); Int pageCount = cs. getint (5); Resultset rs = (resultset) cs. getobject (6); // check whether the System is correct. out. println ("rownum =" + rownum); System. out. println ("total page number:") + pagecount); While (rs. next () {system. out. println ("No.:" + rs. getInt (1) + "name" + rs. getstring (20) + "salary" ;}} Catch (exception e) {e. printstacktrace ();} Finally {close each link }}}

A seemingly complex paging process, from simple to complex decomposition step by step, from a stored procedure without return values to a stored procedure with a return value, and then to a stored procedure with a set form as a return value, the simple paging algorithm is based on the pl/SQL package, cursor, mod statement, if branch statement, variable definition, and oder by clause. The learning process is like this. It is impossible to get a fat man. It is also a rule of human cognition, from simplicity to complexity, when encountering complicated problems, consider how to split it into simple and well-known problems and learn them step by step.