第四阶段

Q.编写一过程以接受用户输入的三个部门编号并显示其中两个部门编号的部门名称。

A.

CREATE OR REPLACE PROCEDURE DeptName(no1 dept.deptno%TYPE,no2 dept.deptno%TYPE,no3 dept.deptno%TYPE) AS

       vflag NUMBER;

       vdeptno1 dept.deptno%TYPE;

       vdeptno2 dept.deptno%TYPE;

       vdname1 dept.dname%TYPE;

       vdname2 dept.dname%TYPE;

BEGIN

       vflag:=TO_NUMBER(TO_CHAR(SYSDATE,''''SS''''));

       IF (vflag>=1 AND vflag<=10) OR (vflag>=50 AND vflag<60) THEN

              SELECT deptno,dname INTO vdeptno1,vdname1 FROM dept WHERE deptno=no1;

              SELECT deptno,dname INTO vdeptno2,vdname2 FROM dept WHERE deptno=no2;

       ELSIF (vflag>=11 AND vflag<=20) OR (vflag>=40 AND vflag<50) THEN

              SELECT deptno,dname INTO vdeptno1,vdname1 FROM dept WHERE deptno=no1;

              SELECT deptno,dname INTO vdeptno2,vdname2 FROM dept WHERE deptno=no3;

       ELSE

              SELECT deptno,dname INTO vdeptno1,vdname1 FROM dept WHERE deptno=no2;

              SELECT deptno,dname INTO vdeptno2,vdname2 FROM dept WHERE deptno=no3;

       END IF;

       DBMS_OUTPUT.PUT_LINE(''''部门编号:''''||vdeptno1 ||''''  ''''||''''部门名称:'''' ||vdname1);

       DBMS_OUTPUT.PUT_LINE(''''部门编号:''''||vdeptno2 ||''''  ''''||''''部门名称:'''' ||vdname2);

END;

/

EXECUTE DeptName(10,20,30);

Q.编写一过程以显示所指定雇员名的雇员部门名和位置。

A.

CREATE OR REPLACE PROCEDURE DeptMesg(pename emp.ename%TYPE,pdname OUT dept.dname%TYPE,ploc OUT dept.loc%TYPE) AS

BEGIN

       SELECT dname,loc INTO pdname,ploc

       FROM emp,dept

[1] [2] [3] [4]  下一页