Paging is any website (bbs, E-Shop ,blog) All the technologies that we use , So learn pl/sql Programming development must master this technology . Here's the picture :

1.  Write a stored procedure with no return value

The first is to master the simplest stored procedure , Stored procedure with no return value .

Case study : There is a table book, The table structure is as follows :

Please write a process , Can be directed to book Table add book , Ask to pass java The program calls the procedure .  --in: Indicates that this is an input parameter , The default is in  --out: Represents an output parameter   The stored procedure code is as follows :

[c-sharp]  view plain copy

  1. create or replace procedure sp_pro7(spBookId in number,spbookName in varchar2,sppublishHouse in varchar2) is
  2. begin
  3. insert into book values(spBookId,spbookName,sppublishHouse);
  4. end;

stay java The procedure code is called as follows :

[java]  view plain copy

  1. // Call a procedure with no return value
  2. import java.sql.*;
  3. public class Test2{
  4. public static void main(String[] args){
  5. try{
  6. //1. The load driver
  7. Class.forName("oracle.jdbc.driver.OracleDriver");
  8. //2. Get connected
  9. Connection ct = DriverManager.getConnection("jdbc:oracle:thin@127.0.0.1:1521:MYORA1","scott","m123");
  10. //3. establish CallableStatement
  11. CallableStatement cs = ct.prepareCall("{call sp_pro7(?,?,?)}");
  12. //4. to ? assignment
  13. cs.setInt(1,10);
  14. cs.setString(2," Smiling and proud in the Jianghu ");
  15. cs.setString(3," People's Publishing House ");
  16. //5. perform
  17. cs.execute();
  18. } catch(Exception e){
  19. e.printStackTrace();
  20. } finally{
  21. //6. Close open resources
  22. cs.close();
  23. ct.close();
  24. }
  25. }
  26. }

perform , The record was added .

2.  Stored procedure with return value ( Not a list ) Case study : Write a process , You can enter the employee's number , Return the employee's name .  Case expansion : Write a process , You can enter the employee's number , Return the employee's name 、 Salary and position .

The stored procedure code is as follows :

[c-sharp]  view plain copy

  1. -- Stored procedures with input and output
  2. create or replace procedure sp_pro8
  3. (spno in number, spName out varchar2) is
  4. begin
  5. select ename into spName from emp where empno=spno;
  6. end;

java The calling procedure code is as follows :

