Java执行Oracle存储过程

1 创建表和存储过程，并在DB测试存储过程：

==============================
CREATE TABLE ZTEST 
  (
    NAME VARCHAR2(10),
    ID   NUMBER
  )
==============================

==============================
create or replace
PROCEDURE usp_ztest (tid IN NUMERIC,out_cursor OUT SYS_REFCURSOR)
AS
   temp   VARCHAR2 (100);
BEGIN
    DBMS_OUTPUT.put_line ('id is ==='||tid); 
    OPEN out_cursor FOR  
    select * from ztest where id = tid;
    RETURN;
END usp_ztest;
==============================

set serveroutput  on;
declare 
out_cursor SYS_REFCURSOR;
v_row ZTEST%ROWTYPE;
begin
    usp_ztest(3,out_cursor);
    loop 
      fetch out_cursor into v_row;
      exit when out_cursor%notfound;
        dbms_output.put_line('--'||v_row.id||'--'||v_row.name);
    end loop;
    close out_cursor;
    commit;

exception 
  when too_many_rows then
  DBMS_OUTPUT.PUT_LINE('返回值多于1行');
  when others then
  DBMS_OUTPUT.PUT_LINE('在RUNBYPARMETERS过程中出错！');
end;

==============================
或者：
==============================
set serveroutput on;
declare 
out_cursor SYS_REFCURSOR;
v_id NUMBER;
v_name varchar2(20);
begin
    usp_ztest(5,out_cursor);
    fetch out_cursor into  v_name,v_id;
    while out_cursor%found 
    loop
      dbms_output.put_line('--'||v_id||'--'||v_name);
      fetch out_cursor into v_name,v_id;
    end loop;
    close out_cursor;
    commit;
exception 
  when too_many_rows then
  DBMS_OUTPUT.PUT_LINE('返回值多于1行');
  when others then
  DBMS_OUTPUT.PUT_LINE('在RUNBYPARMETERS过程中出错！');
  dbms_output.put_line(SQLCODE || '::'||SQLERRM);  
end;

2 Java调用存储过程

Session session = getSessionFactory().openSession();
session.beginTransaction();
Connection conn = ((SessionImplementor)session).connection();
CallableStatement   proc   =   null; 
proc = conn.prepareCall("{call usp_ztest(?,?)}");
proc.setInt(1, 3);
proc.registerOutParameter(2, OracleTypes.CURSOR);
proc.executeUpdate();
ResultSet result = (ResultSet)proc.getObject(2);
while(result.next()){
 System.out.println(result.getString("name"));
}
session.getTransaction().commit();
session.close();