Java调用Oracle存储过程返回查询结果集

Java调⽤Oracle存储过程详解Java调⽤Oracle存储过程详解步骤：1、编写Oracle存储过程2、编写数据库获取连接⼯具类3、编写简单应⽤调⽤存储过程实现：1、Oracle存储过程：/*测试表*/create table test(id varchar2(32),name varchar2(32));/*存储过程插⼊数据*/CREATE OR REPLACE PROCEDURE insert_procedure(PARA1 IN VARCHAR2,PARA2 IN VARCHAR2) ASBEGININSERT INTO test (id, name) VALUES (PARA1, PARA2);END insert_procedure;/*存储过程返回结果集*/CREATE OR REPLACE PROCEDURE select_procedure(para_id IN VARCHAR2,name OUT sys_refcursor /* 这个sys_refcursor类型在SYS.STANDARD包中 */) ASBEGINOPEN name FORSELECT * FROM test WHERE id = para_id;END;2、JDBC⼯具类import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class DBUtil {public static final String DRIVER = "oracle.jdbc.driver.OracleDriver";public static final String URL = "jdbc:oracle:thin:@localhost:1521/orcl";public static final String USERNAME = "pfm";public static final String PASSWORD = "pfm";/*** 通过静态代码块注册数据库驱动*/static {try {Class.forName(DRIVER);} catch (ClassNotFoundException e) {e.printStackTrace();}}/*** 获得Connection** @returnConnection conn = null;try {conn = DriverManager.getConnection(URL, USERNAME, PASSWORD); } catch (SQLException e) {e.printStackTrace();}return conn;}/*** 获得Statement** @return*/public static Statement getStatement() {Statement st = null;try {st = getConnection().createStatement();} catch (SQLException e) {e.printStackTrace();}return st;}/*** 关闭ResultSet** @param rs*/public static void closeResultSet(ResultSet rs) {if (rs != null) {try {rs.close();} catch (SQLException e) {e.printStackTrace();}}}/*** 关闭Statement** @param st*/public static void closeStatement(Statement st) {if (st != null) {try {st.close();} catch (SQLException e) {e.printStackTrace();}}}/*** 关闭Connection** @param conn*/public static void closeConnection(Connection conn) {if (conn != null) {try {conn.close();} catch (SQLException e) {e.printStackTrace();}}}/*** 关闭全部** @param rs* @param sta* @param connpublic static void closeAll(ResultSet rs, Statement sta, Connection conn) {closeResultSet(rs);closeStatement(sta);closeConnection(conn);}}3、调⽤存储过程：import java.sql.CallableStatement;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import oracle.jdbc.driver.OracleTypes;/*** 测试调⽤存储过程**/public class StoredTest {public static void main(String[] args) {insert_call();//select_call();}/*** 执⾏存储过程插⼊数据*/public static void insert_call() {Connection conn = DBUtil.getConnection();PreparedStatement pst = null;CallableStatement proc = null; // 创建执⾏存储过程的对象try {proc = conn.prepareCall("{ call insert_procedure(?,?) }");proc.setString(1, "1"); // 设置第⼀个输⼊参数proc.setString(2, "hello call"); // 设置第⼀个输⼊参数proc.execute();// 执⾏} catch (SQLException e) {e.printStackTrace();} finally {try {// 关闭IO流proc.close();DBUtil.closeAll(null, pst, conn);} catch (Exception e) {e.printStackTrace();}}}/*** 执⾏存储过程查询数据*/public static void select_call() {Connection conn = DBUtil.getConnection();CallableStatement stmt;try {stmt = conn.prepareCall("{ call select_procedure(?, ?) }"); // ⽤此调⽤⽅法不能实现多⾏语法 stmt.setString(1, "1");stmt.registerOutParameter(2, OracleTypes.CURSOR);stmt.execute();ResultSet rs = (ResultSet) stmt.getObject(2);while (rs.next()) {System.out.println(rs.getString("name"));}} catch (SQLException e) {e.printStackTrace();} finally {DBUtil.closeConnection(conn);}}感谢阅读，希望能帮助到⼤家，谢谢⼤家对本站的⽀持！。

java调用存储过程返回数组Java调用存储过程：结合SQL操作与存储过程createprocedureet_death_age(poetVARCHAR2,poet_ageNUMBER)poet_idNUMBER;beginSELECTidINTOpoet_idFROMpoetWHEREname=poet;INSERTINTOdeath(mort_id,age)VALUES(poet_id,poet_age);endet_death_age;下面是调用上面存储过程的Java代码：publictaticvoidetDeathAge(PoetdyingBard,intage)throwSQLE某ception{Connectioncon=null;CallableStatementproc=null;try{con=connectionPool.getConnection();proc=con.prepareCall(\proc.etString(1,dyingBard.getName());proc.etInt(2,age);proc.e某ecute();}finally{try{proc.cloe();}catch(SQLE某ceptione){}con.cloe();}}Function存储过程可以有返回值，所以CallableStatement类有类似getReultSet这样的方法来获取返回值。

当存储过程返回一个值时，你必须使用regiterOutParameter方法告诉JDBC驱动器该值的SQL类型是什么。

你也必须调整存储过程调用来指示该过程返回一个值。

下面接着上面的例子。

这次我们查询DylanThoma逝世时的年龄。

这次的存储过程使用：createfunctionnuffed_it_when(VARCHAR)returninteger''declarep oet_idNUMBER;poet_ageNUMBER;begin--firtgettheidaociatedwiththepoet.SELECTidINTOpoet_idFROMpoetWHERE name=$1;--getandreturntheage.SELECTageINTOpoet_ageFROMdeathWHEREmort_id=poet_id;returnage;end;下面是调用这个存储过程的Java代码：CallableStatementproc=connection.prepareCall(\=callnuffed_it _when()}\proc.regiterOutParameter(1,Type.INTEGER);proc.etString( 2,poetName);c.e某ecute();intage=proc.getInt(2);在使用存储过程中，我们有时需要传递可变数组，存在两种情况，存储过程有输入或输出参数为自定义可变数组的。

Java调⽤Oracle存储过程返回结果集CREATE OR REPLACE PACKAGE typesASTYPE ref_cursor IS REF CURSOR;END;/CREATE TABLE STOCK_PRICES(RIC VARCHAR(6) PRIMARY KEY,PRICE NUMBER(7,2),UPDATED DATE );/CREATE OR REPLACE FUNCTION sp_get_stocks(v_price IN NUMBER)RETURN types.ref_cursorASstock_cursor types.ref_cursor;BEGINOPEN stock_cursor FORSELECT ric,price,updated FROM stock_prices WHERE price < v_price;RETURN stock_cursor;END;2. ⽤ sqlplus 测试过程SQL> var results refcursorSQL> exec :results := sp_get_stocks(20.0)SQL> print results3. 从 Java 调⽤import java.sql.*;import java.io.*;import oracle.jdbc.driver.*;public class JDBCDemo {/*** Compile-time flag for deciding which query to use*/private boolean useOracleQuery = true;/*** Class name of Oracle JDBC driver*/private String driver = "oracle.jdbc.driver.OracleDriver";/*** Initial url fragment*/private String url = "jdbc:oracle:thin:@";/*** Standard Oracle listener port*/private String port = "1521";/*** Oracle style of calling a stored procedure*/private String oracleQuery = "begin ? := sp_get_stocks(?); end;";/*** JDBC style of calling a stored procedure*/private String genericQuery = "{ call ? := sp_get_stocks(?) }";/*** Connection to database*/private Connection conn = null;/*** Constructor. Loads the JDBC driver and establishes a connection** @param host the host the db is on* @param db the database name* @param user user's name* @param password user's password*/public JDBCDemo(String host, String db, String user, String password) throws ClassNotFoundException, SQLException {// construct the urlurl = url + host + ":" + port + ":" + db;// load the Oracle driver and establish a connectiontry {Class.forName(driver);conn = DriverManager.getConnection(url, user, password);}catch (ClassNotFoundException ex) {System.out.println("Failed to find driver class: " + driver);throw ex;}catch (SQLException ex) {System.out.println("Failed to establish a connection to: " + url);throw ex;}}/*** Execute the stored procedure** @param price price parameter for stored procedure*/private void execute(float price)throws SQLException {String query = useOracleQuery ? oracleQuery : genericQuery;System.out.println("Query: " + query + "n");CallableStatement stmt = conn.prepareCall(query);// register the type of the out param - an Oracle specific typestmt.registerOutParameter(1, OracleTypes.CURSOR);// set the in paramstmt.setFloat(2, price);// execute and retrieve the result setstmt.execute();ResultSet rs = (ResultSet)stmt.getObject(1);// print the resultswhile (rs.next()) {System.out.println(rs.getString(1) + "t" +rs.getFloat(2) + "t" +rs.getDate(3).toString());}rs.close();stmt.close();}/*** Cleanup the connection*/private void cleanup() throws SQLException {if (conn != null)conn.close();}/*** Prints usage statement on stdout*/static private void usage() {System.out.println("java com.enterprisedt.demo.oracle.JDBCDemo " + " host db user password price");}/*** Runs the class*/public static void main(String[] args) throws Exception {if (args.length != 5) {age();System.exit(1);}else {try {// assign the args to sensible variables for clarityString host = args[0];String db = args[1];String user = args[2];String password = args[3];float price = Float.valueOf(args[4]).floatValue();// and execute the stored procJDBCDemo jdbc = new JDBCDemo(host, db, user, password); jdbc.execute(price);jdbc.cleanup();}catch (ClassNotFoundException ex) {System.out.println("Demo failed");}catch (SQLException ex) {System.out.println("Demo failed: " + ex.getMessage());}}}}。

java通过调⽤存储过程获取结果集⼀般在java中,数据查询是通过Statement, PreparedStatement获取结果集,今天向⼤家介绍通过CallableStatement调⽤存储过程,从⽽获取结果集. 本⽂是所⽤的数据库为oracle. ⼀. 测试数据库表:sql 代码1. create table wilent_user(2. id number(5) primary key,3. name varchar2(100),4. sex varchar2(1), --Y为男,F为⼥;5. group_id number(5),6. teach varchar2(50) --学历;7. );8.9. create table wilent_group(10. id number(5) primary key,11. name varchar2(100)12. );13.14. insert into wilent_group values(1,'组1');15. insert into wilent_group values(2,'组2');16. insert into wilent_group values(3,'组3');17. insert into wilent_group values(4,'组4');18. insert into wilent_group values(5,'组5');19.20. insert into wilent_user values(1,'吴','Y',1,'⼤专');21. insert into wilent_user values(2,'李','Y',1,'⼤专');22. insert into wilent_user values(3,'赵','N',2,'本科');23. insert into wilent_user values(4,'⾦','Y',2,'⾼中');24. insert into wilent_user values(5,'钱','N',2,'⼤专');25. insert into wilent_user values(6,'孙','N',1,'⼤专');26. insert into wilent_user values(7,'⾼','Y',3,'本科');27. insert into wilent_user values(8,'宋','N',3,'⾼中');28. insert into wilent_user values(9,'伍','Y',3,'⼤专');29. insert into wilent_user values(10,'欧','Y',4,'本科');30. insert into wilent_user values(11,'庄','N',4,'硕⼠');31. insert into wilent_user values(12,'纪','Y',4,'本科');32. insert into wilent_user values(13,'陈','Y',5,'⼤专');33. insert into wilent_user values(14,'龙','N',5,'⼤专');34. insert into wilent_user values(15,'袁','Y',5,'⾼中');35. insert into wilent_user values(16,'杨','Y',1,'本科');36. insert into wilent_user values(17,'江','N',1,'⼤专');37. insert into wilent_user values(18,'刘','Y',1,'硕⼠');38. insert into wilent_user values(19,'郭','N',3,'硕⼠');39. insert into wilent_user values(20,'张','Y',3,'⼤专');40. insert into wilent_user values(21,'⽂','N',3,'硕⼠');41. insert into wilent_user values(22,'李','N',4,'⼤专');42. insert into wilent_user values(23,'梅','Y',4,'本科');43. insert into wilent_user values(24,'王','N',4,'⼤专');44. insert into wilent_user values(25,'吕','N',5,'⾼中');45. insert into wilent_user values(26,'范','Y',5,'本科');46. insert into wilent_user values(27,'许','N',1,'⼤专');47. insert into wilent_user values(28,'墨','Y',1,'⾼中');48. insert into wilent_user values(29,'孔','N',1,'本科');49. insert into wilent_user values(30,'蔡','Y',1,'⼤专');⼆. oracle 存储过程sql 代码1. --⾃定义类型;2. Create Or Replace Type wilent_row_table As Object3. (4. group_name Varchar2(100),5. group_count Number(4),6. male_count Number(4),7. woman_count Number(4),8. da_count Number(4),9. ben_count Number(4)10. );11. /12.13. --定义⼀个嵌套表类型;14. Create Or Replace Type wilent_tab_type Is Table Of wilent_row_table;15. /16. --返回⼀个游标类型;17. Create Or Replace Package wilent_types As18. Type cursor_type Is Ref Cursor;19. End wilent_types;20. /21. Create Or Replace Procedure wilent_group_count(recordSet Out wilent_types.cursor_type)22. As23. v_tab wilent_tab_type := wilent_tab_type();24. index_max Number(4); --wilent_group最⼤的id;25. index_min Number(4); --wilent_group最⼩的id;26. index_for Number(4);27.28. group_name Varchar2(100);29. user_count Number(4);30. male_count Number(4);31. woman_count Number(4);32. da_count Number(4);33. ben_count Number(4);34. Begin35. dbms_output.put_line('as');36. Select Max(g.Id) Into index_max From wilent_group g;37. --dbms_output.put_line(index_max);38. Select Min(g.Id) Into index_min From wilent_group g;39. --dbms_output.put_line(index_min);40. For index_for In Index_min..index_max Loop41. --添加新记录;42. v_tab.Extend;43. Select Name Into group_name From wilent_group Where Id=index_for;44. Select Count(*) Into user_count From wilent_user u, wilent_group g Where u.group_id=g.Id And g.Id=index_for;45. Select Count(*) Into male_count From wilent_user u, wilent_group g Where u.group_id=g.Id And g.Id=index_for And sex='Y';46. Select Count(*) Into woman_count From wilent_user u, wilent_group g Where u.group_id=g.Id And g.Id=index_for And sex='N';47. Select Count(*) Into da_count From wilent_user u, wilent_group g Where u.group_id=g.Id And g.Id=index_for And teach='⼤专';48. Select Count(*) Into ben_count From wilent_user u, wilent_group g Where u.group_id=g.Id And g.Id=index_for And teach='本科';49. --把记录写⼊;50. v_tab(v_st) := wilent_row_table(group_name,user_count,male_count,woman_count,da_count,ben_count);51. End Loop;52.53. --把记录放在游标⾥;54. Open recordset For55. --Table(Cast(v_tab As wilent_tab_type))⽬的是把v_tab强转为wilent_tab_type表56. Select group_name,group_count ,male_count ,woman_count ,da_count ,ben_count From Table(Cast(v_tab As wilent_tab_type)) Order By group_name;57. End wilent_group_count;58. /59.60. --测试wilent_group_count();61. declare62. recordset wilent_types.cursor_type;63. Begin64. wilent_group_count(recordset);65. End;三. java代码:java 代码1. package com.wilent.oracle;2.3. import java.sql.CallableStatement;4. import java.sql.Connection;5. import java.sql.ResultSet;6. import java.sql.SQLException;7.8. import oracle.jdbc.driver.OracleTypes;9.10. import com.wilent.db.ConnectionManager;11.12. public class TestProcedure {13. public static void main(String[] args) {14. //获得conn连接,读者可以⾃⾏写;15. Connection conn = ConnectionManager.getConnection();16. ResultSet rs = null;17. try {18. CallableStatement proc = conn.prepareCall("{call wilent_group_count(?)}");19. proc.registerOutParameter(1, OracleTypes.CURSOR);20. proc.execute();21.22. rs = (ResultSet) proc.getObject(1);23. System.out.println("组名\t总计\t男性\t⼥性\t⼤专\t本科");24. while(rs.next())25. {26. StringBuffer buffer = new StringBuffer();27. buffer.append(rs.getString("group_name"));28. buffer.append("\t");29. buffer.append(rs.getInt("group_count"));30. buffer.append("\t");31. buffer.append(rs.getInt("male_count"));32. buffer.append("\t");33. buffer.append(rs.getInt("woman_count"));34. buffer.append("\t");35. buffer.append(rs.getInt("da_count"));36. buffer.append("\t");37. buffer.append(rs.getInt("ben_count"));38. System.out.println(buffer.toString());39. }40. } catch (Exception e) {41. e.printStackTrace();42. }43. finally{44. try {45. conn.close();46. } catch (SQLException e) {47. e.printStackTrace();48. }49. }50. }51. }四. 运⾏结果组名总计男性⼥性⼤专本科组1 10 6 4 6 2 组2 3 1 2 1 1 组3 6 3 3 2 1 组4 6 3 3 2 3组5 5 3 2 2 1。

jdbcTemplate调用Oracle存储过程返回List集合作者:xyzc(cr10210206@)1.编写存储过程-- 模糊查询返回多条数据CREATE OR REPLACE PROCEDURE P_EMP_SELECT(RESULTLIST OUT SYS_REFCURSOR,V_ID IN NUMBER,V_NAME IN VARCHAR2)ISSQL_STR VARCHAR2(500);BEGINSQL_STR:='SELECT * FROM EMP WHERE 1=1 ';DBMS_OUTPUT.put_line('V_ID='||V_ID ||' V_NAME='||V_NAME);IF(V_ID<>0)THENBEGINSQL_STR:=SQL_STR ||' AND EMPNO>= '|| V_ID;END;END IF;IF(V_NAME IS NOT NULL)THEN-- 判断字符串是否为空BEGINSQL_STR:=SQL_STR ||' AND ENAME LIKE '''||V_NAME ||'%'' ';-- 字符串是四个单引号 ''A%'' END;END IF;DBMS_OUTPUT.put_line(' SQL_STR:'||SQL_STR);-- 输出SQL语句OPEN RESULTLIST FOR SQL_STR;END;-- 测试DECLAREMYCRS SYS_REFCURSOR;-- 注意这里用分号；V_EMP EMP%ROWTYPE;BEGIN-- 调用过程，返回的是已经打开的CURSORP_EMP_SELECT(MYCRS,7900,'');LOOPFETCH MYCRS INTO V_EMP;EXIT WHEN MYCRS%NOTFOUND;DBMS_OUTPUT.put_line(V_EMP.EMPNO||' '||V_EMP.ENAME);END LOOP;CLOSE MYCRS;END;2.编写Java代码package com.zc.test;import java.sql.CallableStatement;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.sql.SQLException;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import oracle.jdbc.driver.OracleTypes;import org.springframework.dao.DataAccessException;import org.springframework.jdbc.core.CallableStatementCallback; import org.springframework.jdbc.core.JdbcTemplate;/*** 测试Oracle存储过程* */public class TestOraclePro {private static JdbcTemplate jdbcTemplate= TestDao.getJdbcTemplateOracle();/*** 测试* */public static void main(String[] args) {TestOraclePro test = new TestOraclePro();List<HashMap<String, Object>> result = test.testPro();System.out.println("\nresult:\n" + result);/*** 返回的结果：** [{DEPTNO=20, COMM=null, HIREDATE=1980-12-17 00:00:00.0, MGR=7902, SAL=800, JOB=CLERK, ENAME=SMITH, EMPNO=7369},* {DEPTNO=30, COMM=300, HIREDATE=1981-02-20 00:00:00.0, MGR=7698, SAL=1600, JOB=SALESMAN, ENAME=ALLEN, EMPNO=7499}, * {DEPTNO=30, COMM=500, HIREDATE=1981-02-22 00:00:00.0, MGR=7698, SAL=1250, JOB=SALESMAN, ENAME=WARD, EMPNO=7521},* {DEPTNO=20, COMM=null, HIREDATE=1981-04-02 00:00:00.0,MGR=7839, SAL=2975, JOB=MANAGER, ENAME=JONES, EMPNO=7566},* {DEPTNO=30, COMM=1400, HIREDATE=1981-09-28 00:00:00.0, MGR=7698, SAL=1250, JOB=SALESMAN, ENAME=MARTIN, EMPNO=7654}, * {DEPTNO=30, COMM=null, HIREDATE=1981-05-01 00:00:00.0, MGR=7839, SAL=2850, JOB=MANAGER, ENAME=BLAKE, EMPNO=7698},* {DEPTNO=10, COMM=null, HIREDATE=1981-06-09 00:00:00.0, MGR=7839, SAL=2450, JOB=MANAGER, ENAME=CLARK, EMPNO=7782},* {DEPTNO=20, COMM=null, HIREDATE=1987-04-19 00:00:00.0, MGR=7566, SAL=3000, JOB=ANALYST, ENAME=SCOTT, EMPNO=7788},* {DEPTNO=10, COMM=null, HIREDATE=1981-11-17 00:00:00.0, MGR=null, SAL=5000, JOB=PRESIDENT, ENAME=KING, EMPNO=7839}, * {DEPTNO=30, COMM=0, HIREDATE=1981-09-08 00:00:00.0, MGR=7698, SAL=1500, JOB=SALESMAN, ENAME=TURNER, EMPNO=7844}, * {DEPTNO=20, COMM=null, HIREDATE=1987-05-23 00:00:00.0, MGR=7788, SAL=1100, JOB=CLERK, ENAME=ADAMS, EMPNO=7876},* {DEPTNO=30, COMM=null, HIREDATE=1981-12-03 00:00:00.0, MGR=7698, SAL=950, JOB=CLERK, ENAME=JAMES, EMPNO=7900},* {DEPTNO=20, COMM=null, HIREDATE=1981-12-03 00:00:00.0, MGR=7566, SAL=3000, JOB=ANALYST, ENAME=FORD, EMPNO=7902},* {DEPTNO=10, COMM=null, HIREDATE=1982-01-23 00:00:00.0, MGR=7782, SAL=1300, JOB=CLERK, ENAME=MILLER, EMPNO=7934}]* */}/*** 调用存储过程返回 List<HashMap<String, Object>>* */@SuppressWarnings({ "unchecked", "rawtypes" })public List<HashMap<String, Object>> testPro(){final String sql = "{call P_EMP_SELECT(?,?,?)}";List<HashMap<String, Object>> result = (List<HashMap<String, Object>>) jdbcTemplate.execute(sql,new CallableStatementCallback(){ public Object doInCallableStatement(CallableStatement cs) throws SQLException,DataAccessException {List<HashMap<String, Object>> list = new ArrayList<HashMap<String, Object>>();cs.registerOutParameter(1, OracleTypes.CURSOR); //输出参数：游标cs.setInt(2, 2000);//输入参数cs.setString(3, "");//输入参数cs.execute();//执行ResultSet rs = null;try {rs = (ResultSet) cs.getObject(1);//获取结果集while (rs.next()) {HashMap<String, Object> dataMap = new HashMap<String, Object>();ResultSetMetaData rsMataData = rs.getMetaData();for(int i = 1; i <= rsMataData.getColumnCount(); i++) {dataMap.put(rsMataData.getColumnName(i),rs.getString(rsMataData.getColumnName(i)));}list.add(dataMap);}} catch (Exception e) {e.printStackTrace();}finally{if(rs != null){rs.close();}}return list;}});return result;}}。

本文由我司收集整编，推荐下载，如有疑问，请与我司联系java 获取存储过程返回的结果集(多个结果集)2014/01/16 11690 在中，DataSet 可以自动接收多个结果集，形成DataTable 数组。

使用JAVA 时，也想要实现这个效果，百度一顿查找，终于找到解决的办法。

核心方法：CallableStatement ResultSet具体实现代码：import java.sql.CallableStatement; import java.sql.ResultSet;public void search() {String re = ““;String sql = “call pro1(?,?);”;Connection con = DBConnPool.getConnection();//建立数据连接对像CallableStatement callsm = null;try {callsm = con.prepareCall(sql);// 给存储过程传参// callsm.setString(1, “res”);// callsm.setString(2, “me”);//执行存储过程callsm.execute();// 获取结果集ResultSet rs = callsm.getResultSet();while(rs.next()){re += (re!=““?”,”:”“)+rs.getString(“ID”);}re+= “|”;//判断是否有第二个结果集if (callsm.getMoreResults()) {// 这个判断会自动指向下一个结果集ResultSet rs1 = callsm.getResultSet();while(rs1.next()){re += (re!=““?”,”:”“)+rs1.getString(“Q_ID”);}try{// 关闭rs1if (rs1 != null){rs1.close();}}catch (Exception e) {e.printStackTrace();}}try {// 关闭rs1if (rs != null) {rs.close();}}catch (Exception e) {e.printStackTrace();}System.out.println(re);}catch (Exception e) {e.printStackTrace();}finally {try {callsm.close();con.close();}catch (Exception e) {e.printStackTrace();}} }tips:感谢大家的阅读，本文由我司收集整编。

java oracle存储过程写法及调用Java中调用和编写Oracle存储过程是非常常见的操作，可以利用存储过程来执行数据库操作，提高数据库的性能和安全性。

本文将为您详细介绍Java中调用和编写Oracle存储过程的方法和步骤。

一、什么是Oracle存储过程？Oracle存储过程是一段预定义在数据库中的PL/SQL代码，可以像函数一样接收参数和返回值，用于完成特定的数据库操作。

存储过程可以包含SQL语句、逻辑控制语句、流程控制语句等，可以完成复杂的业务逻辑和数据库操作。

二、Java中调用Oracle存储过程的步骤1. 导入相关的JDBC驱动在Java中调用Oracle存储过程之前，首先需要导入相关的JDBC驱动。

可以从Oracle官网下载相应版本的JDBC驱动，将其添加到Java项目的classpath中。

2. 建立数据库连接使用JDBC的Connection对象与数据库建立连接。

可以使用如下代码建立连接：String url = "jdbc:oracle:thin:localhost:1521:orcl";String username = "username";String password = "password";Connection conn = DriverManager.getConnection(url, username, password);需要将url、username和password替换为实际的数据库连接信息。

3. 创建CallableStatement对象使用Connection对象的prepareCall方法创建CallableStatement对象，该对象用于执行存储过程的调用。

String sql = "{call 存储过程名称(?, ?)}";CallableStatement cstmt = conn.prepareCall(sql);需要将存储过程名称替换为实际的存储过程名称。

Java 调用存储过程PL/SQL子程序，很多情况下是给应用程序来调用的，所有我们要掌握使用其他编程语言来调用我们写好的存储过程。

下面我们介绍下使用java调用Oracle的存储过程。

1.配置调用类package com.mscncn.plsql.util;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;public class DBUtil {static{try {Class.forName("oracle.jdbc.OracleDriver");} catch (ClassNotFoundException e) {e.printStackTrace();}}public static Connection getConntection(){Connection ct=null;try {ct = DriverManager.getConnection("jdbc:oracle:thin:@192.168.0.25:1521:oracle","scott","scott");} catch (SQLException e) {e.printStackTrace();}return ct;}}2.编写存储过程create or replace package pro_pk istype pager_cursor is ref cursor;procedure add_dept(dept_no in number,dept_name in varchar2,location in varchar2);procedure delete_dept(dept_no in number,num out number);end pro_pk;create or replace package body pro_pk isprocedure add_dept(dept_no in number,dept_name in varchar2,location in varchar2)isexp_remaining exception;pragma exception_init(exp_remaining,-1);/*非预定义错误，-1是违反唯一约束*/ begininsert into dept values(dept_no,dept_name,location);if sql%found then /*隐式游标，sql*/return 1;elsereturn 0;end if;exceptionwhen exp_remaining thendbms_output.put_line('违反唯一约束.');end add_dept;procedure delete_dept(dept_no in number,num out number)isbegindelete from dept where deptno=dept_no;if sql%found thennum:=1;elsenum:=1;end if;end delete_dept;end pro_pk;create or replace package pageUtil istype page_cursor is ref cursor;--定义一个游标类型procedure pager(tName in varchar2, --表名pageNum in number, --页数pageSize in number,--每页记录数totalRecord out number,--总记录数totalPage out number,--总页数p_cursor out page_cursor);end pageUtil;create or replace package body pageUtil isprocedure pager(tName in varchar2, --表名pageNum in number, --页数pageSize in number,--每页记录数totalRecord out number,--总记录数totalPage out number,--总页数p_cursor out page_cursor) is--定义sql语句字符串v_sql varchar2(1000);--分页开始位置，与结束位置v_begin number:=(pageNum-1)*pageSize+1;v_end number:=pageNum*pageSize;beginv_sql:='select * from ( select t.*,rownum rn from '||tName||' t where rownum<='||v_end||') where rn>='||v_begin;--把游标和sql关联dbms_output.put_line(v_sql);open p_cursor for v_sql;--计算totalRecord与totalPagev_sql:='select count(*) from '||tName;--execute immediate v_sql into totalRecord;if mod(totalRecord,pageSize)=0 thentotalPage:=totalRecord/pageSize;elsetotalPage:=totalRecord/pageSize+1;end if;--关闭游标,这儿需要注意，如果我们在java程序中使用cursor,那么就一定不能关闭cursor--否则关闭cursor后，java程序中返回的结果集就是null--close p_cursor;end pager;end pageUtil;3.调用存储过程1）调用没有返回值的存储过程/*** java调用没有返回值的存储过程*/@Testpublic void proNoReulstTest(){Connection ct=DBUtil.getConntection();try {CallableStatement cs=ct.prepareCall("{call pro_pk.add_dept(?,?,?)}");cs.setInt(1, 13);cs.setString(2, "java开发部");cs.setString(3, "中国信阳");cs.execute();} catch (SQLException e) {e.printStackTrace();}finally{try {ct.close();} catch (SQLException e) {e.printStackTrace();}}}2）调用有返回值的存储过程/*** java调用有返回值的存储过程(返回值类型为number)*/@Testpublic void proHasReulstTest(){Connection ct=DBUtil.getConntection();try {CallableStatement cs=ct.prepareCall("{call pro_pk.delete_dept(?,?)}");cs.setInt(1, 13);//注册第二个参数为存储过程的返回值cs.registerOutParameter(2, OracleType.STYLE_INT);cs.execute();//通过参数的索引，来获取存储过程的返回值，索引从1开始int num=cs.getInt(2);System.out.println(num==1?"删除成功":"删除失败");} catch (SQLException e) {e.printStackTrace();}finally{try {ct.close();} catch (SQLException e) {e.printStackTrace();}}}3）java程序调用存储过程返回值为游标/*** 存储过程返回一个游标*/@Testpublic void proReturnCursorTest(){Connection ct=DBUtil.getConntection();try {CallableStatement cs=ct.prepareCall("{call pageUtil.pager(?,?,?,?,?,?)}");cs.setString(1, "emp");cs.setInt(2, 2);cs.setInt(3, 5);cs.registerOutParameter(4, OracleTypes.NUMBER);cs.registerOutParameter(5, OracleTypes.NUMBER);cs.registerOutParameter(6, OracleTypes.CURSOR);cs.execute();//通过参数的索引，来获取存储过程的返回值，索引从1开始int totalRecord=cs.getInt(4);int totalPage=cs.getInt(5);ResultSet rs=(ResultSet)cs.getObject(6);System.out.println("总记录数为:"+totalRecord+",总页数为:"+totalPage);while(rs.next()){System.out.println("雇员编号："+rs.getInt("empno")+",雇员姓名:"+rs.getString("ename"));}} catch (SQLException e) {e.printStackTrace();}finally{try {ct.close();} catch (SQLException e) {e.printStackTrace();}} }。

java调用存储过程后处理数据的方法
在Java中调用存储过程后，我们需要对返回的数据进行处理。

以下是一些处理数据的方法：
1. 使用ResultSet对象来获取存储过程返回的结果集。

可以使用ResultSet的getXXX()方法来获取不同类型的数据。

2. 使用CallableStatement对象来调用存储过程，并使用其方法registerOutParameter()来注册输出参数的类型。

然后可以使用getXXX()方法来获取输出参数的值。

3. 使用ORM框架，如Hibernate或MyBatis，可以将存储过程映射为一个Java方法，并使用返回值或输出参数来获取数据。

4. 使用JdbcTemplate（Spring框架中的一个类），可以方便地调用存储过程并获取结果集或输出参数的值。

5. 使用Java8中的Streams API，可以对ResultSet对象进行流式处理，例如使用stream()方法将ResultSet转换为流，然后使用map()、filter()、reduce()等方法来处理数据。

总之，在Java中调用存储过程后，我们有多种处理数据的方式。

我们可以根据具体情况选择最合适的方法。

- 1 -。