oracle数据库游标

‘壹’ oracle 游标是做什么用的

游标（CURSOR）也叫光标，在关系数据库中经常使用，在PL/sql程序中可以用CURSOR与SELECT一起对表或者视图中的数据进行查询并逐行读取。

Oracle游标分为显示游标和隐式游标。
显示游标（Explicit Cursor）:在PL/SQL程序中定义的、用于查询的游标称作显示游标。
隐式游标（Implicit Cursor）:是指非PL/SQL程序中定义的、而且是在PL/SQL中使用UPDATE/DELETE语句时，Oracle系统自动分配的游标。
一.显示游标
1.使用步骤
(1)定义 (2)打开 (3)使用 (4)关闭
2.使用演示
首先创建测试用表STUDENT，脚本如下：

CREATE TABLE "STUDENT" (
"STUNAME" VARCHAR2(10 BYTE),
"STUNO" VARCHAR2(4 BYTE),
"AGE" NUMBER,
"GENDER" VARCHAR2(2 CHAR)
)

(1).使用WHILE循环处理游标
create or replace PROCEDURE PROC_STU1 AS
BEGIN
--显示游标使用，使用while循环
declare
--1.定义游标，名称为cur_stu
cursor cur_stu is
select stuno,stuname from student order by stuno;
--定义变量，存放游标取出的数据
v_stuno varchar(4);
v_stuname varchar(20);
begin
--2.打开游标cur_stu
open cur_stu;
--3.将游标的当前行取出存放到变量中
fetch cur_stu into v_stuno,v_stuname;
while cur_stu%found --游标所指还有数据行，则继续循环
loop
--打印结果
dbms_output.PUT_LINE(v_stuno||'->'||v_stuname);
--继续将游标所指的当前行取出放到变量中
fetch cur_stu into v_stuno,v_stuname;
end loop;
close cur_stu; --4.关闭游标
end;
END PROC_STU1;
(2).使用IF..ELSE代替WHILE循环处理游标
create or replace PROCEDURE PROC_STU2 AS
BEGIN
--显示游标使用，使用if判断
declare
--1.定义游标，名称为cur_stu
cursor cur_stu is
select stuno,stuname from student order by stuno;
--定义变量，存放游标取出的数据
v_stuno varchar(4);
v_stuname varchar(20);
begin
--2.打开游标cur_stu
open cur_stu;
--3.将游标的当前行取出存放到变量中
fetch cur_stu into v_stuno,v_stuname;
loop
if cur_stu%found then --如果游标cur_stu所指还有数据行
--打印结果
dbms_output.PUT_LINE(v_stuno||'->'||v_stuname);
--继续将游标所指的当前行取出放到变量中
fetch cur_stu into v_stuno,v_stuname;
else
exit;
end if;
end loop;
close cur_stu; --4.关闭游标
end;
END PROC_STU2;
(3).使用FOR循环处理游标
create or replace PROCEDURE PROC_STU3 AS
BEGIN
--显示游标使用，使用for循环
declare
--定义游标，名称为cur_stu
cursor cur_stu is
select stuno,stuname from student order by stuno;
begin
for stu in cur_stu
loop
dbms_output.PUT_LINE(stu.stuno||'->'||stu.stuname);
--循环做隐含检查 %notfound
end loop;
--自动关闭游标
end;
END PROC_STU3;
(4).常用的使用EXIT WHEN处理游标
create or replace
PROCEDURE PROC_STU1_1 AS
BEGIN
--显示游标使用，使用exit when循环
declare
--1.定义游标，名称为cur_stu
cursor cur_stu is
select stuno,stuname from student order by stuno;
--定义变量，存放游标取出的数据
v_stuno varchar(4);
v_stuname varchar(20);
begin
--2.打开游标cur_stu
open cur_stu;
loop
--3.将游标的当前行取出存放到变量中
fetch cur_stu into v_stuno,v_stuname;
exit when cur_stu%notfound; --游标所指还有数据行，则继续循环
--打印结果
dbms_output.PUT_LINE(v_stuno||'->'||v_stuname);
end loop;
close cur_stu; --4.关闭游标
end;
END PROC_STU1_1;
二.隐式游标
1.使用演示
create or replace PROCEDURE PROC_STU4 AS
BEGIN
--隐式游标使用
update student set stuname='张燕广' where stuno='1104';
--如果更新没有匹配则插入一条新记录
if SQL%NOTFOUND then
insert into student(STUNO,STUNAME,AGE,GENDER)
values('1104','张燕广',18,'男');
end if;
END PROC_STU4;
2.说明
所有的SQL语句在上下文区内部都是可执行的，因为都有一个游标指向上下文区，此游标就是
SQL游标，与现实游标不同的是，SQL游标在PL/SQL中不需要打开和关闭，而是在执行UPDATE、
DELETE是自动打开和关闭。
上面例子中就是通过SQL%NOTFOUND游标属性判断UPDATE语句的执行结果决定是否需要插入新记录。

