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_te