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

答案：
第四阶段

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 

       WHERE emp.deptno=dept.deptno AND emp.ename=pename;

END;

/

VARIABLE vdname VARCHAR2(14)

VARIABLE vloc VARCHAR2(13)

 

EXECUTE DeptMesg('SMITH',:vdname,:vloc);

PRINT vdname vloc;

 

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

A.

CREATE OR REPLACE PROCEDURE Raise_Sal(no IN NUMBER) AS

       vhiredate DATE;

       vsal emp.sal%TYPE;

BEGIN

       SELECT hiredate,sal INTO vhiredate,vsal FROM emp WHERE empno=no;

       IF MONTHS_BETWEEN(SYSDATE,vhiredate)>60 THEN

              vsal:=NVL(vsal,0)*1.1+3000;

       ELSE

              vsal:=NVL(vsal,0)*1.1;

       END IF;

       UPDATE emp SET sal=vsal WHERE empno=no;

END;

/

VARIABLE no NUMBER

BEGIN

       :no:=7369;

END;

/

EXECUTE Raise_Sal(:no)

SELECT empno,ename,sal,comm,hiredate FROM emp WHERE empno=:no;

 

Q.编写一个函数以检查所指定雇员的薪水是否有效范围内。不同职位的薪水范围为：

 Designation          Raise

 Clerk                   1500-2500

 Salesman             2501-3500

 Analyst                3501-4500

 Others                 4501 and above.

 如果薪水在此范围内，则显示消息“Salary is OK”，否则，更新薪水为该范围内的最水值。

A.

CREATE OR REPLACE FUNCTION Sal_Level(no emp.empno%TYPE) RETURN CHAR AS

       vjob emp.job%TYPE;

       vsal emp.sal%TYPE;

       vmesg CHAR(50);

BEGIN

       SELECT job,sal INTO vjob,vsal FROM emp WHERE empno=no;

       IF vjob='CLERK' THEN

              IF vsal>=1500 AND vsal<=2500 THEN

                     vmesg:='Salary is OK.';

              ELSE

                     vsal:=1500;

                     vmesg:='Have updated your salary to '||TO_CHAR(vsal);

              END IF;

       ELSIF vjob='SALESMAN' THEN

              IF vsal>=2501 AND vsal<=3500 THEN

                     vmesg:='Salary is OK.';

              ELSE

                     vsal:=2501;

                     vmesg:='Have updated your salary to '||TO_CHAR(vsal);

              END IF;

       ELSIF vjob='ANALYST' THEN

              IF vsal>=3501 AND vsal<=4500 THEN

                     vmesg:='Salary is OK.';

              ELSE

                     vsal:=3501;

                     vmesg:='Have updated your salary to '||TO_CHAR(vsal);

              END IF;

       ELSE

              IF vsal>=4501 THEN

                     vmesg:='Salary is OK.';
上一个：几十个实用的PL/SQL(5)
下一个：几十个实用的PL/SQL(3)
更多图片编程知识:

SQL Server 2005 错误 233 的解决办法

mssql server 数据库附加不上解决办法

C#调用SQL自定义函数返回值

ASP.NET缓存依赖--SQL Server 2005与SQL Server 2008缓存依赖

T-SQL自定义函数返回前一天或后一天日期

PlSql复制角色、权限和添加角色权限