发布时间 : 星期六 文章plSql面试题更新完毕开始阅读5b9e8a566edb6f1aff001ff5
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)||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 <= :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程序块，对名字以\或\开始的所有雇员按他们的基本薪水的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，对所有的\销售员\增加佣金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.
如果薪水在此范围内，则显示消息\，否则，更新薪水为该范围内的最水值。 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; /