前提

由于业务需要得通过存储过程返回结果集，方便现场实施进行自主调整sql语句条件，查到结果集需要重新进行组合，按照和前端约定的数据结构进行返回，此次调用oracle存储函数通过jdbctemplate的方式调用

介绍

Oracle存储过程是一种用于执行特定数据库功能的SQL语句集合。它们经过编译后存储在数据库系统中，可以通过指定存储过程名称并给出相应的参数来调用和执行。

Oracle存储过程通常包含以下三部分：

过程声明：定义存储过程的名称、参数和返回值等信息。 例如，以下代码定义了一个名为"get_employee_details"的存储过程，它接受一个参数"employee_id"并返回一个结果集：

sql
CREATE OR REPLACE PROCEDURE get_employee_details (  
    employee_id IN NUMBER,  
    employee_details OUT sys_refcursor  
) AS  
BEGIN  
    -- 执行查询操作  
    OPEN employee_details FOR  
    SELECT * FROM employees WHERE employee_id = employee_id;  
END;  
/

执行过程部分：包含具体的数据库操作和逻辑判断。 例如，以下代码展示了一个简单的存储过程，它接受两个参数"start_date"和"end_date"，并返回一个结果集：

sql
CREATE OR REPLACE PROCEDURE get_sales_report (  
    start_date IN DATE,  
    end_date IN DATE,  
    sales_report OUT sys_refcursor  
) AS  
BEGIN  
    -- 执行查询操作  
    OPEN sales_report FOR  
    SELECT * FROM sales WHERE sale_date BETWEEN start_date AND end_date;  
END;  
/

存储过程异常：用于处理可能出现的异常情况，以保证存储过程的稳定性和可靠性。 例如，以下代码展示了一个带有异常处理的存储过程，它接受一个参数"employee_id"，并返回一个结果集：

sql
CREATE OR REPLACE PROCEDURE get_employee_details (  
    employee_id IN NUMBER,  
    employee_details OUT sys_refcursor  
) AS  
BEGIN  
    -- 判断参数是否为空  
    IF employee_id IS NULL THEN  
        RAISE_APPLICATION_ERROR(-20001, 'Please provide an employee ID.');  
    END IF;  
    -- 执行查询操作  
    OPEN employee_details FOR  
    SELECT * FROM employees WHERE employee_id = employee_id;  
EXCEPTION WHEN OTHERS THEN  
    ROLLBACK; -- 处理异常并回滚事务  
END;  
/

实现

创建oracle存储过程

sql

create or replace procedure PRO_REC_HOMEPAGE_PANE(deptCode  in varchar2,
                                                  startTime in date,
                                                  endTime   in date,
                                                  list2     out sys_refcursor) is
begin
  OPEN list2 FOR
  
    select '出院人数' as CARD_NAME,
           0 as UNDO_COUNT,
           (SELECT COUNT(*) AS people_count
              FROM pat_visit_v a
             WHERE a.DISCHARGE_TIME IS NOT NULL
               AND a.DISCHARGE_TIME >= startTime
               AND a.DISCHARGE_TIME <= endTime
               AND (deptCode IS NULL OR a.DEPT_DISCHARGE_FROM = deptCode)) as DO_COUNT
      from dual
    union all
    select '病案提交' as CARD_NAME,
           (select count(*) as people_count
              from rec_mrhp_submit b, pat_visit_v t
             where t.patient_id = b.PATIENT_ID(+)
               and t.VISIT_NO = b.VISIT_ID(+)
               and (b.mr_status is null or b.MR_STATUS = '0')
               and t.DISCHARGE_TIME is not null
               AND t.DISCHARGE_TIME >= startTime
               AND t.DISCHARGE_TIME <= endTime
               AND (deptCode IS NULL OR t.DEPT_DISCHARGE_FROM = deptCode)) as UNDO_COUNT,
           (select count(*) as people_count
              from rec_mrhp_submit b, pat_visit_v t
             where t.patient_id = b.PATIENT_ID(+)
               and t.VISIT_NO = b.VISIT_ID(+)
               and b.mr_status in ('4', '5')
               and t.DISCHARGE_TIME is not null
               AND t.DISCHARGE_TIME >= startTime
               AND t.DISCHARGE_TIME <= endTime
               AND (deptCode IS NULL OR t.DEPT_DISCHARGE_FROM = deptCode)) as DO_COUNT
      from dual;

end PRO_REC_HOMEPAGE_PANE;

java调用

java

@Resource
    private JdbcTemplate jdbcTemplate;
    @Override
    public QueryPatientCountResponse getQueryPatientCount(SystemOverviewSearchRequest request) {
        QueryPatientCountResponse response=new QueryPatientCountResponse();
        List<PatientCountEntity>  patientCountEntityList =new ArrayList<>();
        List resultList = (List) jdbcTemplate.execute(
                new CallableStatementCreator() {
                    public CallableStatement createCallableStatement(Connection con) throws SQLException {
                        String storedProc = "{call PRO_REC_HOMEPAGE_PANE(?,?,?,?)}";// 调用的sql
                        CallableStatement cs = con.prepareCall(storedProc);
                        cs.setString(1, StrUtil.isBlank(request.getDeptCode())?null:request.getDeptCode());// 设置输入参数的值

                        //javaDate转sqlDate
                        java.util.Date utilDate=request.getStartTime();
                        //java.util.Date转Timestamp
                        Timestamp sqlTimestamp = new Timestamp(utilDate.getTime());
                        //Timestamp转java.sql.Date
                        Date startTime = new Date(sqlTimestamp.getTime());

                        utilDate=request.getEndTime();
                        sqlTimestamp = new Timestamp(utilDate.getTime());
                        Date endTime = new Date(sqlTimestamp.getTime());
                        cs.setDate(2, startTime);
                        cs.setDate(3,endTime);
                        cs.registerOutParameter(4, OracleTypes.CURSOR);// 注册输出参数的类型
                        return cs;
                    }
                }, new CallableStatementCallback() {
                    public Object doInCallableStatement(CallableStatement cs) throws SQLException{
                        List resultsMap = new ArrayList();
                        cs.execute();
                        ResultSet rs = (ResultSet) cs.getObject(4);// 获取游标一行的值
                        while (rs.next()) {// 转换每行的返回值到Map中
                            if (rs.getString("card_Name").equals("出院人数")){
                                response.setDischargeCount(rs.getInt("do_Count"));
                            }else {
                                PatientCountEntity entity = new PatientCountEntity();
                                entity.setCardName(rs.getString("card_Name"));
                                entity.setDoCount(rs.getInt("do_Count"));
                                entity.setUndoCount(rs.getInt("undo_Count"));
                                patientCountEntityList.add(entity);
                            }
                        }
                        rs.close();
                        return resultsMap;
                    }
                });
        response.setPatientCountEntityList(patientCountEntityList);
        return response;
    }