String sql="{call queryEmpInfo(?,?,?,?)}";

package com.tfy.oracle.jbbc.test;


import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;


import oracle.jdbc.OracleTypes;
import oracle.jdbc.internal.OracleCallableStatement;
import oracle.jdbc.oracore.OracleType;


import org.junit.Test;


import com.tfy.oracle.util.DbcpUtil;
import com.tfy.oracle.util.OracleJdbcUtil;


public class TestOracle {
/*
* create or replace procedure queryEmpInfo(eno in number,
pename out varchar2,
psal out number,
pjob out varchar2)
*/
// {call <procedure-name>[(<arg1>,<arg2>, ...)]}
@Test
public void ProcedureTestqueryEmpInfo(){
String sql="{call queryEmpInfo(?,?,?,?)}";
Connection conn=null;
CallableStatement call=null;
ResultSet rs=null;
try{
conn=OracleJdbcUtil.getConnection();
call=conn.prepareCall(sql);
//赋值
call.setInt(1, 7839);
//申明out参数
call.registerOutParameter(2, OracleTypes.VARCHAR);
call.registerOutParameter(3, OracleTypes.NUMBER);
call.registerOutParameter(4,OracleTypes.VARCHAR);
//执行
call.execute();

String name=call.getString(2);
double sal=call.getDouble(3);
String job=call.getString(4);

System.out.println(name);
System.out.println(sal);
System.out.println(job);

}catch(Exception e){
e.printStackTrace();

}finally{
OracleJdbcUtil.realese(conn, call, rs);
}

}
/*
* create or replace function queryEmpIncome(eno in number)
return number
*/
// {?= call <procedure-name>[(<arg1>,<arg2>, ...)]}
@Test
public void TestFunction(){
String sql="{?=call queryEmpIncome(?)}";
Connection conn=null;
CallableStatement call=null;
ResultSet rs=null;

try{
conn=OracleJdbcUtil.getConnection();
call=conn.prepareCall(sql);
//赋值
call.registerOutParameter(1, OracleTypes.NUMBER);
call.setInt(2, 7839);

//执行
call.execute();
//取出员工的年收入
// String name=call.getString(3);
double incom=call.getDouble(1);
// System.out.println(name+"的年收入为:"+incom);
System.out.println("的年收入为:"+incom);

}catch(Exception e){
e.printStackTrace();
}finally{
OracleJdbcUtil.realese(conn, call, rs);
}
}
/*
* CREATE OR REPLACE PACKAGE MYPACKAGE AS


type empcursor is ref cursor;
procedure queryEmpList(pdno in number,empList out empcursor);


END MYPACKAGE;


注意:
1. 光标是否关闭?
2. 能在mysql上运行吗?
*/
@Test
public void Testpakge(){
//{call <procedure-name>[(<arg1>,<arg2>, ...)]}
String sql="{call queryEmpList(?,?)}";
Connection conn=null;
CallableStatement call=null;
ResultSet rs=null;
try{
conn=OracleJdbcUtil.getConnection();
call=conn.prepareCall(sql);
//赋值
call.setInt(1, 20);
//申明
call.registerOutParameter(2, OracleTypes.CURSOR);
//执行
call.execute();
rs=((OracleCallableStatement)call).getCursor(2);
while(rs.next()){
String name=rs.getString("ename");
String job=rs.getString("empjob");
double sal=rs.getDouble("sal");
System.out.println("name=="+name+" job=="+job+" sal=== "+sal);
}

}catch(Exception e){
e.printStackTrace();
}finally{
OracleJdbcUtil.realese(conn, call, rs);
}
}

}