做一点,记一点 ~ Java调用Oracle存储过程
一、需求
传入一个参数,返回一条或多条记录(列表)。
二、实现步骤
1. 编写Oracle存储过程。
-- 声明包和包体的语句应该分两次执行,即使声明在前定义在后,如果一起执行依然会编译出错
-- 调用能够返回多条记录的存储过程需要定义在包内 --声明包及包内的游标、存储过程 CREATE OR REPLACE PACKAGE pack_myPackage IS TYPE myCursor IS REF CURSOR; PROCEDURE proc_querySomeInfo(pNo IN VARCHAR2, myCursor OUT myCursor); END pack_myPackage;
--定义包体
CREATE OR REPLACE PACKAGE BODY pack_myPackage IS
PROCEDURE proc_querySomeInfo(pNo IN VARCHAR2, outcursor OUT myCursor) IS
BEGIN
OPEN outcursor FOR
SELECT * FROM tableA WHERE P_NO = pNo;
END proc_querySomeInfo;
END pack_myPackage;
2. 编写Java代码
package vip.yaocn.test;
import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet;
/** * 存储过程测试 * * @author yacon */ public class ProcedureTest { public static void main(String[] args) throws Exception { String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl"; String driverName = "oracle.jdbc.driver.OracleDriver"; String username = "scott"; String password = "tiger"; Connection conn = null; CallableStatement cstmt = null; ResultSet rs = null; try { Class.forName(driverName); conn = DriverManager.getConnection(url, username, password); String callSql = "{call pack_myPackage.proc_getSomeInfo(?, ?) }"; cstmt = conn.prepareCall(callSql); cstmt.setString(1, "1001"); cstmt.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR); cstmt.execute(); rs = (ResultSet) cstmt.getObject(2); if (rs != null) { while (rs.next()) { System.out.println(rs.getString(1)); } } } catch (Exception e) { e.printStackTrace(); } finally { try { if (rs != null) { rs.close(); } } catch (Exception e) { e.printStackTrace(); } try { if (cstmt != null) { cstmt.close(); } } catch (Exception e) { e.printStackTrace(); } try { if (conn != null) { conn.close(); } } catch (Exception e) { e.printStackTrace(); } } } }
三、 其他需求
1. Java调用有返回值的Oracle存储过程(返回一个简单类型的值)
(1)定义存储过程:
CREATE OR REPLACE PROCEDURE proc_A(param1 IN VARCHAR2, param2 OUT VARCHAR2) AS BEGIN SELECT name INTO param2 FROM T_A WHERE id = param1; END;
(2)编写Java代码:
package vip.yaocn.test; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet;
/** * 存储过程测试 * * @author yacon */ public class ProcedureTest { public static void main(String[] args) throws Exception { String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl"; String driverName = "oracle.jdbc.driver.OracleDriver"; String username = "scott"; String password = "tiger"; Connection conn = null; CallableStatement cstmt = null; ResultSet rs = null; try { Class.forName(driverName); conn = DriverManager.getConnection(url, username, password); String callSql = "{call proc_A(?, ?) }"; cstmt = conn.prepareCall(callSql); cstmt.setString(1, "1001"); cstmt.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR); cstmt.execute();
System.out.println("Output is:" + cstmt.getString(2)); } catch (Exception e) { e.printStackTrace(); } finally { try { if (rs != null) { rs.close(); } } catch (Exception e) { e.printStackTrace(); } try { if (cstmt != null) { cstmt.close(); } } catch (Exception e) { e.printStackTrace(); } try { if (conn != null) { conn.close(); } } catch (Exception e) { e.printStackTrace(); } } } }
2. Java调用无返回值的Oracle存储过程(无返回值)
(1)定义存储过程:
CREATE OR REPLACE PROCEDURE proc_B(param1 IN VARCHAR2, param2 IN VARCHAR2) AS BEGIN INSERT INTO T_B VALUES(param1, param2); END;
(2)编写Java代码:
package vip.yaocn.test; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet;
/** * 存储过程测试 * * @author yacon */ public class ProcedureTest { public static void main(String[] args) throws Exception { String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl"; String driverName = "oracle.jdbc.driver.OracleDriver"; String username = "scott"; String password = "tiger"; Connection conn = null; CallableStatement cstmt = null; ResultSet rs = null; try { Class.forName(driverName); conn = DriverManager.getConnection(url, username, password); String callSql = "{call proc_B(?, ?) }"; cstmt = conn.prepareCall(callSql); cstmt.setString(1, "1001"); cstmt.setString(2, "TEST"); cstmt.execute(); } catch (Exception e) { e.printStackTrace(); } finally { try { if (rs != null) { rs.close(); } } catch (Exception e) { e.printStackTrace(); } try { if (cstmt != null) { cstmt.close(); } } catch (Exception e) { e.printStackTrace(); } try { if (conn != null) { conn.close(); } } catch (Exception e) { e.printStackTrace(); } } } }
四、相关知识
1. 在plsql developer中测试存储过程时可以在存储过程名上右击选择 “测试” ,如果需要提供参数可以在下方填入参数,如果没有参数则点击执行按钮即可。