存储过程例子

create or replace function abtain_Yjcsl(v_Date in date)
    return number is
           v_StartDate date:=to_date('2008-06-02','yyyy-MM-dd');
           v_SgDate date:=to_date('2008-06-02','yyyy-MM-dd');
           v_SgSl number:=1;/*送股数量*/
           v_Zmcsl number:=0;
           v_Yjcsl number:=55000000;/*应减持部分*/
           v_Zccsl number:=1;
           cursor sgInfo is select fdate,fjysl from tb_001_2008_imp_trade where fsetid='8' and fzqdm='000667' and fdate>=to_date('2008-06-02','yyyy-MM-dd') and fbs='07' order by fdate;
    begin
         open sgInfo;
         fetch sgInfo into v_SgDate,v_SgSl;
         while sgInfo%FOUND loop
               fetch sgInfo into v_SgDate,v_SgSl;/*获得本次送股数据，包括送股日期、送股数量*/
               select case when sum(fjysl) is null then 0 else sum(fjysl) end into v_Zmcsl from tb_001_2008_imp_trade where fsetid='8' and fzqdm='000667' and (fdate>=v_StartDate and fdate<v_SgDate) and fbs='02';/*获得自上次送股到本次送股期间的累计卖出量*/
               v_Yjcsl:=v_Yjcsl-v_Zmcsl;/*在应减持部分中扣除本次累计卖出*/

               select faendbal into v_Zccsl from tb_001_2008_imp_balance where fsetid='8' and facctcode='000667' and fdate=v_SgDate-1 and fsubtsftypecode='ZC01';/*取得本次送股前一天的总持仓数量*/
               v_Yjcsl:=v_Yjcsl+(v_Yjcsl/v_Zccsl)*v_SgSl;/*在应减持部分上加上‘本次送股含应减持部分’*/
         end loop;

         /*退出上面while语句，应将最后一次送股日至当前日间的卖出累计扣除*/
         select case when sum(fjysl) is null then 0 else sum(fjysl) end into v_Zmcsl from tb_001_2008_imp_trade where fsetid='8' and fzqdm='000667' and (fdate>=v_SgDate and fdate<=v_Date) and fbs='02';/*获得自上次送股到本次送股期间的累计卖出量*/
         v_Yjcsl:=v_Yjcsl-v_Zmcsl;/*在应减持部分中扣除本次累计卖出*/

         return v_Yjcsl;
    end;

 

 

create or replace function EndDate_Days_StartDate(v_EndDate in date,v_Days in number)
    return date is
           v_StartDate date:=v_EndDate;
           v_Count number:=1;/*统计交易日天数*/
           v_Flag number:=1;/*判断是否为节假日或休息日 0：工作日 1：节假日或休息日*/
    begin
         while v_Count < v_Days loop
               select count(*) into v_Flag from tb_base_childholiday where fholidayscode = 'sbjjr' and fdate = v_StartDate-1;/*取明天是否为交易日标识*/
               if v_Flag = 0 then /*判断是否为交易日*/
                     v_Count:=v_Count+1;
               end if;
               v_StartDate:=v_StartDate - 1;
         end loop;
         return v_StartDate;
    end;

 

 

 

create or replace function StartDate_Days_EndDate(v_StartDate in date,v_Days in number)
    return date is
           v_EndDate date:=v_StartDate;
           v_Count number:=1;/*统计交易日天数*/
           v_Flag number:=1;/*判断是否为节假日或休息日 0：工作日 1：节假日或休息日*/
    begin
         while v_Count < v_Days loop
               select count(*) into v_Flag from tb_base_childholiday where fholidayscode = 'sbjjr' and fdate = v_EndDate+1;/*取明天是否为交易日标识*/
               if v_Flag = 0 then /*判断是否为交易日*/
                     v_Count:=v_Count+1;
               end if;
               v_EndDate:=v_EndDate + 1;
         end loop;
         return v_EndDate;
    end;