oracle中存储过程如何使用游标返回数据

存储过程 专栏收录该内容
5 篇文章 0 订阅

大家好,本人是一只写存储过程和游标的菜鸟,本篇博客没有什么干货,纯粹是想记录一下自己第一次使用游标的所得。之前只是听说个这两个概念,上周我们老大突然让我尝试着写存储过程和游标来实现一些功能,我花了两天才把功能完成。所以想先记录一下现在的理解。
首先我先把我写的存储过程贴出来,我用的是Oracle数据库

create or replace procedure p_censor_feedback_state(r_codes    out varchar2,
                                                 r_msg      out varchar2,
                                                 r_data     out varchar2,
                                                 r_cur_data out sys_refcursor,info007_start in varchar2,
info007_end in varchar2,info008_start in varchar2,info008_end in varchar2,p_info010 in varchar2,p_info012 in varchar2,p_info011 in varchar2,p_info013 in varchar2
,p_info003 in varchar2,p_info004 in varchar2,p_info101 in varchar2) is

    --统计逾期未反馈的部门情况

    m_uuid varchar2(32); -- 唯一id
    m_time varchar2(30);  -- 执行时间
    m_list varchar2(2000); --

begin

    r_codes := 'success';
    r_msg :=null;
    r_data :=null;
    --生成唯一id信息
    select sys_guid() into m_uuid from dual;
    dbms_output.put_line(m_uuid);
    -- 记录插入时间
    select to_char(systimestamp, 'yyyy-mm-dd hh24:mi:ss') into m_time from dual;

    -- 统计逾期未反馈的流程信息
    declare
        cursor m_list is
            select * from t_censor_flow_inst_major t
                where (t.del_flag = '0' or t.del_flag is null ) and t.info103 = '1'
                and(info007_start is null or t.info007 > info007_start)  
                    and(info007_end is null or t.info007 < info007_end)  
                    and(info008_start is null or t.info008 > info008_start)  
                    and(info008_end is null or t.info008 > info008_end)  
                    and(p_info010 is null or instr(t.info010,p_info010)>0)  
                    and(p_info011 is null or instr(t.info011,p_info011)>0)  
                    and(p_info012 is null or instr(t.info012,p_info012)>0)  
                    and(p_info013 is null or instr(t.info013,p_info013)>0)  
                    and(p_info003 is null or instr(t.info003,p_info003)>0)   
                    and(p_info004 is null or t.info004 = p_info004)
                    and(p_info101 is null or t.info101 = p_info101);
    begin
        for instMajorObj in m_list loop
            insert into t_tmp_censor_feedback_state
                --(select m_uuid,sys_guid(),1,instMajorObj.info066 from dual );
                (select m_uuid,sys_guid(),1,t.info066
                from t_censor_flow_instance t where t.id in (select distinct first_value(t1.id) over(partition by t1.info065 order by t1.info071 desc)
                                                                from t_censor_flow_instance t1
                                                             where t1.id_flow_inst_major = instMajorObj.id and t1.info110 != '2' and t1.del_flag = '0'));
        end loop;
    end ;       
    --获得查询结果(输出)
     begin 
       open r_cur_data for 
       select * from (select u.name_org,sum(u.count) as count from t_tmp_censor_feedback_state u group by u.name_org order by sum(u.count) desc) where rownum<6;
     end;  

    -- 删除中间表数据
    --delete t_tmp_censor_feedback_state t where t.uuid = m_uuid;
    commit;

end p_censor_feedback_state;

需要实现的功能大概就是:根据一部分查询条件需要实时的,查询符合条件的数据,在前端页面图形显示。在写存储过程的时候我本来是想全部使用游标进行操作的,结果想了好久没想到方法,没办法只能使用临时表t_tmp_censor_feedback_state暂时存储数据,然后使用游标把查询到的数据返回到后台,就是输出参数r_cur_data,它保存着要返回的数据,大概就是这样,在这里要注意一点,对数据进行处理的时候尽量根据uuid来操作,这样可以避免不必要的麻烦。之后我会更新后台如何接收游标传过来的数据。

后台代码:

