一、游标是构件在pl/sql中，用来查询数据库，获取记录集合或者结果集合的指针，它可以让开发者一次访问一行结果集。
oracle中游标的分类：
显示游标
隐式游标
1、显示游标是早期定义的用于处理多行结果集的游标。
2、隐式游标是由oracle定的，它是和单行select…into语句，insert语句、update和delete语句关联在一起使用的
二、显示游标的属性
%found 指明是否取到了指定的记录行
%isopen 指明游标是打开的还是关闭的
%notfound 指示fetch是否失败或是否还有可取的记录行
%rowcount 指明总共取到了多少行数据
--1-对所有员工，如果职位是manager 并且在dallas工作，那么给他加薪15%
--如果职位是clerk,并且在new york工作的扣薪5%;其他情况不作处理
declare
cursor cur_emp is select e.*,d.loc,d.dname from emp e,dept d
where e.deptno=d.deptno for update of e.empno;
begin
for v_emp in cur_emp loop
if v_emp.job = 'manager' and v_emp.loc = 'dallas'
then
update emp set sal=sal*1.15 where current of cur_emp;
elsif v_emp.job = 'clerk' and v_emp.loc = 'new york'
then
update emp set sal=sal*0.95 where current of cur_emp;
end if;
end loop;
end;
--2-对直接上级是'blake'的所有员工，按照参加工作时间加薪；
--81年6月以前的加薪10%
select to_number(to_char(e.hiredate,'yy.mm'),'99.90') date_num from emp e;
select to_number('1981-6-1','99.90') date_num from dual;
select e.hiredate
from emp e;
if to_number(to_char(e.hiredate,'yy.mm'),'99.99')update emp set sal=sal*1.1;
--81年6月以后的加薪5%
if to_number(to_char(e.hiredate,'yy.mm'),'99.99')>to_number(to_char('1981-6-1','yy.mm'),'99.99');
update emp set sal=sal*1.05;
--substr('abcdef',3,2)/to_number('','');
-- 直接上级是'blake'的所有员工
-------------------------------------------------------------
select empno,lpad(' ',level*2)||ename
from emp e
start with job='clerk' connect by prior empno=mgr
--------------------------------------------------------------
select empno,lpad(' ',level*2)||ename
from emp e
start with ename='scott' connect by prior --mgr=empno
empno=mgr
--------------------**************************------------------------
declare
cursor cur_emp is select empno,lpad(' ',level*2)||ename,e.hiredate
from emp e
start with job='clerk'
connect by prior empno=mgr for update;
v_date emp.hiredate%type;
begin
--open cur_emp;
v_date:=to_date('1981-6-1','yyyy:mm:dd');
for v_emp in cur_emp loop
if to_number(to_char(v_emp.hiredate,'yy.mm'),'99.99') then
update emp set sal=sal*1.1;
elsif to_number(to_char(v_emp.hiredate,'yy.mm'),'99.99')>to_number(to_char(v_date,'yy.mm'),'99.99')
then
update emp set sal=sal*1.05;
end if;
end loop;
--close cur_emp;
end;
--3 -移动收费 执行一次扣一次费
--写一个存储过程，执行一次，更新table2中余额，按照 table1要求，减少（要求使用更新游标）
-- table 1 --
create table sermobile(
serno number,
sername varchar2(10),
sersale number
);
alter table sermobile add constraints pk_sno primary key(serno);
drop table sermobile;
insert into sermobile values(1,'套餐1',40);
insert into sermobile values(2,'套餐2',50);
insert into sermobile values(3,'套餐3',60);
insert into sermobile values(4,'套餐4',70);
-- table2 --
create table seruser(
uno number,
uname varchar2(10),
serno number,
usal number
);
alter table seruser add constraints pk_uno primary key(uno);
alter table seruser add constraints fk_sno foreign key(serno) references sermobile(serno);
insert into seruser values(1001,'用户1',1,500);
insert into seruser values(1002,'用户2',2,500);
insert into seruser values(1003,'用户3',3,500);
insert into seruser values(1004,'用户4',4,500);
insert into seruser values(1005,'用户5',1,500);
insert into seruser values(1006,'用户6',2,500);
insert into seruser values(1007,'用户7',3,500);
insert into seruser values(1008,'用户8',4,500);
insert into seruser values(1009,'用户9',1,500);
declare
cursor cur_ser is select ser.*,us.uname,us.uno
from sermobile ser,seruser us
where ser.serno=us.serno for update of us.uno;
begin
for v_row in cur_ser loop
if v_row.serno=1 then
update seruser set usal=usal-40 where current of cur_ser;
elsif v_row.serno=2 then
update seruser set usal=usal-50 where current of cur_ser;
elsif v_row.serno=3 then
update seruser set usal=usal-60 where current of cur_ser;
elsif v_row.serno=4 then
update seruser set usal=usal-70 where current of cur_ser;
end if;
end loop;
end;