‘贰’ 数据库里面静态游标包含哪两种类型

游标是SQL的一个内存工作区，由系统或用户以变量的形式定义。游标的作用就是用于临时存储从数据库中提取的数据块。Oracle数据库的Cursor类型包含三种: 静态游标：分为显式（explicit）游标和隐式（implicit）游标；REF游标：是一种引用类型，类似于指针。下面我们一一介绍它们的使用。

1.隐式游标

1)Select …INTO…语句，DML语句，使用隐式Cursor。此外，还有一种使用FOR LOOP的Implicit Cursor用法。

2)可以通过隐式Cusor的属性来了解操作的状态和结果。Cursor的属性包含：

SQL%ROWCOUNT 整型代表DML语句成功执行的数据行数。

SQL%FOUND 布尔型值为TRUE代表插入、删除、更新或单行查询操作成功。

SQL%NOTFOUND 布尔型与SQL%FOUND属性返回值相反。

SQL%ISOPEN 布尔型DML执行过程中为真，结束后为假。

3) 隐式Cursor由系统自动打开和关闭.

例如：

• setserveroutputon

• declare

• begin

• updateemployeessetemployee_name='Mike'whereemployee_id=1001;

• ifSQL%FOUNDthen

• dbms_output.put_line('Nameisupdated');

• else

• dbms_output.put_line('Nameisnotupdated');

• endif;

• end;

• /

• setserveroutputon

• declare

• begin

• fortableInfoin(select*fromuser_tables)loop

• dbms_output.put_line(tableInfo.table_name);

• endloop;

• exception

• whenothersthen

• dbms_output.put_line(sqlerrm);

• end;

• /

2.显式游标

1) 显式Cursor的属性包含：

游标的属性 返回值类型 意义

%ROWCOUNT 整型 获得FETCH语句返回的数据行数

%FOUND 布尔型 最近的FETCH语句返回一行数据则为真，否则为假

%NOTFOUND 布尔型 与%FOUND属性返回值相反

%ISOPEN 布尔型 游标已经打开时值为真，否则为假

2) 对于显式游标的运用分为四个步骤：

a 定义游标---Cursor [Cursor Name] IS;

b 打开游标---Open [Cursor Name];

c 操作数据---Fetch [Cursor name]

d 关闭游标---Close [Cursor Name]

以下是几种常见显式Cursor用法。




• <p>setserveroutputon

• declare

• cursorcurisselect*fromuser_tables;

• tableInfouser_tables%rowtype;

• begin

• opencur;

• loop

• fetchcurintotableInfo;

• exitwhencur%notfound;

• dbms_output.put_line(tableInfo.table_name);

• endloop;</p><p>exception

• whenothersthen

• dbms_output.put_line(sqlerrm);</p><p>closecur;

• end;

• /</p>

• setserveroutputon

• declare

• cursorcurisselect*fromuser_tables;

• begin

• fortableInfoincurloop

• dbms_output.put_line(tableInfo.table_name);

• endloop;

• exception

• whenothersthen

• dbms_output.put_line(sqlerrm);

• end;

• /

还可以使用带参数open的cursor。




• <p>setserveroutputon

• declare

• cursorcur(tblNamevarchar2)isselect*fromuser_constraintswheretable_name=tblName;

• tableInfouser_constraints%rowtype;

• begin

• opencur('EMPLOYEES');

• loop

• fetchcurintotableInfo;

• exitwhencur%notfound;

• dbms_output.put_line(tableInfo.constraint_name);

• endloop;</p><p>exception

• whenothersthen

• dbms_output.put_line(sqlerrm);</p><p>closecur;

• end;

• /</p><p></p>

• setserveroutputon

• declare

• cursorcur(tblNamevarchar2)isselect*fromuser_constraintswheretable_name=tblName;

• begin

• fortableInfoincur('EMPLOYEES')loop

• dbms_output.put_line(tableInfo.constraint_name);

• endloop;

• exception

• whenothersthen

• dbms_output.put_line(sqlerrm);

• end

• /

可以使用WHERE CURRENT OF子句执行UPDATE或DELETE操作。




• setserveroutputon

• declare

• cursorcurisselect*fromemployeesforupdate;

• begin

• fortableInfoincurloop

