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

 

第二阶段

Q.编写一个PL/SQL程序块，对名字以“A”或“S”开始的所有雇员按他们的基本薪水的10%加薪。

A.

DECLARE

       CURSOR c1 IS

          SELECT * FROM emp WHERE SUBSTR(ename,1,1)=@#A@# OR SUBSTR(ename,1,1)=@#S@# FOR UPDATE OF sal;

 

BEGIN

       FOR i IN c1

       LOOP

              UPDATE emp SET sal=NVL(sal,0)+NVL(sal,0)*0.1 WHERE CURRENT OF c1;

       END LOOP;

END;

/

Q.编写一PL/SQL，对所有的“销售员”(SALESMAN)增加佣金500.

A.

DECLARE

       CURSOR c1 IS

          SELECT * FROM emp WHERE job=@#SALESMAN@# FOR UPDATE OF sal;

BEGIN

       FOR i IN c1

       LOOP

              UPDATE emp SET sal=NVL(sal,0)+500 WHERE CURRENT OF c1;

       END LOOP;

END;

/

Q.编写一PL/SQL，以提升两个资格最老的“职员”为“高级职员”。（工作时间越长，优先级越高）

A.

DECLARE

       CURSOR c1 IS

          SELECT * FROM emp WHERE job=@#CLERK@# ORDER BY hiredate FOR UPDATE OF job;

          --升序排列，工龄长的在前面

 

BEGIN

       FOR i IN c1

       LOOP

              EXIT WHEN c1%ROWCOUNT>2;

              DBMS_OUTPUT.PUT_LINE(i.ename);

              UPDATE emp SET job=@#HIGHCLERK@# WHERE CURRENT OF c1;

       END LOOP;

END;

/

Q.编写一PL/SQL，对所有雇员按他们基本薪水的10%加薪，如果所增加的薪水大于5000，则取消加薪。

A.

DECLARE

       CURSOR c1 IS SELECT * FROM emp  FOR UPDATE OF sal;

 

BEGIN

       FOR i IN c1

       LOOP

             

              IF (i.sal+i.sal*0.1)<=5000 THEN

                     UPDATE emp SET sal=sal+sal*0.1 WHERE CURRENT OF c1;

                     DBMS_OUTPUT.PUT_LINE(i.sal);

              END IF;

             

       END LOOP;

END;

/

Q.显示EMP中的第四条记录。

A.

DECLARE

       CURSOR c1 IS SELECT * FROM emp;

 

BEGIN

       FOR i IN c1

       LOOP

              IF c1%ROWCOUNT=4 THEN

                     DBMS_OUTPUT.PUT_LINE(i. EMPNO || @# @# ||i.ENAME ||  @# @# || i.JOB ||  @# @# || i.MGR || @# @# || i.HIREDATE || @# @# || i.SAL || @# @# || i.COMM  || @# @# || i.DEPTNO);

                     EXIT;

              END IF;

       END LOOP;

END;

/

原文转自：http://www.ltesting.net