PLSQL练习1.doc

1、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；如果该员工职位

2、是 CLERK，并且在 NEW YORK 工作那么就给他薪金扣除 5;其他情况不作处理对所有员工,如果该员工部门是 SALES，并且工资少于 1500 那么就给他薪金加15；如果该员工部门是 RESEARCH，并且职位是 CLERK 那么就给他薪金增加5;其他情况不作处理 declarecursor c1 is select empno,sal,dname,job from emp e,dept d where e.deptno = d.deptno;c1rec c1%rowtype;beginfor c1rec in c1 loopif c1rec.dname = SALES and c1r

3、ec.sal 一条 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 isselect * from dept;ccrec cc%rowtype;cursor ck(no number) isselect * from emp where deptno = no order by sal desc;ckrec ck%

4、rowtype;i number;j number;v_sal number:=-1;beginfor ccrec in cc loopi := 0;for ckrec in ck(ccrec.deptno) loopi := 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;

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

6、3,没有单位的职工不能涨工资);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，对所有

7、的“销售员“(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

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

9、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 a

10、bove.如果薪水在此范围内，则显示消息“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=

11、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:=vmesg:=Sal_Level(vempno);DBMS_OUTPUT.PUT_LINE(vmesg);END;/-SELECT empno,ename

12、,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.5555 9.00004 9.8888 18.88885select id,quantity,(select sum(quantity)from mytable where id=t.id) as acount from mytable t十三、创建一个序列，第一次从 5 循环到 10，以后再从 0 开始循环create sequence test_seqstart with 5increment by 1maxvalue 10minvalue 0cyclenocache