Java_procedure with return value(oracle)

via: http://slbszq.iteye.com/blog/2008076

 

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就不支持状态参数。