oracle数据库存储过程，触发器，方法运用整理/笔记

-- 自动添加版本号
CREATE OR REPLACE PROCEDURE AUTO_ADDBDGVER(DBGVER IN varchar2,OUTDBGVER OUT number)
 is
  N_DBGVER NUMBER(5);
 begin
   N_DBGVER := to_number(DBGVER);
   IF N_DBGVER <> NULL THEN
   OUTDBGVER := N_DBGVER+1;
 END IF;
 END;

给个字段，让它三个月后自动修改字段的值

create or replace procedure auto_finish_fstatus is
       -- 这里是自动结束时间，三个月后自动归档
        
       prj_finish_time date;   -- 归档时间
begin
       --用for 循环遍历出所有的数据，就是把表中的数据以“行的形式”来存放
       for row in (select * from prj_budget) 
        -- 开启loop循环模式
        loop 
         
         -- 3个月后归档时间，add_months()是一个特用的行数
         select add_months(ctime,3) into prj_finish_time from prj_budget where PRJBDGID=row.prjbdgid;           
       
         -- 计算归档后的时间
         -- 判断当前时间是否大于计算的时间
         if  sysdate>prj_finish_time then
           -- 把状态改为2
           update prj_budget set fstatus = 2 where PRJBDGID=row.prjbdgid;
         end if;
       end loop;
    commit;
end auto_finish_fstatus;

下面这里是定时器来调用过程

begin
  sys.dbms_job.submit(job => :job,
                      -- 这里是调用过程名
                      what => 'auto_finish_fstatus;',
                      -- 下一次执行时间
                      next_date => to_date('01-01-4000', 'dd-mm-yyyy'),
                      -- 执行的时间
                      interval => 'TRUNC(sysdate) + 1 +1/ (24)');
  sys.dbms_job.broken(job => :job,
                      broken => true,
                      next_date => to_date('01-01-4000', 'dd-mm-yyyy'));
  commit;
end;
/

下面写一写流水号的方法

CREATE OR REPLACE FUNCTION SERIAL_NUMBER(v_type INT)

  /*
    参数说明：
    v_type:编码前缀

  */
   RETURN VARCHAR2 is
   v_old_no VARCHAR2(500); --原编码
   v_new_no VARCHAR2(500); --新编码
   v_qian VARCHAR2(500); -- 前缀

 begin
  IF v_type = 6 THEN
     v_qian := 'YS';
  ELSIF v_type = 10 THEN
     v_qian := 'BS';
  ELSIF v_type = 0 THEN
     v_qian := 'GY';
  ELSIF V_TYPE = 11 THEN
     V_QIAN := 'TZ';
  END IF;
    
    -- 流水号的编码格式
   SELECT TO_CHAR(SYSDATE,'YYYYMMDDHHIISS') INTO v_old_no FROM DUAL;
    -- 进行拼接
   v_new_no := v_qian || v_old_no;
   RETURN(v_new_no);

end SERIAL_NUMBER;

下面写写触发器的练习，调用上面的流水号函数方法

CREATE OR REPLACE TRIGGER INSERT_OA_WORKORDER
  BEFORE INSERT OR UPDATE OR DELETE ON OA_WORKORDER
  FOR EACH ROW   -- 行级触发器
DECLARE
  --申明一个临时存放的表
 /* L_STRFILTER VARCHAR2(200);   
  关于触发器的使用一定要了解 :new和:old 两个在触发器中的作用。
  :new 在触发器的BEFORE之前出发的 INSERT状态下才能使用,如果在
  alter之后触发，想要去修改:new 的值就会报错。*/
BEGIN
  IF INSERTING THEN
    --这里是获取要走那条流程的值
    SELECT SERIAL_NUMBER(:NEW.busi_type) INTO L_STRFILTER FROM DUAL;
    --然后把值赋值给工单编号(流水号)
    :NEW.WORKORDERNO := L_STRFILTER;
  
  ELSIF UPDATING THEN
    NULL;
  ELSIF DELETING THEN
    NULL;
  END IF;
