`
LJ你是唯一LT
  • 浏览: 243522 次
社区版块
存档分类
最新评论

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
0
2
分享到:
评论

相关推荐

    Oracle游标实例.txt

    标题“Oracle游标实例”直接点明了文章的主题,即通过具体的示例来讲解Oracle中的游标使用方法。描述重复了标题内容,这表明文件的主要目的是提供关于Oracle游标的实例代码,供学习和参考。 ### 标签 标签“Oracle...

    oracle游标使用及实例

    ### Oracle游标使用及实例详解 #### 一、Oracle游标概述 在Oracle数据库中,游标(Cursor)是一种用于处理SQL查询结果集的方式。它允许用户逐行地读取和处理查询结果,这对于需要对每一行数据进行特定操作的情况非常...

    oracle游标实例

    Oracle游标是数据库管理系统中处理查询结果的一种方法,它允许我们逐行处理数据,而不仅仅是一次性获取所有结果。在Oracle中,游标分为隐式游标和显式游标。 ### 隐式游标 Oracle数据库在执行DML(插入、删除、更新...

    ORACLE 游标使用示例

    在"游标.txt"文件中,可能包含了更多关于Oracle游标的使用实例和技巧,包括游标的声明、动态游标、游标变量、游标表达式以及游标在存储过程和函数中的应用。这些内容可以帮助你更深入地理解和掌握Oracle游标,提高你...

    Oracle游标的使用实例详解

    Oracle游标是数据库管理系统中的一种重要机制,它允许应用程序按需逐行处理查询结果集,而不是一次性处理所有数据。游标对于交互式和事务性的应用尤其有用,因为它们可以控制数据流并根据需要进行交互。 首先,让...

    Orcle游标实例.txt

    ### Oracle 游标实例解析及应用 #### 一、Oracle游标概述 在Oracle数据库中,游标(Cursor)是一种数据库对象,它允许程序能够逐行处理查询结果集中的数据。与直接执行SQL语句获取整个结果集不同,游标可以逐行...

    Oracle游标使用大全

    ### Oracle游标使用大全 #### 一、概述 在Oracle数据库中,游标是一种重要的机制,用于处理查询结果集中的数据。游标允许程序逐行处理数据,这在需要对多行数据进行循环处理时非常有用。本文档旨在提供一个全面的...

    oracle游标优化

    ### Oracle游标优化 在Oracle数据库管理中,游标是一种重要的机制,用于处理查询结果集。游标可以被看作是存储查询结果的一种临时区域,它允许用户通过循环逐行处理这些结果。游标不仅可以提高应用程序的灵活性,还...

    多个ORACLE 游标+SQL 游标 小例子+帮助文档

    本教程将深入介绍Oracle和SQL中的游标概念,以及如何通过实例来理解和应用它们。 首先,我们要明白什么是游标。游标(Cursor)是一个数据库系统组件,它允许我们在查询结果集中移动,一次处理一行数据。这在处理...

    oracle游标的用法

    oracle游标的用法,比较通俗易懂的讲解游标用法,有实例

    oracle 的函数、存储过程、游标、简单实例

    本主题将深入探讨Oracle中的几个核心概念:函数、存储过程、游标以及简单的实例,这些都是数据库管理员和开发人员日常工作中不可或缺的部分。 首先,我们来了解**Oracle函数**。函数是预定义的代码块,接受零个或多...

    详解Oracle游标的简易用法

    本篇将详细解释Oracle游标的简易用法,并通过实例代码来演示如何使用。 首先,游标定义了一个SQL查询的结果集,但并不立即执行该查询。在Oracle中,游标由`DECLARE`语句定义,可以包括变量、条件等。例如: ```sql...

    既简单,又使用的游标实例

    以下是对"既简单,又使用的游标实例"的详细解释。 游标,英文名为Cursor,是数据库管理系统(DBMS)提供的一种机制,用于在查询结果集中移动并处理单个记录。它在关系数据库中起着类似指针的作用,可以定位到结果...

    游标实例与返回结果集的存储过程

    根据提供的文件信息,我们可以深入探讨其中涉及的关键概念和实践应用,包括Oracle中的游标使用方法、存储过程的定义以及如何创建并调用返回结果集的存储过程。 ### 1. 游标的使用方法 #### 1.1 游标基本概念 在...

    触发器,游标实例

    在PL/SQL(Oracle的编程语言)中,游标允许我们通过声明、打开、提取和关闭等步骤来遍历和操作数据。在上述例子中,定义了一个名为`cur_age`的游标,用于从`t_borrowRecord`表中选取特定学生的所有借书日期...

    Oracle存储过程实例使用显示游标

    在本例中,“Oracle存储过程实例使用显示游标”着重展示了如何在存储过程中调用函数,并通过游标来处理和更新数据。 首先,我们需要了解存储过程的基本结构。一个存储过程通常包含以下部分: 1. **声明部分**:在...

    Oracle游标使用参考语句实例解析

    Oracle游标是数据库管理系统中的一种重要机制,它允许程序员按需逐行处理查询结果集,而不是一次性加载所有数据。游标对于处理大量数据时的效率和控制流程具有显著优势,尤其是在需要对每条记录进行单独操作或者需要...

    job 存储过程 视图 游标结合实例

    以上就是Oracle Job、存储过程、视图和游标在实际应用中的基本概念和结合实例,它们共同构成了数据库管理和自动化操作的重要部分。理解并熟练掌握这些工具,能显著提升数据库的管理和维护效率。

    ORACLE PLSQL实例精解(第4版) 中文版

    ### ORACLE PL/SQL 实例精解(第4版)知识点概述 #### 一、PL/SQL基础 **1.1 PL/SQL简介** - **定义**:PL/SQL(Procedural Language for SQL)是一种过程化语言,专门用于增强Oracle数据库的功能。它将SQL命令与...

    oracle精品实例,练习总结

    这个"oracle精品实例,练习总结"的压缩包文件显然包含了nickcheng个人整理的一系列关于Oracle数据库的操作实例和学习心得,旨在帮助用户深入理解和应用Oracle技术。下面我们将深入探讨Oracle数据库的一些关键知识点。...

Global site tag (gtag.js) - Google Analytics