不胜人生一场醉

今天闲的无聊写了个存储过程，把itpub NBA菠菜的胜负关系倒腾到数据库中，进行分析，方便菠菜时进行参考。

初始的时候写了个存储过程，只是想把比赛名单拷贝参数中，由存储过程对名单按照“，”进行分解，写入数据表中，然后通过分组函数求出最高胜率的。

完成之后，突然想自己还没写过包的东西，以前只是纸上谈兵，不如写写看看，后来逐渐把面向对象的一些基本的set,get方法和多态也加进来了，有点意思。

不过写的过程中也遇到不少语法问题，看样子还得多写点才行，:)

create table ITPUBNBABET
(
ITPUBID VARCHAR2(100) not null,
WINSTATUS CHAR(1) not null,
OPERDATE DATE,
MATCH VARCHAR2(100) not null,
MATCHDATE DATE not null
)

alter table ITPUBNBABET
add constraint TTTTTT primary key (ITPUBID, WINSTATUS, MATCH, MATCHDATE)

create or replace package DBMS_ITPUBBET is
-- Author : ADMINISTRATOR
-- Created : 2007-11-4 20:05:48
-- Purpose : For itpub bet purpose
type RefList is REF CURSOR; --return records by cursor
pTopN INTEGER:=5; --define default display return result

FUNCTION f_get_topN RETURN INTEGER; --get the value of topN variable
Procedure p_set_topN(iTopN in INTEGER); --set the value of topN variable
--INPUT THE MATCH INFORMATIION
Procedure p_input_matchitem(
iWinList in VARCHAR2, --the winner list copy from itpub
iLostList in VARCHAR2, --the loster list copy from itpub
iMatchName in VARCHAR2, --the NBA match name copy from intpub
iMatchDate in DATE, --the NBA match date, default is today
oRetCode out int, --the execute status,0 success,1 fause
oRetMsg out VARCHAR2 --the execute message,include the success information and error message
);

Procedure p_get_topNResult(oCur out RefList); --return result by cursor type
Procedure p_get_topNResult(oRet out varchar2); --return result by varchar
end DBMS_ITPUBBET;

create or replace package body DBMS_ITPUBBET is

FUNCTION f_get_topN RETURN INTEGER
IS
BEGIN
RETURN DBMS_ITPUBBET.pTopN;
END;

Procedure p_set_topN(iTopN INTEGER)
IS
BEGIN
IF iTopN is not Null then
DBMS_ITPUBBET.pTopN :=iTopN;
END IF;
END;

Procedure p_get_topNResult(oCur out RefList)
IS
BEGIN

open oCur for select itpubID,topN from (select itpubID,count(*) topN from itpubNBAbet group by itpubID order by topN desc) where rownum<=ptopN;

END;

Procedure p_get_topNResult(oRet out varchar2)
IS
cursor cur_topN is select itpubID,topN from (select itpubID,count(*) topN from itpubNBAbet where winstatus='W' group by itpubID order by topN desc) where rownum<=DBMS_ITPUBBET.ptopN;
vitpubID VARCHAR2(200);
vTopN integer;
strlen integer;
vRet VARCHAR2(1000);
BEGIN

open cur_topN;
loop
fetch cur_topN into vitpubID,vTopN;
exit when cur_topN%notfound;
vRet := vRet||'itpub ID ='||LPAD(vitpubID,15,' ')||' Win total='||to_char(vTopN)||chr(13)||chr(10);
end loop;
close cur_topN;
oRet:=vRet;
END;

--INPUT THE MATCH INFORMATIION
Procedure p_input_matchitem(
iWinList in VARCHAR2, --the winner list copy from itpub
iLostList in VARCHAR2, --the loster list copy from itpub
iMatchName in VARCHAR2, --the NBA match name copy from intpub
iMatchDate in DATE, --the NBA match date, default is today
oRetCode out int, --the execute status,0 success,1 fause
oRetMsg out VARCHAR2 --the execute message,include the success information and error message
)
is
all_bet varchar2(4000);
betname varchar2(50);
lengstr integer;
bpos integer;
matchname varchar2(200);
matchdate date;
begin
matchdate:=imatchdate;
matchname:=imatchname;
all_bet :=iWinList;
bpos := instr(all_bet,',');
while bpos>0 loop
betname:=substr(all_bet,1,bpos-1);
lengstr:=length(all_bet);
all_bet:=substr(all_bet,bpos+1,lengstr-bpos);
insert into itpubNBAbet values(betname,'W',sysdate,matchname,matchdate);
bpos := instr(all_bet,',');
end loop;
betname:=all_bet;
insert into itpubNBAbet values(betname,'W',sysdate,matchname,matchdate);
commit;

all_bet :=iLostList;
bpos := instr(all_bet,',');
while bpos>0 loop
betname:=substr(all_bet,1,bpos-1);
lengstr:=length(all_bet);
all_bet:=substr(all_bet,bpos+1,lengstr-bpos);
insert into itpubNBAbet values(betname,'L',sysdate,matchname,matchdate);
bpos := instr(all_bet,',');
end loop;
betname:=all_bet;
insert into itpubNBAbet values(betname,'L',sysdate,matchname,matchdate);
commit;
end;

end DBMS_ITPUBBET;