|
--SELECT empno,ename,sal,comm,hiredate FROM emp WHERE empno=:no;
Q.编写一个函数以显示该雇员在此组织中的工作天数。
A.
CREATE OR REPLACE FUNCTION Hire_Day(no emp.empno%TYPE) RETURN NUMBER AS
vhiredate emp.hiredate%TYPE;
vday NUMBER;
BEGIN
SELECT hiredate INTO vhiredate FROM emp WHERE empno=no;
vday:=CEIL(SYSDATE-vhiredate);
RETURN vday;
END;
/
DECLARE
vday NUMBER;
vempno emp.empno%TYPE;
BEGIN
vempno:=&empno;
vday:=Hire_Day(vempno);
DBMS_OUTPUT.PUT_LINE(vday);
END;
/
--SELECT empno,ename,sal,comm,hiredate FROM emp WHERE empno=:no;
第五阶段
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;
/
----8i下通过。资料来自ACCP
上一页 [1] [2] [3]
[办公软件]在sybase中插入图片、PDF、文本文件 [办公软件]安装Sybase ASE
[办公软件]linux指令大全(完整篇) [办公软件]Linux新手入门常用命令大全
[办公软件]在RedHat Linux 9里安装gaim0.80 [办公软件]浅谈Linux 下Java 1.5 汉字方块问题解决方法
[办公软件]Linux程序员必读：中文化与GB18030标准 [办公软件]linux指令大全
[办公软件]制作Linux启动盘的四种方法 [办公软件]Linux文件系统的反删除方法
|
注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网] 
