oracle 数据库=存储过程和触发器练习习题(1)

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/qq_36570464/article/details/80539883

前言:完全是刚学,可以能有一些错误或者不足的地方欢迎指出。微笑


在第8题中-->            题目是不允许修改雇员员工和奖金   ,但是我实现的是知己不允许修改表,在这里有点缺陷

8.	编写触发器,在每天12点以后,不允许修改雇员工资和奖金。
--1.	定义过程,根据雇员编号找到雇员姓名及工资。
create or replace procedure pro_select is
    empno1 emp.empno%type;
    ename1 emp.ename%type;
    sal1 emp.sal%type;
begin
    select empno,ename  ,sal into empno1, ename1,sal1 from emp
    where empno=&empno1;
    DBMS_OUTPUT.put_line('查询的员工编号:'||empno1||' 的姓名是:'||ename1||
    '  工资是:'||sal1);
    end pro_select;
    
set serverout on;   
execute pro_select;

--2.	创建一个功能为:删除给定职工号的职工信息的存储过程,并调用该存储过程

create or replace procedure pro_delete is
    empno1 emp.empno%type;
begin
    select empno into empno1 from emp where empno = &empno1;
    delete from emp where empno = empno1;
    dbms_output.put_line('已经删除员工号:'||empno1||'记录了');
end pro_delete;

set serverout on; 
--调用存储过程  execute 
execute pro_delete;
select * from emp;

--3.	利用存储过程增加部门信息。

create or replace procedure pro_insert is
begin
    insert into dept values(50,'tgzs','zzxy');
end pro_insert;

execute pro_insert;

select * from dept;
--4.	创建一个过程avg_sal,用于输出emp表中某个部门的平均工资,
并在PL/SQL匿名块中调用该过程输出20号部门的平均工资。要求:利用输入输出参数实现。

create or replace procedure avg_sal is
    var_sal emp.sal%type;
    var_deptno emp.deptno%type;
begin
    select avg(sal),deptno into var_sal,var_deptno
    from emp   where deptno = &var_deptno
    group by deptno;
    dbms_output.put_line('部门'||var_deptno||'  平均工资'||var_sal);
end avg_sal;
set serverout on;
execute avg_sal;

--5.	从雇员基本信息表(EMP)中统计各部门(DEPTNO)人数后,将结果输出。

create or replace procedure pro_count is
    cursor cur_count  is select deptno , count(deptno) as var_count
    from emp 
    group  by deptno;
   
begin
    for v_count in cur_count loop
        dbms_output.put_line('部门号:'||v_count.deptno||'人数:'||v_count.var_count);
    end loop;
end pro_count;

set serverout on;
execute pro_count;
--7.	利用触发器,在星期一、周末及每天下班时间(每天9:00以前、18:00以后)后不允许更新emp数据表。
CREATE OR REPLACE TRIGGER forbid_emp_trigger
BEFORE INSERT OR DELETE OR UPDATE
ON emp
DECLARE  
    v_currentweak VARCHAR(20) ;   
    v_currenthour VARCHAR(20) ;
BEGIN
    SELECT TO_CHAR(SYSDATE,'day'),TO_CHAR(SYSDATE,'hh24') 
    INTO v_currentweak, v_currenthour FROM dual ;  
    IF TRIM(v_currentweak)='星期一' OR TRIM(v_currentweak)='星期六' 
    OR TRIM(v_currentweak)='星期日' THEN   
    RAISE_APPLICATION_ERROR(-20008,'在周末及周一不允许更新emp数据表!') ;    
    ELSIF TRIM(v_currenthour)<'9' OR TRIM(v_currenthour)>'18' THEN    
    RAISE_APPLICATION_ERROR(-20009,'在下班时间不能够修改emp表数据!') ;   
    END IF ;
END ;

--8.	编写触发器,在每天12点以后,不允许修改雇员工资和奖金。

create or replace trigger tri_no_gzjiang
before update 
on emp
declare

    var_date varchar2(20); --用来记录时间
begin
    --查询时间并记录下来
    select TO_CHAR(SYSDATE,'hh24') into var_date from dual;
    --判断时间段
    if var_date >'12' then
        --当超过12 点后 发生异常
        RAISE_APPLICATION_ERROR(-20009,'12点以后不能够修改emp表数据!') ;   
    end if;
end;

--验证
update  emp set ename='tiangui' where empno = 999;

select * from emp;
--9.	创建一个行级触发器CASCADE_DEL_UPD,当修改部门编号时,
--EMP表的相关行的部门编号也自动修改;当删除部门表中某个部门号是,EMP表该部门号的员工一并删除
9.创建一个行级触发器CASCADE_DEL_UPD,当修改部门编号时,

EMP表的相关行的部门编号也自动修改;当删除部门表中某个部门号是,EMP表该部门号的员工一并删除

create or replace trigger CASCADE_DEL_UPD
before  update of deptno or delete
on dept
for each row
declare
begin
    if deleting  then 
        delete from emp where deptno=:old.deptno;  
    end if;  
    if updating then 
        update emp set deptno=:new.deptno 
        where deptno=:old.deptno;  
    end if;
end;

10.	创建一个显示员工编号、员工姓名、所在部门号及部门名的视图。利用触发器实现向该视图中添加记录的操作。
--创建一个视图
create or replace view view_emp_dept as
select empno,ename,dept.deptno,dname,job,hiredate
from emp ,dept
where emp.deptno = dept.deptno;

--插入记录
insert into view_emp_dept(empno,ename,deptno,dname,job,hiredate)
values (999,'ttgg',10,'ACCOUNTING','CASHIER',sysdate);

--插入数据失败,通过关联触发器来
--创建触发器
create or replace trigger tri_insert_view
instead of insert
on view_emp_dept
for each row
declare
    row_dept dept%rowtype;
begin
    select * into row_dept from dept where deptno=:new.deptno;
    if sql%notfound then
    insert into dept(deptno,dname) values(:new.deptno,:new.dname);
    end if;
    insert into emp(empno,ename,deptno,job,hiredate)
    values (:new.empno,:new.ename,:new.deptno,:new.job,:new.hiredate);
end;
--验证触发器

insert into view_emp_dept(empno,ename,deptno,dname,job,hiredate)
values (999,'ttgg',10,'ACCOUNTING','CASHIER',sysdate);

select * from view_emp_dept;

















展开阅读全文

没有更多推荐了,返回首页