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);