创设存储过程并用java调用实例
创建存储过程并用java调用实例
参考:http://www.blogjava.net/TrampEagle/archive/2005/12/13/23605.html
先要在pl/sql中建立相关表和存储过程
--《创建表》 create table test(id number,name varchar(20)); --《创建无返回值的存储过程》 create or replace procedure testa(para1 in number,para2 in varchar2) as begin insert into test(id,name) values (para1, para2); end testa; --《创建有返回值的存储过程》 create or replace procedure testb(para1 in number,para2 out varchar2) as begin select name into para2 from test where id= para1; end testb; --《创建有多条返回记录的存储过程》 --* 由于oracle存储过程没有返回值,它的所有返回值都是通过out参数来替代的,列表同样也不例外,但由于是集合,所以不能用一般的参数,必须要用pagkage了 --1.建一个程序包 create or replace package testpackage as type test_cursor is ref cursor; end testpackage; --2.建立存储过程 create or replace procedure testc(p_cursor out testpackage.test_cursor) is begin open p_cursor for select * from test; end testc; --测试用 select * from test delete from test --测试创建存储过程小实例,用pl/sql创建时可以在browser的my objects中查看存储过程创建的失败与否,如下图所示,有红叉图标的表示没有编译成功 create or replace procedure say as begin dbms_output.put_line('hi'); end; --执行下面语句可在pl/sql output中查看结果 begin say; end;
用eclipse建立测试类TestProcedure
import java.sql.*; public class TestProcedure { /** * @param args */ public static void main(String[] args) { String driver = "oracle.jdbc.driver.OracleDriver"; String strUrl = "jdbc:oracle:thin:@192.168.16.161:1521:ora11g"; Connection conn = null; CallableStatement cstmt = null; ResultSet rs=null; try { Class.forName(driver); conn = DriverManager.getConnection(strUrl, "wu", "1"); /** 调用无返回值的存储过程 */ cstmt = conn.prepareCall("{ call testa(?,?) }"); cstmt.setInt(1, 1); cstmt.setString(2, "TestOne"); cstmt.execute(); System.out.println("插入数据成功"); /** 调用有返回值的存储过程 */ cstmt = conn.prepareCall("{ call testb(?,?) }"); cstmt.setInt(1, 1); cstmt.registerOutParameter(2, Types.VARCHAR); cstmt.execute(); String testPrint = cstmt.getString(2); System.out.println("name:"+testPrint); /** 调用有返回值且是多条记录的存储过程 */ cstmt = conn.prepareCall("{ call testc(?) }"); cstmt.setInt(1, 1); cstmt.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR); cstmt.execute(); rs = (ResultSet)cstmt.getObject(1); while(rs.next()) { System.out.println("id:" + rs.getString(1) + " name:"+rs.getString(2)); } } catch (Exception ex) { ex.printStackTrace(); } finally{ try { if(conn!=null){ conn.close(); } if(cstmt!=null){ cstmt.close(); } }catch (Exception e) { e.printStackTrace(); } } } }
参考:http://www.blogjava.net/TrampEagle/archive/2005/12/13/23605.html