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