oracle触发器、函数、存储过程简介

oracle是我们生产环境使用最频繁的数据库之一，因此将之前日常使用的相关操作记录下，备忘和方便使用。

1、触发器

触发器一般用来处理数据在插入、更新或删除时的一些额外操作，比如针对交换的前置表，有数据更新时修改标记位或时间戳。

触发器要慎用，因为对于数据的读写效率有不小的影响。

create or replace trigger trg_table1_insert_update
  before insert or update on TABLE1  
  for each row
declare
  -- local variables here
begin
  :new.COLUMN1 := sysdate;
  if :new.COLUMN2!='1' and :new.COLUMN2!='2' then
  :new.COLUMN2 := '0';
  end if;
  -- 当判断是更新操作时<!--  -->
  if updating then
  :new.COLUMN3:='XX';
  end if; 
end trg_table1_insert_update;

2、函数

oracle内置常用函数

内置函数一般用来简化查询、插入等操作，但是会对sql的效率有影响。

时间处理

-- to_char
select to_char(sysdate,'yyyy-MM-dd hh24:mi:ss') from dual;
-- to_date
select to_date('2019-08-27 10:11:33','yyyy-MM-dd hh24:mi:ss') from dual;

字符截取

select substr('asdf',0,2) from dual;
select substr('asdf',1,2) from dual;

去除空格

select trim('as df') from dual;

长度

length字符长度，lengthb字节长度，lengthb与数据库的字符集有关系，如ZHS16GBK采用 2 个byte位来定义一个汉字。而在UTF8，采用 3 个byte。

--字符长度 length
select length('李四') from dual;
--字节长度 lengthb
select lengthb('李四') from dual;

like优化的效率很低 应使用

        instr(title,’手册’)>0 相当于like
        instr(title,’手册’)=0 相当于not like

自定义函数

有时候对于内置函数无法满足业务实际的需要，比如业务编码、顺序号等，这时候就需要编写自己的函数

国家办件库rowguid生成函数

-- 实际与国家对接过程中，通过原有陕西办件库的主键动态生成国家办件库要求的rowguid，因此利用表guidforgj和函数getGjGuid动态生成。
create or replace function getGjGuid(p1 varchar, p2 varchar, p3 int) return varchar2
is PRAGMA AUTONOMOUS_TRANSACTION;
  FunctionResult varchar2(64);
  counNum int;
begin
  select count(1) into counNum from guidforgj g  where g.projid = p1 and g.orgbusno = p2 and g.dataver = p3;
  --dbms_output.put_line('counNum_'  ||  counNum ||  '输出');
  if counNum >0
  then
    select g.gjguid into FunctionResult from guidforgj g where g.projid = p1 and g.orgbusno = p2 and g.dataver = p3;
     --dbms_output.put_line('FunctionResult_'  ||  FunctionResult ||  '输出');
   return(FunctionResult);
  end if;
  if FunctionResult is null
    then
      FunctionResult:=sys_guid();
      --dbms_output.put_line('FunctionResult2_'  ||  FunctionResult ||  '输出');
      insert into guidforgj values(FunctionResult,p1,p3,p2);
      COMMIT;
      --dbms_output.put_line('ok_'  ||  FunctionResult ||  '输出');
   end if;     
  return(FunctionResult);
end getGjGuid;
--表guidforgj建表语句如下：
-- Create table
create table GUIDFORGJ
(
  gjguid   VARCHAR2(64) default sys_guid() not null,
  projid   VARCHAR2(30),
  dataver  NUMBER(4),
  orgbusno VARCHAR2(64)
)
tablespace WORKINFOGJ
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate indexes 
create index INDEX_DATAVER_GJ on GUIDFORGJ (DATAVER)
  tablespace WORKINFOGJ
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
create index INDEX_ORGBUSNO_GJ on GUIDFORGJ (ORGBUSNO)
  tablespace WORKINFOGJ
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
create index INDEX_PROJID_GJ on GUIDFORGJ (PROJID)
  tablespace WORKINFOGJ
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table GUIDFORGJ
  add constraint PK_GJGUID primary key (GJGUID)
  using index 
  tablespace WORKINFOGJ
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

工作日函数

