Oracle数据库学习示例

create or replace package EventsProfile is --全局变量 Type curEvents is ref cursor; --函数或存储过程 procedure EventsToBeDoneToday( executer in varchar2, --执行者代码 eventsList out curEvents ); procedure EventsToBeDoneOtherDay( executer in varchar2, --执行者代码 eventsList out curEvents ); procedure EventsFinishedToday( executer in varchar2, --执行者代码 eventsList out curEvents ); procedure EventsUnFinished( executer in varchar2, --执行者代码 eventsList out curEvents ); procedure EventsExpired( executer in varchar2, --执行者代码 eventsList out curEvents ); procedure EventsDeleted( executer in varchar2, eventsList out curEvents); procedure EventHasFinished( executer in varchar2, eventsList out curEvents); procedure NewEventSave( eventName in varchar2, eventContent in varchar2, eventDesc in varchar2, eventExer in varchar2, planBeginTime in Date, planFinishTime in date, finalFinishTime in date, isFinished in varchar2 default 'N', isExpired in varchar2 default 'N', isDeleted in varchar2 default 'N', result out varchar2 ); procedure GetEventById( var_eventId in varchar2, var_events out curEvents, result out number ); procedure SaveEventStatus( var_eventId in varchar2, var_actBeginTime in varchar2, var_actFinishTime in varchar2, var_isFinished in varchar2, var_isExpired in varchar2, var_isDeleted in varchar2, result out number ); procedure SaveEvent( var_eventId in varchar2, var_eventName in varchar2, var_eventContent in varchar2, var_eventDesc in varchar2, var_eventExer in varchar2, var_planBeginTime in varchar2, var_planFinishTime in varchar2, var_finalFinishTime in varchar2, var_actBeginTime in varchar2, var_actFinishTime in varchar2, var_isFinished in varchar2, var_isExpired in varchar2, var_isDeleted in varchar2, result out number ); end EventsProfile; create or replace package body EventsProfile is -- Private type declarations --type <TypeName> is <Datatype>; -- Private constant declarations --<ConstantName> constant <Datatype> := <Value>; -- Private variable declarations --<VariableName> <Datatype>; -- Function and procedure implementations procedure EventsToBeDoneToday( executer in varchar2, --执行者代码 eventsList out curEvents ) as cur curEvents; begin open cur for select * from events where ( upper(isExpired)='N' and upper(isDeleted)='N' and isFinished='N') and upper(eventExer) = upper(executer) and trunc(planBeginTime)=trunc(sysdate); eventsList := cur; exception when others then close cur; end EventsToBeDoneToday; procedure EventsToBeDoneOtherday( executer in varchar2, --执行者代码 eventsList out curEvents ) as cur curEvents; begin open cur for select * from events where ( upper(isExpired)='N' and upper(isDeleted)='N' and isFinished='N') and upper(eventExer) = upper(executer) and trunc(planBeginTime)!=trunc(sysdate); eventsList := cur; exception when others then close cur; end EventsToBeDoneOtherday; procedure EventsFinishedToday( executer in varchar2, --执行者代码 eventsList out curEvents ) as cur curEvents; begin open cur for select * from events where (upper(isExpired)='N' and upper(isDeleted)='N' and upper(isFinished)='Y') and trunc(actFinishTime)=trunc(current_date) and upper(eventExer) = upper(executer); eventsList := cur; exception when others then close cur; end Eventsfinishedtoday; procedure EventsUnFinished( executer in varchar2, --执行者代码 eventsList out curEvents ) as cur curEvents; begin open cur for select * from events where (upper(isExpired)='N' and upper(isDeleted)='N' and upper(isFinished)='N') and upper(eventExer) = upper(executer); eventsList := cur; exception when others then close cur; end EventsUnFinished; procedure EventsExpired( executer in varchar2, --执行者代码 eventsList out curEvents ) as cur curEvents; begin open cur for select * from events where ( upper(isExpired)='Y' and upper(eventexer)=upper(executer) ) or ( upper(isDeleted)='N' and upper(isFinished)='N' and current_date>finalFinishTime and upper(eventexer)=upper(executer) ); eventsList :=cur; exception when others then close cur; end EventsExpired; procedure EventsDeleted( executer in varchar2, --执行者代码 eventsList out curEvents ) --选出所有删除的事件 as cur curEvents; begin open cur for select * from events where upper(isDeleted) = 'Y' and upper(eventexer)=upper(executer); eventsList := cur; exception when others then close cur; end EventsDeleted; procedure EventHasFinished( executer in varchar2, --执行者代码 eventsList out curEvents ) --选出所有完成的任务 as cur curEvents; begin open cur for select * from events where upper(isDeleted)='N' and upper(isFinished)='Y' and upper(isExpired)='N' and upper(eventexer)=upper(executer); eventsList := cur; exception when others then close cur; end Eventhasfinished; procedure NewEventSave( eventName in varchar2, eventContent in varchar2, eventDesc in varchar2, eventExer in varchar2, planBeginTime in Date, planFinishTime in date, finalFinishTime in date, isFinished in varchar2 default 'N', isExpired in varchar2 default 'N', isDeleted in varchar2 default 'N', result out varchar2 ) as begin insert into events( eventName,eventContent,eventDesc,eventExer,planBeginTime, planFinishTime,finalFinishTime,isFinished,isExpired,isDeleted ) values( eventName,eventContent,eventDesc,eventExer,planBeginTime,planFinishTime, finalFinishTime,isFinished,isExpired,isDeleted ); commit; result := 1; exception when others then rollback; result := -1; end NewEventSave; procedure GetEventById( var_eventId in varchar2, var_events out curEvents, result out number ) is idx number; begin select rownum into idx from events where lower(eventId) = lower(var_eventId); if sql%rowcount>=1 then result := 1; open var_events for select * from events where lower(eventId) = lower(var_eventId);