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

王朝mssql·作者佚名  2006-01-09
窄屏简体版  字體: |||超大  

第五阶段

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;

/

(全文完)

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
© 2005- 王朝網路 版權所有 導航