Oracle（七）触发器、函数和存储过程

*******************=========触发器=========*****************
  :old 代表之前的值
  :new 更改之后现在的值
  这两个值  只能在 for each row 中使用

update语句  :old   :new
insert语句  :new
delete语句  :old
 
--创建一个teacher_log （只要有人动teacher表，数据就会记录在teacher_log表中）
create table teacher_log(
logid number not null,
old_value varchar2(200),
create_date date,
log_type number,
tno number
)
--给logid设置主键
alter  table teacher_log add constraint pk_teacher_logid primary key(logid);

--创建序列
create sequence sq_teacher_logid
minvalue 1
maxvalue 999999999
start with 1
increment by 1
cache 20;

--创建触发器 or replace 如果存在 则 修改
create or replace  trigger tr_teacher
after insert or update or delete --会在增删改之后 触发
on teacher for each row
--声明
declare
v_old_value teacher_log.old_value%type;
v_type      teacher_log.log_type%type;
v_tno       teacher_log.tno%type;
begin
  if inserting then
    v_type:=1; --新增
    v_tno :=:new.tno;
    v_old_value:=:new.tno||'====='||:new.tname;
   elsif deleting then
    v_type:=2; --删除
    v_tno :=:old.tno;
    v_old_value:=:old.tno||'====='||:old.tname;
   else
    v_type:=3; --修改
    v_tno :=:old.tno;
    v_old_value:=:old.tno||'====='||:old.tname||'===='||:new.sal;
  end if;
 
--将记录写入到 teacher_log
insert into teacher_log values
(sq_teacher_logid.nextval,v_old_value,sysdate,v_type,v_tno);

end tr_teacher;

***************=========函数=======******************

--函数 function
create or replace function fn_teacher_tid
(
f_tid varchar2
)
return varchar2
is
f_result  teacher.tid%type;

begin
    if length(f_tid)!=18  then
      dbms_output.put_line('身份证不正确');
      else dbms_output.put_line('身份证正确'); 
    end if;
    --给返回值赋值
    f_result:=substr(f_tid,1,6)||'********'||substr(f_tid,15);
    return f_result;

end fn_teacher_tid;
 




--调用函数
select fn_teacher_tid(110101198603304014) from dual;

***************=========存储过程=======******************

--存储过程   一组完成特定功能的sql语句集
-- 新增教师   身份证不满足要求 报错
create or  replace   procedure pro_add_teacher
(
p_tno teacher.tno%type,
p_tname  teacher.tname%type,
p_tid  teacher.tid%type,
p_sal  teacher.sal%type
)
is
e_tid_validate exception;

begin
  if length(p_tid)!=18  --判断身份证号不满足18位
    then  --抛出异常
      raise e_tid_validate;
  end if;
 
  --新增
  insert into teacher(tno,tname,tid,sal)
  values(p_tno,p_tname,p_tid,p_sal);
  --手动提交事务
  commit;
 
--对异常进行处理
exception
    when e_tid_validate then
      dbms_output.put_line('请输入正确的身份证号');
      when others then
          dbms_output.put_line('其他的异常');
end pro_add_teacher;

 
 
--调用存储过程
call pro_add_teacher(2001,'小黑黑','123456789123456789',5000);