实验6触发器

1.函数定义和使用

1. 创建一个为单个员工进行加薪的函数. 该函数有两个IN的参数, 员工编号和加薪幅度, 并返回加薪后的总薪水.

CREATE FUNCTION add_sal (emp_number IN NUMBER,a IN NUMBER) RETURN NUMBER AS
newsal emp.sal%TYPE;
BEGIN

UPDATE emp SET sal = sal*(1+a) WHERE empno=emp_number;
SELECT sal INTO newsal FROM emp WHERE empno = emp_number;
RETURN newsal;
END add_sal;
/

输出：

set serveroutput on
declare
BEGIN
DBMS_OUTPUT.PUT_LINE(add_sal(7900,0.1));
END;
/

2.创建函数avg_sal,根据部门编号计算该部门所有职工的平均工资

CREATE FUNCTION avgsal(dept_number IN NUMBER) RETURN NUMBER AS
sal_avg emp.sal%TYPE;
BEGIN
SELECT AVG(sal) INTO sal_avg FROM emp WHERE deptno = dept_number;
RETURN sal_avg;
END avgsal;
/
SELECT avgsal(20) FROM dual

3.创建一个存储过程，并定义3个in模式的变量，然后将这3个变量的值插入到emp表empno,ename,job列中

CREATE OR REPLACE PROCEDURE a_insert
(emp_number IN emp.empno%TYPE,
emp_name IN emp.ename%TYPE,
emp_job IN emp.job%TYPE) AS
BEGIN
INSERT INTO emp(empno,ename,job) VALUES(emp_number,emp_name,emp_job);
END a_insert;
/
EXEC a_insert(1000,'Fang','carry');
SELECT empno,ename,job FROM emp WHERE empno=1000;

 

4.创建存储过程，根据员工编号查询员工的姓名及工资，使用in参数传入员工编号，使用out参数传出姓名及工资。并对未查询到的数据进行错误处理。?????????????????????????????????????????????

CREATE PROCEDURE select_emp
(emp_number IN NUMBER,emp_name OUT VARCHAR2,emp_sal OUT NUMBER) AS
BEGIN
SELECT ename,sal INTO emp_name,emp_sal FROM emp WHERE empno=emp_number;
END select_emp;
/
(DROP PROCEDURE select_emp;)
VARIABLE name VARCHAR2(10);
VARIABLE sal NUMBER;
EXEC select_emp(7900,:name,:sal);
PRINT name;
PRINT sal;

5.创建存储过程为员工涨工资，有部门的员工每人涨500，没有部门的员工每人涨100。

CREATE PROCEDURE addsal
(emp_number IN emp.empno%TYPE) AS
a NUMBER(10);
BEGIN
a:=0;
SELECT deptno INTO a FROM emp WHERE empno=emp_number;
IF a=0 THEN
	UPDATE emp SET sal=sal+100 WHERE  empno=emp_number;
ELSE
	UPDATE emp SET sal=sal+500 WHERE  empno=emp_number;
END IF;
END addsal;
/
SELECT sal FROM emp WHERE empno=7900;
EXEC addsal(7900);
SELECT sal FROM emp WHERE empno=7900;

6.创建一个事前触发器，当对emp表的sal列进行修改时，将员工号、加薪前和加薪后的薪资存入temp表中（temp表需要事先建立）。

CREATE TABLE temp(
content VARCHAR2(100),
rtime TIMESTAMP
);
CREATE TRIGGER update_emp
AFTER UPDATE
ON emp
FOR EACH ROW
BEGIN
INSERT INTO temp VALUES('执行了更新操作，empno='||:OLD.empno||'更新前的工资 sal='||:OLD.sal||'new_sal='||:new.sal,SYSDATE);
END update_emp;
/
UPDATE emp SET sal = 1595 WHERE empno=7900;
SELECT * FROM temp;

7.创建一个事前触发器tri_dept，该触发器在insert、update和delete事件下都可以被触发，并且操作的数据对象是emp表。然后要求在触发器执行时输出对emp表所做的具体操作。

