内容发布更新时间 : 2024/6/29 23:29:50星期一 下面是文章的全部内容请认真阅读。
OraclePL/SQL面试题
文章分类:数据库
一、 求1-100之间的素数 set serveroutput on
declare
flag boolean:=true; begin
for i in 1..100 loop for j in 2..i-1 loop if mod(i,j)=0 then flag:=false; end if; end loop; if flag then
dbms_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 = (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.deptno and sal > a.sal) as ord from emp a
order by deptno,sal desc;
<2> PL/SQL块 declare
cursor cc is
select * from dept; ccrec cc%rowtype;
cursor ck(no number) is
select * from emp where deptno = no order by sal desc;
ckrec ck%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)||ckrec.ename||chr(9)||ckrec.sal||chr(9)||j);