@Override
	public ReturnDatas listCensorFeedbackState(String info007_start, String info007_end, String info008_start,
			String info008_end, String info010, String info012, String info011, String info013, String info003,
			String info004, String info101) throws Exception {
		ReturnDatas returnDatas = ReturnDatas.getSuccessReturnDatas();
		CountCensorOrgAllPo countCensorOrgAllPo=new CountCensorOrgAllPo();
        ReturnProcDatas returnProcDatas = null;
        if(StringUtils.isBlank(info007_start)) {
        	info007_start = "";
        }
        if(StringUtils.isBlank(info007_end)) {
        	info007_end = "";
        }
        if(StringUtils.isBlank(info008_start)) {
        	info008_start = "";
        }
        if(StringUtils.isBlank(info008_end)) {
        	info008_end = "";
        }
        if(StringUtils.isBlank(info010)) {
        	info010 = "";
        }
        if(StringUtils.isBlank(info012)) {
        	info012 = "";
        }
        if(StringUtils.isBlank(info011)) {
        	info011 = "";
        }
        if(StringUtils.isBlank(info013)) {
        	info013 = "";
        }
        if(StringUtils.isBlank(info003)) {
        	info003 = "";
        }
        if(StringUtils.isBlank(info004)) {
        	info004 = "";
        }
        if(StringUtils.isBlank(info101)) {
        	info101 = "";
        }
        
        //1 定义一个List<String> 需要传入的参数统一添加进去
        List<String> p_listStr = new ArrayList<>();
        p_listStr.add(info007_start);
        p_listStr.add(info007_end);
        p_listStr.add(info008_start);
        p_listStr.add(info008_end);
        p_listStr.add(info010);
        p_listStr.add(info012);
        p_listStr.add(info011);
        p_listStr.add(info013);
        p_listStr.add(info003);
        p_listStr.add(info004);
        p_listStr.add(info101);
        
        //2 调用创造参数函数  返回List<ProcEntity> 参数:p_listStr
        @SuppressWarnings("rawtypes")
        List<ProcEntity> proInPrams = baseInfoService.createInProcParameter(p_listStr);
        returnProcDatas = baseInfoService.execuseProc(proInPrams,"p_censor_feedback_state",null);
        List<Object[]> censorFeedbackStateForFive = (List<Object[]>) returnProcDatas.getCurData();
        List<String> censorOrgNameList=new ArrayList<String>();
        List<String> censorOrgCountList=new ArrayList<String>();
        List<PieData> censorTypeAndCountList=new ArrayList<PieData>();
        for (int j = 0; j < censorFeedbackStateForFive.size(); j++) {
        	String a=""+censorFeedbackStateForFive.get(j);
            JSONObject object=JSONObject.fromObject(a);
            if(object.get("NAME_ORG")!=null) {
            	censorOrgNameList.add(object.get("NAME_ORG").toString());
            	censorOrgCountList.add(object.get("COUNT").toString());
            	PieData pieData = new PieData(object.get("NAME_ORG").toString(),object.get("COUNT"));
            	censorTypeAndCountList.add(pieData);
            }
            //censorOrgCountList.add(object.get("COUNT").toString());
            
        }
        countCensorOrgAllPo.setCensorOrgNameList(censorOrgNameList);
        countCensorOrgAllPo.setCensorOrgAllAndCountList(censorTypeAndCountList);
        countCensorOrgAllPo.setCensorOrgCountList(censorOrgCountList);
        returnDatas.setData(countCensorOrgAllPo);
        return returnDatas;
	}

createInProcParameter:

@SuppressWarnings("unchecked")
	@Override
	public List<ProcEntity> createInProcParameter(List<String> listStr)
			throws Exception {
		List<ProcEntity> inList = new ArrayList<>();
		//判断是否为空
		if(CollectionUtils.isEmpty(listStr)) {
			return inList;
		}
		int procIndex = 5; //默认为5
		for(int i = 0 ;i<listStr.size();i++) {
			ProcEntity pe = new ProcEntity();
			pe.setIndex(procIndex);
			pe.setType(OracleTypes.VARCHAR);
			pe.setValue(listStr.get(i));
			inList.add(pe);
			procIndex++;
		}
		return inList;
	}

execuseProc:

