ORACL的E复习.ppt

CREATE OR REPLACE PACKAGE PKG_DEPTNO AS V_DEPTNO EMP.DEPTNO%TYPE; V_SAL EMP.SAL%TYPE; END; CREATE OR REPLACE TRIGGER trg_updateemp BEFORE UPDATE ON EMP FOR EACH ROW BEGIN PKG_DEPTNO.V_SAL:=:NEW.SAL; PKG_DEPTNO.V_DEPTNO:=:NEW.DEPTNO; END; CREATE OR REPLACE TRIGGER trg_statement AFTER UPDATE ON EMP DECLARE v_highsal emp.sal%type; v_lowsal emp.sal%type; BEGIN SELECT MAX(SAL),MIN(SAL) INTO v_highsal,v_lowsal FROM EMP WHERE DEPTNO= PKG_DEPTNO.V_DEPTNO; IF PKG_DEPTNO.V_SALv_highsal or pkg_deptno.v_salv_lowsal THEN RAISE_APPLICATION_ERROR(-20001,THE SAL IS BEYOND!); END IF; END; 3、用无条件多表插入，将emp表中工资高于2000的员工信息查询后分别插入emp_sal和emp_mgr表。 INSERT ALL INTO emp_sal VALUES(empno,hiredate,sal) INTO emp_mgr VALUES(empno,mgr,sal) SELECT empno,hiredate,mgr,sal FROM emp WHERE sal2000； 4、创建一个存储过程，以部门号为参数，查询该部门的平均工资，并输出该部门中比平均工资高的员工号、员工名。 CREATE OR REPLACE PROCEDURE show_emp( p_deptno emp.deptno%TYPE) AS v_sal emp.sal%TYPE; BEGIN SELECT avg(sal) INTO v_sal FROM emp WHERE deptno=p_deptno; DBMS_OUTPUT.PUT_LINE(p_deptno|| ||average salary is:|| v_sal); FOR v_emp IN (SELECT * FROM emp WHERE deptno=p_deptno AND salv_sal) LOOP DBMS_OUTPUT.PUT_LINE(v_emp.empno|| ||v_emp.ename); END LOOP; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(The department doesn’’t exists!); END show_emp; 2、创建一个存储过程，以部门号为参数，返回该部门的人数和平均工资。 CREATE OR REPLACE FUNCTION ret_deptinfo( p_deptno dept.deptno%TYPE, p_num OUT NUMBER, p_avg OUT NUMBER) RETURN dept.dname%TYPE AS v_dname dept.dname%TYPE; BEGIN SELECT dname INTO v_dname FROM dept WHERE deptno=p_deptno; SELECT count(*),avg(sal) INTO p_num,p_avg FROM emp WHERE deptno=p_deptno; RETURN v_dname; END ret_maxsal; 3、创建一个函数，以部门号为参数，返回该部门最高工资。 CREATE OR REPLACE FUNCTION return_maxsal (p_deptno emp.deptno%TYPE) RETURN emp.sal%TYPE AS v_maxsal emp.sal%TYPE; BEGIN SELECT max(sal) INTO v_maxsal FROM emp WHERE deptno=p_deptno; RETURN v_maxsal; EXCEPTION WHEN NO_DATA_FOUND THEN DB