基于Oracle触发器存储过程

先说下需求,有一套表触发器监听这一套表的增删改,并记录操作到监听记录表中City_cfjlb

触发器:

create or replace trigger Tri_city_scsbxxb_Change --
  after delete or update or insert on city_scsbxxb --
  for each row
declare
  -- kid       number;
  cfsj      date;
  tableName VARCHAR2(50);
  sjId      VARCHAR2(50);
  czlx      VARCHAR2(50);

begin
  tableName := 'city_scsbxxb'; --
  sjId      := 'city_scspxxid=' || :OLD.city_scspxxid; --

  --select S_CITY_CFJLB.Nextval into kid from dual;
  select sysdate into cfsj from dual;

  CASE
    WHEN UPDATING THEN
      czlx := 'E';
    WHEN INSERTING THEN
      sjId      := 'city_scspxxid=' || :NEW.city_scspxxid; --
      czlx := 'A';
    WHEN DELETING THEN
      czlx := 'D';
  END CASE;
  Copy_City_BZTOMY(tableName, czlx, cfsj, sjId);
end Tri_city_scsbxxb_Change;

存储过程:

create or replace procedure Pro_Copy_City(Cfbmc  in City_Cfjlb.Cfbmc%type,
                                             Cflx   in City_Cfjlb.Cflx%type,
                                             Cfsj   in City_Cfjlb.Cfsj%type,
                                             Cfsjid in City_Cfjlb.Cfsjid%type) as
  K_ID  number := 0;
  v_sql varchar2(1000);
  v_sql2 varchar2(1000);
