JAVA调用存储过程
1)无返回值
java:
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL", "scott", "Changeme_123");
CallableStatement cs = conn.prepareCall("{call sp_pro10(?,?,?)}");
cs.setInt(1, 1);
cd.setString(2,"倚天屠龙记");
cd.setString(3,"清华大学出版社");
cs.execute();
cs.close();
conn.close();
对应oracle存储过程
create or replace procedure sp_pro10(v_id in number,v_bookname in varchar2,v_publishHouse in varchar2) is
begin
insert into book values(v_id,v_bookname,v_publishHouse);
end;
/
2)有一行返回值
java:
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL", "scott", "Changeme_123");
CallableStatement cs = conn.prepareCall("{call sp_pro11(?,?,?)}");
cs.setInt(1, 1);
cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);
cs.registerOutParameter(3, oracle.jdbc.OracleTypes.VARCHAR);
cs.execute();
System.out.println(cs.getString(2));
System.out.println(cs.getString(3));
cs.close();
conn.close();
对应oracle存储过程:
create or replace procedure sp_pro11(v_id in number,v_bookname out varchar2,v_publishHouse out varchar2) is
begin
select bookName,publishHouse into v_bookname,v_publishHouse from book where id=v_id;
end;
/
3)有多行返回值
java:
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL", "scott", "Changeme_123");
CallableStatement cs = conn.prepareCall("{call sp_pro12(?)}");
cs.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
cs.execute();
ResultSet rs=(ResultSet)cs.getObject(1);
while(rs.next()){
System.out.println(rs.getString(2));
}
cs.close();
conn.close();
对应oracle存储过程:
create or replace package testpackage as
type test_cursor is ref cursor;
end testpackage;
create or replace procedure sp_pro12(p_cursor out testpackage.test_cursor) is
begin
open p_cursor for select * from book;
end;
/
1)无返回值
java:
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL", "scott", "Changeme_123");
CallableStatement cs = conn.prepareCall("{call sp_pro10(?,?,?)}");
cs.setInt(1, 1);
cd.setString(2,"倚天屠龙记");
cd.setString(3,"清华大学出版社");
cs.execute();
cs.close();
conn.close();
对应oracle存储过程
create or replace procedure sp_pro10(v_id in number,v_bookname in varchar2,v_publishHouse in varchar2) is
begin
insert into book values(v_id,v_bookname,v_publishHouse);
end;
/
2)有一行返回值
java:
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL", "scott", "Changeme_123");
CallableStatement cs = conn.prepareCall("{call sp_pro11(?,?,?)}");
cs.setInt(1, 1);
cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);
cs.registerOutParameter(3, oracle.jdbc.OracleTypes.VARCHAR);
cs.execute();
System.out.println(cs.getString(2));
System.out.println(cs.getString(3));
cs.close();
conn.close();
对应oracle存储过程:
create or replace procedure sp_pro11(v_id in number,v_bookname out varchar2,v_publishHouse out varchar2) is
begin
select bookName,publishHouse into v_bookname,v_publishHouse from book where id=v_id;
end;
/
3)有多行返回值
java:
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL", "scott", "Changeme_123");
CallableStatement cs = conn.prepareCall("{call sp_pro12(?)}");
cs.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
cs.execute();
ResultSet rs=(ResultSet)cs.getObject(1);
while(rs.next()){
System.out.println(rs.getString(2));
}
cs.close();
conn.close();
对应oracle存储过程:
create or replace package testpackage as
type test_cursor is ref cursor;
end testpackage;
create or replace procedure sp_pro12(p_cursor out testpackage.test_cursor) is
begin
open p_cursor for select * from book;
end;
/