oracle相关知识

//oracle存储过程

CREATE OR REPLACE PROCEDURE "PROC_GROUP_SUMMARY_EBM" (
  VAR_CELL_ID IN VARCHAR2,
  VAR_GROUP_ID IN VARCHAR2,
  VAR_DATE IN DATE,
  VAR_OWNERLAND IN VARCHAR2,
  VAR_MOBILE_MANU IN VARCHAR2,
  VAR_OUT OUT PK_MOBILE.CUR_GROUP_SUMMARY
)
AS
  QUERY_STR VARCHAR2(1000) :=
  'SELECT DISTINCT
    F_GROUP_ID_CRBG,
    F_GROUP_NAME_CRBG,
    F_CELL_ID_CRBG,
    F_CELL_LOCAL_DESP_CRBG,
    F_RE_DATE_CRBG,
    COUNT(DISTINCT F_MSISDN_CRBG)
  FROM
    V_CS_RECORDS_BY_GROUP_EBM WHERE 1=1';
BEGIN

  IF (VAR_DATE IS NOT NULL) THEN
    BEGIN
      QUERY_STR := QUERY_STR||' AND F_RE_DATE_CRBG='''||VAR_DATE||'''';
    END;
  END IF;
  IF (VAR_CELL_ID IS NOT NULL) THEN
    BEGIN
      QUERY_STR := QUERY_STR||' AND F_CELL_ID_CRBG LIKE ''%'||VAR_CELL_ID||'%''';
    END;
  END IF;
  IF (VAR_GROUP_ID IS NOT NULL) THEN
    BEGIN
      QUERY_STR := QUERY_STR||' AND F_GROUP_ID_CRBG='''||VAR_GROUP_ID||'''';
    END;
  END IF;
  IF (VAR_OWNERLAND IS NOT NULL) THEN
    BEGIN
      QUERY_STR := QUERY_STR||' AND F_MSISDN_OWNERLAND_CRBG LIKE ''%'||VAR_OWNERLAND||'%''';
    END;
  END IF;
  IF (VAR_MOBILE_MANU IS NOT NULL) THEN
    BEGIN
      QUERY_STR := QUERY_STR||' AND LOWER(F_MSISDN_MANU_CRBG) LIKE ''%'||VAR_MOBILE_MANU||'%''';
    END;
  END IF;

  -- Group by AND Order by
  QUERY_STR := QUERY_STR||' GROUP BY
    F_GROUP_ID_CRBG,
    F_GROUP_NAME_CRBG,
    F_CELL_ID_CRBG,
    F_CELL_LOCAL_DESP_CRBG,
    F_RE_DATE_CRBG
  ORDER BY
    F_RE_DATE_CRBG DESC,
    F_CELL_ID_CRBG ASC';

  OPEN VAR_OUT FOR QUERY_STR;

END;






//package
CREATE OR REPLACE PACKAGE "PK_MOBILE" AS
  TYPE CUR_MOBILE_SOURCE_STATISTICS IS REF CURSOR;
End PK_MOBILE;




//view
CREATE OR REPLACE VIEW V_CELLS_BY_GROUP_EBM
(f_group_name_cbg, f_cell_id_cbg, f_cell_name_cbg, f_cell_lat_cbg, f_cell_lon_cbg, f_cell_local_desp_cbg, f_cell_district_cbg, f_group_id_cbg)
AS
SELECT
    CG.F_NAME_CG,
CI.F_CELL_ID_CI,
CI.F_CELL_NAME_CI,
CI.F_LAT_CI,
CI.F_LON_CI,
    LD.F_DESCRIPTION_LD,
    DIST.F_NAME_DIST,
    CG.F_ID_CG
FROM
    T_CELL_INFO_EBM CI
LEFT JOIN
    T_CELL_GROUP_EBM CG ON CI.F_GROUP_ID_CI=CG.F_ID_CG
LEFT JOIN
    T_DISTRICT_EBM DIST ON CI.F_DISTRICT_ID_CI=DIST.F_ID_DIST
LEFT JOIN
T_LOCAL_DESP_EBM LD ON CI.F_LOCAL_DESP_ID_CI=LD.F_ID_LD


//调用
String procCallableString = "{call PROC_GROUP_SUMMARY_SOURCE_EBM(?,?,?,?,?,?)}";
ResultSet resultSet = null;


try {

CallableStatement callableStatement = connection.prepareCall(procCallableString);
callableStatement.setString(1, cellId);
callableStatement.setString(2, groupId);
callableStatement.setDate(3, date);
callableStatement.setString(4, ownerLand);
callableStatement.setString(5, mobileManu);
callableStatement.registerOutParameter(6, OracleTypes.CURSOR);
callableStatement.execute();

resultSet = (ResultSet) callableStatement.getObject(6);
while (resultSet.next()) {

Map<String, Object> currentMap = new HashMap<String, Object> ();
currentMap.put("groupName", resultSet.getString("F_GROUP_NAME_CRBG"));
currentMap.put("ownerLand", resultSet.getString("F_MSISDN_OWNERLAND_CRBG"));
currentMap.put("date", DatetimeUtil.stringFormat10(resultSet.getDate("F_RE_DATE_CRBG")));
currentMap.put("count", resultSet.getLong(4));
records.add(currentMap);
}

} catch (SQLException e) {
e.printStackTrace();
}

return records;