java-oracle中几十个实用的PL_SQL(2)

文章作者 100test 发表时间 2007:03:26 17:55:09
来源 100Test.Com百考试题网

第二阶段
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.
/