前言:完全是刚学,可以能有一些错误或者不足的地方欢迎指出。
在第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;