几十个实用的PL/SQL(5)

MILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">第五阶段

Q.编写一个数据包，它有两个函数和两个过程以操作“emp”表。

  该数据包要执行的任务为：

   插入一个新雇员；删除一个现有雇员；显示指定雇员的整体薪水（薪水+佣金）；显示指定雇员所在部门名称。

A.

CREATE OR REPLACE PACKAGE emppack AS

       PROCEDURE insrec(pempno emp.empno%TYPE,pename emp.ename%TYPE,

                                    pjob emp.job%TYPE,pmgr emp.mgr%TYPE,

                                    phiredate emp.hiredate%TYPE,psal emp.sal%TYPE,

                                    pcomm emp.comm%TYPE,pdeptno emp.deptno%TYPE);

       PROCEDURE delrec(pempno IN NUMBER);

       FUNCTION selsal(pempno NUMBER) RETURN NUMBER;

       FUNCTION seldname(pempno NUMBER) RETURN VARCHAR2;

END;

/

CREATE OR REPLACE PACKAGE BODY emppack AS

       PROCEDURE insrec(pempno emp.empno%TYPE,pename emp.ename%TYPE,

                                    pjob emp.job%TYPE,pmgr emp.mgr%TYPE,

                                    phiredate emp.hiredate%TYPE,psal emp.sal%TYPE,

                                    pcomm emp.comm%TYPE,pdeptno emp.deptno%TYPE)

       IS

       BEGIN

              INSERT INTO emp VALUES(pempno,pename,pjob,pmgr,phiredate,

                                                        psal,pcomm,pdeptno);

              DBMS_OUTPUT.PUT_LINE('1 record is created.');

       END insrec;

       PROCEDURE delrec(pempno IN NUMBER)

       IS

       BEGIN

              DELETE FROM emp WHERE empno=pempno;

              DBMS_OUTPUT.PUT_LINE('1 record is deleted.');

       END delrec;

       FUNCTION selsal(pempno NUMBER) RETURN NUMBER

       IS

              vTotalSal NUMBER;

       BEGIN

              SELECT NVL(sal,0)+NVL(comm,0) INTO vTotalSal

              FROM emp

              WHERE empno=pempno;

              RETURN vTotalSal;

       END selsal;

       FUNCTION seldname(pempno NUMBER) RETURN VARCHAR2

       IS

              vdname dept.dname%TYPE;

       BEGIN

              SELECT dname INTO vdname

              FROM emp,dept

              WHERE empno=pempno AND emp.deptno=dept.deptno;

              RETURN vdname;

       END seldname;

END;

/

--执行包中的过程和函数

EXECUTE emppack.insrec(1111,'Goldens','MANAGER',7698,'2003-01-18',2000,400,30);

EXECUTE emppack.delrec(1111);

DECLARE

       salary NUMBER;

BEGIN

       salary:=emppack.selsal(7369);

       DBMS_OUTPUT.PUT_LINE('Total Salary is '||salary);

END;

/

DECLARE

       department VARCHAR2(30);

BEGIN

       department:=emppack.seldname(7369);

       DBMS_OUTPUT.PUT_LINE('Department name is '||department);

END;

/

Q.编写一个数据库触发器以显示当任何时候雇员加薪时的加薪情况。

A.

CREATE OR REPLACE TRIGGER emp_SalUp

AFTER UPDATE OF sal ON emp

FOR EACH ROW

DECLARE

       vsal NUMBER;

BEGIN

       vsal:=NVL(:NEW.sal,0)-NVL(:OLD.sal,0);

       IF vsal<=0 THEN

              RAISE_APPLICATION_ERROR(-20001,'Increased Salary is not zero and littler than zero');

       END IF;

END;

/

Q.编写一个数据库触发器，它允许用户只在上午9.00到下午5.00之间执行DML任务。

A.

CREATE OR REPLACE TRIGGER operate_time_limited

BEFORE INSERT OR UPDATE OR DELETE ON emp

--FOR EACH ROW

DECLARE

       vtime NUMBER;

BEGIN

       vtime:=TO_NUMBER(TO_CHAR(SYSDATE,'HH24'));

       IF vtime NOT BETWEEN 9 AND 17 THEN

              RAISE_APPLICATION_ERROR(-20444,'Sorry!Not Except 9AM and 5PM.');

       END IF;

END;

/

Q.编写一个数据为触发器以检查某个组织中不能有两个总裁。

A.

CREATE OR REPLACE TRIGGER check_president

BEFORE INSERT OR UPDATE ON emp

FOR EACH ROW

WHEN (UPPER(NEW.job)='PRESIDENT')

DECLARE

       vCount NUMBER;

BEGIN

       SELECT COUNT(job) INTO vCount

       FROM emp

       WHERE UPPER(job)='PRESIDENT';  --把总统的个数统计出来，当为0时，变量值为0

       IF vCount>0 THEN

              RAISE_APPLICATION_ERROR(-20444,'Sorry!Can''t have two President.');

       END IF;

END;

/

Q.编写一个数据库触发器，当任何时候某个部门从”dept”中删除时，该触发器将从”emp”表中删除该部门的所有雇员。

A.

CREATE OR REPLACE TRIGGER del_emp_deptno

BEFORE DELETE ON dept

FOR EACH ROW

BEGIN

       DELETE FROM emp WHERE deptno=:OLD.deptno;      

END;

/

(全文完)