--book表create table book(book_id varchar2(20),book_name varchar2(100),book_publish varchar2(100)); --in表示输入参数，默
--book表
create table book(
book_id varchar2(20),
book_name varchar2(100),
book_publish varchar2(100)
);
--in表示输入参数，默认为in
--out 表示输出参数
create or replace procedure sp_pro7(spbookid in varchar2, spbookname in varchar2, spbookpub varchar2)
 is
 begin
   insert into book values(spbookid, spbookname,spbookpub);
 end;
--返回单个值
create or replace procedure sp_pro8(spbookid in varchar2, spbookname out varchar2)
 is
 begin
   select t.book_name into spbookname from book t where t.book_id = spbookid;
 end;
--返回结果集
--1 建立一个游标
create or replace package testpackage as
 type test_cursor is ref cursor;
end testpackage;
--2 创建过程
create or replace procedure sp_pro9(spbookid varchar2, sp_cursor out testpackage.test_cursor) is
 begin
   open sp_cursor for select t.book_id,t.book_name,t.book_publish from book t;
 end;
--分页
select *
  from (select rownum rn, t.* from book t where rownum  where rn > 2;
create or replace procedure fenye(
       tablename varchar2,
       pagesize number,  -- 一页显示的记录数
       pagenow number, --当前页数
       myrows out number, --总记录数
       mypagecount out number,
       sp_cursor out testpackage.test_cursor) is
v_sql varchar2(1000);
v_begin number:=(pagenow - 1) * pagesize + 1;
v_end number:=(pagenow) * pagesize;
begin
    v_sql:='select * from (select rownum rn, t.* from '|| tablename || ' t where rownum '|| v_begin;
    open sp_cursor for v_sql;
    --计算myrows mypagecount
    v_sql := 'select count(*) from '|| tablename;
    --快速执行sql,并将结果赋值给myrows
    execute immediate v_sql into myrows;
    if mod(myrows, pagesize) = 0 then
      mypagecount:=myrows / pagesize;
    else
      mypagecount:=myrows / pagesize + 1;
    end if;
    --关闭游标
    close sp_cursor;
end;
package com.leeket;
import java.sql.callablestatement;
import java.sql.connection;
import java.sql.drivermanager;
import java.sql.resultset;
/**
 * @author administrator
 * @description 分页
 */
public class testprocedure4 {
 public static void main(string[] args) {
  callablestatement call = null;
  connection conn = null;
  resultset rs = null;
  try {
   class.forname(oracle.jdbc.driver.oracledriver);
   conn = drivermanager
     .getconnection(jdbc:oracle:thin:pas_perf/pas_perf@localhost:1521:orcl);
   call = conn.preparecall({call fenye(?,?,?,?,?,?)});
   call.setstring(1, book);
   call.setint(2, 10);
   call.setint(3, 1);
   call.registeroutparameter(4, oracle.jdbc.oracletypes.integer);
   call.registeroutparameter(5, oracle.jdbc.oracletypes.integer);
   call.registeroutparameter(6, oracle.jdbc.oracletypes.cursor);
   call.execute();
   //取出返回值 要注意问好的顺序
   int num = call.getint(4);
   int mypagecount = call.getint(5);
   rs = (resultset)call.getobject(6);
   if (rs != null)
    while (rs.next()) {
     system.out.println(num + \t + mypagecount + \t + rs.getstring(2) + \t + rs.getstring(3) + \t + rs.getstring(4));
    }
  } catch (exception e) {
   e.printstacktrace();
  } finally {
   try {
    if (rs != null)
     rs.close();
    if (call != null)
     call.close();
    if (conn != null)
     conn.close();
   } catch (exception e) {
    e.printstacktrace();
   }
  }
 }
}
，