Oracle Package在itpub菠菜上的一点应用

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

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

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

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

create table ITPUBNBABET
!W^ ~6R#a9@0(ITPUB个人空间5Lk;Q%L?2DQS
ITPUBID VARCHAR2(100) not null,ITPUB个人空间7Y7t1fc(s(kg
WINSTATUS CHAR(1) not null,ITPUB个人空间X6RThQ[Q
OPERDATE DATE,
7o!y4TTh6{q0MATCH VARCHAR2(100) not null,ITPUB个人空间J
J^vMG0rMo.U
MATCHDATE DATE not null
?`D%Q mjA6V0)

alter table ITPUBNBABET
&\UC;v8P4bG,x0add constraint TTTTTT primary key (ITPUBID, WINSTATUS, MATCH, MATCHDATE)

create or replace package DBMS_ITPUBBET isITPUB个人空间P'X!F3w7M3`c Ay
-- Author : ADMINISTRATOR
7?zAa/K_0-- Created : 2007-11-4 20:05:48
j:K&M C0Y0-- Purpose : For itpub bet purposeITPUB个人空间_(Z/E/aZo-Z
type RefList is REF CURSOR; --return records by cursorITPUB个人空间E+Fz6{U%B0A${A
pTopN INTEGER:=5; --define default display return resultITPUB个人空间@Og3^4l/^!n5J

;R&h,t\7z9aQ0FUNCTION f_get_topN RETURN INTEGER; --get the value of topN variableITPUB个人空间[~t8lNi#~x+cV3K
Procedure p_set_topN(iTopN in INTEGER); --set the value of topN variableITPUB个人空间"s snAZ
--INPUT THE MATCH INFORMATIION
Y"[ bU]D:Rz!M0Procedure p_input_matchitem(
wa/bj7s%fTA0iWinList in VARCHAR2, --the winner list copy from itpubITPUB个人空间:mM k/s BZ
iLostList in VARCHAR2, --the loster list copy from itpub
9URk8{pi0iMatchName in VARCHAR2, --the NBA match name copy from intpubITPUB个人空间+?D1t U6X&jW
iMatchDate in DATE, --the NBA match date, default is todayITPUB个人空间3x4nS8g'k x0Hn
oRetCode out int, --the execute status,0 success,1 fauseITPUB个人空间-X'QnE.i,z
oRetMsg out VARCHAR2 --the execute message,include the success information and error messageITPUB个人空间v~{R*V,C
);
*w]vwX
fX0
&n"d
|5g,nzs)k:D0Procedure p_get_topNResult(oCur out RefList); --return result by cursor typeITPUB个人空间-wXS3}wjQ
Procedure p_get_topNResult(oRet out varchar2); --return result by varcharITPUB个人空间Oq(iZC+ht"OZ7s,L
end DBMS_ITPUBBET;

create or replace package body DBMS_ITPUBBET is

FUNCTION f_get_topN RETURN INTEGER
s"KNU@\M,|0IS
:|'_uUVDd;D0BEGIN
qU WMQD0RETURN DBMS_ITPUBBET.pTopN;
"I$b1Bt,W%HBo0END;

Procedure p_set_topN(iTopN INTEGER)
WB{s2l/]0IS
(@1~zZ_R%i*ab0BEGIN
mvs [0~:m$d5o,l)qeM0IF iTopN is not Null then
O,u6V*jjH9E0DBMS_ITPUBBET.pTopN :=iTopN;ITPUB个人空间Y!yC'lv5v
E
END IF;ITPUB个人空间r6m%iS^Z8x
END;

Procedure p_get_topNResult(oCur out RefList)
4K.`k6]Lp0IS
z$~3Db,U WWeA~0BEGINITPUB个人空间mv @gsx2p5u
ITPUB个人空间Z9X)k+}!d
}8|S
open oCur for select itpubID,topN from (select itpubID,count(*) topN from itpubNBAbet group by itpubID order by topN desc) where rownum<=ptopN;ITPUB个人空间0aB8B
b*M{C;N G
ITPUB个人空间vy]|v
END;ITPUB个人空间c \1c-Gis*eV
ITPUB个人空间7W_#z5] l?
Procedure p_get_topNResult(oRet out varchar2)ITPUB个人空间ru"V8N4Oe
ISITPUB个人空间!t,v'jeG0y"F1byW
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;ITPUB个人空间,]-V%S,QO&}@B
vitpubID VARCHAR2(200);
$Kxgk8Ah+q`B/n0vTopN integer;ITPUB个人空间6miq-a7]QucAp}
strlen integer;
8pIU*`9]d0vRet VARCHAR2(1000);ITPUB个人空间6T-WW3b~.^R
BEGIN

open cur_topN;
!@o~^Xtf%Re*Z0loopITPUB个人空间j#r(hGqzU&~
fetch cur_topN into vitpubID,vTopN;ITPUB个人空间2CR-LNB#a%aD
z
exit when cur_topN%notfound;
*T k@a o+E3L0vRet := vRet||'itpub ID ='||LPAD(vitpubID,15,' ')||' Win total='||to_char(vTopN)||chr(13)||chr(10);ITPUB个人空间\t"f,T/d i
end loop;ITPUB个人空间k:S9pn]Cl
close cur_topN;ITPUB个人空间
J,@7V_ SN2e\N
oRet:=vRet;
'L ~.KNEY i0END;
wy;`W~P'X"jS0ITPUB个人空间 PvU STJs'qv
--INPUT THE MATCH INFORMATIIONITPUB个人空间J[l+kKi a
Procedure p_input_matchitem(ITPUB个人空间}qr EiQ1w&i X
iWinList in VARCHAR2, --the winner list copy from itpub
4n\B8nw,aV0iLostList in VARCHAR2, --the loster list copy from itpub
"^9EA,o'?0iMatchName in VARCHAR2, --the NBA match name copy from intpub
'O!g*k5| }Vmt0iMatchDate in DATE, --the NBA match date, default is today
_7y,|!C7bJPyc.i0oRetCode out int, --the execute status,0 success,1 fauseITPUB个人空间JVy wAw^lS%e
oRetMsg out VARCHAR2 --the execute message,include the success information and error messageITPUB个人空间
Bn dl8}J
)
y1e X9b)u7pqpx0isITPUB个人空间4M)C;IE^F0^;P[B
all_bet varchar2(4000);ITPUB个人空间,d PG(jdt;X%D0X
betname varchar2(50);
&Sr#WQA'a0lengstr integer;ITPUB个人空间"w-P$oJpmJ.I%i{
bpos integer;
{;Env(_j(}F0matchname varchar2(200);ITPUB个人空间,k:k&CEtM(HP
matchdate date;ITPUB个人空间,I W p[&o%Uk5r5l}}%z
beginITPUB个人空间k0o"rt:i6D
matchdate:=imatchdate;ITPUB个人空间D'Gu-HwN~ ?
matchname:=imatchname;
5?{chb-{0all_bet :=iWinList;ITPUB个人空间goJ
A
m^
bpos := instr(all_bet,',');
%UN1RJ Jr(v'kMs0while bpos>0 loopITPUB个人空间VW@0k`|/^
betname:=substr(all_bet,1,bpos-1);
u?Qdr9x0lengstr:=length(all_bet);ITPUB个人空间(pm-P,d&z&TA
all_bet:=substr(all_bet,bpos+1,lengstr-bpos);
*}%uy{#f1rBUr`"T.z_0insert into itpubNBAbet values(betname,'W',sysdate,matchname,matchdate);
0R%JA9c$XO$P]0bpos := instr(all_bet,',');ITPUB个人空间-?jT ~ o'?R%M
end loop;ITPUB个人空间3l)[}{Cy8\,|/P-U#N
betname:=all_bet;
4t7A$j5K ?S!B0insert into itpubNBAbet values(betname,'W',sysdate,matchname,matchdate);ITPUB个人空间S!YRUVp^|
commit;
*F"RhZ Kf"Q7|0ITPUB个人空间"O[FlFp
all_bet :=iLostList;ITPUB个人空间-HglOr0D&gka:g [
bpos := instr(all_bet,',');
m&V2]1[SQ+rz0while bpos>0 loop
JWZ UuY]Y0betname:=substr(all_bet,1,bpos-1);ITPUB个人空间R[ x?:D
lengstr:=length(all_bet);ITPUB个人空间|8S`W2B^A
all_bet:=substr(all_bet,bpos+1,lengstr-bpos);ITPUB个人空间 m cmq|EbQ%?9i)Z0Q
insert into itpubNBAbet values(betname,'L',sysdate,matchname,matchdate);
6rP Iy(]1e0bpos := instr(all_bet,',');
2|7@Aj8H4Q"g5m)d0end loop;
M@4b!O"j:B0betname:=all_bet;
b Bku0e0insert into itpubNBAbet values(betname,'L',sysdate,matchname,matchdate);ITPUB个人空间~*|9e;_A ^"CpN
commit;
^0efE}0end;
5K-K
hB w Wh9G0
x+nS+QV0end DBMS_ITPUBBET;