PLSQL练习1.doc

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);
       v_sal := ckrec.sal;   
    end loop; 
  end loop;
end;

四、编写一个触发器实现如下功能：
  对修改职工薪金的操作进行合法性检查：
   a) 修改后的薪金要大于修改前的薪金
   b) 工资增量不能超过原工资的10%
   c) 目前没有单位的职工不能涨工资
create or replace trigger tr1
after update of sal on emp
for each row
begin
  if :new.sal <= :old.sal then
       raise_application_error(-20001,'修改后的薪金要大于修改前的薪金');
  elsif :new.sal > :old.sal * 1.1 then
       raise_application_error(-20002,'工资增量不能超过原工资的10%');   
  elsif :old.deptno is null then
       raise_application_error(-20003,'没有单位的职工不能涨工资');          
  end if;
end;

四、    编写一个PL/SQL程序块，对名字以"A"或"S"开始的所有雇员按他们的基本薪水的10%加薪。
DECLARE
CURSOR c1 IS
SELECT * FROM emp WHERE SUBSTR(ename,1,1)=´A´ OR SUBSTR(ename,1,1)=´S´ FOR UPDATE OF sal;

BEGIN
FOR i IN c1
LOOP
UPDATE emp SET sal=NVL(sal,0)+NVL(sal,0)*0.1 WHERE CURRENT OF c1;
END LOOP;
END;
/
五、编写一PL/SQL，对所有的"销售员"(SALESMAN)增加佣金500.
DECLARE
CURSOR c1 IS
SELECT * FROM emp WHERE job=´SALESMAN´ FOR UPDATE OF sal;
BEGIN
FOR i IN c1
LOOP
UPDATE emp SET sal=NVL(sal,0)+500 WHERE CURRENT OF c1;
END LOOP;
END;
/
六、编写一PL/SQL，以提升两个资格最老的"职员"为"高级职员"。（工作时间越长，优先级越高）
DECLARE
CURSOR c1 IS
SELECT * FROM emp WHERE job=´CLERK´ ORDER BY hiredate FOR UPDATE OF job;
--升序排列，工龄长的在前面

BEGIN
FOR i IN c1
LOOP
EXIT WHEN c1%ROWCOUNT>2;
DBMS_OUTPUT.PUT_LINE(i.ename);
UPDATE emp SET job=´HIGHCLERK´ WHERE CURRENT OF c1;
END LOOP;
END;
/
七、编写一PL/SQL，对所有雇员按他们基本薪水的10%加薪，如果所增加的薪水大于5000，则取消加薪。
DECLARE
CURSOR c1 IS SELECT * FROM emp FOR UPDATE OF sal;
BEGIN
FOR i IN c1
LOOP
IF (i.sal+i.sal*0.1)<=5000 THEN
UPDATE emp SET sal=sal+sal*0.1 where
Empno=i.empno
DBMS_OUTPUT.PUT_LINE(i.sal);
END IF;
END LOOP;
END;
/
八、显示EMP中的第四条记录。
DECLARE
CURSOR c1 IS SELECT * FROM emp;
BEGIN
FOR i IN c1
LOOP
IF c1%ROWCOUNT=4 THEN
DBMS_OUTPUT.PUT_LINE(i. EMPNO || ´ ´ ||i.ENAME || ´ ´ || i.JOB || ´ ´ || i.MGR || ´ ´ || i.HIREDATE || ´ ´ || i.SAL || ´ ´ || i.COMM || ´ ´ || i.DEPTNO);
EXIT;
END IF;
END LOOP;
END;
/
九、.编写一个给特殊雇员加薪10%的过程，这之后，检查如果已经雇佣该雇员超过60个月，则给他额外加薪3000.
CREATE OR REPLACE PROCEDURE Raise_Sal(no IN NUMBER) AS
vhiredate DATE;
vsal emp.sal%TYPE;
BEGIN
SELECT hiredate,sal INTO vhiredate,vsal FROM emp WHERE empno=no;
IF MONTHS_BETWEEN(SYSDATE,vhiredate)>60 THEN
vsal:=NVL(vsal,0)*1.1+3000;
ELSE
vsal:=NVL(vsal,0)*1.1;
END IF;
UPDATE emp SET sal=vsal WHERE empno=no;
END;
/
VARIABLE no NUMBER
BEGIN
:no:=7369;
END;
/
十、编写一个函数以检查所指定雇员的薪水是否有效范围内。不同职位的薪水范围为：
Designation Raise
Clerk 1500-2500
Salesman 2501-3500
Analyst 3501-4500
Others 4501 and above.
如果薪水在此范围内，则显示消息"Salary is OK"，否则，更新薪水为该范围内的最水值。
CREATE OR REPLACE FUNCTION Sal_Level(no emp.empno%TYPE) RETURN CHAR AS
vjob emp.job%TYPE;
vsal emp.sal%TYPE;
vmesg CHAR(50);
BEGIN
SELECT job,sal INTO vjob,vsal FROM emp WHERE empno=no;
IF vjob=´CLERK´ THEN
IF vsal>=1500 AND vsal<=2500 THEN
vmesg:=´Salary is OK.´;
ELSE
vsal:=1500;
vmesg:=´Have updated your salary to ´||TO_CHAR(vsal);
END IF;
ELSIF vjob=´SALESMAN´ THEN
IF vsal>=2501 AND vsal<=3500 THEN
vmesg:=´Salary is OK.´;
ELSE
vsal:=2501;
vmesg:=´Have updated your salary to ´||TO_CHAR(vsal);
END IF;
ELSIF vjob=´ANALYST´ THEN
IF vsal>=3501 AND vsal<=4500 THEN
vmesg:=´Salary is OK.´;
ELSE
vsal:=3501;
vmesg:=´Have updated your salary to ´||TO_CHAR(vsal);
END IF;
ELSE
IF vsal>=4501 THEN
vmesg:=´Salary is OK.´;
ELSE
vsal:=4501;
vmesg:=´Have updated your salary to ´||TO_CHAR(vsal);
END IF;
END IF;
UPDATE emp SET sal=vsal WHERE empno=no;
RETURN vmesg;
END;
/
DECLARE
vmesg CHAR(50);
vempno emp.empno%TYPE;
BEGIN
vempno:=&empno;
vmesg:=Sal_Level(vempno);
DBMS_OUTPUT.PUT_LINE(vmesg);
END;
/
--SELECT empno,ename,sal,comm,hiredate FROM emp WHERE empno=:no;

十二、有如下MyTable:
日期 日产
1    3.3333
2    4.2222
3    1.5555
4    9.8888
5 ………
要求用SQL语句生成如下查询
日期 日产     累计日产
1    3.3333   3.3333
2    4.2222   7.5555
3    1.5555   9.0000
4    9.8888   18.8888
5………
select id,quantity,(select sum(quantity)from mytable where id<=t.id) as acount from mytable t

十三、创建一个序列，第一次从5循环到10，以后再从0开始循环
create sequence test_seq
     start with 5
     increment by 1
     maxvalue 10
     minvalue 0
     cycle
     nocache