END INSERT_OA_WORKORDER;

触发器也可以用来修改当前行中的数据

create or replace trigger AUTO_INCREMENT_BDGVER
--修改时对版本号进行递增
  before  update or insert
  on  PRJ_FLW_BUDGET
  for each row
declare
  -- local variables here
  temp_number varchar2(10);   --声明一个临时的值
begin
  --查询出来当前要修改的数据的版本号 
    select BDGVER into temp_number from PRJ_FLW_BUDGET
    where FLWPRJBDGID = :old.FLWPRJBDGID;
    --判断版本号是否大于1
    if temp_number >= 1 then
       --判断表数据要修改调整时触发
       if updating then
      --对旧的版本好进行修改
      UPDATE PRJ_FLW_BUDGET SET BDGVER=temp_number+1  
      where FLWPRJBDGID = :OLD.FLWPRJBDGID; 
      
      --插入最新的日期时间
      update prj_flw_budget set ETIME=sysdate
      where FLWPRJBDGID= :old.flwprjbdgid;
       end if;
    end if
  
  ;
end AUTO_INCREMENT_BDGVER;

触发器也可以用来修改本行的数据，但有一点是before之前触发的，且是insert或是update。

after之后触发的都是不行的，不能修改数据的。

create or replace trigger update_ADJSCOPE_FLW_BUDGETITEM
-- 这是一个用来调整的值的触发器
  before update
  on prj_flw_budgetitem 
  for each row
declare
  -- local variables here
begin
   IF INSERTING THEN
      
         -- 这里调用就的值向减然后得到了一个 调整后的幅度
          :new.adjscope := :old.bdgamount2new - NVL(:old.bdgamount2old,0);
          
          -- 这里判断 变化类型,并赋值
          if :new.adjscope = 0 or :new.adjscope is null then
            :new.changetype := 1;
          elsif :new.adjscope >1 and :old.bdgamount2old  is not null then
            :new.changetype := 2;
          elsif :new.adjscope <0 and :old.bdgamount2old is not null then
            :new.changetype :=3;
            -- 这里表示的新增
          elsif :old.bdgamount2old is null then
            :new.changetype :=0;
          end if;
          
    ELSIF UPDATING THEN
          -- 这里调用就的值向减然后得到了一个 调整后的幅度
          :new.adjscope := :old.bdgamount2new - NVL(:old.bdgamount2old,0);
          
          -- 这里判断 变化类型,并赋值
          if :new.adjscope = 0 or :new.adjscope is null then
            :new.changetype := 1;
          elsif :new.adjscope >1 and :old.bdgamount2old is not  null then
            :new.changetype := 2;
          elsif :new.adjscope <0 and :old.bdgamount2old is not  null then
            :new.changetype :=3;
            -- 这里表示的新增
          elsif :old.bdgamount2old is null then
            :new.changetype :=0;
          end if;
    ELSIF DELETING THEN
      NULL;
    END IF;
end ;

例如有些触发器是要计算的，遇到除数为零时，要处理的话可以用cast语句

 -- 总项目预算使用率    RATIO
      select
      ( ROUND (
      case when
      (SELECT NVL(SUM (a.ADJSCOPE), 0 )
      FROM PRJ_FLW_PAYITEM a , PRJ_FLW_PAY b
      WHERE a.FLWPAYID= b.FLWPAYID
      AND b.PRJID= :old.prjid )
       = 0
      then null
      end
      /
      (SELECT NVL( SUM (a.bdgamount),0)
      FROM PRJ_BUDGETITEM a , PRJ_BUDGET b , PRJ_PROJECT c
      WHERE a.PRJBDGID = b.PRJBDGID AND b.prjid = c.prjid
      AND b.prjid = :old.prjid   ),2))
      INTO P_RATIO from dual;
      -- 这里出现了（除数为零的问题，是有数据库的机制决定的改变不了） 用case语句解决
       If P_RATIO is null then
        :new.ratio := 0;
      else
        :new.ratio := P_RATIO;
      end if;