一:无返回值的存储过程调用
存储过程:
CREATE OR REPLACE PROCEDURE PRO_1(PARA1 IN VARCHAR2,PARA2 IN VARCHAR2) AS BEGIN INSERT INTO DBO.EMP (ID,NAME) VALUES (PARA1, PARA2); END PRO_1;
Java代码:
import java.sql.*;
import java.sql.ResultSet;
public class CallProcedureTest1 {
public CallProcedureTest1() {
super();
}
public static void main(String[] args) {
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl ";
String user = "admin";
String pwd = "password";
Connection conn = null;
CallableStatement cs = null;
ResultSet rs = null;
try {
Class.forName(driver);
conn = DriverManager.getConnection(url, user, pwd);
cs = conn.prepareCall("{ call DBO.PRO_1(?,?) }");
cs.setString(1, "10");
cs.setString(2, "Peter");
cs.execute();
} catch (SQLException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (rs != null) {
rs.close();
}
if (cs != null) {
cs.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
}
}
}
}
备注,存储过程PRO_1中用到了表EMP(ID, NAME),需事先建好
二:有返回值的存储过程(非结果集)
存储过程:
CREATE OR REPLACE PROCEDURE PRO_2(PARA1 IN VARCHAR2,PARA2 OUT VARCHAR2) AS BEGIN SELECT INTO PARA2 FROM EMP WHERE ID= PARA1; END PRO_2;
Java代码:
import java.sql.*; public class CallProcedureTest2 { public CallProcedureTest2() { super(); } public static void main(String[] args) { String driver = "oracle.jdbc.driver.OracleDriver"; String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl"; String user = "admin"; String pwd = "password"; Connection conn = null; CallableStatement cs = null; ResultSet rs = null; try { Class.forName(driver); conn = DriverManager.getConnection(url, user, pwd); cs = conn.prepareCall("{ call DBO.PRO_2(?,?) }"); cs.setString(1, "10"); cs.registerOutParameter(2, Types.VARCHAR); cs.execute(); String name = cs.getString(2); System.out.println("name: " + name); } catch (SQLException e) { e.printStackTrace(); } catch (Exception e) { e.printStackTrace(); } finally { try { if (rs != null) { rs.close(); } if (cs != null) { cs.close(); } if (conn != null) { conn.close(); } } catch (SQLException e) { } } } }
注意:cs.getString(2)中的数值2并非任意的,而是和存储过程中的out列对应的,如果out是在第一个位置,那就是proc.getString(1),如果是第三个位置,就是proc.getString(3),当然也可以同时有多个返回值,那就是再多加几个out参数了。
三:返回列表
由于oracle存储过程没有返回值,它的所有返回值都是通过out参数来替代的,列表同样也不例外,但由于是集合,所以不能用一般的参数,必须要用pagkage了.所以要分两部分,
1.建一个程序包。如下:
CREATE OR REPLACE PACKAGE MYPACKAGE AS TYPE MY_CURSOR IS REF CURSOR; END MYPACKAGE;
2.建立存储过程,如下:
CREATE OR REPLACE PROCEDURE PRO_3(p_CURSOR out MYPACKAGE.MY_CURSOR) IS BEGIN OPEN p_CURSOR FOR SELECT * FROM DBO.EMP; END PRO_3;
可以看到,它是把游标(可以理解为一个指针),作为一个out 参数来返回值的。
Java代码:
import java.sql.*; import java.sql.ResultSet; public class CallProcedureTest2 { public CallProcedureTest2() { super(); } public static void main(String[] args) { String driver = "oracle.jdbc.driver.OracleDriver"; String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl"; String user = "admin"; String pwd = "password"; Connection conn = null; CallableStatement cs = null; ResultSet rs = null; try { Class.forName(driver); conn = DriverManager.getConnection(url, user, pwd); cs = conn.prepareCall("{ call DBO.PRO_3(?) }"); cs.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR); cs.execute(); rs = (ResultSet) cs.getObject(1); while (rs.next()) { System.out.println("\t" + rs.getString(1) + "\t" + rs.getString(2) + "\t"); } } catch (SQLException e) { e.printStackTrace(); } catch (Exception e) { e.printStackTrace(); } finally { try { if (rs != null) { rs.close(); if (cs != null) { cs.close(); } if (conn != null) { conn.close(); } } } catch (SQLException e) { } } } }
注意:在执行前一定要先把oracle的驱动包放到class路径里,否则会报错。