oracle游标

1.游标概念

 在PL/SQL块中执行SELECT、INSERT、DELETE和UPDATE语句时，ORACLE会在内存中为其分配上下文区（Context Area），即缓冲区。游标是指向该区的一个指针，或是一种结构化数据类型。它提供了一种对具有多行数据查询结果集中的每一行数据分别进行单独处理的方法，是设计嵌入式SQL语句的应用程序的常用编程方式。

 

2.游标使用

游标分为两种；

　　1，隐式游标

　　2，显示游标,

 

A，隐式游标

隐式游标是当执行sql语句时自动创建的，当没有声明显示游标时，程序是无法控制隐式游标的。

当执行DML语句（insert,update 和 delete）时，隐式游标就会自动创建，对于insert操作，游标控制插入的数据，对于delete,update,游标会记录影响的行数

注意：任何游标都能访问 sql%attribute_name .

 

游标属性

 

属性

描述

%FOUND

 

如果insert,update,delete 执行影响了行数，或者select into 有返回的行数，意思就是有记录被更新，则返回true,否则返回false.

%NOTFOUND

跟上面相反，没有记录被更新则返回true,否则返回false.

%ISOPEN

对于隐式游标总是返回false,因为oracle在执行相关的sql语句后会自动关闭。

对于显示游标，如果被打开了，就返回true,否则返回false.

%ROWCOUNT

返回受影响的行数.当执行Insert,update 或delete语句时，或者是select into 时

 

隐式游标属性

属性

值

SELECT

INSERT

UPDATE

DELETE

SQL%ISOPEN

 

FALSE

FALSE

FALSE

FALSE

SQL%FOUND

TRUE

有结果

 

成功

成功

SQL%FOUND

FALSE

没结果

 

失败

失败

SQL%NOTFUOND

TRUE

没结果

 

失败

失败

SQL%NOTFOUND

FALSE

有结果

 

成功

失败

SQL%ROWCOUNT

 

返回行数，只为1

插入的行数

修改的行数

删除的行数

 

看例子的前提还是先准备数据

 

drop table customer;
create table customer
(
name varchar(100) primary key,
age int,
address varchar(100),
salary decimal
)
delete from customer;
insert into customer values ('jack',22,'Singapore',5000.00);
insert into customer values ('rose',22,'Japan',2000.00);
insert into customer values ('Jet',32,'HongKong',7000.00);
insert into customer values ('John',30,'American',5000.00);
insert into customer values ('Merry',25,'Singapore',3000.00);
insert into customer values ('Peter',26,'China',1000.00);
insert into customer values ('Adi',27,'India',2400.00);

select * from customer

select * from customer where salary <3000
View Code

 

例1；

例1，
set serveroutput on;
DECLARE 
   total_rows number(2);
BEGIN
   UPDATE customer
   SET salary = salary + 500
   where salary < 3000;
   IF sql%notfound THEN
      dbms_output.put_line('no customers selected');
   ELSIF sql%found THEN
      total_rows := sql%rowcount;
      dbms_output.put_line( total_rows || ' customers selected ');
   END IF; 
END;
/
View Code

 

结果：

3 customers selected

查看数据会发现，数据库更新了3条记录。

大多数时候我们还是使用的显示游标，这样更加容易去控制。

 

B，显式游标

 

游标的声明：

    CURSOR cursor_name[(parameter[, parameter]…)] 
              [RETURN datatype]
    IS 
        select_statement;

 

 

实用显示游标会有以下4个步骤;

 

  • 声明游标初始化内存
  • 打开游标分配内存
  • Fetch cursor 获取记录
  • 关闭游标释放内存

 

  1. 声明游标
CURSOR c1 is select name, salary from customer;

    2，打开游标分配内存

OPEN c1;

    3，获取记录

FETCH c1 INTO c_name, c_salary;

　4，关闭游标

CLOSE c_1;

 

下面看一个简单使用显示游标的完整例子

 

例2;

 

set serveroutput on;
DECLARE 
   c_salary customer.salary%TYPE;
   c_name customer.name%TYPE;
   CURSOR c1 is
   select name,salary from customer;
