oracle 复习体系四 PL/SQL(procedure,pagckage cursor)--分页处理

--语句块

conn scott/tiger;

 

declare

v_ename varchar2(10);

begin

   select ename into v_ename from emp;

end;

 

 

--create procedure single result

 

creaate or replace procedure pro_getName(v_empno in number,v_ename out varchar2) is

begin

   select ename into v_ename from emp where empno=v_empno;

end;

 

--java  code

 

//loading driver

 

Class.forName("oracle.jdbc.driver.OracleDriver");

 

//getConnection

 

Connection conn = DriverManager.getConnection("jdbc:oracle:thin@localhost:1521:orcl","scott","tiger");

 

//getStatement

 

CallableStatement cs = conn.prepareCall("{pro_getName(?,?)}");

 

//setParameter

 

cs.setInt(1,111);

 

//registerOutParameter

 

cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);

 

//execute();

 

cs.execute();

 

//getValue

 

String ename= cs.getString(2);

//close resource

cs.close();

conn.close();

 

 

--create procedure (resultSet) before create package ,cursor

 

--create package

 

create or replace package myPackage as

 type p_myemp is ref cursor;

end myPackage;

 

--procedure

 

create or replace procedure pro_resultSet(v_cursor out myPackage.p_myemp) is

begin

   open v_cursor is select  * from emp;

end;

 

 

--java code

//loading driver

 

Class.forName("oracle.jdbc.driver.OracleDriver");

 

//getConnection

 

Connection conn = DriverManager.getConnection("jdbc:oracle:thin@localhost:1521:orcl","scott","tiger");

 

//getStatement

 

CallableStatement cs = conn.prepareCall("{pro_getName(?)}");

 

//registerOutParameter

 

cs.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR);  //type is cursor

 

//execute();

 

cs.execute();

 

//getValue

ResultSet rs = cs.getObject(1);

//print value

while(rs.next()){

   System.out.println(rs.get(0));

}

//close resource

rs.close();

cs.close();

conn.close();

 

 

 

 

 

 

 

--cursor

 

declare

cursor cs_emp is select empno,ename from emp;

v_no number(4);

v_name varchar2(20);

begin

   open cs_emp;

   fetch cs_emp into v_no,v_name;

   loop

   dbms_output.put_line(v_no || v_name);

   exit when cs_emp%notfound;

  end loop;

end;

 

 

declare

cursor cs_emp is select * from emp;

v_type cs_emp%rowtype;

begin

fetch cs_emp into v_type;

 dbms_output.put_line(v_type.empno|| v_type.ename|| '......');

end;

 

 

 

declare

cursor cs_emp is select * from emp;

begin

   for cs_emp into v_type loop

      dbms_output.put_line(v_type.empno|| v_type.ename|| '......');    

   end loop;

end; 

 

 

 

 

 

 --输入表名:tableName,每页显示的记录数:num,当前页数:currentPage,排序字段:filed,顺序:sequence
--返回总记录数:sums,总页数:pageCount,结果集:rs;

create or replace package tPackage as

type p_page is ref cursor;

end tPackage;

 

 

create or replace pro_page(tableName in varchar2, num in number, currentPage in number, filed in varchar2, seq in varchar2 , v_page out tPackage.p_page,total out number,pageCount out number) is

v_minSize number;

v_maxSize number:=currentPage*num;

v_str varchar2(100);

begin

v_minSize:=(currentPage-1)*num+1;

v_str:='select * from (select r.*,rownum rn from (select * from '||tableName||' order by '||filed||' '||seq||') r where rownum<='||maxSize||') where rn>='|| minSize;

 

open v_page  for v_str;

 

--计算总记录数和总页数

v_str :='select count(*) from '|| tableName;

 

execute immediate v_str into total;

 

--pageCount

if mod(total,num)=0 then

pageCount = total/num;

else

pageCount = total/num+1;

end if;

close v_page;

end;

 

 

 

--java  调用

public static void main(String[] args) {

       int currentPage = 1;

       int pageSize = 12;

       try {

           //load driver

           Class.forName("oracle.jdbc.driver.OracleDriver");

           //get connection

           Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","scott","tiger");

           //get CallableStatement

           CallableStatement cs = connection.prepareCall("{call pro_page(?,?,?,?,?,?,?,?)}");

           //set parammeter

           cs.setString(1, "emp");

           cs.setInt(2,pageSize);

           cs.setInt(3,currentPage);

           cs.setString(4,"sal");

           cs.setString(5,"");

           //registerOutParameter

           cs.registerOutParameter(6,oracle.jdbc.OracleTypes.INTEGER);

           cs.registerOutParameter(7,oracle.jdbc.OracleTypes.INTEGER);

           cs.registerOutParameter(8,oracle.jdbc.OracleTypes.CURSOR);

           //执行

           cs.execute();

           //总记录数

           int rows = cs.getInt(6);

           //总页数

           int pageCount = cs.getInt(7);

           //结果集

           ResultSet rs = (ResultSet) cs.getObject(8);

           System.out.println("总记录数为:"+rows+"--总页数为:"+pageCount+"----当前是第"+currentPage+"页每页显示"+pageSize+"条");

           while(rs.next()){

              System.out.println("--------------------------------");

              System.out.print("编号:"+rs.getInt(1));

              System.out.print("姓名:"+rs.getString(2));

              System.out.print("薪水:"+rs.getDouble(6));

              System.out.println("--------------------------------");

           }

       } catch (Exception e) {

           e.printStackTrace();

       }

    }