PLSQL练习1.doc

OraclePL/SQL面试题文章分类:数据库 一、 求1-100之间的素数set serveroutput ondeclareflag 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月以前的加薪1081年6月以后的加薪5declare 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;三、 根据员工在各自部门中的工资高低排出在部门中的名次(允许并列). 一条SQL语句select deptno,ename,sal,(select count(*) + 1from emp where deptno = a.deptno and sal a.sal) as ordfrom emp a order by deptno,sal desc; 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 empfor each rowbegin if :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%加薪。DECLARECURSOR c1 IS SELECT * FROM emp WHERE SUBSTR(ename,1,1)=A OR SUBSTR(ename,1,1)=S FOR UPDATE OF sal;BEGINFOR i IN c1LOOPUPDATE emp SET sal=NVL(sal,0)+NVL(sal,0)*0.1 WHERE CURRENT OF c1;END LOOP;END;/五、编写一PL/SQL，对所有的销售员(SALESMAN)增加佣金500.DECLARECURSOR c1 IS SELECT * FROM emp WHERE job=SALESMAN FOR UPDATE OF sal;BEGINFOR i IN c1LOOPUPDATE emp SET sal=NVL(sal,0)+500 WHERE CURRENT OF c1;END LOOP;END;/六、编写一PL/SQL，以提升两个资格最老的职员为高级职员。（工作时间越长，优先级越高）DECLARECURSOR c1 IS SELECT * FROM emp WHERE job=CLERK ORDER BY hiredate FOR UPDATE OF job; -升序排列，工龄长的在前面BEGINFOR i IN c1LOOPEXIT WHEN c1%ROWCOUNT2;DBMS_OUTPUT.PUT_LINE(i.ename);UPDATE emp SET job=HIGHCLERK WHERE CURRENT OF c1;END LOOP;END;/七、编写一PL/SQL，对所有雇员按他们基本薪水的10%加薪，如果所增加的薪水大于5000，则取消加薪。DECLARECURSOR c1 IS SELECT * FROM emp FOR UPDATE OF sal; BEGINFOR i IN c1LOOPIF (i.sal+i.sal*0.1)60 THENvsal:=NVL(vsal,0)*1.1+3000;ELSEvsal:=NVL(vsal,0)*1.1;END IF;UPDATE emp SET sal=vsal WHERE empno=no;END;/VARIABLE no NUMBERBEGIN:no:=7369;END;/十、编写一个函数以检查所指定雇员的薪水是否有效范围内。不同职位的薪水范围为：Designation RaiseClerk 1500-2500Salesman 2501-3500Analyst 3501-4500Others 4501 and above.如果薪水在此范围内，则显示消息Salary is OK，否则，更新薪水为该范围内的最水值。CREATE OR REPLACE FUNCTION Sal_Level(no emp.empno%TYPE) RETURN CHAR ASvjob emp.job%TYPE;vsal emp.sal%TYPE;vmesg CHAR(50);BEGINSELECT job,sal INTO vjob,vsal FROM emp WHERE empno=no;IF vjob=CLERK THENIF vsal=1500 AND vsal=2501 AND vsal=3501 AND vsal=4501 THENvmesg:=Salary is OK.;ELSEvsal:=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;/DECLAREvmesg CHAR(50);vempno emp.empno%TYPE;BEGINvempno:=&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.33332 4.22223 1.55554 9.88885 要求用SQL语句生成如下查询日期 日产 累计日产1 3.3333 3.33332 4.2222 7.55553 1.555