• =salary*1.1wherecurrentofcur;

• endloop;

• commit;

• exception

• whenothersthen

• dbms_output.put_line(sqlerrm);

• end;

• /

3.REF CURSOR（Cursor Variables）

REF Cursor在运行的时候才能确定游标使用的查询。利用REF CURSOR，可以在程序间传递结果集（一个程序里打开游标变量，在另外的程序里处理数据）。

也可以利用REF CURSOR实现BULK SQL，提高SQL性能。

REF CURSOR分两种，Strong REF CURSOR 和 Weak REF CURSOR。

Strong REF CURSOR:指定retrun type，CURSOR变量的类型必须和return type一致。

Weak REF CURSOR:不指定return type，能和任何类型的CURSOR变量匹配。

Ref cursor的使用:

1) Type [Cursor type name] is ref cursor

2) Open cursor for...

3) Fetch [Cursor name]

4) Close Cursor

例如：

Step1:




• createorreplacepackageTESTas

• typeemployees_refcursor_%rowtype;

• procereemployees_loop(employees_curINemployees_refcursor_type);

• endTEST;

• /

Step2:




• procereemployees_loop(employees_curINemployees_refcursor_type)is

• empemployees%rowtype;

• begin

• loop

• fetchemployees_curintoemp;

• exitwhenemployees_cur%NOTFOUND;

• dbms_output.put_line(emp.employee_id);

• endloop;

• endemployees_loop;

• endTEST;

• /

Step3:




• setserveroutputon

• declare

• empRefCurTEST.employees_refcursor_type;

• begin

• foriin10..20loop

• dbms_output.put_line('DepartmentID='||i);

• openempRefCurforselect*fromemployeeswheredepartment_id=i;

• TEST.employees_loop(empRefCur);

• endloop;

• exception

• whenothersthen

• dbms_output.put_line(sqlerrm);

• closeempRefCur;

• end;

• /

4.BULK SQL

使用FORALL和BULK COLLECT子句。利用BULK SQL可以减少PLSQL Engine和SQL Engine之间的通信开销，提高性能。

1. To speed up INSERT, UPDATE, and DELETE statements, enclose the SQL statement within a PL/SQL FORALL statement instead of a loop construct. 加速INSERT, UPDATE, DELETE语句的执行，也就是用FORALL语句来替代循环语句。

2. To speed up SELECT statements, include the BULK COLLECT INTO clause in the SELECT statement instead of using INTO. 加速SELECT，用BULK COLLECT INTO 来替代INTO。




• SQL>createtableemployees_tmpasselectfirst_name,last_name,salaryfromemployeeswhere0=1;

• setserveroutputon

• declare

• cursoremployees_cur(depIdemployees.department_id%type)isselectfirst_name,last_name,_id=depId;

• typeemployee_table_typeistableofemployees_cur%rowtypeindexbypls_integer;

• employee_tableemployee_table_type;

• begin

• openemployees_cur(100);

• fetchemployees_curbulkcollectintoemployee_table;

• closeemployees_cur;

• foriin1..employee_table.countloop

• dbms_output.put_line(employee_table(i).first_name||''||employee_table(i).last_name||','||employee_table(i).salary);

• endloop;

• foralliinemployee_table.first..employee_table.last

• insertintoemployees_tmpvalues(employee_table(i).first_name,employee_table(i).last_name,employee_table(i).salary);

• commit;

• end;

• /

‘叁’ oracle存储过程游标有什么用

1，什么是游标？
①从表中检索出结果集，从中每次指向一条记录进行交互的机制。

②关系数据库中的操作是在完整的行集合上执行的。
由SELECT 语句返回的行集合包括满足该语句的WHERE 子句所列条件的所有行。由该语句返回完整的行集合叫做结果集。
应用程序，尤其是互动和在线应用程序，把完整的结果集作为一个单元处理并不总是有效的。
这些应用程序需要一种机制来一次处理一行或连续的几行。而游标是对提供这一机制的结果集的扩展。

游标是通过游标库来实现的。游标库是常常作为数据库系统或数据访问API 的一部分而得以实现的软件，
用来管理从数据源返回的数据的属性（结果集）。这些属性包括并发管理、在结果集中的位置、返回的行数，
以及是否能够在结果集中向前和/或向后移动（可滚动性）。

游标跟踪结果集中的位置，并允许对结果集逐行执行多个操作，在这个过程中可能返回至原始表，也可能不返回至原始表。
换句话说，游标从概念上讲基于数据库的表返回结果集。
由于它指示结果集中的当前位置 ，就像计算机屏幕上的光标指示当前位置一样，“游标”由此得名。