begin
  if Cflx = 'E' then
    v_sql := 'select nvl(max(CITY_CFJLB.KID), 0) KID
      from CITY_CFJLB
     where CFBMC =  ''' || Cfbmc || '''
       and cflx = ''' || Cflx || '''
       AND CFSJID = ''' || Cfsjid || '''
       AND sfcl=0
       and rownum = 1';
        dbms_output.put_line(v_sql);
    EXECUTE IMMEDIATE v_sql
      INTO K_ID;
  end if;
  if Cflx = 'D' then
    v_sql2:=' DELETE FROM CITY_CFJLB city WHERE city.cfsjid ='''||Cfsjid||''' and city.cfbmc='''||Cfbmc||'''' ;
    dbms_output.put_line(v_sql2);
     EXECUTE IMMEDIATE v_sql2;
  end if;
  if K_ID > 0 then
    UPDATE CITY_CFJLB
       SET KID = s_CITY_CFJLB.Nextval, CITY_CFJLB.CFSJ = Cfsj
     where KID = K_ID;
  else
    INSERT INTO CITY_CFJLB city
      (city.KID, city.CFBMC, city.CFLX, city.CFSJ, city.CFSJID, SFCL)
    VALUES
      (s_CITY_CFJLB.Nextval, Cfbmc, Cflx, cfsj, Cfsjid, '0');
  end if;
end Copy_City_BZTOMY;
--0表示未处理

存储过程包含异常处理

create or replace procedure pro_M_ProPriceHistory as
   spid NUMBER;
   hiscount number;
   v_sql1  varchar2(1000);
   v_sql2  varchar2(1000);
   v_sql3  varchar2(1000);
   new_MALLPRICE NUMBER(10,2) default 0;                    --最新官网价格
   old_MALLPRICE NUMBER(10,2) default 0;                    --历史最新官网价
   old_price NUMBER(10,2);                        --历史最新报价
begin
   FOR my_cur IN (select min(price) minprice,PRODUCTID from m_productquote where ISEFFECT=1 and QUOTESTATUS =1 group by PRODUCTID) LOOP
    spid:=my_cur.productid;
    --dbms_output.put_line(my_cur.productid);
     --my_cur.productid
     --查询出该商品最新的官网价。
      v_sql1:='select MALLPRICE from m_productinfo where PRODUCTID= ''' || my_cur.productid || '''';
      --查询出最新历史官网价、与售价
      v_sql2:='select MALLPRICE,PRICE from M_ProPriceHistory where  PRICEHISTORYID =(select max(PRICEHISTORYID) from M_ProPriceHistory where PRODUCTID= ''' || my_cur.productid || '''  group by PRODUCTID)';
      v_sql3:='select count(1) hiscount from M_ProPriceHistory where PRODUCTID= ''' || my_cur.productid || '''';

       begin
       EXECUTE IMMEDIATE v_sql1  INTO new_MALLPRICE;
       EXECUTE IMMEDIATE v_sql3 into hiscount;
       --保证有数据
       if hiscount>0 then
               EXECUTE IMMEDIATE v_sql2  INTO old_MALLPRICE,old_price;             
       end if;
        EXCEPTION WHEN  NO_DATA_FOUND then
                 dbms_output.put_line(spid);
       end;
          if  new_MALLPRICE <>old_MALLPRICE or my_cur.minprice<> old_price then
           --dbms_output.put_line(22);
            insert into M_ProPriceHistory his(his.pricehistoryid,his.productid,his.price,his.mallprice,his.syntime)values(s_M_ProPriceHistory.Nextval,my_cur.productid,my_cur.minprice,new_MALLPRICE,sysdate);
       end if;
  END LOOP;
    COMMIT;
    EXCEPTION WHEN OTHERS THEN
      dbms_output.put_line(spid);
    rollback;
end pro_M_ProPriceHistory;

View Code

批量报价

create or replace procedure pro_plbj(code in varchar,clz in number,bid in number,pSUPPLYAREA in varchar,resultcount out number) as
  v_sql varchar2(1000);
  auditstatu char default 'Y';
  jicount number default 0;
  p_price number(10,2);
  --pSUPPLYAREA varchar2(50);
  bjsj date;
  bjsj_sql varchar2(1000);
  yyyymm varchar(8) default 'yyyymm';
  StartDay varchar(10) default 'StartDay';
  BjJzDay varchar(10) default 'BjJzDay';
  fdnumber number default 4;   --报价浮动值
  fuclz number;   --报价浮动值
  fd_sql varchar2(1000);
begin
  resultcount:=0;
  --获取一个在报价时间段的随机时间
  bjsj_sql:='select fun_timeregion(
          (select to_char(sysdate,'''||yyyymm||''') || (select PARAMVALUE from  m_sysconfig where PARAMCODE='''||StartDay||''') from dual),
          (select to_char(sysdate,'''||yyyymm||''') || (select PARAMVALUE from  m_sysconfig where PARAMCODE='''||BjJzDay||''') from dual),10,17) from dual';
  --获取一个浮动策略值        
  fd_sql:='select mod(DBMS_RANDOM.random,'''||fdnumber||''') from dual';
  --查询供应商供应的全部商品
  FOR my_cur IN (select boss.proid,boss.productid,pro.sku,pro.categorycode,pro.price,pro.mallprice from m_productinfo pro inner join m_bossproduct boss on boss.productid = pro.productid where  pro.status=1  and pro.auditstatus=auditstatu and boss.status=1 and boss.bossid = bid and pro.mallprice- pro.price>50) LOOP
  --计算报价
      if my_cur.categorycode!=code  and code is not NULL then
      continue;
      end if;          
      EXECUTE IMMEDIATE fd_sql into fuclz;
      --随机上下浮动-3~3 以保证不是一家供应商中标
      fuclz:=fuclz+clz; 
     -- if fuclz>100 then  fuclz:=100; end if;
     -- if fuclz<0 then fuclz:=0; end if;  
     --最终浮动策略值不能大于100且不能小于0   
      if fuclz>100 then  fuclz:=100;elsif fuclz<0 then  fuclz:=0; end if;                 
      p_price:=(my_cur.mallprice*fuclz+my_cur.price*(100-fuclz))/100;
      --最终利润小于50元 不保价
      if  p_price- my_cur.price <=50 or p_price>my_cur.mallprice  then
          continue;
      end if;
      --获得随机的报价时间
      EXECUTE IMMEDIATE bjsj_sql into bjsj;
      --查询当前还未报价/或者报价已失效的报价
      v_sql:='select count(*) jicount from m_productquote where proid='''||my_cur.proid||''' and PRODUCTID='''||my_cur.productid||''' and ISEFFECT=2';
      EXECUTE IMMEDIATE v_sql into jicount;
      --如果没得记录则新增
      if  jicount=0 then
           insert into m_productquote (QUOTEID,PRICE,QUOTETIME,QUOTEIDENTIFICATION,SERVICE,SUPPLYAREA,PROID,SKU,QUOTESTATUS,TAKEEFFECTTIME,ISEFFECT,PRODUCTID,CACODE)
                      values(s_m_productquote.nextval,p_price,bjsj,bid||my_cur.sku||to_char(bjsj, 'yyyymmdd')  ,'',pSUPPLYAREA,my_cur.proid,my_cur.sku,1,null,2,my_cur.productid,'');
      ELSE
       --修改商品价格
        update m_productquote set QUOTESTATUS= 1,TAKEEFFECTTIME=null,SUPPLYAREA=pSUPPLYAREA,QUOTETIME=bjsj,PRICE=p_price
         where QUOTEID=(select max(QUOTEID)
          from m_productquote where proid=my_cur.proid and PRODUCTID=my_cur.productid and ISEFFECT =2);
    end if;
     resultcount:=resultcount+1;
  END LOOP;
  COMMIT;
   dbms_output.put_line(resultcount);
EXCEPTION
  WHEN OTHERS THEN
    --dbms_output.put_line(2);
    dbms_output.put_line('出错了！');
    dbms_output.put_line(p_price);
     dbms_output.put_line(jicount);
    rollback;
end pro_plbj;

View Code

批量加入商品库

create or replace procedure M_PLJRSPK(code in varchar,bossid in varchar,resultCount out int) as
   dcount   number := 0;
   dcount2   number := 0;
   v_sql  varchar2(1000);
   v_sql2  varchar2(1000);

  --A020204
begin

   resultCount:=0;
  FOR my_cur IN (select productid,Categorycode from m_productinfo) LOOP
    --根据品目编号查询商品。
    IF my_cur.Categorycode = code or code is null THEN
    v_sql:=  'select count(*) dcount from m_bossproduct  where BOSSID =''' || bossid || ''' and  productid =''' || my_cur.productid || ''' and STATUS=1';
    v_sql2:= 'select count(*) dcount from m_bossproduct  where BOSSID =''' || bossid || ''' and  productid =''' || my_cur.productid || ''' and STATUS=0';

     EXECUTE IMMEDIATE v_sql  INTO dcount;
     EXECUTE IMMEDIATE v_sql2  INTO dcount2;

      if dcount =0  then
        resultCount:=resultCount+1;

         if dcount2=0 then
         insert into m_bossproduct boss (boss.proid,boss.bossid,boss.status,boss.productid) VALUES(s_m_bossproduct.nextval,bossid,1,my_cur.productid);
         else
            update m_bossproduct set status=1 where BOSSID ='' || bossid || '' and  productid ='' || my_cur.productid || '' and STATUS=0;
            end if;
      end if;
    END IF;
    COMMIT;
  END LOOP;
    dbms_output.put_line(resultCount);
end M_PLJRSPK;
--0表示未处理

View Code

Oracle在一段时间范围内生成随机时间

--startdat:开始时间
--endday：结束时间
--starttime、endtime 9：00~17:00范围
create or replace function fun_timeregion(startdat in varchar, endday in varchar, starttime in number default 9,endtime in number default 17) return date is
  FunctionResult date;
  v_sql varchar(2000);
  J char default 'J';
  yyyymmdd varchar(10) default 'yyyymmdd';
begin
--20121001
--20121007
 v_sql:= 'SELECT to_date(TRUNC(DBMS_RANDOM.VALUE(to_number(to_char(to_date('''||startdat||''',  
                                                                 '''||yyyymmdd||'''),  
                                                         '''||J||''')),  
                                       to_number(to_char(to_date('''||endday||''',  
                                                                  '''||yyyymmdd||''') + 1,  
                                                         '''||J||''')))),  
               '''||J||''')  + (TRUNC(DBMS_RANDOM.VALUE('''||starttime||''','''||endtime||'''))*3600+TRUNC(DBMS_RANDOM.VALUE(0,60))*60 + TRUNC(DBMS_RANDOM.VALUE(0,60)))/(24*60*60)  
FunctionResult  FROM dual';  
  
  EXECUTE IMMEDIATE v_sql into FunctionResult;
  return(FunctionResult);
end fun_timeregion;

View Code

posted on 2018-11-28 09:57 许一朵岁月阅读(1085) 评论(0) 编辑收藏举报