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

柔光的暖阳◎ 2022-05-23 04:09 415阅读 0赞

*前言：完全是刚学，可以能有一些错误或者不足的地方欢迎指出。![微笑][smile.gif]*

*在第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;

[smile.gif]: https://static-blog.csdn.net/xheditor/xheditor_emot/default/smile.gif