1.利用游标显示职工号、来厂日期、职位、部门号、部门名、部门所在地、工资所处的等级。
declare
outempno int;
outhiredate date;
outjob varchar(10);
outdeptno int;
outloc varchar(10);
outgrade int;
cursor list_emp_dept_salgrade is select empno,hiredate,job,deptno,loc,grade from emp natural join dept,salgrade where sal between losal and hisal;
begin
open list_emp_dept_salgrade;
loop
fetch list_emp_dept_salgrade into outempno,outhiredate,outjob,outdeptno,outloc,outgrade;
exit when list_emp_dept_salgrade%notfound;
dbms_output.put_line(outempno||' '||outhiredate||' '||outjob||' '||outdeptno||' '||outloc||' '||outgrade);
end loop;
close list_emp_dept_salgrade;
commit;
end;
2、为所有的经理涨工资,每人提升10%,如果工作超过5年的话,再加3000元。
declare
outempno int;
outename varchar(10);
outjob varchar(10);
outhiredate date;
outsal int;
cursor list_emp is select empno,ename,job,hiredate,sal from emp where job='MANAGER';
begin
open list_emp;
loop
fetch list_emp into outempno,outename,outjob,outhiredate,outsal;
exit when list_emp%notfound;
update emp set sal=(1+0.1)*outsal where empno=outempno;
if months_between(sysdate,outhiredate)>=60 then
update emp set sal=sal+3000 where empno=outempno;
end if;
end loop;
close list_emp;
commit;
end;
第二种方法:
declare
cursor cur_emp is select * from emp where job='MANAGER' for update of sal;
semp emp%rowtype;
begin
open cur_emp;
loop
fetch cur_emp into semp;
exit when cur_emp%notfound;
update emp set sal=(1+0.1)*sal where current of cur_emp;
if months_between(sysdate,outhiredate)>=60 then
update emp set sal=sal+3000 where current of cur_emp;//释放当前行的锁
end if;
end loop;
close cur_emp;
commit;
end;
3、将emp表中工作年限最长的两个人提升为经理。
declare
outhiredate date;
outjob varchar(10);
outempno number;
cursor list_emp is select job,hiredate,empno from emp order by hiredate asc;
begin
for i in 1..2 loop
fetch list_emp into outjob,outhiredate,outempno;
exit when list_emp%notfound;
update emp set job='MANAGER' where empno=outempno;
end loop;
commit;
end;
第二种方法:
declare
cursor cur_emp is select * from emp where job not like 'MANAGER' order by hiredate asc for update of job;
semp emp%rowtype;
begin
open cur_emp;
loop
fetch cur_emp into semp;
exit when cur_emp%rowcount>=2;
update emp set job='MANAGER' where current of cur_emp;
end loop;
close cur_emp;
commit;
end;
4、将emp表中姓名以A或S开始的员工工资提升10%。
declare
cursor cur_emp is select empno from emp where ename like 'A%' or ename like 'S%';
outempno int;
begin
open cur_emp;
loop
fetch cur_emp into outempno;
exit when cur_emp%notfound;
update emp set sal=sal*1.1 where empno=outempno;
end loop;
close cur_emp;
commit;
end;
第二种方法:
declare
cursor cur_emp is select * from emp where ename like 'A%' or ename like 'S%' for update of sal;
semp emp%rowtype;
begin
open cur_emp;
loop
fetch cur_emp into semp;
exit when cur_emp%notfound;
update emp set sal=sal*1.1 where current of cur_emp;
end loop;
close cur_emp;
commit;
end;
5.将员工的工资进行修改,少于1000 改为1000,在1001-2000 之间改为2500,2001-3000 之间改为3500。
declare
outempno int;
outsal int;
cursor list_emp is select empno,sal from emp;
begin
open list_emp;
loop
fetch list_emp into outempno,outsal;
exit when list_emp%notfound;
if outsal<1500 then
outsal:=1500;
elsif outsal<3000 then
outsal:=3000;
else
outsal:=5000;
end if;
update emp set sal=outsal where empno=outempno;
end loop;
dbms_output.put_line('更新成功');
close list_emp;
commit;
end;
declare
cursor cur_emp is select * from emp for update of sal;
semp emp%rowtype;
begin
open cur_emp;
loop
fetch cur_emp into semp;
exit when cur_emp%notfound;
if semp.sal<1500 then
semp.sal:=1500;
elsif semp.sal<3000 then
semp.sal:=3000;
else
semp.sal:=5000;
end if;
update emp set sal=semp.sal where current of sal;
end loop;
close cur_emp;
commit;
end;