@Override
	public <T> ReturnProcDatas execuseProc(List<ProcEntity> procInParams, String procName, Class<T> t) throws Exception {
		ReturnProcDatas returnProcDatas = new ReturnProcDatas();
		SessionImplementor session = em.unwrap(SessionImplementor.class);
		Connection conn = session.connection();
		//创造输出参数
		List<ProcEntity> outVal = CreateProcParameters();
		if (outVal.size() == 0) {
			throw new NullPointerException();
		}
		StringBuffer sb = new StringBuffer();
		sb.append(" {call " + procName + "(");
		int count = outVal.size()+ procInParams.size();
		for (int i = 0; i < count; i++) {
			sb.append("?,");
		}
		sb.delete(sb.length() - 1, sb.length());
		sb.append(")}");
		CallableStatement call = null;
		List<T> list = null;

		call = conn.prepareCall(sb.toString());
		for (int i = 0; i < procInParams.size(); i++) {
			ProcEntity pe = procInParams.get(i);
			String valType = pe.getValue().getClass().getName();
			if (valType.equals("java.lang.Integer")) {
				call.setInt(pe.getIndex(), (Integer) pe.getValue());
			} else if (valType.equals("java.lang.Long")) {
				call.setLong(pe.getIndex(), (Long) pe.getValue());
			} else if (valType.equals("java.lang.Double")) {
				call.setDouble(pe.getIndex(), (Double) pe.getValue());
			} else {
				call.setString(pe.getIndex(), (String) pe.getValue());
			}
		}

		for (int i = 0; i < outVal.size(); i++) {
			ProcEntity pe = outVal.get(i);
			call.registerOutParameter(pe.getIndex(), pe.getType());
		}
		try {
			call.executeQuery();

		} catch (SQLSyntaxErrorException e) {
			// TODO Auto-generated catch block
			LogUtil.warn("java.sql.SQLSyntaxErrorException: ORA-00900: 无效 SQL 语句,该异常已处理。");

		}
		for (int i = 0; i < outVal.size(); i++) {
			ProcEntity pe = outVal.get(i);
			int index = i + 1;
			// 对应 r_codes
			if(i == 0 ) {
				if(pe.getType() == OracleTypes.VARCHAR) {
					String r_codes = call.getString(index);
					returnProcDatas.setCodes(r_codes);
				}
			}else if(i==1) { // 对应 r_msg
				if(pe.getType() == OracleTypes.VARCHAR) {
					String r_msg = call.getString(index);
					returnProcDatas.setMessage(r_msg);
				}
			}else if(i==2) {// 对应 r_data
				if(pe.getType() == OracleTypes.VARCHAR) {
					String r_data = call.getString(index);
					returnProcDatas.setR_Data(r_data);
				}
			}else if(i==3) { // 对应 r_cur_data
				if(pe.getType() == OracleTypes.CURSOR) {
					ResultSet rs;
					rs = (ResultSet) call.getObject(index);
					list = ResultSetUtil.convertToList(rs, t);
					returnProcDatas.setCurData(list);
				}
			}

		}

		return returnProcDatas;
	}

ReturnProcDatas.java:

/**
 * 返回存储过程对象的封装
 *
 * @author lilong
 */
@SuppressWarnings("serial")
public class ReturnProcDatas implements Serializable {
    public static final String SUCCESS = "success";
    public static final String ERROR = "error";
    public static final String WARNING = "warning";
    private String codes;
    private String message;
    private String r_Data;
    private Object curData;

    public ReturnProcDatas() {

    }

    public String getMessage() {
        return message;
    }

    public String getCodes() {
        return codes;
    }

    public void setCodes(String codes) {
        this.codes = codes;
    }

    public String getR_Data() {
        return r_Data;
    }

    public void setR_Data(String r_Data) {
        this.r_Data = r_Data;
    }

    public Object getCurData() {
        return curData;
    }

    public void setCurData(Object curData) {
        this.curData = curData;
    }

    public void setMessage(String message) {
        this.message = message;
    }


}

这篇博客我会不断更新,谢谢大家的阅读,希望我们都能不断进步.

  • 4
    点赞
  • 0
    评论
  • 2
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 深蓝海洋 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值