oracle存储过程调用，以及返回游标的存储过程调用

create or replace procedure test_xg_p2(a in number,x out number) is
begin
x:=a;
end test_xg_p2;

java代码调用
public void test1() {
try {
String cnstr = "jdbc:oracle:thin:@127.0.0.1:1521:fruitkingdb";
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection(cnstr, "fruitking","123456");
CallableStatement cstmt = conn.prepareCall("{call test_xg_p2(?,?)}");
cstmt.setInt(1,105);
cstmt.registerOutParameter(2, OracleTypes.INTEGER);
cstmt.execute();
System.out.println("return value is " + cstmt.getInt(2));
cstmt.close();
} catch (Exception e) {
e.printStackTrace();
}
}

create or replace procedure test_xg_p3(a in number,x out varchar2) is
begin
if a>=90 then
begin
x := 'A';
end;
end if;
if a<90 then
begin
x:='B';
end;
end if;
if a<80 then
begin
x:='C';
end;
end if;
if a<70 then
begin
x:='D';
end;
end if;
if a<60 then
begin
x:='E';
end;
end if;
end test_xg_p3;

java调用
public void test2() {
try {
String cnstr = "jdbc:oracle:thin:@127.0.0.1:1521:fruiktingdb";
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection(cnstr, "fruitking","123456");
CallableStatement cstmt = conn.prepareCall("{call test_xg_p3(?,?)}");
cstmt.setInt(1,87);
cstmt.registerOutParameter(2, OracleTypes.VARCHAR);
cstmt.execute();
System.out.println("return value is " + cstmt.getString(2));
cstmt.close();
} catch (Exception e) {
e.printStackTrace();
}
}

带游标的存储过程
--为游标创建一个包，并定义一种类型
create or replace package into_xgtest
as
type cursorType is ref cursor;
end;

--创建存储过程,游标类型返回参数
create or replace procedure test_xg_p7(v_companyName in varchar,p_cursor out into_xgtest.cursorType) is
-- vs_sql varchar2(2000);
--v_companyName varchar2(100); --公司名称
begin
OPEN p_cursor FOR
select t.hotelid,t.hotelname
from hotel t
where t.hotelname like v_companyName||'%'
order by t.hotelid asc;
null;
end test_xg_p7;

java调用
public void test3(){
try {
String driver_class = "oracle.jdbc.driver.OracleDriver";
String cnStr = "jdbc:oracle:thin:@127.0.0.1:1521:fruitking";
Connection conn = null;
Class.forName(driver_class);
conn = DriverManager.getConnection(cnStr, "fruitking","123456");
CallableStatement cstmt = conn.prepareCall("{call test_xg_p7(?,?)}");
cstmt.setString(1,"浙江");
cstmt.registerOutParameter(2, OracleTypes.CURSOR);
cstmt.execute();
ResultSet rset = (ResultSet) cstmt.getObject(2);
while (rset.next()) {
System.out.println("id->" + rset.getString(1) + " name->" + rset.getString(2));
}
cstmt.close();
} catch (Exception e) {
e.printStackTrace();
}
}