第三章 PLSQL编程上机操作.docx

第三章 PL/SQL编程（技能训练）上机练习11．计算某雇员应缴纳的所得税declareshuiluNUMBER(2);susuankouchuluNUMBER(5);v_taxNUMBER(10,2);gongziNUMBER(10,2);gongziqizhengdianCONSTANTNUMBER(10):=3500;beginselectsalintogongzifromempwhereename=KING;v_tax:=0;if(gongzi=3500)thengongzi:=gongzi-gongziqizhengdian;casewhengongzi1500thenshuilu:=3;susuankouchulu:=0;whengongzi4500andgongzi=1500thenshuilu:=10;susuankouchulu:=105;whengongzi9000andgongzi=4500thenshuilu:=20;susuankouchulu:=555;whengongzi35000andgongzi=9000thenshuilu:=25;susuankouchulu:=1005;whengongzi55000andgongzi=35000thenshuilu:=30;susuankouchulu:=2755;whengongzi80000andgongzi=55000thenshuilu:=35;susuankouchulu:=5505;elseshuilu:=45;susuankouchulu:=13505;endcase;v_tax:=gongzi*shuilu/100-susuankouchulu;endif;dbms_output.put_line(king应缴纳的所得税是||v_tax);end;2．更新某雇员的薪水declarenianxianjiangjinNUMBER(10,2);ruzhishijianemp.hiredate%TYPE;beginselecthiredateintoruzhishijianfromempwhereename=SCOTT;if(sysdate-ruzhishijian=356*6)thennianxianjiangjin:=2000;elsenianxianjiangjin:=1500;endif;updateempsetsal=nianxianjiangjinwhereename=SCOTT;end;修改后3．显示某雇员相应的工资级别并显示所在部门名称、薪水和所在级别declarejibieNUMBER(1);bumenNamedept.dname%TYPE;xinshuiemp.sal%TYPE;beginselectdname,salintobumenName,xinshuifromemp e innerjoindept done.deptno=d.deptnowhereename=SCOTT;casewhenxinshui=3200andxinshui=700thenjibie:=1;whenxinshui=4400andxinshui=3201thenjibie:=2;whenxinshui=5000andxinshui=4401thenjibie:=3;whenxinshui=7000andxinshui=5001thenjibie:=4;whenxinshui=9999andxinshui=7001thenjibie:=5;endcase;dbms_output.put_line(SCOTT所在部门是||bumenName||薪水||xinshui||级别||jibie);end;4．为某雇员增加工资，每次增加100元，直到10000元停止declaregongziemp.sal%TYPE;beginselectsalintogongzifromempwhereename=SCOTT;loopgongzi:=gongzi+100;exitwhengongzi=10000;endloop;updateempsetsal=gongziwhereename=SCOTT;end;PL/Sql语句执行后，修改后的数据上机练习2DECLAREv_enamevarchar2(4);BEGINSELECTenameINTOv_enameFROMempWHEREempno=1234;dbms_output.put_line(雇员名:||v_ename);EXCEPTIONWHEN NO_DATA_FOUND THENdbms_output.put_line(雇员号不存在);WHENvalue_errorTHENdbms_output.put_line(