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

<script type="text/javascript"> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>

<script type="text/javascript"> </script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>

第四阶段

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

A.

CREATEORRE PLACEPROCEDUREDeptName(no1dept.deptno%TYPE,no2dept.deptno%TYPE,no3dept.deptno%TYPE)AS

      vflagNUMBER;

      vdeptno1dept.deptno%TYPE;

      vdeptno2dept.deptno%TYPE;

      vdname1dept.dname%TYPE;

      vdname2dept.dname%TYPE;

 

BEGIN

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

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

            SELECTdeptno,dnameINTOvdeptno1,vdname1FROMdeptWHEREdeptno=no1;

            SELECTdeptno,dnameINTOvdeptno2,vdname2FROMdeptWHEREdeptno=no2;

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

            SELECTdeptno,dnameINTOvdeptno1,vdname1FROMdeptWHEREdeptno=no1;

            SELECTdeptno,dnameINTOvdeptno2,vdname2FROMdeptWHEREdeptno=no3;

      ELSE

            SELECTdeptno,dnameINTOvdeptno1,vdname1FROMdeptWHEREdeptno=no2;

            SELECTdeptno,dnameINTOvdeptno2,vdname2FROMdeptWHEREdeptno=no3;

      ENDIF;

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

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

      

END;

/

EXECUTEDeptName(10,20,30);

 

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

A.

CREATEORRE PLACEPROCEDUREDeptMesg(penameemp.ename%TYPE,pdnameOUTdept.dname%TYPE, PLocOUTdept.loc%TYPE)AS

BEGIN

      SELECTdname,locINTOpdname, PLoc

      FROMemp,dept

      WHEREemp.deptno=dept.deptnoANDemp.ename=pename;

END;

/

VARIABLEvdnameVARCHAR2(14)

VARIABLEvlocVARCHAR2(13)

 

EXECUTEDeptMesg('SMITH',:vdname,:vloc);

PRINTvdnamevloc;

 

Q.编写一个给特殊雇员加薪10%的过程，这之后，检查如果已经雇佣该雇员超过60个月，则给他额外加薪3000.

A.

CREATEORRE PLACEPROCEDURERaise_Sal(noINNUMBER)AS

      vhiredateDATE;

      vsalemp.sal%TYPE;

BEGIN

      SELECThiredate,salINTOvhiredate,vsalFROMempWHEREempno=no;

      IFMONTHS_BETWEEN(SYSDATE,vhiredate)>60THEN 1 <script type="text/javascript"> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>

<script type="text/javascript"> </script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>