《PLSQL编程(上机操作)》由会员分享，可在线阅读，更多相关《PLSQL编程(上机操作)（11页珍藏版）》请在金锄头文库上搜索。
1、文档供参考，可复制、编制，期待您的好评与关注！ 第三章 PL/SQL编程（技能训练） 上机练习11计算某雇员应缴纳的所得税declare shuilu NUMBER(2); susuankouchulu NUMBER(5); v_tax NUMBER(10,2); gongzi NUMBER(10,2); gongziqizhengdian CONSTANT NUMBER(10):=3500;begin select sal into gongzi from emp where ename=KING; v_tax:=0; if (gongzi=3500) then gongzi:=gongzi
2、-gongziqizhengdian; case when gongzi1500 then shuilu:=3;susuankouchulu:=0; when gongzi=1500 then shuilu:=10;susuankouchulu:=105; when gongzi=4500 then shuilu:=20;susuankouchulu:=555; when gongzi=9000 then shuilu:=25;susuankouchulu:=1005; when gongzi=35000 then shuilu:=30;susuankouchulu:=2755; when g
3、ongzi=55000 then shuilu:=35;susuankouchulu:=5505; else shuilu:=45;susuankouchulu:=13505; end case; v_tax:=gongzi*shuilu/100-susuankouchulu; end if; dbms_output.put_line(king应缴纳的所得税是|v_tax);end;2更新某雇员的薪水declare nianxianjiangjin NUMBER(10,2); ruzhishijian emp.hiredate%TYPE;begin select hiredate into r
4、uzhishijian from emp where ename=SCOTT; if (sysdate-ruzhishijian=356*6) then nianxianjiangjin:=2000; else nianxianjiangjin:=1500; end if; update emp set sal=nianxianjiangjin where ename=SCOTT;end;修改后3显示某雇员相应的工资级别并显示所在部门名称、薪水和所在级别declare jibie NUMBER(1); bumenName dept.dname%TYPE; xinshui emp.sal%TYP
5、E;begin select dname,sal into bumenName,xinshui from emp e inner join dept d on e.deptno=d.deptno where ename=SCOTT; case when xinshui=700 then jibie:=1; when xinshui=3201 then jibie:=2; when xinshui=4401 then jibie:=3; when xinshui=5001 then jibie:=4; when xinshui=7001 then jibie:=5; end case; dbms
6、_output.put_line(SCOTT所在部门是|bumenName|薪水|xinshui|级别|jibie);end;4为某雇员增加工资，每次增加100元，直到10000元停止declare gongzi emp.sal%TYPE; begin select sal into gongzi from emp where ename=SCOTT; loop gongzi:=gongzi+100; exit when gongzi=10000; end loop; update emp set sal=gongzi where ename=SCOTT; end;PL/Sql语句执行后，修改
7、后的数据上机练习2DECLARE v_ename varchar2(4);BEGIN SELECT ename INTO v_ename FROM emp WHERE empno=1234; dbms_output.put_line(雇员名:|v_ename);EXCEPTION WHEN NO_DATA_FOUND THEN dbms_output.put_line(雇员号不存在); WHEN value_error THEN dbms_output.put_line(姓名长度超过4位); WHEN OTHERS THEN dbms_output.put_line(错误号：|SQLCODE|
8、错误描述：|SQLERRM);END;DECLARE v_ename varchar2(4);BEGIN SELECT ename INTO v_ename FROM emp WHERE empno=7654; dbms_output.put_line(雇员名:|v_ename);EXCEPTION WHEN NO_DATA_FOUND THEN dbms_output.put_line(雇员号不存在); WHEN value_error THEN dbms_output.put_line(姓名长度超过4位); WHEN OTHERS THEN dbms_output.put_line(错误号
9、：|SQLCODE|错误描述：|SQLERRM);END;上机练习3DECLARE v_sal emp.sal%TYPE; v_deptno emp.deptno%TYPE; e_comm_is_null EXCEPTION; -定义异常类型变量BEGIN SELECT sal,deptno INTO v_sal,v_deptno FROM emp WHERE empno=7788; IF v_deptno=10 THEN IF v_sal10000 THEN UPDATE emp SET sal=10000 WHERE empno=7788; ELSE RAISE e_comm_is_nul
10、l; END IF; END IF;EXCEPTION WHEN NO_DATA_FOUND THEN dbms_output.put_line(雇员不存在！错误为:|SQLCODE|SQLERRM); WHEN e_comm_is_null THEN dbms_output.put_line(工资不低于10000元); WHEN others THEN dbms_output.put_line(出现其他异常);END;解释1：以empno为7788为例，因为部门是“20”，则不显示解释2：修改代码：DECLARE BEGIN SELECT sal,deptno INTO v_sal,v_de
11、ptno FROM emp WHERE empno=7782; IF v_deptno=10 THEN IF v_sal10000 THEN UPDATE emp SET sal=10000 WHERE empno=7782; ELSE RAISE e_comm_is_null; END IF; END IF EXCEPTIONEND;以empno为7782为例，因为部门是“10”，sal10000，做更新处理，不抛异常。解释3：再次执行以上的代码，因为部门是“10”，sal=10000，抛出异常，显示消息“工资不低于10000元”解释4：修改代码：DECLARE BEGIN SELECT s
12、al,deptno INTO v_sal,v_deptno FROM emp WHERE empno=3782; IF v_deptno=10 THEN IF v_sal10000 THEN UPDATE emp SET sal=10000 WHERE empno=3782; ELSE RAISE e_comm_is_null; END IF; END IF EXCEPTION END;再次执行以上的代码，因为empno为3782的雇员不存在，抛出异常上机练习41使用游标计算所有雇员应缴纳的所得税declare shuilu NUMBER(2); susuankouchulu NUMBER(5); v_tax NUMBER(10,2); gongzi NUMBER(10,2); v_sum NUMBER(10,2):=0; gongziqizhengdian CONSTANT NUMBER(10):=3500; CURSOR emp_cursor IS SELECT sal FROM emp;begin OPEN emp_cursor;
