--1.1-100之间的奇数
declare
i integer;
begin
for i in 1..100 loop
if mod(i,2)<>0 then
dbms_output.put_line(i);
end if;
end loop;
end;
--2.对所有员工,如果该员工职位是MANAGER,并且在DALLAS工作那么就给他薪金加15%;如果该员工职位是CLERK,并且在NEWYORK工作那么就给他薪金扣除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 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;
select * from emp;
--3、编写一个PL/SQL程序块,对名字以"A"或"S"开始的所有雇员按他们的基本薪水的10%加薪。
update emp set sal=sal*1.1 where ename like '%A%' or ename like '%S%';
select * from emp;
--4、编写一PL/SQL,对所有的"销售员"(SALESMAN)增加佣金500.
update emp set sal= sal+500 where job = 'SALESMAN';
select * from emp;
--5、编写一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;
--6、编写一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;
--7、显示EMP中的第四条记录。
select * from (select rownum rn,a.* from emp a) b where b.rn=4