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

MILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">第二阶段

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;

/