java 调用存储过程 实例

共4个一.只有输入参数没有返回结果的存储过程二. 有输入和有一个返回值的存储过程三、返回多行记录的存储过程（返回的是游标）四、Hibernate调用oracle存储过程 一.只有输入参数没有返回结果的存储过程建表 create table table_1(id varchar2(10),name varchar2(10)); --没有返回值的存储过程create or replace procedure prc_1(t_id in varchar2,t_name in varchar2)as begin insert into table_1 values(t_id,t_name); end prc_1; 1.在sql plus 中执行SQL> exec prc_1('1','杨2');PL/SQL procedure successfully completed2. 在java 中执行，返回结果 Class.forName("oracle.jdbc.OracleDriver").newInstance(); String url = "jdbc:oracle:thin:atf_ygj/mas123@192.168.6.19:1531:orcl"; String user = "atf_ygj"; String password = "mas123"; conn = DriverManager.getConnection(url, user, password); conn.setAutoCommit(false); cstmt = conn.prepareCall("{ call prc_1(?,?) }"); cstmt.setString(1, "100"); cstmt.setString(2, "TestOne"); cstmt.execute(); conn.commit(); ========================================二. 有输入和有一个返回值的存储过程--有一个返回值的存储过程create or replace procedure prc_2(t_id IN VARCHAR2,t_name OUT VARCHAR2) ASbegin SELECT name INTO t_name FROM table_1 WHERE id= t_id and rownum<2 end="" prc_2="" 1="" sql="" plus="" sql=""> var name varchar2(100);SQL> exec prc_2('1',:name);PL/SQL procedure successfully completedname---------杨22. 在java 中执行，返回结果 Class.forName("oracle.jdbc.OracleDriver").newInstance(); String url = "jdbc:oracle:thin:atf_ygj/mas123@192.168.6.19:1531:orcl"; String user = "atf_ygj"; String password = "mas123"; conn = DriverManager.getConnection(url, user, password); conn.setAutoCommit(false); cstmt = conn.prepareCall("{ call prc_2(?,?) }"); cstmt.setString(1, "1"); cstmt.registerOutParameter(2, Types.VARCHAR); cstmt.execute(); conn.commit(); String testPrint = cstmt.getString(2); System.out.println("返回值：：：：：： " + testPrint);输出：返回值：：：：：： 杨2 三、返回多行记录的存储过程（返回的是游标） 由于oracle存储过程没有返回值，它的所有返回值都是通过out参数来替代的，列表同样也不例外，但由于是集合，所以不能用一般的参数，必须要用pagkage了.所以要分两部分，1， 建一个程序包。create or replace package test_package as type test_CURSOR is ref cursor;end test_package;2，建立存储过程，存储过程为：create or replace procedure prc_3(p_CURSOR out test_package.test_CURSOR) is begin OPEN p_CURSOR FOR SELECT * FROM table_1 order by id;end prc_3;可以看到，它是把游标（可以理解为一个指针），作为一个out 参数来返回值的。===========================3.在java中的执行 Class.forName("oracle.jdbc.OracleDriver").newInstance(); String url = "jdbc:oracle:thin:atf_ygj/mas123@192.168.6.19:1531:orcl"; String user = "atf_ygj"; String password = "mas123"; conn = DriverManager.getConnection(url, user, password); cstmt = conn.prepareCall("{ call prc_3(?) }"); cstmt.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR); cstmt.execute(); rs = (ResultSet) cstmt.getObject(1); while (rs.next()) { System.out.println("" + rs.getString(1) + "" + rs.getString(2) + ""); }输出结果：1杨2100TestOne100杨1100TestOne200Test24.在sql plus中执行用一个存储过程调用prc_3create or replace procedure prc_4 isc test_package.test_CURSOR; id varchar2(100); name varchar2(100); begin prc_3(c); LOOP FETCH c INTO id,name; EXIT WHEN c%NOTFOUND; DBMS_OUTPUT.PUT_LINE('=='||id||'==='||name); END LOOP; CLOSE c; end prc_4;执行SQL> set serveroutput on;SQL> exec dbms_output.enable(999999999999999999999);PL/SQL procedure successfully completedSQL> exec prc_4;==1===杨2==100===TestOne==100===杨1==100===TestOne==200===Test2PL/SQL procedure successfully completed 四、Hibernate调用oracle存储过程private void prc_test() { getHibernateTemplate().execute(new HibernateCallback() { public Object doInHibernate(Session session) throws HibernateException, SQLException { CallableStatement cstmt = session.connection().prepareCall("{ call prc_3(?) }"); cstmt.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR); cstmt.execute(); ResultSet rs = (ResultSet) cstmt.getObject(1); while (rs.next()) { System.out.println("" + rs.getString(1) + "" + rs.getString(2) + ""); } return null; } }, true); }