PLSQ练习1.docVIP

,,,HYPERLINK,,,/blog/565861,,,OraclePL/SQL面试题 文章分类:,,,HYPERLINK,,,/blogs/category/database,,,数据库,,, 一、???,,,求1-100之间的素数set,,,serveroutput,,,on declareflag,,,boolean:=true;beginfor,,,i,,,in,,,1..100,,,loopfor,,,j,,,in,,,2..i-1,,,loopif,,,mod(i,j)=0,,,thenflag:=false;end,,,if;end,,,loop;if,,,flag,,,thendbms_output.put_line(i);end,,,if;flag:=true;end,,,loop;end;二、???,,,对所有员工,如果该员工职位是MANAGER，并且在DALLAS工作那么就给他薪金加15％；如果该员工职位是CLERK，并且在NEW,,,YORK工作那么就给他薪金扣除5％;其他情况不作处理 对所有员工,如果该员工部门是SALES，并且工资少于1500那么就给他薪金加15％；如果该员工部门是RESEARCH，并且职位是CLERK那么就给他薪金增加5％;其他情况不作处理,,, declare ,,,,,,cursor,,,c1,,,is,,,,,,select,,,empno,sal,dname,job,,,from,,,emp,,,e,dept,,,d,,,where,,,e.deptno,,,=,,,d.deptno; ,,,,,,c1rec,,,c1%rowtype; begin ,,, ,,,,,,for,,,c1rec,,,in,,,c1,,,loop ,,,,,,,,,,,,if,,,c1rec.dname,,,=,,,SALES,,,and,,,c1rec.sal,,,1500,,,then ,,,,,,,,,,,,,,,,,,,,,update,,,emp,,,set,,,sal,,,=,,,sal,,,*,,,1.15,,,where,,,empno,,,,,,=,,,c1rec.empno; ,,,,,,,,,,,,elsif,,,c1rec.dname=RESEARCH,,,and,,,c1rec.job=CLERK,,,,,,then ,,,,,,,,,,,,,,,,,,,,,update,,,emp,,,set,,,sal,,,=,,,sal,,,*,,,1.05,,,where,,,empno,,,,,,=,,,c1rec.empno; ,,,,,,,,,,,,else ,,,,,,,,,,,,,,,null;,,, ,,,,,,,,,end,,,if;,,,,,,,,,,,,,,, ,,,,,,end,,,loop;,,, ,,,,,, end;declare?,,,cursor,,,c1,,,is,,,select,,,*,,,from,,,emp;?,,,c1rec,,,c1%rowtype;?,,,v_loc,,,varchar2(20);begin?,,,for,,,c1rec,,,in,,,c1,,,loop???,,,select,,,loc,,,into,,,v_loc,,,from,,,dept,,,where,,,deptno,,,=,,,c1rec.deptno;???,,,if,,,c1rec.job,,,=,,,MANAGER,,,and,,,v_loc,,,=,,,DALLAS,,,then??????,,,update,,,emp,,,set,,,sal,,,=,,,sal,,,*,,,1.15,,,where,,,empno?,,,=,,,c1rec.empno;???,,,elsif,,,c1rec.job=CLERK,,,and,,,v_loc,,,=,,,NEW,,,YORK,,,then??????,,,update,,,emp,,,set,,,sal,,,=,,,sal,,,*,,,0.95,,,where,,,empno?,,,=,,,c1rec.empno;???,,,else???,,,?null;,,,??,,,end,,,if;????,,,?,,,end,,,loop;??,,,end;?,,,三、对直接上级是BLAKE的所有员工，按照参加工作的时间加薪：???,,,81年6月以前的加薪10％81年6月以后的加薪5％declare?,,,cursor,,,c1,,,is,,,select,,,*,,,from,,,emp,,,where,,,mgr,,