Oracle Package在itpub菠菜上的一點應用

今天閒的無聊寫了個儲存過程，把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;