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;