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

转载请注明出处：http://blog.csdn.net/feng1790291543

一、游标是构件在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')<to_number(to_char('1981-6-1','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')<to_number(to_char(v_date,'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;