JDBC调用oracle存储过程

--声明包
create or replace package p_base
as
Type p_cur is ref cursor;
end p_base;

--游标类型
create or replace procedure test(cur out p_base.p_cur) as
begin
  open cur for
    select t.deptno, t.dname, t.loc From dept t;
end test;

--基本类型
create or replace procedure obtaindeptnames(p_dname out varchar2) as
begin
  select dname into p_dname from dept t where t.deptno = 10;
end;

//数据连接类DatabaseConnection.java
package invokeprocedure;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DatabaseConnection
{
    private Connection conn;
    
    public Connection getConnection()
    {
        String username = "scott";
        String password = "tiger";
        String url = "jdbc:oracle:thin:@10.40.71.13:1521:mdsp";
        try
        {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            conn = DriverManager.getConnection(url, username, password);
        }
        catch (ClassNotFoundException e)
        {
            e.printStackTrace();
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
        return conn;
    }
    
    public static void main(String[] args)
    {
        Connection conn1 = new DatabaseConnection().getConnection();
        System.out.print(conn1);
    }
}
//JavaBean:DepartmentInfo.java
package invokeprocedure;

public class DepartmentInfo
{
    private int deptno;
    private String deptname;
    private String loc;
    
    public int getDeptno()
    {
        return deptno;
    }
    
    public void setDeptno(int deptno)
    {
        this.deptno = deptno;
    }
    
    public String getDeptname()
    {
        return deptname;
    }
    
    public void setDeptname(String deptname)
    {
        this.deptname = deptname;
    }
    
    public String getLoc()
    {
        return loc;
    }
    
    public void setLoc(String loc)
    {
        this.loc = loc;
    }
}
//调用 QueryWithProcedure.java
package invokeprocedure;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import oracle.jdbc.OracleTypes;

public class QueryWithProcedure
{
    private Connection con;
    
    private CallableStatement cs;
    
    private ResultSet rs;
    
    private void getConn()
    {
        con = new DatabaseConnection().getConnection();
    }
    
    public List<DepartmentInfo> getResults()
    {
        getConn();
        List<DepartmentInfo> list = new ArrayList<DepartmentInfo>();
        try
        {
            cs = con.prepareCall("{call test(?)}");
            cs.registerOutParameter(1, OracleTypes.CURSOR);
            boolean ifsuccess = cs.execute();
            if (!ifsuccess)
            {
                rs = (ResultSet)cs.getObject(1);
                while (rs.next())
                {
                    DepartmentInfo dept = new DepartmentInfo();
                    dept.setDeptno(rs.getInt(1));
                    dept.setDeptname(rs.getString(2));
                    dept.setLoc(rs.getString(3));
                    list.add(dept);
                }
                return list;
            }
            else
            {
                System.out.print("execute procedure failed!");
                return list;
            }
        }
        catch (SQLException e)
        {
            e.printStackTrace();
            return list;
        }
        finally
        {
            try
            {
                rs = null;
                if (cs != null)
                    cs.close();
                if (con != null)
                    con.close();
            }
            catch (SQLException e)
            {
                e.printStackTrace();
            }
        }
    }
    
    public List<String> getDeptNames()
    {
        getConn();
        List<String> list = new ArrayList<String>();
        try
        {
            cs = con.prepareCall("{call obtaindeptnames(?)}");
            cs.registerOutParameter(1, oracle.jdbc.OracleTypes.VARCHAR);
            boolean ifsuccess = cs.execute();
            if (!ifsuccess)
            {
                String str = (String)cs.getObject(1);
                list.add(str);
                return list;
            }
            else
            {
                System.out.print("execute procedure failed!");
                return list;
            }
        }
        catch (SQLException e)
        {
            e.printStackTrace();
            return list;
        }
        catch (Exception e)
        {
            e.printStackTrace();
            return null;
        }
        finally
        {
            try
            {
                rs = null;
                if (cs != null)
                    cs.close();
                if (con != null)
                    con.close();
            }
            catch (SQLException e)
            {
                e.printStackTrace();
            }
        }
    }
    
    public static void main(String[] arg)
    {
        testGetResult();
    }
    
    private static void testGetResult()
    {
        List<DepartmentInfo> list1 = new QueryWithProcedure().getResults();
        for (DepartmentInfo di : list1)
        {
            System.out.print(di.getDeptno() + "-->" + di.getDeptname() + "-->" + di.getLoc());
        }
    }
    
    @SuppressWarnings("unused")
    private static void testGetDeptNames()
    {
        List<String> list2 = new QueryWithProcedure().getDeptNames();
        for (String str : list2)
        {
            System.out.print(str);
        }
    }
}