1、 | m | & | erec.deptno);END;/ Q.编写一个PL/SQL程序块以计算某个雇员的年度薪水总额。esal NUMBER;eename emp.ename%TYPE;SELECT (NVL(sal,0)+NVL(comm,0)*12,ename INTO esal,eename FROM emp WHERE empno=&DBMS_OUTPUT.PUT_LINE(eename | &s Years Salary is & | esal);Q.按下列加薪比执行:Deptno Raise(%age) 10 5% 20 10% 30 15% 40 20% 加薪的百分比是以他们现
2、有的薪水为根据的。写一PL/SQL以对指定雇员加薪。vcounter NUMBER:=10;vraise NUMBER;LOOP EXIT WHEN vcounter40;UPDATE emp set sal=NVL(sal,0)+NVL(sal,0)*0.05 WHERE deptno=vcounter;vcounter:=vcounter+10;END LOOP;Q.编写一PL/SQL以向emp表添加10个新雇员编号。(提示:如果当前最大的雇员编号为7900,则新雇员编号将为7901到7910) vcounter NUMBER;SELECT MAX(empno) INTO vcounter
3、 FROM emp;FOR i IN 1.10 =vcounter+1;INSERT INTO emp(empno) VALUES(vcounter);Q.只使用一个变量来解决实验课作业4。A - vraise NUMBER;SELECT * INTO erec FROM emp WHERE ename=&ename&;IF erec.job=&CLERK& THEN UPDATE emp SET sal=sal+500 WHERE empno=erec.empno;ELSIF erec.job=&SALESMAN&UPDATE emp SET sal=sal+1000 WHERE empno
4、=erec.empno;ANALYST&UPDATE emp SET sal=sal+1500 WHERE empno=erec.empno;ELSE UPDATE emp SET sal=sal+2000 WHERE empno=erec.empno;END IF;- UPDATE emp SET sal=sal+vraise WHERE empno=erec.empno;- DBMS_OUTPUT.PUT_LINE(vraise);Q.接受两个数相除并且显示结果。如果第二个数为0,则显示消息DIVIDE BY ZERO。num1 NUMBER;num2 NUMBER;num1:=#
5、num2:DBMS_OUTPUT.PUT_LINE(num1 | &/& | num2 | & is & | num1/num2);EXCEPTION WHEN ZERO_DIVIDE THEN Didn&t your teacher tell you not to DIVIDE BY ZERO?/第二阶段 Q.编写一个PL/SQL程序块,对名字以A或S开始的所有雇员按他们的基本薪水的10%加薪。CURSOR c1 IS SELECT * FROM emp WHERE SUBSTR(ename,1,1)=&A& OR SUBSTR(ename,1,1)=&S& FOR UPDATE OF sa
6、l;FOR i IN c1 UPDATE emp SET sal=NVL(sal,0)+NVL(sal,0)*0.1 WHERE CURRENT OF c1;Q.编写一PL/SQL,对所有的销售员(SALESMAN)增加佣金500. SELECT * FROM emp WHERE job=&UPDATE emp SET sal=NVL(sal,0)+500 WHERE CURRENT OF c1;Q.编写一PL/SQL,以提升两个资格最老的职员为高级职员(工作时间越长,优先级越高) ORDER BY hiredate FOR UPDATE OF job;-升序排列,工龄长的在前面 EXIT W
7、HEN c1%ROWCOUNT2;DBMS_OUTPUT.PUT_LINE(i.ename);UPDATE emp SET job=&HIGHCLERK& WHERE CURRENT OF c1;Q.编写一PL/SQL,对所有雇员按他们基本薪水的10%加薪,如果所增加的薪水大于5000,则取消加薪。CURSOR c1 IS SELECT * FROM emp FOR UPDATE OF sal;IF (i.sal+i.sal*0.1)=1 AND vflag=50 AND vflag=11 AND vflag=40 AND vflag50) THEN SELECT deptno,dname INTO vdeptno2,vdname2 FROM dept WHERE deptno=no3;SELECT deptno,dname INTO vdeptno1,vdname1 FROM dept WHERE deptno=no2;部门编号:|vdeptno1 |&|&部门名称: |vdname1);|vdeptno2 |& |vdname2);EXECUTE DeptName(10,20,30);Q.编写一过程以显示所指定雇员名的雇员部门名和位置。CREATE OR REPLACE PROCEDU
copyright@ 2008-2022 冰豆网网站版权所有
经营许可证编号:鄂ICP备2022015515号-1