首页 > 数据库 > 其他综合 > 正文
oracle触发器和存储过程
2016-09-26       个评论      
收藏    我要投稿

触发器
只针对delete|insert|update等语句进行操作,
分为行级触发器和语句级触发器

语法
create [or replace] trigger
before|after
delete|insert|update
on 表名
for each row --行级触发器
PLSQL块

触发器实施安全性检查
–不能再非工作是时间进行数据库操作
1、非上班时间
2、星期六和星期日
sql语句:to_char(sysdate,’day’) in (‘星期六’,’星期天’)
to_number(to_char(sysdate),’hh24’) not between 9 and 18

create or replace trigger securityEmp
before insert on emp
begin
  if to_char(sysdate,'day') in ('星期六','星期天') or
     to_number(to_char(sysdate),'hh24') not between 9 and 18 then
     raise_application_error(-20001,'不能再非工作时间插入数据库');
  end if;
end;
insert into emp(empno,ename,sal,comm) values(30322,'Johns',8000,2000);

第一个触发器
插入一条数据之后,自动打印一条数据“您已经成功插入一条员工”

create or replace trigger firstTrigger
after insert
on emp
begin
  dbms_output.put_line('您已经成功插入一条员工');
end;
/

行级触发器
:old
:new
指定是同一个值,一个指代原值,一个指代修改后的值
确认修改的薪水不能低于原来的薪水值

create or replace trigger upSal
before update 
on emp for each row 
begin
  if :new.sal < :old.sal then
    raise_application_error(-20001,'涨后的薪水不能少于涨前的薪水');
  end if;
end;
update emp set sal = sal - 1 where ename like '%S'

练习题
限制每个部门只招聘5名职工,超过计划则报出错误信息
这个应该是语句级别报错信息,如果员工总数超过5人,则抛出报错信息
统计员工人数
select count(*) from emp where deptno=”

alter trigger  securityEmp disable
create or replace trigger empPerson
before insert or delete
on emp
for each row
declare 
  deptNum number :=0;
begin
  select count(*) into deptNum from emp where deptno = :new.deptno;
      if deptNum > 5 then 
      raise_application_error(-20004,'部门号:'||:new.deptno||'已经有'||deptNum||'人');
      end if;
end;
insert into emp(empno,ename,sal,comm,deptno) values(1002,'KittyMe',3000,400,30);
delete emp where deptno=30

闭某表上所有触发器

trriger alter table table_name disable all triggers;

–开启某表上所有触发器

trriger alter table table_name enable all triggers

–关闭某个触发器

alter trigger  securityEmp disable

存储过程
create or replace procedure 过程名(参数列表)
AS
plsql子程序体
打印hello world
调用存储过程
1.exec sayHelloWorld();
2.begin
sayHelloWorld();
end;

create or replace procedure sayHelloWorld
as
begin
  dbms_output.put_line('hello World');
end;
/
exec sayHelloWorld();
begin
  sayHelloWorld();
  sayHelloWorld();
end;

带参数的存储过程
给员工涨100元工资,并打印出涨前和涨后的工资

create or replace procedure raiseSal(eno in number) --in 表示输入函数
as
  eSal emp.sal%type;
begin
--获取涨前工资
  select sal into eSal from emp where empno = eno;
--给员工涨100元工资
  update emp set sal = sal+100 where empno = eno;
  dbms_output.put_line('涨前工资:'||eSal||'涨后工资:'||(eSal+100));
end;
/
exec raiseSal(7929);

存储函数
与存储过程类似,唯一区别,存储函数有返回值
语法
create or replace function 函数名(参数列表)
return 函数值类型
AS
plsql子程序体
–算出某个员工的年薪(sal*12+nvl(comm,0))

create or replace function totalEmpSal(eno in number)
return number
as
  psal emp.sal%type;
  pcomm emp.sal%type;
begin
  select sal,comm into psal,pcomm from emp where empno = eno;
  return psal*12+nvl(pcomm,0);
end;

out参数
可以指定返回多值
查询员工的姓名,薪水,奖金

create or replace procedure queryEmpInfo(eno in number,
                                         aname out varchar2,
                                         asal out number,
                                         acomm out number)
as
begin
  select ename,sal,comm into aname,asal,acomm from emp where empno=eno;
end;

–dictionary

select * from dictionary;

–注释表

select * from user_tab_comments where table_name='EMP';

–查看当前用户的权限

select * from session_privs;
点击复制链接 与好友分享!回本站首页
上一篇:Logistic回归
下一篇:SQL查询学习笔记
相关文章
图文推荐
文章
推荐
点击排行

关于我们 | 联系我们 | 广告服务 | 投资合作 | 版权申明 | 在线帮助 | 网站地图 | 作品发布 | Vip技术培训
版权所有: 红黑联盟--致力于做实用的IT技术学习网站