计算两个时间之间相差多少个工作日，只去掉了周末。

create or replace function workdays(p1 date, p2 date) return varchar2 is
  FunctionResult varchar2(64);
begin
select (trunc(p1 - p2) - 
         ((case
         WHEN (8 - to_number(to_char(p2,'D'))) > trunc(p1 - p2) + 1 THEN 0
         ELSE
          trunc((trunc(p1 - p2) -
                (8 - to_number(to_char(p2,'D'))))/7) + 1 END) + 
         (case
         WHEN mod(8 - to_char(p2, 'D'), 7) > trunc(p1 - p2) - 1 THEN 0
         ELSE
          trunc((trunc(p1 - p2) - (mod(8 - to_char(p2,'D'),7) + 1))/7) + 1  END)))
         into FunctionResult
  from dual;
  return(FunctionResult);
end workdays;

3、存储过程

存储过程在早起的数据库运维、开发及管理中是经常使用的，但是随着应用程序、服务器配置的提升，已经越来越依赖应用去处理很多的业务逻辑，所以存储过程也用的越来越少，但是其实这部分在实际项目中还是很方便的，有些不是特别复杂，只需要数据库函数或脚本即可实现的小程序其实可以利用存储过程来实现。

存储过程的效率要远高于普通的sql脚本。

存储过程中的组成也是由基础sql脚本组成的，只是在部分语法上是存在差别，如：定义变量，静态赋值，查询赋值。

-- 这是实际项目中的一个统计存储过程，用于数据库定时任务，不需要看里边的具体逻辑。
create or replace procedure pro_sjzx_tjfx(sjfl in varchar2,sjxl in varchar2) is
  czsj date;
  t_tjnd varchar(4);
  tbnd varchar(4);
  hbnd varchar(4);
  tbyf varchar(2);
  hbyf varchar(2);
  --tjyf varchar(2);
  v_id varchar2(20);
  v_tempSql varchar2(3000);
  v_tempSqlSon varchar2(3000);
  v_tempid number;
  v_dateFrom varchar(12);
  v_dateTo varchar(12);
begin
---变量赋值
  czsj := to_date('20120101','yyyymmdd');
  --确定统计年度
  --- if else
  if(to_char(czsj,'mm') = '01')
  then 
       t_tjnd := to_char(add_months(czsj,-1),'yyyy');
       --tjyf := '12';
  else 
       t_tjnd := to_char(czsj,'yyyy');
       --tjyf := to_char(add_months(czsj,-1),'mm');
  end if;

  tbnd := t_tjnd/1-1;
  --确定环比月份
  if(sjfl = '月份统计')
  then
      hbnd := to_char(add_months(to_date(t_tjnd||substr(sjxl,1,2),'yyyymm'),-1),'yyyy');
      hbyf := to_char(add_months(to_date(t_tjnd||substr(sjxl,1,2),'yyyymm'),-1),'mm');
  end if;

  --插入逻辑
  insert 
  ....
       commit;
--查询结果赋值
  select substr(v_id, 1, 9) ||
           to_char(substr(v_id, 10)  1 + nvl(max(rownum),0)+1)
      into v_id
   from sjzx_tjfx;
    --dbms_output.put_line('v_id-------------'||v_id);
  commit; 
---删除逻辑
delete from sjzx_tjfx t where t.tjfs1 is null;
commit;
end pro_sjzx_tjfx;

存储过程调用

create or replace procedure tj_season is
       czsj date;
       sjflval varchar(10);
       tjjd varchar(10);
       tjyf varchar(4);
begin
     czsj := to_date('20190101','yyyymmdd');
     sjflval := '季度统计';
     tjyf := to_char(add_months(czsj,-1),'mm');
     if(tjyf = '03')
     then
         tjjd := '第一季度';
     else
         if(tjyf = '06')
         then tjjd := '第二季度';
         else 
              if (tjyf = '09')
              then tjjd := '第三季度';
              else tjjd := '第四季度';
              end if;
          end if;
       end if;
     dbms_output.put_line('季度统计'||tjjd);
     begin
     --调用上边定义的存储过程
          pro_sjzx_tjfx(sjfl => sjflval,sjxl => tjjd);
      end;
end tj_season;