Oracle触发器与存储过程的学习小练习

数据库 waitig 461℃ 百度已收录 0评论

在调用PL/SQL的输出语句时必须打开输出开关,执行以下语句

set serveroutput on;

 

–1、在scott中创建存储过程xs_avg,该存储过程包括一个输入参数,用于获取部门号,一个输出参数用于返回指定部门的平均工资。并请给出此存储过程的一个调用实例。

 

创建存储过程

create or replace procedure xs_avg(

  t_deptno in dept.deptno%type, t_avg_sal out emp.sal%type)

as

begin

  select avg(sal) into t_avg_sal from emp where deptno=t_deptno;

end;

 

调用存储过程

declare

  t_deptno dept.deptno%type:=&t_deptno;

  t_avg_sal emp.sal%type;

begin

  xs_avg(t_deptno,t_avg_sal);

  dbms_output.put_line(t_avg_sal);

end;

 

运行结果:

 

 

 

–2、在emp表上创建一个触发器,当删除记录的时候,将被删除的数据、删除时间和用户保存到另一个表emp2中,请先自行设计并创建表emp2

复制表empemp2

create table emp2 as select * from emp;

清空表

delete from emp2;

新增删除时间

alter table emp2 add del_time date;

 

创建触发器

create or replace trigger del_info

  before delete on emp for each row

declare

  t_time date;

begin

  –select sysdate into t_time from dual;

  insert into emp2(empno,ename,job,mgr,hiredate,sal,comm,deptno,del_time) values

 

(:old.empno,:old.ename,

  :old.job,:old.mgr,:old.hiredate,:old.sal,:old.comm,:old.deptno,sysdate);

  –insert into emp2(del_time) values(to_date(t_time,’yy-MM-dd’));

end;

 

添加一行数据用来删除,防止删除原表中数据难以恢复

insert into emp values(7000,’1′,’CLERK’,7902,’17-12-80′,1000,500,10);

delete from emp where EMPNO=7000;

 

运行结果:

 

 

 

 

–3、在dept中创建触发器,当添加或修改部门记录时,同一个城市(loc)不允许超过2个部门。

/*注:在触发器的创建中,如果使用了for each row,那么在后面的PL/SQL语句中无法使用select语句,虽然会

 

编译通过,但是执行无效,所以如果需要用到查询,我们需要在定义触发器时不使用for each row*/

create or replace trigger in_up_dept

  after insert or update on dept

declare

  —用于储存当地部门的数量

  dept_num number;

  —统计当地的部门数

  cursor deptno_count

    is select count(deptno) from dept group by loc;

begin

  open deptno_count;

  fetch deptno_count into dept_num;

  while deptno_count%found

  loop

  —判断,如果当地部门数大于2,那么撤回前面的操作

    if dept_num>2 then

    — 抛出错误,撤回更改

      raise_application_error(-20000,’over count!’);

    end if;

  fetch deptno_count into dept_num;

  end loop;

  close deptno_count;

end;

 

用来查看某部门的人数,便于迅速插入剩余的数据来检测触发器

select count(deptno),loc from dept group by loc;

添加数据进行测试

insert into dept values(50,’赌馆‘,’NEW YORK’);

insert into dept values(60,’步行街‘,’NEW YORK’);

 

测试结果:

 

 

 

 

 

–4.emp表上创建触发器,限制每个部门人数不能超过10人。

create or replace trigger limt_menber

  before insert or update on emp

declare

  —用来计数储存每个部门的员工号

  menber_num number;

  —定义游标用来遍历每个部门的员工数

  cursor empno_num

    is

      select count(empno) from emp group by deptno;

begin

  open empno_num;

  —遍历统计部门的员工数

  fetch empno_num into menber_num;

  —如果empno_num能够读入有效的行数据,那么就执行

  while empno_num%found

  loop

    —判断,如果当地部门数大于2,那么撤回前面的操作

    if menber_num>=10 then

      raise_application_error(-20000,’当地部门员工人数超过十人,无法更改或添加!‘);

    end if;

    fetch empno_num into menber_num;

  end loop;

  close empno_num;

end;

添加人数进行测试

insert into emp values(7001,’2′,’CLERK’,7902,’17-12-80′,1000,500,10);

insert into emp values(7002,’3′,’CLERK’,7902,’17-12-80′,1000,500,10);

insert into emp values(7003,’4′,’CLERK’,7902,’17-12-80′,1000,500,10);

insert into emp values(7004,’5′,’CLERK’,7902,’17-12-80′,1000,500,10);

insert into emp values(7005,’6′,’CLERK’,7902,’17-12-80′,1000,500,10);

insert into emp values(7006,’7′,’CLERK’,7902,’17-12-80′,1000,500,10);

insert into emp values(7007,’8′,’CLERK’,7902,’17-12-80′,1000,500,10);

insert into emp values(7008,’9′,’CLERK’,7902,’17-12-80′,1000,500,10);

insert into emp values(7009,’10’,’CLERK’,7902,’17-12-80′,1000,500,10);

 

运行结果:

 

 

 

–5.限制非上班时间(每天9:00以前、18:00以后)不允许更新salgrade数据。

获取当前日期

select to_char(sysdate) from dual;

获取当前时间(24小时制)

select to_char(sysdate,’hh24:mi:ss’) from dual;

获取当前的时钟数

select to_char(sysdate,’hh24′) from dual;

获取当前的分钟数

select to_char(sysdate,’mi’) from dual;

获取当前的秒钟数

select to_char(sysdate,’ss’) from dual;

 

create or replace trigger ban_salgrade

  before update or delete or insert on salgrade for each row

declare

  —定义非法时间

  morningTime varchar(10) := to_char(’09:00:00′);

  nightTime varchar(10) := to_char(’18:00:00′);

  —用于储存当前时间

  nowTIme varchar(10);

begin

  select to_char(sysdate,’hh24:mi:ss’) into nowTime from dual;

  if nowTime<morningTime or nowTime>nightTime then

    RAISE_APPLICATION_ERROR(-20000, ‘在非上班期间不能更改数据‘);

  end if;

end;

 

测试用例

update salgrade set grade=10 where GRADE=1;

 

运行结果:

时间在:

插入一行数据得:

 

/*

这段代码是用来测试字符串的比较是否能够成立的

declare

  —定义非法时间

  morningTime varchar(10) := to_char(’09:00:00′);

  nightTime varchar(10) := to_char(’18:00:00′);

  —用于储存当前时间

  nowTIme varchar(10);

begin

  select to_char(sysdate,’hh24:mi:ss’) into nowTime from dual;

  if nowTime>morningTime then

    DBMS_OUTPUT.PUT_LINE(‘大于九点‘);

  elsif nowTime<nightTime then

    DBMS_OUTPUT.PUT_LINE(‘小于六点‘);

  end if;

end;*/

 


本文由【waitig】发表在等英博客
本文固定链接:Oracle触发器与存储过程的学习小练习
欢迎关注本站官方公众号,每日都有干货分享!
等英博客官方公众号
点赞 (0)分享 (0)