Java 调用Oracle 存储过程

1、存储过程是指保存在数据库并在数据库端执行的程序，它有两种类型。一种类似于SELECT查询，用于检索数据，检索到的数据能够以数据集的形式返回给客户。另一种类似于INSERT或DELETE查询，它不返回数据，只是执行一个动作。有的服务器允许同一个存储过程既可以返回数据又可以执行动作。Oracle使用PL/SQL，PostgreSQL使用pl/pgsql，DB2使用Procedural SQL.
2、什么时候需要用oracle存储过程(好处)
　　如果服务器定义了存储过程，应当根据需要决定是否要用oracle存储过程。oracle存储过程通常是一些经常要执行的任务，这些任务往往是针对大量的记录而进行的。在服务器上执行存储过程，可以改善应用程序的性能。这是因为：
　　.服务器往往具有强大的计算能力和速度。
.避免把大量的数据下载到客户端，减少网络上的传输量。
Java代码中并不关心数据库模式是怎么实现的，因为它仅调用存储过程。我们以后可以改变数据库模式以提高性能，但是我们不必修改我们代码。
　　例如，假设一个应用程序需要计算一个数据，这个数据需要涉及到许多记录。如果不使用存储过程的话，把这些数据下载到客户端，导致网络上的流量剧增。
　　不仅如此，客户端可能是一台老掉牙的计算机，它的运算速度很慢。而改用存储过程后，服务器会很快地把数据计算出来，并且只需传递一个数据给客户端，其效率之高是非常明显的。
3、oracle存储过程的参数
　　要执行服务器上的存储过程，往往要传递一些参数。这些参数分为四种类型：
　　第一种称为输入参数，由客户程序向存储过程传递值。
　　第二种称为输出参数，由存储过程向客户程序返回结果。
　　第三种称为输入/输出参数，既可以由客户程序向存储过程传递值，也可以由存储过程向客户程序返回结果。
　　第四种称为状态参数，由oracle存储过程向客户程序返回错误信息。
　　要说明的是，并不是所有的服务器都支持上述四种类型的参数，例如，InterBase就不支持状态参数。
4、oracle存储过程的基本语法
　　1.基本结构
　　CREATE OR REPLACE PROCEDURE oracle存储过程名字
( 参数1 IN NUMBER, 参数2 IN NUMBER)
IS/A S
　　变量1 INTEGER :=0;
　　变量2 DATE;
　　BEGIN
　　END oracle存储过程名字
　　2. SELECT INTO STATEMENT
　　将select查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条
　　记录，否则抛出异常(如果没有记录抛出NO_DATA_FOUND)
　　例子：
　　BEGIN
　　SELECT col1,col2 into 变量1, 变量2 FROM tables1 where xxx;
　　EXCEPTION
　　WHEN NO_DATA_FOUND THEN
　　xxxx;
　　END;

　　public  class TestProcedureOne {
　　	public  TestProcedureOne() { }
　　	public static void main(String[] args ){
　　		String driver = "oracle.jdbc.driver.OracleDriver";
　　		String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521: hyq ";
　　		Statement stmt = null;
　　		ResultSet rs = null;
　　		Connection conn = null;
　　		CallableStatement cstmt = null;

　　		try {
　　			Class.forName(driver);
　　			conn = DriverManager.getConnection(strUrl, " hyq ", " hyq ");
　　			CallableStatement proc = null;
　　			proc = conn.prepareCall("{ call HYQ.TESTA(?,?) }");
　　			proc.setString(1, "100");
　　			proc.setString(2, "TestOne");
　　			proc.execute();
　　		}
　　		catch (SQLException ex2) {
　　			ex2.printStackTrace();
　　		}
　　		catch (Exception ex2) {
　　			ex2.printStackTrace();
　　		}
　　		finally{
　　			try {
　　				if(rs != null){
　　					rs.close();
　　					if(stmt!=null){
　　						stmt.close();
　　					}
　　					if(conn!=null){
　　						conn.close();
　　					}
　　				}
　　			}
　　			catch (SQLException ex1) {
　			}
　　		}
　　	}
　　}

二、有返回值的存储过程（非列表）
IN表示向存储过程传递参数，OUT表示从存储过程返回参数
存储过程为：(根据雇员号查工资)

create or replace procedure selemp_sal (para1 in number,para2 out number)
as
begin
  select sal into para2 from emp where empno=para1;
end selemp_sal;

在数据库的SQL窗口中调用：

declare     
  vstr2 varchar2(10); 
begin  
  selemp_sal(7654,vStr2);--//7654为雇员号
  dbms_output.put_line(vStr2);   
end;

在JAVA里调用就用下面的的代码：

         Class.forName(driver);
	conn=DriverManager.getConnection(url,”scott”,”tiger”);
	CallableStatement cstmt=null;
	cstmt=conn..prepareCall(“{call selemp_sal(?,?)}”);
	cstmt..setInt(1, 7654);
	cstmt.registerOutparameter(2,Types.VARCHAR);
	cstmt.execute();
	String thesal=cstmt.getString(2);
	System.out.pringln(“The people’s salary is ”+thesal);

【注：emp表为oracle数据库方案scott中的一个表,
这里的cstmt.getString(2) 中的数值2并非任意的，而是和存储过程的out列对应的，如果out是在第一个位置，那就是cstmt.getString(1)，如果out是在第三个位置，那就是cstmt.getString(3)，当然可以有多个返回值，那就是多增加几个out参数啦】

三、返回列表
由于oracle 存储过程没有返回值，他的所有返回值都是通过out参数来替代的，列表也同样不例外，但由于是集合，所以不能用一般的参数，必须要用pagkage了，所以要分两部分，
1. 建立一个程序包，如下：
create or replace package mypackage as
TYPE my_cursor is REF CURSOR;
end mypackage;
2.建立存储过程，存储过程为：（查询工资等级）

create or replace procedure selsal_grade (p_cur out mypackage.my_cursor)
as
begin
  open p_cur for select * from salgrade;
end selsal_grade; 

可以看到，它把一个游标（可以理解为一个指针），作为一个out参数来返回的
在Oracle的SQL窗口中调用用下面的代码：

declare     
  v_cur mypackage.my_cursor; 
  grad salgrade%rowtype;
begin 
  selsal_grade(v_cur);
  dbms_output.put_line('等级   最高工资   最低工资');
  loop 
    fetch v_cur into grad;
    exit when v_cur%NOTFOUND;
    dbms_output.put_line(grad.grade||'      ' ||grad.losal ||'      '||grad.hisal);  
  end loop;  
  close v_cur;
end;

调用:

CallableStatement cstmt=null;
	cstmt=conn..prepareCall(“{call selsal_grade (?)}”);
	cstmt.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR);
	cstmt.execute();
	rs=(ResultSet) cstmt.getObject(1);
	System.out.println(“等级   最高工资   最低工资”);
	while(rs.next())
          {
	    System.out.println(rs.getString(1)+”  ”|+rs.getString(2)+”  ”+rs.getString(3));
           }