本文介绍了一种使用Oracle PL/SQL存储过程结合JAVA程序来实现球队比赛数据的统计与分析的方法。通过定义自定义类型TEAMVO及TEAMVOS，创建存储过程team_manager，实现了根据条件查询语句获取球队的比赛场次、胜负情况、进球数等数据，并通过JAVA程序调用该存储过程，最终返回球队统计信息列表。
1. 建立type
CREATE OR REPLACE TYPE TEAMVO AS OBJECT (
team_name varchar2(100),
team_race_name varchar2(100),
team_showing number(4),
team_race_season_name varchar2(100),
team_type varchar2(10),
team_scores number(4),
win_showing number(3),
equal_showing number(3),
lose_showing number(3),
win_rate varchar2(10),
equal_rate varchar2(10),
lose_rate varchar2(10),
in_goals number(4),
lose_goals number(4),
in_goals_avg varchar2(10),
lose_goals_avg varchar2(10),
goal_difference number(3)
)
CREATE OR REPLACE TYPE TEAMVOS is table of TEAMVO
2. 建立存储过程
create or replace package body team is
procedure team_manager(
p_ConditionSelect varchar2, --条件查询语句
p_Out out TEAMVOS)
is
type refCursorType IS REF CURSOR;
type teams is table of string(100) index by pls_integer;
v_sql varchar2(1000);
against_c refCursorType;
t_race_name varchar2(100); --联赛名称
t_race_season_name varchar2(100); --赛季名
t_host_name varchar2(100);
t_guest_name varchar2(100);
t_team_type char(1);
t_score varchar2(20);
t_race_result varchar2(10);
t_host_goal number(4):=0;
t_guest_goal number(4):=0;
contains_flag1 int := -1;
contains_flag2 int := -1;
against_teams teams;
team_vo TEAMVO;
begin
team_vo := TEAMVO('','',0,'','',0,0,0,0,'','','',0,0,'','',0);
p_Out := TEAMVOS();
v_sql := 'select t.race_name,t.race_season_name,t.host_name,t.guest_name,t.type,t.score,t.race_result,
to_number(substr(t.score,0,instr(t.score,'':'')-1)) as host_goal,
(case when instr(t.score,''*'') = 0 then to_number(substr(t.score,instr(t.score,'':'')+1,length(t.score)))
else to_number(substr(t.score,instr(t.score,'':'')+1,instr(t.score,''*'')-instr(t.score,'':'')-1)) end ) as guest_goal
from t_against t where t.status = 2 '|| p_ConditionSelect ;
open against_c for v_sql;
loop
fetch against_c into t_race_name,t_race_season_name,t_host_name,t_guest_name,t_team_type,t_score,t_race_result,t_host_goal,t_guest_goal;
EXIT WHEN against_c%NOTFOUND;
for x in 0..against_teams.count-1 loop
--已包含球队
if against_teams(x) = t_host_name then
contains_flag1 := 1;
elsif against_teams(x) = t_guest_name then
contains_flag2 := 1;
end if;
end loop;
-- 增加主队
if contains_flag1 = -1 then
against_teams(against_teams.count) := t_host_name;
p_Out.extend;
p_Out(against_teams.count) := team_vo;
p_Out(against_teams.count).team_name := t_host_name;
if t_team_type ='1' then p_Out(against_teams.count).team_type := '联赛队';
elsif t_team_type ='2' then p_Out(against_teams.count).team_type := '国家队';
else p_Out(against_teams.count).team_type := '其他队';
end if;
p_Out(against_teams.count).team_race_name := t_race_name;
p_Out(against_teams.count).team_race_season_name := t_race_season_name;
end if;
--增加客队
if contains_flag2 = -1 then
against_teams(against_teams.count) := t_guest_name;
p_Out.extend;
p_Out(against_teams.count) := team_vo;
p_Out(against_teams.count).team_name := t_guest_name;
if t_team_type ='1' then p_Out(against_teams.count).team_type := '联赛队';
elsif t_team_type ='2' then p_Out(against_teams.count).team_type := '国家队';
else p_Out(against_teams.count).team_type := '其他队';
end if;
p_Out(against_teams.count).team_race_name := t_race_name;
p_Out(against_teams.count).team_race_season_name := t_race_season_name;
end if;
/*
DBMS_OUTPUT.PUT_LINE('-------------------------------');
for i in 1..against_teams.count loop
DBMS_OUTPUT.PUT_LINE(p_Out(i).team_name);
end loop;
DBMS_OUTPUT.PUT_LINE('-------------------------------');
DBMS_OUTPUT.PUT_LINE('主队：'|| t_host_name ||'客队：'||t_guest_name);
*/
for i in 1..against_teams.count loop
--主场
if p_Out(i).team_name = t_host_name then
p_Out(i).team_showing := p_Out(i).team_showing +1;
if t_race_result = '3' then
p_Out(i).win_showing := p_Out(i).win_showing +1;
elsif t_race_result = '1' then
p_Out(i).equal_showing := p_Out(i).equal_showing +1;
else
p_Out(i).lose_showing := p_Out(i).lose_showing +1;
end if;
p_Out(i).team_scores := p_Out(i).team_scores + t_race_result;
p_Out(i).in_goals := p_Out(i).in_goals + t_host_goal;
p_Out(i).lose_goals := p_Out(i).lose_goals + t_guest_goal;
--客场
elsif p_Out(i).team_name = t_guest_name then
p_Out(i).team_showing := p_Out(i).team_showing +1;
if t_race_result = '3' then
p_Out(i).lose_showing := p_Out(i).lose_showing +1;
elsif t_race_result = '1' then
p_Out(i).equal_showing := p_Out(i).equal_showing +1;
p_Out(i).team_scores := p_Out(i).team_scores + 1;
else
p_Out(i).win_showing := p_Out(i).win_showing +1;
p_Out(i).team_scores := p_Out(i).team_scores + 3;
end if;
p_Out(i).in_goals := p_Out(i).in_goals + t_guest_goal;
p_Out(i).lose_goals := p_Out(i).lose_goals + t_host_goal;
end if;
end loop;
contains_flag1 := -1;
contains_flag2 := -1;
end loop;
for i in 1..against_teams.count loop
if p_Out(i).team_showing != 0 then
p_Out(i).win_rate := to_char( floor(p_Out(i).win_showing *10000/ p_Out(i).team_showing+0.5)/100)||'%';
p_Out(i).equal_rate := to_char( floor(p_Out(i).equal_showing *10000/ p_Out(i).team_showing+0.5)/100)||'%';
p_Out(i).lose_rate := to_char( floor(p_Out(i).lose_showing *10000/ p_Out(i).team_showing+0.5)/100)||'%';
p_Out(i).in_goals_avg := to_char( floor(p_Out(i).in_goals *100/ p_Out(i).team_showing+0.5)/100);
p_Out(i).lose_goals_avg := to_char( floor(p_Out(i).lose_goals *100/ p_Out(i).team_showing+0.5)/100);
end if;
p_Out(i).goal_difference := p_Out(i).in_goals - p_Out(i).lose_goals;
end loop;
DBMS_OUTPUT.PUT_LINE('球队名称'||'--' ||'场次' ||'--' ||'球队类型'
||'--' ||'总进球数'||'--'||'总失球数'||'--'||'胜'||'--'||'平'
||'--'||'负' ||'--'||'平均得球'||'--'||'平均失球'||'--'||'总积分'
||'--'||'净胜球' );
for x in 1..against_teams.count loop
DBMS_OUTPUT.PUT_LINE(p_Out(x).team_name||'--' ||
p_Out(x).team_showing ||'--' ||
p_Out(x).team_type ||'--' ||
p_Out(x).in_goals ||'--' ||
p_Out(x).lose_goals ||'--' ||
p_Out(x).win_rate ||'--' ||
p_Out(x).equal_rate ||'--' ||
p_Out(x).lose_rate ||'--' ||
p_Out(x).in_goals_avg ||'--' ||
p_Out(x).lose_goals_avg ||'--' ||
p_Out(x).team_scores ||' ' ||
p_Out(x).goal_difference ||' ' ||
p_Out(x).team_scores
);
--DBMS_OUTPUT.PUT_LINE(1);
end loop;
CLOSE against_c;
end team_manager;
end team;
3.JAVA调用
public List<TeamVo> findAllTeamManager(String conditionSelect){
Connection conn = null;
CallableStatement stmt = null;
List<TeamVo> teamList = null;
TeamVo teamVo = null;
Object[] objs = null;
String sql = "{ call team.team_manager(?,?)}";
try {
conn = SessionFactoryUtils.getDataSource(getSessionFactory()).getConnection();
stmt = conn.prepareCall(sql.replace("%", "'").replace("'", ""));
stmt.setString(1, conditionSelect);
stmt.registerOutParameter(2,OracleTypes.ARRAY,"LOTTERY.TEAMVOS");
stmt.execute();
Object[] obj = (Object[])stmt.getArray(2).getArray();
if(obj != null){
teamList = new ArrayList<TeamVo>();
for(Object o : obj){
objs = ((STRUCT)o).getAttributes();
teamVo = new TeamVo();
teamVo.setHostName((String)objs[0]);
teamVo.setReceName((String)objs[1]);
teamVo.setShowing((BigDecimal)objs[2]);
teamVo.setRaceSeasonName((String)objs[3]);
teamVo.setTeamType((String)objs[4]);
teamVo.setTeamScores((BigDecimal)objs[5]);
teamVo.setWinShowing((BigDecimal)objs[6]);
teamVo.setEqualShowing((BigDecimal)objs[7]);
teamVo.setLostShowing((BigDecimal)objs[8]);
teamVo.setWinShowingRate((String)objs[9]);
teamVo.setEqualShowingRate((String)objs[10]);
teamVo.setLostShowingRate((String)objs[11]);
teamVo.setInGoals((BigDecimal)objs[12]);
teamVo.setLostGoals((BigDecimal)objs[13]);
teamVo.setInGoals_avg((String)objs[14]);
teamVo.setLostGoals_avg((String)objs[15]);
teamVo.setTeam_gd((BigDecimal)objs[16]);
teamList.add(teamVo);
}
Collections.sort(teamList,Collections.reverseOrder());
for(int i = 0;i<teamList.size();i++){
teamList.get(i).setRanking(i+1);
}
}
} catch (SQLException e) {
conn = null;
stmt = null;
teamList = null;
e.printStackTrace();
return null;
} finally{
conn = null;
stmt = null;
}
return teamList;
}
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
