Java_procedure with return value(oracle)

Java调用Oracle中有返回值的存储过程

1) 在编写存储过程时，输入参数用in(如果不写默认为in),输出参数用out

-- 编写过程，要求输入雇员编号，返回雇员姓名。  

create or replace procedure getNameByNo(no in number, name out varchar2) is  

begin  

  select ename into name from emp where empno = no;  

end;   

-- 输入部门号，返回该部门所有员工  

-- 先建一个包，定义一个游标类型  

create or replace package pkg_cursor is  

  type my_cursor_type is ref cursor;  

end pkg_cursor;  

-- 创建过程  

create or replace procedure getByDeptno(depno in number, emp_cursor out pkg_cursor.my_cursor_type) is  

begin  

  open emp_cursor for  

  select * from emp where deptno = depno;  

end;

2) java 中调用的过程

a. 注册驱动类,并获取数据库连接

b. 用过程调用SQL语句(用{}括起来)获取CallableStatement对象。

c. 设置输入参数,如：cs.setInt(1, 7788);

d. 注册输出参数,如：cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);

e. 执行过程cs.execute();

f. 获取过程返回结果,如：ResultSet rs = (ResultSet)cs.getObject(2);

g. 最后在finally中关闭资源。

import java.sql.CallableStatement;  
import java.sql.Connection;  
import java.sql.DriverManager;  
import java.sql.ResultSet;  
public class TestPro1 {  
    static final int empno = 7788;  
    static final int depno = 10;  
    public static void main(String[] args) {  
        Connection con = null;  
        CallableStatement cs = null;  
        try {  
            //注册JDBC驱动类  
            Class.forName("oracle.jdbc.driver.OracleDriver");  
            //获取连接  
            con = DriverManager.  
                    getConnection("jdbc:oracle:thin:@localhost:1521:orcltest", "test", "mm");  
            /************************* getNameByNo start ****************************/  
            cs = con.prepareCall("{call getNameByNo(?,?)}");  
            //设置参数  
            cs.setInt(1, empno);  
            //注册输出参数  
            cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);  
            //执行过程  
            cs.execute();  
              
            //获取结果  
            String ename = cs.getString(2);  
            System.out.println("编号为" + empno + " 的姓名为：" + ename);  
            //编号为7788 的姓名为：SCOTT  
            /************************* getNameByNo  end  ****************************/  
              
            /************************* getByDeptno start ****************************/  
            cs = con.prepareCall("{call getByDeptno(?, ?)}");  
            //设置参数  
            cs.setInt(1, depno);  
            //注册输出参数  
            cs.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);  
            cs.execute();  
            //得到结果集  
            ResultSet rs = (ResultSet)cs.getObject(2);  
            System.out.println("部门号为" + depno + "的所有员工如下：");  
            while(rs.next()) {  
                System.out.println("员工编号："+rs.getInt(1)+",员工姓名："+rs.getString(2));
            }  
            /************************* getByDeptno  end  ****************************/  
            //关闭资源(应在finally, 由于是示例就在这里关了)  
            cs.close();  
            con.close();  
        } catch (Exception e) {  
            e.printStackTrace();  
        }  
    }    
}

注意事项：

1.对于存储过程的输入参数，使用setXXX；对于输出参数，使用registerOutParameter,问号的顺序要对应，同时需要考虑类型。

2.取出存储过程返回值的方法是CallableStatement提供的getXX()注意输出参数的位置，同时也需要考虑输出参数的类型。

附:

1、什么是存储过程。存储过程是数据库服务器端的一段程序，它有两种类型。一种类似于SELECT查询，用于检索数据，检索到的数据能够以数据集的形式返回给客户。另一种类似于INSERT或DELETE查询，它不返回数据，只是执行一个动作。有的服务器允许同一个存储过程既可以返回数据又可以执行动作。

2、什么时候需要用存储过程

　　如果服务器定义了存储过程，应当根据需要决定是否要用存储过程。存储过程通常是一些经常要执行的任务，这些任务往往是针对大量的记录而进行的。在服务器上执行存储过程，可以改善应用程序的性能。这是因为：

　　.服务器往往具有强大的计算能力和速度。

　　.避免把大量的数据下载到客户端，减少网络上的传输量。

　　例如，假设一个应用程序需要计算一个数据，这个数据需要涉及到许多记录。如果不使用存储过程的话，把这些数据下载到客户端，导致网络上的流量剧增。

　　不仅如此，客户端可能是一台老掉牙的计算机，它的运算速度很慢。而改用存储过程后，服务器会很快地把数据计算出来，并且只需传递一个数据给客户端，其效率之高是非常明显的。

3、存储过程的参数

　　要执行服务器上的存储过程，往往要传递一些参数。这些参数分为四种类型：

　　第一种称为输入参数，由客户程序向存储过程传递值。

　　第二种称为输出参数，由存储过程向客户程序返回结果。

　　第三种称为输入/输出参数，既可以由客户程序向存储过程传递值，也可以由存储过程向客户程序返回结果。

　　第四种称为状态参数，由存储过程向客户程序返回错误信息。

要说明的是，并不是所有的服务器都支持上述四种类型的参数，例如，InterBase就不支持状态参数。