create or replace trigger tri_dept
  before insert or update or delete
  on dept --创建触发器，当dept表发生插入，修改，删除操作时引起该触发器执行
declare
  var_tag varchar2(10);--声明一个变量，存储对dept表执行的操作类型
begin
  if inserting then --当触发事件是INSERT时
    var_tag := '插入';--标识插入操作
  elsif updating then --当触发事件是UPDATE时
    var_tag := '修改';--标识修改操作
  elsif deleting then--当触发事件是DELETE时
    var_tag := '删除';--标识删除操作
  end if;
  insert into dept_log
  values(var_tag,sysdate);--向日志表中插入对dept表的操作信息
end tri_dept;

8.创建一个事前行触发器。对修改雇员工资的操作进行合法性检查：没有单位（部门号为空）的员工不能修改工资，修改后的工资要大于修改前的工资；工资增量不能超过原工资的20%，如果违反合法性检查，将输出错误信息。

create or replace trigger judge_emp
		before update of sal
		on emp
		for each row
	begin
			IF :old.deptno is null then
			dbms_output.put_line(:old.empno||'没有部门，不能修改工资');
			ELSIF :new.sal<:old.sal  then
			dbms_output.put_line(:old.empno||'修改后的工资应该大于修改前的工资');
			ELSIF :new.sal-:old.sal>=:old.sal*0.2  then
			dbms_output.put_line(:old.empno||'工资增量不能超过原工资的20%');
			END IF;
	end judge_emp;
	/
	update scott.emp
	set sal = sal-100
	where deptno is null;
	update scott.emp
	set sal = sal*1.5
	where deptno is null;

9.创建事后触发器，当插入或删除的雇员记录属于30号部门时，输出操作种类和雇员号。输出格式如下所示：

插入30号部门一个记录,雇员号为：18

create or replace trigger aft_emp
	after insert or update or delete
	on emp
	for each row
	when(old.deptno=30)
	begin
		if inserting or updating then
		dbms_output.put_line('插入30号部门的一个记录，雇员号为：'||:new.empno);
		elsif deleting then
		dbms_output.put_line('删除30号部门的一个记录，雇员号为：'||:old.empno);
		end if;
	end aft_emp;
	/
	INSERT 
	INTO scott.emp(empno,ename,deptno) 
	VALUES(18,'lihao',30); 
	DELETE
	FROM emp
	WHERE empno=18;

10.创建一个程序包的“规范”pack_emp，首先在该程序包中声明一个可以获取指定部门的平均工资的函数，然后再声明一个可以实现按照指定比例上调指定职务的工资的存储过程。创建程序包pack_emp的主体，在该主体中实现对应“规范”中声明的函数和存储过程。

CREATE  PACKAGE pack_emp
	AS 
	FUNCTION avq_sal2(dno IN scott.emp.deptno%TYPE)  RETURN scott.emp.sal%TYPE;
	PROCEDURE rise_sal2(ejob IN scott.emp.job%TYPE, range IN number );
	END pack_emp;
	/

	CREATE  OR REPLACE  PACKAGE BODY pack_emp IS
		FUNCTION avq_sal2(dno IN scott.emp.deptno%TYPE)
		RETURN scott.emp.sal%TYPE
		IS
			salary scott.emp.sal%TYPE;
		BEGIN
			SELECT avg(sal) 
			INTO salary 
			FROM scott.emp 
			WHERE deptno=dno;
			RETURN salary;
			EXCEPTION
				WHEN NO_DATA_FOUND THEN
				dbms_output.put_line('未找到相应的部门编号');
				RETURN 0;
		END avq_sal2;
		PROCEDURE rise_sal2(ejob IN scott.emp.job%TYPE, range IN number )
		IS
			salary scott.emp.sal%TYPE;
		BEGIN
			update scott.emp
			set sal = sal*(1+range)
			where job=ejob;
			EXCEPTION
				WHEN NO_DATA_FOUND THEN
				dbms_output.put_line('未找到相应的工作岗位');
		END rise_sal2;
	END pack_emp ;
	/