oracle 游标实例

oracle游标

游标-----内存中的一块区域,存放的是select的结果
   
    游标用来处理从数据库中检索的多行记录（使用SELECT语句）。利用游标，程序可以逐个地处理和遍历一次检索返回的整个记录集。
    为了处理SQL语句，Oracle将在内存中分配一个区域，这就是上下文区。这个区包含了已经处理完的行数、指向被分析语句的指针，游标就是指向上下文区句柄或指针。

缺点：游标使用时会对行加锁，可能会影响其他业务的正常进行。
       而且，数据量大时其效率也较低。因为游标其实是相当于把磁盘数据整体放入了内存中，如果游标数据量大会造成内存不足，也有可能导致cpu高，所以，在数据量小时才使用游标。

优点：相当于程序中的FOR循环处理。一条一条的处理你读取的记录内容。
       数据存放在内存中，数据量少的时候速度比较快。

一、显示游标
    显示游标被用于处理返回多行数据的SELECT 语句，游标名通过CURSOR….IS 语句显示地赋给SELECT 语句。
     在PL/SQL中处理显示游标所必需的四个步骤：
     1）声明游标；CURSOR cursor_name IS select_statement
     2）为查询打开游标；OPEN cursor_name
     3）取得结果放入PL/SQL变量中；
            FETCH cursor_name INTO list_of_variables;
            FETCH cursor_name INTO PL/SQL_record;
     4）关闭游标。CLOSE cursor_name
     注意：在声明游标时，select_statement不能包含INTO子句。当使用显示游标时，INTO子句是FETCH语句的一部分。
  
二、隐式游标
     所有的隐式游标都被假设为只返回一条记录。
     使用隐式游标时，用户无需进行声明、打开及关闭。PL/SQL隐含地打开、处理，然后关掉游标。
     例如：
     SELECT studentNo,studentName INTO curStudentNo,curStudentName
     FROM StudentRecord  WHERE name=’gg’;
     上述游标自动打开，并把相关值赋给对应变量，然后关闭。执行完后，PL/SQL变量curStudentNo,curStudentName中已经有了值。
  
     单条sql语句所产生的结果集合
     用关键字SQL表示隐式游标
     4个属性 %rowcount  影响的记录的行数  整数
             %found     影响到了记录  true
             %notfound  没有影响到记录  true
             %isopen    是否打开  布尔值 永远是false
     多条sql语句 隐式游标SQL永远指的是最后一条sql语句的结果
     主要使用在update 和 delete语句上
    
三、常见游标实例    
(1)for循环游标
SQL> set timing on
SQL> set serverout on
SQL> set pagesize 1000
SQL> set linesize 400
SQL> declare
        cursor c1 is select id,ins_time,random_string from mytest where random_id=50;  ---定义游标
        c1cur c1%rowtype;       ---定义一个游标变量
     begin
        for c1cur in c1 loop    ---for循环
          dbms_output.put_line(c1cur.id||':'||c1cur.ins_time||'-'||c1cur.random_string);
        end loop;
     end;
     /
113:2015-12-23 16:39:41-1TP6AU8U5JJ1O16FIUSZ
176:2015-12-23 16:40:44-N77Y47YGZQJH2ONG504R
187:2015-12-23 16:40:55-PIQG1HNPM6EVFZXD8N7G

PL/SQL procedure successfully completed.
Elapsed: 00:00:00.02


(2) fetch游标    --使用的时候 必须要明确的打开和关闭
      declare
        cursor c1 is select id,ins_time,random_id,random_string from mytest where random_id=50;  ---定义显示游标
        c1cur c1%rowtype;       ---定义一个游标变量
      begin
         open c1;  --打开游标
         loop
            fetch c1 into c1cur;     ---取一行数据到游标变量     
            exit when c1%notfound;   ---判断是否取到了值
            dbms_output.put_line(c1cur.id||':'||c1cur.ins_time||'-'||c1cur.random_string);          
         end loop;
         close c1;   ---关闭游标
      end;
      /
