调用存储过程方式总汇.docxVIP

spring调用Oracle存储过程，并用cursor返回结果集1. 创建表： Java代码 ?create?table?TEST_USERS(?USER_ID??VARCHAR2(10)?not?null,?NAME?????VARCHAR2(10)?not?null,?PASSWORD?VARCHAR2(20)?not?null)??2. 创建存储过程： Java代码 ?create?or?replace?package?display_users_package?is?type?search_results?is?ref?cursor;?procedure?display_users_proc(results_out?out?search_results,?userId?in?test_users.user_id%type);end?display_users_package;?create?or?replace?package?body?display_users_package?is?procedure?display_users_proc(results_out?out?search_results,?userId?in?test_users.user_id%type)?is?begin?if?userId?is?not?null?then?open?results_out?for?select?*?from?test_users?where?user_id?like?userId?||?%;?else?open?results_out?for??select?*?from?test_users;?end?if;?end?display_users_proc;end?display_users_package;??这个results_out是一个游标类型，用来返回查找的结果集。 3.　完整实现代码： Java代码 ?import?java.sql.CallableStatement;import?java.sql.Connection;import?java.sql.ResultSet;import?java.sql.SQLException;import?java.util.ArrayList;import?java.util.HashMap;import?java.util.List;import?java.util.Map;?import?javax.sql.DataSource;?import?oracle.jdbc.OracleTypes;?import?org.springframework.dao.DataAccessException;import?org.springframework.jdbc.core.CallableStatementCallback;import?org.springframework.jdbc.core.CallableStatementCreator;import?org.springframework.jdbc.core.JdbcTemplate;?import?cedure.util.DataContextUtil;?/** ??*?@author?Jane?Jiao ??* ??*/??public?class?SpringStoredProce?{??public?ListMap?execute(String?storedProc,?String?params){?ListMap?resultList?=?null;?try{?final?DataSource?ds?=?DataContextUtil.getInstance().getDataSource();?final?JdbcTemplate?template?=?new?JdbcTemplate(ds);?resultList?=?(ListMap)template.execute(new?ProcCallableStatementCreator(storedProc,?params),?new?ProcCallableStatementCallback());?}catch(DataAccessException?e){?throw?new?RuntimeException(execute?method?error?:?DataAccessException??+?e.getMessage());?}?return?resultList;?}???/** ??*?Create?a?callable?statement?in?this?connection. ??*/???private?class?P