一、返回一个值
--创建存储过程 create or replace procedure sp_hu_test(spcode in varchar2,spname out varchar2)is
begin
select cname into spname from gb_customerinfo where ccode = spcode;
end;
//java代码调用和接受返回值
public static void main(String[] arg) {
try { Class.forName("oracle.jdbc.driver.OracleDriver"); Connection cnn = DriverManager.getConnection(
"jdbc:oracle:thin:@192.168.1.116:1521:cwerp",
"hu_gbxt", "hu_gbxt");
//创建CallableStatement
CallableStatement cs = cnn.prepareCall("{call sp_hu_test(?,?)}"); cs.setString(1, "082");
cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR); cs.execute();
String cname = cs.getString(2);
System.out.println("名字：" + cname); cs.close();
cnn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
二、返回多个值
--创建一个包，包里有个游标变量 create or replace package mypackage as
type sp_cursor is ref cursor;
end mypackage; --创建存储过程
create or replace procedure sp_proc(carcode in varchar2,sp_coesor out mypackage.sp_cursor)is
begin
open sp_coesor for select * from gb_truck where cardcode = carcode;
end;
//注解，这里面的输出参数是个游标类型
//java调用存储过程
public static void main(String[] arg) {
try { Class.forName("oracle.jdbc.driver.OracleDriver"); Connection cnn = DriverManager.getConnection(
"jdbc:oracle:thin:@192.168.1.116:1521:cwerp",
"hu_gbxt", "hu_gbxt");
//创建CallableStatement
CallableStatement cs = cnn.prepareCall("{call sp_proc(?,?)}");
cs.setString(1, "皖1958707");
cs.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);
cs.execute();
ResultSet rs = (ResultSet)cs.getObject(2);
while(rs.next()){
System.out.println(rs.getString(1) + " " + rs.getString(2) + " " + rs.getString(4)); //关闭
cs.close();
cnn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}