1有一个返回值的例子
首先我们以一个简单的例子开始,在这个例子中,只存在一个返回值。存储过程如下
SQL> create or replace procedure lizhe (aa out varchar2)is
2 begin
3 select password into aa from passport where id='123111';
4 end lizhe;
5
6 /
补充说明:要注意的是这个存储过程存在一个(aa out varchar2)这样的标识,这是一个参数,out表示这是一个输出参数,在select语句中将password的值“into”到这个参数里
然后再java端:
public static void runProcedure(){
try {
Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@192.168.0.159:1521:TOPS","YAC3","YAC3");
java.sql.CallableStatement cs = con.prepareCall("{call lizhe(?)}");
cs.registerOutParameter(1, java.sql.Types.VARCHAR);
cs.execute();
System.out.print("value2:" + cs.getString(1)); //取出字符串——关键
} catch (InstantiationException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalAccessException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
补充说明:cs.registerOutParameter(1, java.sql.Types.VARCHAR);
重点只有这一句
2.多个返回值的
SQL> CREATE OR REPLACE PACKAGE TESTPACKAGE AS
2
3 TYPE Test_CURSOR IS REF CURSOR;
4
5 end TESTPACKAGE;
6 /
Package created
SQL> CREATE OR REPLACE PROCEDURE TESTC(p_CURSOR out TESTPACKAGE.Test_CURSOR) IS
2
3 BEGIN
4
5 OPEN p_CURSOR FOR 'SELECT * FROM TB';
6
7 END TESTC;
8 /
Procedure created