plSql面试题.doc

1、PL/SQL 面试题 2010-04-29 14:25 一、 求 1-100 之间的素数 set serverout on create or replace procedure is_prime(inp number) as i number; j number; is_prim boolean; begin dbms_output.new_line; dbms_output.put(to_char(2)| ); for i in 3.inp loop begin is_prim:=true; for j in 2.trunc(sqrt(i) loop if mod(i,j)=0 then

2、begin is_prim:=false; exit; end; end if; end loop; if is_prim then dbms_output.put(to_char(i)| ); end if; end; end loop; dbms_output.new_line; end; / exec is_prime(100) 二、 对所有员工 ,如果该员工职位是 MANAGER，并且在 DALLAS 工作那么就给他薪金加 15；如果该员工职位是 CLERK，并且在 NEW YORK 工作那么就给他薪金扣除 5 ;其他情况不作处理 三、对直接上级是 BLAKE的所有员工，按照参加工作的

3、时间加薪： 81 年 6 月以前的加薪 10 81 年 6 月以后的加薪 5 三、 根据员工在各自部门中的工资高低排出在部门中的名次 (允许并列 ). 四、编写一个触发器实现如下功能： 对修改职工薪金的操作进行合法性检查： 四、 编写一个 PL/SQL 程序块，对 名字以 “A“或 “S“开始的所有雇员按他们的基本薪水的10%加薪。 五、编写一 PL/SQL，对所有的 “销售员 “(SALESMAN)增加佣金 500. 六、编写一 PL/SQL，以提升两个资格最老的 “职员 “为 “高级职员 “。（工作时间越长，优先级越高） 七、编写一 PL/SQL，对所有雇员按他们基本薪水的 10%加薪，如

4、果所增加的薪水大于 5000，则取消加薪。 八、显示 EMP 中的第四条记录。 九、 .编写一个给特殊雇员加薪 10%的过程，这之后，检查如果已经雇佣该雇员超过 60 个月，则给他额外加薪 3000. 十、编写 一个函数以检查所指定雇员的薪水是否有效范围内。不同职位的薪水范围为： 十二、有如下 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 十三、创建

5、一个序列，第一次从 5 循环到 10，以后再从 0 开始循环 一、 求 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；如果该员工职位

6、是 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 empn

7、o = 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 wh

8、ere ename=BLAKE); -直接上级是 BLAKE的所有员工 c1rec c1%rowtype; begin for c1rec in c1 loop if c1rec.hiredate 一条 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; PL/SQL 块 declare cursor cc is select * from dept; ccre

9、c 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_OU

10、TPUT.put_line(ccrec.deptno|chr(9)|ccrec.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

11、 :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

12、,1)=S FOR UPDATE OFsal; 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(sa

13、l,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%ROWCOUNT2; DBMS_OUTPUT.PUT_LINE(i.ename); UPDATE emp SET

14、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)60 THEN vsal:=NVL(vsal,0)*1.1+3000; ELSE vsal:=NVL(vsal,0)*1.1; END IF; UPDATE emp SET sal

15、=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.em

16、pno%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=2501 AND vsal=3501 AND vsal=4501 THEN vmesg:=Salary is OK.; ELSE vsal:=4501; vmesg:=Have updated your salary to |TO_CHA

17、R(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:= vmesg:=Sal_Level(vempno); DBMS_OUTPUT.PUT_LINE(vmesg); END; / -SELECT empno,ename,sal,comm,hiredate FROM emp WHERE empno=:no; 十二、有如下 MyTable: 日期 日产 1 3.

18、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