【Oracle篇】游标的原理和使用

一、游标是构件在PL/SQL中，用来查询数据库，获取记录集合或者结果集合的指针，它可以让开发者一次访问一行结果集。 Oracle中游标的分类： 显示游标 隐式游标 1、显示游标是早期定义的用于处理多行结果集的游标。 2、隐式游标是由Oracle定的，它是和单行sele

一、游标是构件在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;