--语句块
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();
}
}