[java]  view plain copy

  1. import java.sql.*;
  2. public class Test2{
  3. public static void main(String[] args){
  4. try{
  5. //1. The load driver
  6. Class.forName("oracle.jdbc.driver.OracleDriver");
  7. //2. Get connected
  8. Connection ct = DriverManager.getConnection("jdbc:oracle:thin@127.0.0.1:1521:MYORA1","scott","m123");
  9. //3. establish CallableStatement
  10. /*CallableStatement cs = ct.prepareCall("{call sp_pro7(?,?,?)}");
  11. //4. to ? assignment
  12. cs.setInt(1,10);
  13. cs.setString(2," Smiling and proud in the Jianghu ");
  14. cs.setString(3," People's Publishing House ");*/
  15. // See how to call a procedure with a return value
  16. // establish CallableStatement
  17. /*CallableStatement cs = ct.prepareCall("{call sp_pro8(?,?)}");
  18. // Give the first one. ? assignment
  19. cs.setInt(1,7788);
  20. // For the second ? assignment
  21. cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);
  22. //5. perform
  23. cs.execute();
  24. // Take out the return value , it is to be noted that ? The order of
  25. String name=cs.getString(2);
  26. System.out.println("7788 Name "+name);
  27. } catch(Exception e){
  28. e.printStackTrace();
  29. } finally{
  30. //6. Close open resources
  31. cs.close();
  32. ct.close();
  33. }
  34. }
  35. }

function , The result is successful .

Case expansion : Write a process , You can enter the employee's number , Return the employee's name 、 Salary and position .

[c-sharp]  view plain copy

  1. -- Stored procedures with input and output
  2. create or replace procedure sp_pro8
  3. (spno in number, spName out varchar2,spSal out number,spJob out varchar2) is
  4. begin
  5. select ename,sal,job into spName,spSal,spJob from emp where empno=spno;
  6. end;

java The calling procedure code is as follows :

[java]  view plain copy

  1. import java.sql.*;
  2. public class Test2{
  3. public static void main(String[] args){
  4. try{
  5. //1. The load driver
  6. Class.forName("oracle.jdbc.driver.OracleDriver");
  7. //2. Get connected
  8. Connection ct = DriverManager.getConnection("jdbc:oracle:thin@127.0.0.1:1521:MYORA1","scott","m123");
  9. //3. establish CallableStatement
  10. /*CallableStatement cs = ct.prepareCall("{call sp_pro7(?,?,?)}");
  11. //4. to ? assignment
  12. cs.setInt(1,10);
  13. cs.setString(2," Smiling and proud in the Jianghu ");
  14. cs.setString(3," People's Publishing House ");*/
  15. // See how to call a procedure with a return value
  16. // establish CallableStatement
  17. /*CallableStatement cs = ct.prepareCall("{call sp_pro8(?,?,?,?)}");
  18. // Give the first one. ? assignment
  19. cs.setInt(1,7788);
  20. // For the second ? assignment
  21. cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);
  22. // For the third ? assignment
  23. cs.registerOutParameter(3,oracle.jdbc.OracleTypes.DOUBLE);
  24. // For the fourth ? assignment
  25. cs.registerOutParameter(4,oracle.jdbc.OracleTypes.VARCHAR);
  26. //5. perform
  27. cs.execute();
  28. // Take out the return value , it is to be noted that ? The order of
  29. String name=cs.getString(2);
  30. String job=cs.getString(4);
  31. System.out.println("7788 Name "+name+"  Work :"+job);
  32. } catch(Exception e){
  33. e.printStackTrace();
  34. } finally{
  35. //6. Close open resources
  36. cs.close();
  37. ct.close();
  38. }
  39. }
  40. }

function , Record successfully found . 3.  Stored procedure with return value ( list [ Result set ])

Case study : Write a process , Enter the department number , Return all employee information of the Department .  The analysis of this question is as follows :       because oracle The stored procedure did not return a value , All its return values are passed through out Parameters to replace , The list is no exception , But because it's a collection , So we can't use general parameters , It must be used. pagkage 了 . So there are two parts :  (1). Create a package , In this bag , I define types test_cursor, It's a cursor . The code is as follows :

[c-sharp]  view plain copy

  1. create or replace package testpackage as
  2. TYPE test_cursor is ref cursor;
  3. end testpackage;

(2). Create stored procedures , The code is as follows :

[c-sharp]  view plain copy

  1. create or replace procedure sp_pro9(spNo in number,p_cursor out testpackage.test_cursor) is
  2. begin
  3. open p_cursor for
  4. select * from emp where deptno = spNo;
  5. end sp_pro9;

(3). stay java The procedure is called , The code is as follows :

[java]  view plain copy

  1. import java.sql.*;
  2. public class Test2{
  3. public static void main(String[] args){
  4. try{
  5. //1. The load driver
  6. Class.forName("oracle.jdbc.driver.OracleDriver");
  7. //2. Get connected
  8. Connection ct = DriverManager.getConnection("jdbc:oracle:thin@127.0.0.1:1521:MYORA1","scott","m123");
  9. // See how to call a procedure with a return value
  10. //3. establish CallableStatement
  11. /*CallableStatement cs = ct.prepareCall("{call sp_pro9(?,?)}");
  12. //4. Give it to ? assignment
  13. cs.setInt(1,10);
  14. // For the second ? assignment
  15. cs.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR);
  16. //5. perform
  17. cs.execute();
  18. // Get the result set
  19. ResultSet rs=(ResultSet)cs.getObject(2);
  20. while(rs.next()){
  21. System.out.println(rs.getInt(1)+" "+rs.getString(2));
  22. }
  23. } catch(Exception e){
  24. e.printStackTrace();
  25. } finally{
  26. //6. Close open resources
  27. cs.close();
  28. ct.close();
  29. }
  30. }
  31. }

function , The successful department number is 10 All users of .

4.  Write the paging process        requirement , Please write a stored procedure , It is required that you can enter the table name 、 Each page shows the number of records 、 The current page . Returns the total number of records , Total number of pages , And the result set returned .  (1). oracle Implementation of paging in :

[c-sharp]  view plain copy

  1. select t1.*, rownum rn from (select * from emp) t1 where rownum<=10;
  2. -- In paging , You can put the following sql Statement as a template
  3. select * from
  4. (select t1.*, rownum rn from (select * from emp) t1 where rownum<=10)
  5. where rn>=6;

(2). Develop a package

Create a package , In this bag , I define types test_cursor, It's a cursor . The code is as follows :

[c-sharp]  view plain copy

  1. create or replace package testpackage as
  2. TYPE test_cursor is ref cursor;
  3. end testpackage;
  4. -- Start the paging process
  5. create or replace procedure fenye
  6. (tableName in varchar2,
  7. Pagesize in number,-- One page shows the number of records
  8. pageNow in number,
  9. myrows out number,-- Total number of records
  10. myPageCount out number,-- Total number of pages
  11. p_cursor out testpackage.test_cursor-- The recordset returned
  12. ) is
  13. -- Definition section
  14. -- Definition sql sentence   character string
  15. v_sql varchar2(1000);
  16. -- Define two integers
  17. v_begin number:=(pageNow-1)*Pagesize+1;
  18. v_end number:=pageNow*Pagesize;
  19. begin
  20. -- Executive part
  21. v_sql:='select * from (select t1.*, rownum rn from (select * from '||tableName||') t1 where rownum<='||v_end||') where rn>='||v_begin;
  22. -- Put the cursor and sql relation
  23. open p_cursor for v_sql;
  24. -- Calculation myrows and myPageCount
  25. -- Organize a sql sentence
  26. v_sql:='select count(*) from '||tableName;
  27. -- perform sql, And put the returned value , Assign to myrows;
  28. execute inmediate v_sql into myrows;
  29. -- Calculation myPageCount
  30. --if myrows%Pagesize=0 then It's wrong to write like this
  31. if mod(myrows,Pagesize)=0 then
  32. myPageCount:=myrows/Pagesize;
  33. else
  34. myPageCount:=myrows/Pagesize+1
  35. end if;
  36. -- Close cursor
  37. close p_cursor;
  38. end;

(3). Use java Test the paging process , The code is as follows :

[java]  view plain copy

  1. import java.sql.*;
  2. public class FenYe{
  3. public static void main(String[] args){
  4. try{
  5. //1. The load driver
  6. Class.forName("oracle.jdbc.driver.OracleDriver");
  7. //2. Get connected
  8. Connection ct = DriverManager.getConnection("jdbc:oracle:thin@127.0.0.1:1521:MYORA1","scott","m123");
  9. //3. establish CallableStatement
  10. CallableStatement cs = ct.prepareCall("{call fenye(?,?,?,?,?,?)}");
  11. //4. Give it to ? assignment
  12. cs.seString(1,"emp");
  13. cs.setInt(2,5);
  14. cs.setInt(3,2);
  15. // Total records registered
  16. cs.registerOutParameter(4,oracle.jdbc.OracleTypes.INTEGER);
  17. // Total registration pages
  18. cs.registerOutParameter(5,oracle.jdbc.OracleTypes.INTEGER);
  19. // Register the returned result set
  20. cs.registerOutParameter(6,oracle.jdbc.OracleTypes.CURSOR);
  21. //5. perform
  22. cs.execute();
  23. // Total records taken out    / Pay attention here ,getInt(4) in 4, Is determined by the position of the parameter
  24. int rowNum=cs.getInt(4);
  25. int pageCount = cs.getInt(5);
  26. ResultSet rs=(ResultSet)cs.getObject(6);
  27. // Show me , See if it's right
  28. System.out.println("rowNum="+rowNum);
  29. System.out.println(" Total number of pages ="+pageCount);
  30. while(rs.next()){
  31. System.out.println(" Number :"+rs.getInt(1)+"  name :"+rs.getString(2)+"  Wages :"+rs.getFloat(6));
  32. }
  33. } catch(Exception e){
  34. e.printStackTrace();
  35. } finally{
  36. //6. Close open resources
  37. cs.close();
  38. ct.close();
  39. }
  40. }
  41. }

function , Console output :       rowNum=19       Total number of pages :4       Number :7369 name :SMITH Wages :2850.0       Number :7499 name :ALLEN Wages :2450.0       Number :7521 name :WARD Wages :1562.0       Number :7566 name :JONES Wages :7200.0       Number :7654 name :MARTIN Wages :1500.0

New needs , It's required to rank the salary from low to high , Then take out. 6-10.

The code is as follows :

[c-sharp]  view plain copy

  1. begin
  2. -- Executive part
  3. v_sql:='select * from (select t1.*, rownum rn from (select * from '||tableName||' order by sal) t1 where rownum<='||v_end||') where rn>='||v_begin;

Do it again procedure,java Don't change , function , Console output :        rowNum=19        Total number of pages :4        Number :7900 name :JAMES Wages :950.0        Number :7876 name :ADAMS Wages :1100.0        Number :7521 name :WARD Wages :1250.0        Number :7654 name :MARTIN Wages :1250.0         Number :7934 name :MILLER Wages :1300.0

plsql More related articles on pagination

  1. oracle Oracle note

    oracle Oracle note classification : DataBase2011-09-07 10:24 3009 Human reading   Comment on (0)  Collection   report oracle database sqljdbcsystemstring   Catalog (?)[-] ...

  2. Oracle Paging stored procedures and PLSQL Call script in

    Writing process : I collected and tested a lot of Oracle Paging stored procedure code , After finishing, it finally passed the test , I'd like to share with you testing procedure :1. Run the create package command ;2. Run the create stored procedure command ;3. Run the call paging stored procedure statement Test environment :wind ...

  3. PLSQL Realize paging query

    -- Collection to implement cursor query CREATE OR REPLACE PACKAGE emppkg IS TYPE t_record IS RECORD( rn INT, empno emp.empno%TYPE ...

  4. plsql&amp;nbsp; Pagination

     select * from (select rownum rn,ps.* from (select * from user_t) ps ) where rn>=1 and rn<=10 ...

  5. plsql Common function summary

    stay SQLPLUS Next , In the implementation - English character set conversion alter session set nls_language='AMERICAN';alter session set nls_language='SIMPLI ...

  6. C# SQL Optimize And Linq Pagination

    Every time I blog , The first sentence is like this : Programmers are hard pressed , Besides being able to write programs , I have to be able to blog ! Of course , I hope one day in the future , Some boss saw this blog , Pay your programmer staff a little more ! Because the world of programmers is nothing but silence . Most programmers in my eyes don't ...

  7. Oracle Research on paging method

    1.Oracle Three paging methods in 1.1. By analyzing function paging 1.2. adopt ROWNUM Pagination 1.3. adopt ROWID Pagination 2.Oracle Paging solution analysis 2.1. Pure back-end code completes paging 2.2. ...

  8. Oracle02——oracle Pagination 、 Subquery 、 Set operations 、 Processing data 、 Create and manage tables and other database objects

    author : kent peng Reprint please indicate the source : http://www.cnblogs.com/xieyupeng/p/7289451.html --oracle Pagination (Pageing Query) select ...

  9. to PLSQL Put on the wings of flying -PLSQL Optimize

    60-80% of database performance issues are related to poorly performing SQL,60-80% The database performance problem of is due to the bad performance in production S ...

Random recommendation

  1. Chrome Google browser doesn't support css The font is smaller than 12px Solutions for

    Let's start with ie. firefox . Google browser under the display of various Fonts ie Next : Under Firefox : Google next : From the picture above, it's obvious that Google has css Set the font size to 12px And below , The display is the same size , All default 12px; So online one ...

  2. PHP In the middle of json Method of information

    <?php //php In the middle of json Information //json_encode( Array / object ) $color = array('red','blue','green'); //[ The index array ] echo jso ...

  3. Reflection entities are generated automatically EasyUi DataGrid Templates The second edition -- Attached project source code

    I wrote an article before , Address  http://www.cnblogs.com/Bond/p/3469798.html    It's about how to automatically generate correspondence through reflection EasyUi datagrid The template of , And then I posted a lot of ...

  4. log4j Initialization of log

    log4j When not specified ,log4j Will be initialized by default , If you want to make log4j.properties Location time , Can be specified : PropertyConfigurator.configure(): Add in the parameter ...

  5. Sqoop Is an open source tool , Mainly used in HADOOP(Hive) With traditional databases (mysql、oracle...) Transfer of data between

    http://niuzhenxin.iteye.com/blog/1706203   Sqoop Is an open source tool , Mainly used in HADOOP(Hive) With traditional databases (mysql.postgresql.. ...

  6. [ES6] Array -- Destructuring and Rest Parameters &amp;&amp; for ..of &amp;&amp; Arrat.find()

    We can use the destructing and rest parameters at the same time when dealing with Array opration. Ex ...

  7. maven Inherit

    One maven A project can inherit another maven Dependence , Called subprojects Parent project Use scenarios : Multiple subprojects require certain dependencies , You can extract the common dependencies of the child project into the parent project , Subprojects get these dependencies through inheritance , It's more convenient ...

  8. towards Spark Cluster commit task

    1. start-up spark colony . start-up Hadoop colony cd /usr/local/hadoop/ sbin/start-all.sh start-up Spark Of Master Nodes and all slaves node cd /usr/ ...

  9. In depth analysis escape()、encodeURI()、encodeURIComponent() Differences and examples of

    JavaScript There are three functions that can encode strings in , Namely : escape,encodeURI,encodeURIComponent, The corresponding 3 Decoding function :unescape,decodeURI,decod ...

  10. For the first time to use JBoss technological process (windows Next )

    1. First of all, we should understand JBoss Divided into community version (AS) And enterprise (EAP), The community edition has been renamed wildfly( It's a wild fly ?), The enterprise edition is free for individual developers to download and use , Here, because of the company's requirements , I use it jboss-eap ...