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

第五阶段

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;

/

(全文完)

原文转自：http://www.ltesting.net