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

}

}