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) IS
  BEGIN
    errno :=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 THEN
      ROLLBACK;
      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
  -- Initialization
  NULL;
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;
    }
}