存储过程 自定义类型 java_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 findAllTeamManager(String conditionSelect){

Connection conn = null;

CallableStatement stmt = null;

List 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();

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.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;

}

分享到：

2010-03-09 14:53

浏览 2197

分类:数据库

评论