Oracle-plsq存储过程l面试题.docxVIP

OraclePL/SQL面试题一、求1-100之间的素数declare fag boolean:=true; begin for i in 1..100 loop for j in 2..i-1 loop if mod(i,j)=0 then fag:=false; end if; end loop; if fag then dbms_output.put_line(i); end if; fag:=true; end loop; end; 二、对所有员工,如果该员工职位是MANAGER，并且在DALLAS工作那么就给他薪金加15％；如果该员工职位是CLERK，并且在NEW YORK工作那么就给他薪金扣除5％;其他情况不作处理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 = (select empno from emp where ename=BLAKE); --直接上级是BLAKE的所有员工 c1rec c1%rowtype; begin for c1rec in c1 loop if c1rec.hiredate 01-6月-81 then update emp set sal = sal * 1.1 where empno = c1rec.empno; else update emp set sal = sal * 1.05 where empno = c1rec.empno; end if; end loop; end; 三、根据员工在各自部门中的工资高低排出在部门中的名次(允许并列). 1 一条SQL语句 select deptno,ename,sal,(select count(*) + 1 from emp where deptno = a.deptnoand sala.sal) as ordfrom empaorder by deptno,saldesc; 2 PL/SQL块declare cursor cc is select * from dept; ccreccc%rowtype; cursor ck(no number) is select * from emp where deptno = no order by saldesc; ckrecck%rowtype; i number; j number; v_sal number:=-1; begin for ccrec in cc loop i := 0; for ckrec in ck(ccrec.deptno) loop i := i + 1; --写入临时表 if ckrec.sal = v_sal then null; else j:=i; end if; --显示 DBMS_OUTPUT.put_line(ccrec.deptno||chr(9)||ccrec.ename||chr(9)||ckrec.sal||chr(9)||j); v_sal := ckrec.sal; end loop; end lo