BEGIN
  open c1;
  loop
  fetch c1 into c_name,c_salary;
  exit when c1%notfound ;
   dbms_output.put_line(c_name||' '||c_salary);
  end loop;
  close c1;
END;
/
View Code

 

结果；

jack 5000
rose 2500
Jet 7000
John 5000
Merry 3000
Peter 1500
Adi 2900
View Code

 

 

PL/SQL语言提供了游标FOR循环语句，自动执行游标的OPEN、FETCH、CLOSE语句和循环语句的功能；当进入循环时，游标FOR循环语句自动打开游标，并提取第一行游标数据，当程序处理完当前所提取的数据而进入下一次循环时，游标FOR循环语句自动提取下一行数据供程序处理，当提取完结果集合中的所有数据行后结束循环，并自动关闭游标。

 

  FOR index_variable IN cursor_name[(value[, value]…)] LOOP
    -- 游标数据处理代码
  END LOOP;

 

用for loop,可以完成上面例2同样的效果。

例3;

　　

set serveroutput on;
DECLARE 
   c_salary customer.salary%TYPE;
   c_name customer.name%TYPE;
   CURSOR c1 is
   select name,salary from customer;
BEGIN
 for f in c1
  loop
   dbms_output.put_line(f.name||' '||f.salary);
  end loop;
END;
/
View Code

 

带参数的游标

set serveroutput on;
DECLARE 
   c_salary customer.salary%TYPE;
   c_name customer.name%TYPE;
   CURSOR c1(p_salary decimal default 3000) is
   select name,salary from customer where salary >= p_salary;
BEGIN
  --open c1(p_salary=>5000);
  open c1(5000); 
  loop
  fetch c1 into c_name,c_salary;
  exit when c1%notfound ;
   dbms_output.put_line(c_name||' '||c_salary);
  end loop;
  close c1;
END;
View Code

 

使用游标更新和删除数据（Select for update使用）

游标修改和删除操作是指在游标定位下，修改或删除表中指定的数据行。这时，要求游标查询语句中必须使用FOR UPDATE选项，以便在打开游标时锁定游标结果集合在表中对应数据行的所有列和部分列。

 

为了对正在处理(查询)的行不被另外的用户改动，ORACLE 提供一个 FOR UPDATE 子句来对所选择的行进行锁住。该需求迫使ORACLE锁定游标结果集合的行，可以防止其他事务处理更新或删除相同的行，直到您的事务处理提交或回退为止。

 

在多表查询中，使用OF子句来锁定特定的表,如果忽略了OF子句，那么所有表中选择的数据行都将被锁定。如果这些数据行已经被其他会话锁定，那么正常情况下ORACLE将等待，直到数据行解锁。

语法：

 

SELECT column_list FROM table_list FOR UPDATE [OF column[, column]…] [NOWAIT]

 

    如果另一个会话已对活动集中的行加了锁，那么SELECT FOR UPDATE操作一直等待到其它的会话释放这些锁后才继续自己的操作，对于这种情况，当加上NOWAIT子句时，如果这些行真的被另一个会话锁定，则OPEN立即返回并给出：

ORA-0054 ：resource busy  and  acquire with nowait specified.

 

例4;

set serveroutput on;
DECLARE 
   c_salary customer.salary%TYPE;
   c_name customer.name%TYPE;
   CURSOR c1(p_salary customer.salary%TYPE)
   is
   select name,salary from customer where salary >= p_salary
   for update of salary;
BEGIN
  open c1(5000);
  loop
  fetch c1 into c_name,c_salary;  
  exit when c1%notfound ;
   dbms_output.put_line(c_name||' '||c_salary);
   update customer set salary=salary+100 where current of c1; --更新当前游标行对应的数据行
  end loop;
  close c1;
END;
/
View Code

 

 

这篇就先看到基础的东西，游标知识还是比较多,希望起到抛砖引玉的效果。　

这篇文档比较深入，供参考

http://www.codeproject.com/Articles/580628/OracleplusPL-fSQLplusCursor

posted @ 2014-09-10 11:57  chengdu.jack.li  阅读(1480)  评论(0)  编辑  收藏  举报