ORACLE语法-包(package)、存储过程(procedure)、游标(cursor)以及java对Result结果集的处理

本文主要是介绍ORACLE语法-包(package)、存储过程(procedure)、游标(cursor)以及java对Result结果集的处理，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

陈科肇

示例：

包规范

CREATE OR REPLACE PACKAGE PACK_WMS_YX IS-- Author  : CKZ-- Created : 2015/8/28 9:52:29-- Purpose : 同步数据-- Public type declarations，游标 退休订单TYPE retCursor IS REF CURSOR;-- RETURN vi_co_co_return%ROWTYPE;-- 同步退货订单，返回退货订单列表集合游标PROCEDURE sp_syn_returned(errno OUT NUMBER,errtext OUT VARCHAR2,outCursor OUT retCursor);END PACK_WMS_YX;

包主体

CREATE OR REPLACE PACKAGE BODY PACK_WMS_YX IS-- Author  : CKZ-- Created : 2015/8/28 9:52:29-- Purpose : 同步数据-- Public variables declarations，执行状态v_errorcode wms_sys_sperror.errorcode%TYPE;v_errormsg wms_sys_sperror.errormsg%TYPE;v_exception EXCEPTION;--自定义异常-- 同步退货订单PROCEDURE sp_syn_returned(errno OUT NUMBER,errtext OUT VARCHAR2,outCursor OUT retCursor) ISBEGINerrno :=0;errtext:='success';--打开游标，获取数据OPEN outCursor FOR SELECT co_num,cust_code,qty_sum,amt_sum,crt_date,born_date FROM vi_co_co_return;EXCEPTION--异常WHEN OTHERS THENROLLBACK;v_errorcode:=SQLCODE;v_errormsg:=substr(SQLERRM,200);errno:=1;errtext:=v_errormsg;pack_wms_pub.sp_wms_insertSpErr('同步退货订单，返回退货订单列表集合游标',v_errorcode,v_errormsg);END sp_syn_returned;BEGIN-- InitializationNULL;
END PACK_WMS_YX;

PL/SQL调用示例

选中要测试的存储过程名，右键->Test
将看到这个页面

执行Start debugger，Run

查看游标数据

JAVAWEB、HIBERNATE调用示例

注：由于后期修改，这里只有一个输出参数和一个输入参数，动态游标查询

/*** 查询所有报损报溢单信息* * @return 报损报溢单列表信息* @throws Exception*/public List queryReturnedBillByCondition(HashMap conditionMap)throws Exception {HashMap<String, String> configMap = new HashMap<String, String>();int start = 0;int limit = 12;String condition = "";if (!conditionMap.isEmpty()) {if (!ConvertUtil.getConditonMapValue(conditionMap, "start").equals("")) {start = Integer.parseInt(ConvertUtil.getConditonMapValue(conditionMap, "start"));}if (!ConvertUtil.getConditonMapValue(conditionMap, "limit").equals("")) {limit = Integer.parseInt(ConvertUtil.getConditonMapValue(conditionMap, "limit"));}}condition = GetCondition(conditionMap);Session session = getHibernateTemplate().getSessionFactory().getCurrentSession();String procName = "{CALL pack_wms_yx.sp_syn_returned(?,?)}";CallableStatement proc = citePro(session, procName);String querySql = "SELECT r.co_num,r.cust_code,r.qty_sum,r.amt_sum,r.crt_date,r.born_date" +" FROM vi_co_co_return r" +" WHERE 1=1 "+condition;proc.setString(1, querySql);proc.registerOutParameter(2, OracleTypes.CURSOR);ResultSet rs = null;List list = null;try {proc.execute();rs = (ResultSet) proc.getObject(2);list = ResultUtils.toList(rs);} catch (Exception e) {e.printStackTrace();}return list;}

ResultUtils.java处理工具

原文：GITHUB

