oracle游标（一）

浏览数：23 / 时间：2015年06月12日

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 获取记录
关闭游标释放内存

声明游标

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;