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;
?}
?
