欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

oracle相关知识

程序员文章站 2022-06-24 16:54:20
...
//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;