Oracle存储过程:
代码语言：javascript复制CREATE OR REPLACE PROCEDURE getcity (
citycode IN VARCHAR2,
ref_cursor OUT sys_refcursor /* 这个sys_refcursor类型在SYS.STANDARD包中 */
)
AS
BEGIN
OPEN ref_cursor FOR
SELECT *
FROM tb_city_code
WHERE city_code = citycode;
END;
/Java调用代码:
代码语言：javascript复制try {
DriverManager.registerDriver (new oracle.jdbc.OracleDriver());
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:wjw", "sms", "sms");
CallableStatement stmt = conn.prepareCall("BEGIN GETCITY(?, ?); END;");
//CallableStatement stmt = conn.prepareCall("{ call GETCITY(?, ?) }"); //用此调用方法不能实现多行语法
stmt.setString(1, "021");
stmt.registerOutParameter(2, -10 /* OracleTypes.CURSOR = -10 */); //REF CURSOR(OracleTypes.CURSOR==-10)
stmt.execute();
ResultSet rs = (ResultSet) stmt.getObject(2);
while (rs.next()) {
System.out.println(rs.getString("CITY_NAME"));
}
rs.close();
rs = null;
stmt.close();
stmt = null;
conn.close();
conn = null;
}
catch (SQLException e) {
System.out.println(e.getLocalizedMessage());
}