package com.usercard.jdbc;import java.lang.reflect.Field;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.commons.beanutils.BeanUtils;public class ResultSetUtils
{/*** @author 王威 * 用于将ResultSet结果集转化成实体类列表或者List<Map<String,Object>>结果集的工具类，* 用于调用存储过程返回游标结果集转换成实体列表或者List<Map<String,Object>>结果集* @param <T>*/public static List<Map<String, Object>> toList(ResultSet rs){Map<String, Object> record = null;List<String> columnNameList = new ArrayList<String>();List<Map<String, Object>> recordSet = new ArrayList<Map<String, Object>>();try{for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++){String columnName = rs.getMetaData().getColumnName(i).toLowerCase();columnNameList.add(columnName);}while (rs.next()){record = new HashMap<String, Object>();for (String columnName : columnNameList)record.put(columnName, rs.getObject(columnName));recordSet.add(record);}}catch (Exception e){throw new RuntimeException(e.getMessage());}return recordSet;}public static <T> List<T> toList(ResultSet rs, Class<T> classType){List<String> columnNameList = new ArrayList<String>();List<T> entityList = null;try{entityList = new ArrayList<T>();Field[] fields = classType.getDeclaredFields();for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++){String columnName = rs.getMetaData().getColumnName(i).toUpperCase();
//                System.out.println("columnName=> " + columnName);columnNameList.add(columnName);}while (rs.next()){T entity = classType.newInstance();for (Field field : fields){String fieldName = field.getName();// 如果实体类中的fieldName在ResultSet中没有，不索引取值，防止出现异常if (columnNameList.contains(fieldName.toUpperCase())){
//                        System.out.println("fieldName=> " + fieldName);BeanUtils.setProperty(entity, fieldName, rs.getObject(fieldName));}}entityList.add(entity);}}catch (Exception e){throw new RuntimeException(e.getMessage());}return entityList;}public static Object toSingleResult(ResultSet rs){Object result = null;try{while (rs.next()){result = rs.getObject(1);break;}}catch (Exception e){throw new RuntimeException(e.getMessage());}return result;}@SuppressWarnings("unchecked")public static List toArrayList(ResultSet rs){List arrayList = null;try{arrayList = new ArrayList();int iCol = rs.getMetaData().getColumnCount();while (rs.next()){Object[] objArray = new Object[iCol];for (int i = 1; i <= iCol; i++){objArray[i - 1] = rs.getObject(i);}arrayList.add(objArray);}}catch (Exception e){throw new RuntimeException(e.getMessage());}return arrayList;}public static Map<String, Object> toHashMap(ResultSet rs){List<String> columnNameList = new ArrayList<String>();Map<String, Object> record = new HashMap<String, Object>();try{int iRow = 0;for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++){String columnName = rs.getMetaData().getColumnName(i).toLowerCase();columnNameList.add(columnName);}while (rs.next()){if (iRow > 1)throw new RuntimeException("返回Map<String,Object>类型只能存放一条记录！");for (String columnName : columnNameList)record.put(columnName, rs.getObject(columnName));iRow++;}}catch (Exception e){throw new RuntimeException(e.getMessage());}return record;}public static <T> T toBean(ResultSet rs, Class<T> classType){T entity = null;List<String> ColumnNameList = new ArrayList<String>();try{entity = classType.newInstance();Field[] fields = classType.getDeclaredFields();for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++){String columnName = rs.getMetaData().getColumnName(i).toUpperCase();
//                System.out.println("columnName=> " + columnName);ColumnNameList.add(columnName);}while (rs.next()){for (Field field : fields){String fieldName = field.getName();// 如果实体类中的fieldName在ResultSet中没有，不索引取值，防止出现异常if (ColumnNameList.contains(fieldName.toUpperCase())){
//                        System.out.println("fieldName=> " + fieldName);BeanUtils.setProperty(entity, fieldName, rs.getObject(fieldName));}}break;}}catch (Exception e){throw new RuntimeException(e.getMessage());}return entity;}
}

这篇关于ORACLE语法-包(package)、存储过程(procedure)、游标(cursor)以及java对Result结果集的处理的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---