113:2015-12-23 16:39:41-1TP6AU8U5JJ1O16FIUSZ
176:2015-12-23 16:40:44-N77Y47YGZQJH2ONG504R
187:2015-12-23 16:40:55-PIQG1HNPM6EVFZXD8N7G

PL/SQL procedure successfully completed.
Elapsed: 00:00:00.02

(3)参数游标(以下都利用scott自带的几张表测试）
SQL> declare
       cursor c1 is select deptno from dept;
       cursor c2(pno number,pjob varchar2) is select * from emp where deptno=pno and job=pjob; --只能指定类型,不能指定长度
       c1cur c1%rowtype;
       c2cur c2%rowtype;
     begin
       for c1cur in c1 loop
         for c2cur in c2(c1cur.deptno,'MANAGER')loop
           dbms_output.put_line(c1cur.deptno||':'||c2cur.ename||'---'||c2cur.sal);
         end loop;
       end loop;
     end;
     /
10:CLARK---2450
20:JONES---2975
30:BLAKE---2850

PL/SQL procedure successfully completed.
Elapsed: 00:00:00.02

(4)引用游标/动态游标
SQL>declare
       type c1 is ref cursor;     --定义一个类型(ref cursor)弱类型 
       type c2 is ref cursor return emp%rowtype;   --定义一个强类型(返回的结果集有要求)
       c0cur c1;              ---定义一个弱类型的游标变量
       c1cur emp%rowtype;
       c2cur dept%rowtype;
    begin
       dbms_output.put_line('all employees :');
       open c0cur for select * from emp;
       loop
          fetch c0cur into c1cur;
          exit when c0cur%notfound;
          dbms_output.put_line(c1cur.ename);
       end loop;
       dbms_output.put_line('all departments:');
       open c0cur for select * from dept;
       loop
         fetch c0cur into c2cur;
         exit when c0cur%notfound;
         dbms_output.put_line(c2cur.dname);
       end loop;
       close c0cur;
    end;
   /
all employees:
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
all departments:
ACCOUNTING
RESEARCH
SALES
OPERATIONS

PL/SQL procedure successfully completed.
Elapsed: 00:00:00.02

(5)while循环
SQL> set timing on
SQL> set serverout on
SQL> set pagesize 1000
SQL> set linesize 400
SQL> declare
       cursor c1 is select loc from dept;
       c1cur c1%rowtype;
     begin
       open c1;
       fetch c1 into c1cur;
       while c1%found loop
       dbms_output.put_line('location:  '||c1cur.loc);
       fetch c1 into c1cur;   ---与for循环不同，while循环需要将下一行的值赋给游标变量，否则会进入死循环，并报错
       end loop;
       close c1;
     end;
    /
location:  NEW YORK
location:  DALLAS
location:  CHICAGO
location:  BOSTON

PL/SQL procedure successfully completed.
Elapsed: 00:00:00.10


死循环，报错：
SQL> declare
       cursor c1 is select loc from dept;
       c1cur c1%rowtype;
     begin
       open c1;
       fetch c1 into c1cur;
       while c1%found loop
       dbms_output.put_line('location:  '||c1cur.loc);
       end loop;
       close c1; 
     end;
     /
location:  NEW YORK
location:  NEW YORK
location:  NEW YORK
location:  NEW YORK
location:  NEW YORK
declare
*
ERROR at line 1:
ORA-04030: out of process memory when trying to allocate 16328 bytes (koh-kghu sessi,pl/sql vc2)

执行时，单个进程就导致cpu 100%，因此游标还是要小心使用的！


(6)利用游标来更新（update）
6.1 声明更新显示游标：
   Cursor 游标名IS  SELECT 语句   For Update [ Of 更新列列名]；
6.2 使用显示游标当前记录来更新：
   Update  表名   SET   更新语句  Where   Current  Of   游标名；

1.所有人普调
SQL> declare
       cursor c1 is select * from emp for update of sal;
       c1cur c1%rowtype;
       saladd emp.sal%type;
       salnew emp.sal%type;
     begin
       for c1cur in c1 loop
         saladd:=c1cur.sal*0.2;  --所有员工加薪20%
           if saladd<300 then
             salnew:=c1cur.sal+300;   --加薪不足300的，按300加
             dbms_output.put_line(c1cur.ename||':'||'new salary '||salnew);
           else
             salnew:=c1cur.sal+saladd;
             dbms_output.put_line(c1cur.ename||':'||'new salary '||salnew);
           end if;
             update emp set sal=salnew where current of c1;
       end loop;
     end ;
     /
SMITH:new salary 1300  ---原来工资1000
ALLEN:new salary 2304
WARD:new salary 1800
JONES:new salary 4284
MARTIN:new salary 1800
BLAKE:new salary 4104
CLARK:new salary 3528
SCOTT:new salary 4320
KING:new salary 7200
TURNER:new salary 2160
ADAMS:new salary 1620
JAMES:new salary 1450
FORD:new salary 4320
MILLER:new salary 1872

PL/SQL procedure successfully completed.
Elapsed: 00:00:00.04

2.按照部门来调整
declare               
   cursor c1 is select * from emp for update of sal;
   c1cur c1%rowtype;  
   salnew emp.sal%type;
begin                 
   for c1cur in c1 loop
   case                ---必须包含所有的分类，否则会报错ORA-06592: CASE not found while executing CASE statement
      when c1cur.deptno=10
      then salnew:=c1cur.sal*1.1;
      dbms_output.put_line(c1cur.ename||':'||'new salary '||salnew);
      when c1cur.deptno=20
      then salnew:=c1cur.sal*1.15;
      dbms_output.put_line(c1cur.ename||':'||'new salary '||salnew);
      when c1cur.deptno=30
      then salnew:=c1cur.sal*1.2;
      dbms_output.put_line(c1cur.ename||':'||'new salary '||salnew);
      when c1cur.deptno=40
      then salnew:=c1cur.sal*1.3;
      dbms_output.put_line(c1cur.ename||':'||'new salary '||salnew);
   end case;
      update emp set sal=salnew where current of c1;
   end loop;
end;
/

SMITH:new salary 1495
ALLEN:new salary 2764.8
WARD:new salary 2160
JONES:new salary 4926.6
MARTIN:new salary 2160
BLAKE:new salary 4924.8
CLARK:new salary 3880.8
SCOTT:new salary 4968
KING:new salary 7920
TURNER:new salary 2592
ADAMS:new salary 1863
JAMES:new salary 1740
FORD:new salary 4968
MILLER:new salary 2059.2
LITING:new salary 7800

PL/SQL procedure successfully completed.
Elapsed: 00:00:00.07

3.符合if条件的
declare               
   cursor c1 is select * from emp for update of sal;
   c1cur c1%rowtype;  
   salnew emp.sal%type;
begin                 
   for c1cur in c1 loop
     if c1cur.deptno=20 then
        salnew:=c1cur.sal*5-8000;
        dbms_output.put_line(c1cur.ename||':'||'new salary '||salnew);
     else  
        salnew:=c1cur.sal;       
     end if;
        update emp set sal=salnew where current of c1;
   end loop;
end;
/
SMITH:new salary 1525
JONES:new salary 6633
SCOTT:new salary 6840
ADAMS:new salary 1315
FORD:new salary 6840

PL/SQL procedure successfully completed.
Elapsed: 00:00:00.05


4.以平均值为分界
SQL> select avg(sal) from emp;  平均工资3715
  AVG(SAL)
----------
      3715
     
SQL> declare
  2  cursor c1 is select empno,ename,sal,deptno,avg(sal) over(partition by deptno) as depavg from emp for update of sal;
  3  c1cur c1%rowtype;
  4  salnew emp.sal%type;
  5  begin
  6  for c1cur in c1 loop
  7  if c1cur.sal>c1cur.depavg then
  8  salnew:=c1cur.sal+50;    ---高于平均工资的奖金发50
  9  dbms_output.put_line(c1cur.ename||':'||'new salary '||salnew); 
10  else
11  salnew:=c1cur.sal+100;   ---低于平均工资的奖金发100
12  dbms_output.put_line(c1cur.ename||':'||'new salary '||salnew);
13  end if;
14  update emp set sal=salnew where current of c1;
15  end loop;
16  end;
17  /
CLARK:new salary 3980
MILLER:new salary 2160
KING:new salary 7970
JONES:new salary 4976
SMITH:new salary 1595
SCOTT:new salary 5018
FORD:new salary 5071
ADAMS:new salary 1415
WARD:new salary 2260
TURNER:new salary 2692
ALLEN:new salary 2814
JAMES:new salary 1840
MARTIN:new salary 2260
BLAKE:new salary 4974
LITING:new salary 7900

PL/SQL procedure successfully completed.
Elapsed: 00:00:00.47     


(7)利用游标来删除（delete）
7.1 声明删除显示游标：
   Cursor 游标名IS  SELECT 语句   For Delete;
7.2 使用显示游标当前记录来更新或删除：
   Delete  From  表名   Where   Current  Of   游标名;

SQL> create table emp1 as select * from emp;
Table created.
Elapsed: 00:00:03.05

SQL> declare
       cursor c1 is select ename,job from emp1 for update;
       e_job emp1.job%type;
       e_name emp1.ename%type;
     begin
       open c1;
       loop
       fetch c1 into e_name,e_job;  --顺序要跟上面的select顺序对应
       exit when c1%notfound;
          if e_job='ANALYST' then
             delete from emp1 where current of c1;
             dbms_output.put_line(e_name||':'||'deleted');
          end if;
       end loop;
       close c1;
     end;
     / 
SCOTT:deleted
FORD:deleted

PL/SQL procedure successfully completed.
Elapsed: 00:00:00.09

(8)利用游标来插入
SQL> declare
  2  cursor c1 is select * from emp1;
  3  c1cur c1%rowtype;
  4  begin
  5  open c1;
  6  loop
  7  fetch c1 into c1cur;
  8  exit when c1%notfound;
  9  insert into emp values c1cur;
10  dbms_output.put_line(c1cur.ename||': inserted');
11  end loop;
12  close c1;
13  end;
14  /
TINA: inserted
BOBO: inserted
LIO: inserted

PL/SQL procedure successfully completed.
Elapsed: 00:00:00.11

SQL> select * from emp;  ---查看数据，插入成功

     EMPNO ENAME      JOB         MGR HIREDATE     SAL       COMM     DEPTNO
---------- ---------- ---------   ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK       7902 17-DEC-80    1595    20
      7499 ALLEN      SALESMAN   7698 20-FEB-81    2814        300    30
      7521 WARD       SALESMAN   7698 22-FEB-81    2260        500    30
      7566 JONES      MANAGER     7839 02-APR-81    4976    20
      7654 MARTIN     SALESMAN   7698 28-SEP-81    2260       1400    30
      7698 BLAKE      MANAGER     7839 01-MAY-81    4974    30
      7782 CLARK      MANAGER     7839 09-JUN-81    3980    10
      7788 SCOTT      ANALYST     7566 19-APR-87    5018    20
      7839 KING       PRESIDENT      17-NOV-81    7970    10
      7844 TURNER     SALESMAN   7698 08-SEP-81    2692       0      30
      7876 ADAMS      CLERK       7788 23-MAY-87    1415    20
      7900 JAMES      CLERK       7698 03-DEC-81    1840    30
      7902 FORD       ANALYST     7566 03-DEC-81    5071    20
      7934 MILLER     CLERK       7782 23-JAN-82    2160    10
      7200 LITING     CLERK       7788 03-DEC-81    7900        500    40
      6000 TINA       CLERK       7788 02-APR-86    4503        300    10
      6221 BOBO       CLERK       7788 02-APR-86    4000        200    10
      6307 LIO       CLERK       7788 02-APR-86    4409       0      10