羊村懒王

这个屌丝很懒，什么也没留下！

热门标签

ORACLE 游标原理及使用讲解_cursor返回值类型

作者：羊村懒王 | 2024-05-24 22:45:08

踩

cursor返回值类型

1.什么是游标？

从表中检索出结果集，从中每次指向一条记录进行交互的机制。

用来管理从数据源返回的数据的属性（结果集）。这些属性包括并发管理、在结果集中的位置、返回的行数，以及是否能够在结果集中向前和/或向后移动（可滚动性）。

游标跟踪结果集中的位置，并允许对结果集逐行执行多个操作，在这个过程中可能返回至原始表，也可能不返回至原始表。

换句话说，游标从概念上讲基于数据库的表返回结果集。

游动的光标（指针），可以指向一个结果集，通过游标的移动逐行提取每一行的记录。

用来查询数据库，获取记录集合（结果集）的指针，可以让开发者一次访问一行结果集，在每条结果集上作操作。

用来存储多条查询数据的一种数据结构（结果集），它有一个指针，用来从上往下移动，从而达到遍历每条记录的作用。

由于它指示结果集中的当前位置，就像计算机屏幕上的光标指示当前位置一样，“游标”由此得名。

2.游标有什么作用？

①指定结果集中特定行的位置。

②基于当前的结果集位置检索一行或连续的几行。

③在结果集的当前位置修改行中的数据。

④对其他用户所做的数据更改定义不同的敏感性级别。

⑤可以以编程的方式访问数据库。

程序语言是面向记录的，一组变量一次只能存放一个变量或者一条记录，无法直接接收数据库中的查询结果集引入游标就解决了这个问题

3.为什么避免使用游标？

①在创建游标时，最需要考虑的事情是，“是否有办法避免使用游标？”

因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该改写；

如果使用了游标，就要尽量避免在游标循环中再进行表连接的操作。

4.Oracle游标的类型？

①静态游标：结果集已经确实（静态定义）的游标。在执行前，明确知道sql语句游标 ,分为隐式和显示游标。

显示游标
用户显示声明的游标，即指定结果集。当查询返回结果超过一行时，就需要一个显式游标。用户自己写的sql语句，编译时能明确知道sql语句
隐式游标
所有DML语句（增、删、改、查询单条记录）为隐式游标，该变量名不需要用户自己声明，它由系统帮我们定义，叫sql。使用时不需要声明隐式游标，它由系统定义 .
隐式游标的使用：通过%rowcour判断是有使用了。
② 动态游标 (REF游标)
在执行前不知道sql语句游标，执行时才知道sql语句的游标。动态关联结果集的临时对象。
强类型游标
规定返回类型
弱类型游标
不规定返回类型，可以获取任何结果集。

5.Oracle游标的状态有哪些，怎么使用游标属性？

如何判断是否到了结果集的尾部，oracle中，对于游标的属性，它通过属性值来判断的

①游标的状态是通过属性来表示。

%Found ：用于检验游标是否成功，通常在fetch语句前使用，当游标按照条件查询一条记录是，返回true,Fetch语句（获取记录）执行情况True or False。

%NotFound : 最后一条记录是否提取出True or False。到了游标尾部，没有记录了，就返回true

%ISOpen ：游标是否打开True or False。

%RowCount ：游标当前提取的行数 ,即获得影响的行数 1

②使用游标的属性。

例子：

Begin

Update emp Set SAL = SAL + 0.1 Where JOB = 'CLERK';

If SQL%Found Then

DBMS_OUTPUT.PUT_LINE('已经更新！');

Else

DBMS_OUTPUT.PUT_LINE('更新失败！');

End If；

End；

6.如何使用显示游标,？如何遍历循环游标？

①使用显示游标

一,

⑴声明游标：划分存储区域，注意此时并没有执行Select 语句。

CURSOR 游标名( 参数列表) [返回值类型] IS Select 语句；

⑵打开游标：执行Select 语句，获得结果集存储到游标中，此时游标指向结果集头，而不是第一条记录。

Open 游标名( 参数列表);

⑶获取记录：移动游标取一条记录

Fetch 游标名InTo 临时记录或属性类型变量；

⑷关闭游标：将游标放入缓冲池中，没有完全释放资源。可重新打开。

Close 游标名；

二,

1、声明游标：

cursor curname is select.......

2、打开游标:

open curname;

3、提取数据:循环提取数据：游标中的谓词 curname%found 、curname%notfound。

当结果集中的数据没有提取完时 %found的值true,否则是false.

%notfound与%found 相反。通过两个谓词可以控制循环

fetch curname into .......

4、关闭游标

close curname;

②遍历循环游标

⑴For 循环游标

循环游标隐式打开游标，自动滚动获取一条记录，并自动创建临时记录类型变量存储记录。处理完后自动关闭游标。

For 变量名 In 游标名

Loop

数据处理语句；

End Loop；

⑵Loop循环游标

。。。

Loop

Fatch 游标名InTo 临时记录或属性类型变量；

Exit When 游标名%NotFound；

End Loop；

。。。

例子1：

/* conn scott/tiger */

Declare

Cursor myCur is select empno,ename,sal from emp;

vna varchar2(10);

vno number(4);

vsal number(7,2);

Begin

open myCur;

fetch myCur into vno,vna,vsal;

dbms_output.put_line(vno||' '||vna||' '||vsal);

close myCur;

End;

例子2：使用loop遍历游标。

Declare

Cursor myCur is select ename,job,sal,empno from emp;

varE myCur%rowType;

Begin

if myCur%isopen = false then

open myCur;

dbms_output.put_line('Opening...');

end if;

loop

fetch myCur into varE;

exit when myCur%notfound;

dbms_output.put_line(myCur%rowCount||' '||vare.empno||' '||vare.ename||' '||vare.sal);

end loop;

if myCur%isopen then

Close myCur;

dbms_output.put_line('Closing...');

end if;

End;

例子3：使用For循环遍历游标,

/* conn scott/tiger */

Declare

Cursor myCur is select * from emp;

Begin

for varA in myCur

loop

dbms_output.put_line(myCur%rowCount||' '||varA.empno||' '||varA.ename||' '||varA.sal);

end loop;

End;

7.怎样更新和删除显示游标中的记录？

①UPDATE或DELETE语句中的WHERE CURRENT OF子串专门处理要执行UPDATE或DELETE操作的表中取出的最近的数据。

要使用这个方法，在声明游标时必须使用FOR UPDATE子串，当对话使用FOR UPDATE子串打开一个游标时，

所有返回集中的数据行都将处于行级（ROW-LEVEL)独占式锁定，其他对象只能查询这些数据行，

不能进行UPDATE、DELETE或SELECT...FOR UPDATE操作。

在多表查询中，使用OF子句来锁定特定的表,如果忽略了OF子句，那么所有表中选择的数据行都将被锁定。

如果这些数据行已经被其他会话锁定，那么正常情况下ORACLE将等待，直到数据行解锁。

②使用更新或删除：

⑴声明更新或删除显示游标：

Cursor 游标名IS SELECT 语句 For Update [ Of 更新列列名]；

Cursor 游标名IS SELECT 语句 For Delete [ Of 更新列列名]；

⑵使用显示游标当前记录来更新或删除：

Update 表名 SET 更新语句 Where Current Of 游标名；

Delete From 表名 Where Current Of 游标名；

例子1:更新显示游标记录

/*conn scott/tiger*/

Declare

Cursor myCur is select job from emp for update;

vjob empa.job%type;

rsal empa.sal%type;

Begin

open myCur;

loop

fetch myCur into vjob;

exit when myCur%notFound;

case (vjob)

when 'ANALYST' then rsal := 0.1;

when 'CLERK' then rsal := 0.2;

when 'MANAGER' then rsal := 0.3;

else

rsal := 0.5;

end case;

update emp set sal = sal + rsal where current of myCur;

end loop;

End;

例子2：删除显示游标记录

/*conn scott/tiger

Crate table empa Select * from scott.emp;

Declare

Cursor MyCursor Select JOB From empa For Update;

vSal emp.Sal%TYPE;

Begin

Loop

Fetch MyCursor InTo vSal;

Exit When MyCursor%NotFound;

If vSal < 800 Then

Delete From empa Where Cursor Of MyCursor;

End If;

End Loop;

End;/

8.什么是带参数的显示游标？

①与过程和函数相似，可以将参数传递给游标并在查询中使用。

参数只定义数据类型，没有大小(所有Oracle中的形参只定义数据类型，不指定大小)。

与过程不同的是，游标只能接受传递的值，而不能返回值。

　可以给参数设定一个缺省值，当没有参数值传递给游标时，就使用缺省值。

游标中定义的参数只是一个占位符，在别处引用该参数不一定可靠。

②使用带参数的显示游标

⑴声明带参数的显示游标：

CURSOR 游标名 [(parameter[,parameter],...)] IS Select语句；;

参数形式：1，参数名数据类型

2，参数名数据类型 DEFAULT 默认值

例子：

/*conn scott/tiger

Crate table empa Select * from scott.emp;

Declare

Cursor MyCursor(pSal Number Default 800) Select JOB From empa Where SAL > pSal ;

varA MyCursor%ROWTYPE;

Begin

Loop

Fetch MyCursor InTo varA;

Exit When MyCursor%NotFound;

DBMS_OUTPUT.PUT_LINE(MyCursor%RowCount||' '||varA.empno||' '||varA.ename||' '||varA.sal);

End Loop;

End;/

REF CURSOR

1，什么是REF游标？

动态关联结果集的临时对象。即在运行的时候动态决定执行查询。

2，REF 游标有什么作用？

实现在程序间传递结果集的功能，利用REF CURSOR也可以实现BULK SQL，从而提高SQL性能。

3，静态游标和REF 游标的区别是什么？

①静态游标是静态定义，REF 游标是动态关联；

②使用REF 游标需REF 游标变量。

③REF 游标能做为参数进行传递，而静态游标是不可能的。

4，什么是REF 游标变量？

REF游标变量是一种引用REF游标类型的变量，指向动态关联的结果集。

5，怎么使用 REF游标？

①声明REF 游标类型，确定REF 游标类型；

⑴强类型REF游标：指定retrun type，REF 游标变量的类型必须和return type一致。

语法：Type REF游标名 IS Ref Cursor Return 结果集返回记录类型；

⑵弱类型REF游标：不指定return type，能和任何类型的CURSOR变量匹配，用于获取任何结果集。

语法：Type REF游标名 IS Ref Cursor；

②声明Ref 游标类型变量；

语法：变量名已声明Ref 游标类型；

③打开REF游标，关联结果集；

语法：Open Ref 游标类型变量 For 查询语句返回结果集；

④获取记录，操作记录；

语法：Fatch REF游标名InTo 临时记录类型变量或属性类型变量列表；

⑤关闭游标，完全释放资源；

语法：Close REF游标名；

例子：强类型REF游标

/*conn scott/tiger*/

Declare

Type MyRefCurA IS REF CURSOR RETURN emp%RowType;

Type MyRefCurB IS REF CURSOR RETURN emp.ename%Type;

vRefCurA MyRefCurA;

vRefCurB MyRefCurB;

vTempA vRefCurA%RowType;

vTempB vRefCurB.ename%Type;

Begin

Open vRefCurA For Select * from emp Where SAL > 2000;

Loop

Fatch vRefCurA InTo vTempA;

Exit When vRefCurA%NotFound;

DBMS_OUTPUT.PUT_LINE(vRefCurA%RowCount||' '|| vTempA.eno||' '||vTempA.ename ||' '||vTempA.sal)

End Loop;

Close vRefCurA;

DBMS_OUTPUT.PUT_LINE('------------------------------------------------------------------------');

Open vRefCurB For Select ename from emp Where SAL > 2000;

Loop

Fatch vRefCurB InTo vTempB;

Exit When vRefCurB%NotFound;

DBMS_OUTPUT.PUT_LINE(vRefCurB%RowCount||' '||vTempB)

End Loop;

Close vRefCurB;

DBMS_OUTPUT.PUT_LINE('------------------------------------------------------------------------------------');

Open vRefCurA For Select * from emp Where JOB = 'CLERK';

Loop

Fatch vRefCurA InTo vTempA;

Exit When vRefCurA%NotFound;

DBMS_OUTPUT.PUT_LINE(vRefCurA%RowCount||' '|| vTempA.eno||' '||vTempA.ename ||' '||vTempA.sal)

End Loop;

Close vRefCurA;

End;

例子：弱类型REF游标

/*conn scott/tiger*/

Declare

Type MyRefCur IS Ref Cursor;

vRefCur MyRefCur;

vtemp vRefCur%RowType;

Begin

Case(&n)

When 1 Then Open vRefCur For Select * from emp;

When 2 Then Open vRefCur For Select * from dept;

Else

Open vRefCur For Select eno, ename from emp Where JOB = 'CLERK';

End Case;

Close vRefCur;

End;

原文地址:http://www.2cto.com/database/201203/122387.html

9.ORACLE中的游标汇总

游标（Cursor）：用来查询数据库，获取记录集合（结果集）的指针，可以让开发者一次访问一行结果集，在每条结果集上作操作。
游标可分为：
静态游标：分为显式游标和隐式游标。

REF游标：是一种引用类型，类似于指针。----也称为动态游标

1、静态游标

1.1显式游标

定义格式：

CURSOR 游标名 ( 参数 ) [返回值类型] IS Select 语句 ;

例子

open 游标;

loop fetch 游标 into 游标变量;

exit when 游标%notfound;

end loop;

close 游标;

cursor emp_cur ( p_deptid in number) is

set serveroutput on

declare

select * from employees where department_id = p_deptid;

l_emp employees%rowtype;

begin

dbms_output.put_line('Getting employees from department 30');

open emp_cur(30);

loop

fetch emp_cur into l_emp;

exit when emp_cur%notfound;

dbms_output.put_line('Employee id '|| l_emp.employee_id || ' is');

dbms_output.put_line(l_emp.first_name || ' ' || l_emp.last_name);

emp_cur;

end loop;

dbms_output.put_line('Getting employees from department 90');

open emp_cur(90);

loop

fetch emp_cur into l_emp;

exit when emp_cur%notfound;

dbms_output.put_line('Employee id '|| l_emp.employee_id || ' is');

dbms_output.put_line(l_emp.first_name || ' ' || l_emp.last_name);

end loop;

close emp_cur;

end;

1.2隐式游标

不用明确建立游标变量，分两种：

1．在PL/SQL中使用DML语言，使用ORACLE提供的名为“SQL”的隐示游标。

举例：

declare

begin

update departments set department_name=department_name where 1=2;

dbms_output.put_line('update '|| sql%rowcount ||' records');

end;

2．CURSOR FOR LOOP，用于for in loop 语句

for 游标变量 in 游标 loop end loop;用于静态游标中，不能用于动态游标,且不需要显示的打开、关闭、取数据、测试数据的存在、定义数据的变量等等。

举例：

游标FOR循环，简化游标操作my_dept_rec 不需要声明
declare
begin
for my_dept_rec in ( select department_name, department_id from epartments)
loop
dbms_output.put_line(my_dept_rec.department_id || ' : ' || my_dept_rec.department_name);
end loop;
end;
/

1.3静态游标常用属性：

显式游标属性：

%FOUND：变量最后从游标中获取记录的时候，在结果集中找到了记录。

%NOTFOUND：变量最后从游标中获取记录的时候，在结果集中没有找到记录。

%ROWCOUNT：当前时刻已经从游标中获取的记录数量。

%ISOPEN：是否打开。

隐式游标属性:

SQL%FOUND

SQL%NOTFOUND

在执行任何DML语句前SQL%FOUND和SQL%NOTFOUND的值都是NULL,在执行DML语句后，SQL%FOUND的属性值将是：
. TRUE :INSERT
. TRUE :DELETE和UPDATE，至少有一行被DELETE或UPDATE.
. TRUE :SELECT INTO至少返回一行

当SQL%FOUND为TRUE时,SQL%NOTFOUND为FALSE.

SQL%ROWCOUNT

在执行任何DML语句之前，SQL%ROWCOUNT的值都是NULL,对于SELECT INTO语句，如果执行成功，SQL%ROWCOUNT的值为1,如果没有成功，SQL%ROWCOUNT的值为0，同时产生一个异常NO_DATA_FOUND.

SQL%ISOPEN

SQL%ISOPEN是一个布尔值，如果游标打开，则为TRUE, 如果游标关闭，则为FALSE.对于隐式游标而言SQL%ISOPEN总是FALSE，这是因为隐式游标在DML语句执行时打开，结束时就立即关闭。

Declare

Cursor emps is

Select * from employees where rownum<6 order by 1;

emp employees%rowtype;

Row number :=1;

Begin

Open emps;

Fetch emps into emp;

Loop

If emps%found then

Dbms_output.put_line('Looping over record '||row|| ' of ' || emps%rowcount);

Fetch emps into emp;

Row := row + 1;

Else if emps%notfound then

Exit;

End if;

End loop;

If emps%isopen then

Close emps;

End if;

End;

显式和隐式游标的区别：

尽量使用隐式游标，避免编写附加的游标控制代码（声明，打开，获取，关闭），也不需要声明变量来保存从游标中获取的数据。

2、REF CURSOR游标

动态游标，在运行的时候才能确定游标使用的查询。可以分为：

强类型（限制）（Strong REF CURSOR），规定返回类型

弱类型（非限制）（Weak REF CURSOR），不规定返回类型，可以获取任何结果集。

定义格式：

TYPE ref_cursor_name IS REF CURSOR [RETURN return_type]

cursor ref_cursor_name

例如：

open cursor for select ......from (多个open for 同时用时，不需要有关闭游标的语句)

Declare

Type refcur_t is ref cursor;

Type emp_refcur_t is ref cursor return employee%rowtype;

Begin

Null;

End;

强类型举例：

declare

--声明记录类型

type emp_job_rec is record(

employee_id number,

employee_name varchar2(50),

job_title varchar2(30)

);

--声明REF CURSOR，返回值为该记录类型

type emp_job_refcur_type is ref cursor return emp_job_rec;

--定义REF CURSOR游标的变量

emp_refcur emp_job_refcur_tpe;

emp_job emp_job_rec;

begin

open emp_refcur for

select e.employee_id, e.first_name || ' ' ||e.last_name "employee_name",

j.job_title

from employees e, jobs j

where e.job_id = j.job_id and rownum < 11 order by 1;

fetch emp_refcur into emp_job;

while emp_refcur%found loop

dbms_output.put_line(emp_job.employee_name || '''s job is ');

dbms_output.put_line(emp_job.job_title);

fetch emp_refcur into emp_job;

end loop;

end;

指定了retrun 类型，CURSOR变量的类型必须和return 类型一致。

例子：

CREATE OR REPLACE PACKAGE emp_data AS

TYPE empcurtyp IS REF CURSOR RETURN emp%rowtype;

--定义Strong REF CURSOR

PROCEDURE open_emp_cv(emp_cv IN OUT empcurtyp, choice INT);

--根据不同的choice选择不同的CURSOR

PROCEDURE retrieve_data(choice INT);

--通过调用procedure open_emp_cv，返回指定的结果集。

END emp_data;

CREATE OR REPLACE PACKAGE BODY emp_data AS

PROCEDURE open_emp_cv(emp_cv IN OUT empcurtyp, choice INT) IS

--emp_cv作为传入/传出的CURSOR PARAMETER

BEGIN

IF choice = 1 THEN

OPEN emp_cv FOR SELECT * FROM emp WHERE empno < 7800;

ELSIF choice = 2 THEN

OPEN emp_cv FOR SELECT * FROM emp WHERE SAL < 1000;

ELSIF choice = 3 THEN

OPEN emp_cv FOR SELECT * FROM emp WHERE ename like 'J%';

END IF;

END;

PROCEDURE retrieve_data(choice INT) IS

return_cv empcurtyp;

--定义传入open_emp_cv的CURSOR变量

return_row emp%ROWTYPE;

invalid_choice EXCEPTION;

BEGIN

--调用 procedure OPEN_EMP_CV

open_emp_cv(return_cv, choice);
dt>

IF choice = 1 THEN

DBMS_OUTPUT.PUT_LINE('EMPLOYEES with empno less t han7800');

ELSIF choice = 2 THEN

DBMS_OUTPUT.PUT_LINE('EMPLOYEES with salary less than 1000');

ELSIF choice = 3 THEN

DBMS_OUTPUT.PUT_LINE('EMPLOYEES with name starts with ''J''');

ELSE

RAISE invalid_choice;

END IF;

LOOP

FETCH return_cv INTO return_row;

EXIT WHEN return_cv%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(return_row.empno || '--' || return_row.ename || '--' ||

return_row.sal);

END LOOP;

EXCEPTION

WHEN invalid_choice THEN

DBMS_OUTPUT.PUT_LINE('The CHOICE should be in one of (1,2,3)!');

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('Errors in procedure RETRIEVE_DATA!');

END;

END emp_data;

执行：

SQL> EXEC emp_data.retrieve_data(1);

SQL> EXEC emp_data.retrieve_data(2);

SQL> EXEC emp_data.retrieve_data(3);

SQL> EXEC emp_data.retrieve_data(34);

使用弱类型游标的例子

create or replace procedure open_cv(choice IN INT, return_cv OUT SYS_REFCURSOR) is

--参数return_cv为weak REF CURSOR,利用SYS_CURSOR来定义

begin

if choice = 1 then

open return_cv for 'select * from emp';

elsif choice = 2 then

open return_cv for 'select * from dept';

end if;

end open_cv;

CREATE or replace procedure retrieve_data(choice IN INT) is

emp_rec emp%rowtype;

dept_rec dept%rowtype;

return_cv SYS_REFCURSOR;

invalid_choice exception;

BEGIN

if choice=1 then

dbms_output.put_line('employee information');

open_cv(1,return_cv); --调用procedure open_cv;

loop

fetch return_cv into emp_rec;

exit when return_cv%notfound;

dbms_output.put_line(emp_rec.empno||'-'||emp_rec.ename||'-'||emp_rec.sal);

end loop;

elsif choice=2 then

dbm_output.put_line('department information');

open_cv(2,return_cv);

loop

fetch return_cv into dept_rec;

exit when return_cv%notfound;

dbms_output.put_line(dept_rec.deptno||'-'||dept_rec.dname||'-'||dept_rec.loc);

end loop;

else

raise invalid_choice;

end if;

exception

when invalid_choice then

dbms_output.put_line('The CHOICE should be one of 1 and 2!');

when others then

dbms_output.put_line('Errors in procedure retrieve_data');

END retrieve_data;

执行：

SQL> exec retrieve_data(1);

SQL> exec retrieve_data(2);

用REF CURSOR实现BULK功能

1. 可以加速INSERT, UPDATE, DELETE语句的执行，也就是用FORALL语句来替代循环语句。

2. 加速SELECT，用BULK COLLECT INTO 来替代INTO。

SQL> create table tab2 as select empno ID, ename NAME, sal SALARY from emp where 1=2;

create or replace procedure REF_BULK is

type empcurtyp is ref cursor;

type idlist is table of emp.empno%type;

type namelist is table of emp.ename%type;

type sallist is table of emp.sal%type;

emp_cv empcurtyp;

ids idlist;

names namelist;

sals sallist;

row_cnt number;

begin

open emp_cv for select empno, ename, sal from emp;

fetch emp_cv BULK COLLECT INTO ids, names, sals;

--将字段成批放入变量中，此时变量是一个集合

close emp_cv;

for i in ids.first .. ids.last loop

dbms_output.put_line(' || ids(i) || ' || names(i) ||' salary=' || sals(i));

end loop;

FORALL i IN ids.first .. ids.last

insert into tab2 values (ids(i), names(i), sals(i));

commit;

select count(*) into row_cnt from tab2;

dbms_output.put_line('-----------------------------------');

dbms_output.put_line('The row number of tab2 is ' || row_cnt);

end REF_BULK;

3、cursor 和 ref cursor的区别

从技术底层看，两者是相同的。普通plsql cursor在定义时是“静态”的。而

Ref cursors可以动态打开。

例如下面例子：

Declare

typerc is ref cursor;

cursor c is select * from dual;

l_cursor rc;

begin

if ( to_char(sysdate,'dd') = 30 ) then

open l_cursor for 'select * from emp';

elsif ( to_char(sysdate,'dd') = 29 ) then

open l_cursor for select * from dept;

else

open l_cursor for select * from dual;

end if;

open c;

end;

rc根据逻辑动态打开；而游标c定义好了只有就无法修改了。

ref cursor可以返回给客户端，cursor则不行。cursor可以是全局的global ，

ref cursor则必须定义在过程或函数中。

ref cursor可以在子程序间传递，cursor则不行。

cursor中定义的静态sql比ref cursor效率高，所以ref cursor通常用在：向客户端返回结果集。

参考资料:http://blog.sina.com.cn/s/blog_95cfa64601011v0z.html

10.其他教程

--案例：打印所有的员工的全部信息

declare

--声明游标：

cursor c_emp is select * from emp ;

--声明行类型变量

v_emp emp%rowtype;

begin

--打开游标

open c_emp;

--通过loop循环提起结果集

loop

--提取数据

fetch c_emp into v_emp;

exit when c_emp%notfound ;

dbms_output.put_line(v_emp.empno||' '||v_emp.ename

||' '||v_emp.job||' '||v_emp.mgr||' '

||v_emp.hiredate||' '||v_emp.sal||' '

||v_emp.comm||' '||v_emp.deptno );

end loop;

--关闭游标

close c_emp;

end;

--使用while循环改写上例

declare

--声明游标：

cursor c_emp is select * from emp ;

--声明行类型变量

v_emp emp%rowtype;

begin

--打开游标

open c_emp;

--通过loop循环提起结果集

--提取数据

fetch c_emp into v_emp;

while c_emp%found loop

dbms_output.put_line(v_emp.empno||' '||v_emp.ename

||' '||v_emp.job||' '||v_emp.mgr||' '

||v_emp.hiredate||' '||v_emp.sal||' '

||v_emp.comm||' '||v_emp.deptno );

--提取数据

fetch c_emp into v_emp;

end loop;

--关闭游标

close c_emp;

end;

--使用游标 for循环提取数据：游标自动打开，自动关闭

declare

--声明游标：

cursor c_emp is select * from emp ;

begin

for v_emp in c_emp loop

dbms_output.put_line(v_emp.empno||' '||v_emp.ename

||' '||v_emp.job||' '||v_emp.mgr||' '

||v_emp.hiredate||' '||v_emp.sal||' '

||v_emp.comm||' '||v_emp.deptno );

end loop;

exception

when cursor_already_open then

dbms_output.put_line('游标已经打开');

when invalid_cursor then

dbms_output.put_line('无效游标');

end;

--游标使用中的异常：

invalid_cursor:无效的游标

cursor_aleaday_open；游标已经打开在关闭前再次打开

--案例：

使用游标按照下列各式打印信息

部门号：20 总工资：30000 平均工资： 8000

____________________________________________________

———————————————————————————

编号姓名职务领导入职工资奖金

————————————————————————————

7369 aaa aaa 1 1987年-4月-5日 3000 500

————————————————————————————

编号姓名职务领导入职工资奖金

————————————————————————————

7369 aaa aaa 1 1987年-4月-5日 3000 500

————————————————————————————-

部门号：:30 总工资：300000 平均工资：20000

____________________________________________________

———————————————————————————

编号姓名职务领导入职工资奖金

————————————————————————————

7369 aaa aaa 1 1987年-4月-5日 3000 500

————————————————————————————

编号姓名职务领导入职工资奖金

————————————————————————————

7369 aaa aaa 1 1987年-4月-5日 3000 500

declare

--声明分组查询的游标

cursor c_sal_dept is select deptno,sum(sal),avg(sal)

from emp group by deptno;

v_deptno emp.deptno%type;

v_sumsal number(10,2);

v_avgsal number(8,2);

begin

open c_sal_dept;

loop

fetch c_sal_dept into v_deptno,v_sumsal,v_avgsal;

exit when c_sal_dept%notfound;

dbms_output.put_line('部门号：'||v_deptno||

' 总工资：'||v_sumsal||' 平均工资：'

||v_avgsal);

dbms_output.put_line('_______________________________________');

declare

cursor c_emp is select empno,ename,job,mgr,hiredate,sal,comm

from emp where deptno=v_deptno;

v_empno emp.empno%type;

v_ename emp.ename%type;

v_job emp.job%type;

v_mgr emp.mgr%type;

v_hiredate emp.hiredate%type;

v_sal emp.sal%type;

v_comm emp.comm%type;

begin

open c_emp;

fetch c_emp into v_empno,v_ename,v_job,v_mgr,v_hiredate,v_sal,

v_comm;

while c_emp%found loop

dbms_output.put_line(' 编号姓名职务领导入职工资奖金');

dbms_output.put_line('------------------------------------------------------');

dbms_output.put_line(v_empno||' '||v_ename||' '||v_job||' '||v_mgr

||' '||v_hiredate||' '||v_sal||' '||v_comm);

dbms_output.put_line('------------------------------------------------------');

fetch c_emp into v_empno,v_ename,v_job,v_mgr,v_hiredate,v_sal,

v_comm;

end loop;

close c_emp;

end;

end loop;

close c_sal_dept;

end;

ref游标：

1、定义游标类型：

type cursor_type is ref cursor;

2、声明游标变量：

c_emp cursor_type ;

3、游标变量的初始化：

open c_emp for select.................

4、关闭游标变量：

close c_emp;

--案例：通过游标变量打印所有的员工：

declare

--声明游标类型

type c_type is ref cursor;

--声明游标变量：

c_emp c_type;

v_emp emp%rowtype;

begin

--初始化游标变量

open c_emp for select * from emp;

loop

fetch c_emp into v_emp;

exit when c_emp%notfound;

dbms_output.put_line(v_emp.empno||' '||v_emp.ename);

end loop;

--关闭游标变量

close c_emp;

exception

when others then

dbms_output.put_line('出错啦');

end;

其他案例

-- 示例. 显式游标

DECLARE

name emp.name%type;

sal emp.sal%type; --定义2个变量来存放ename和sal的内容

CURSOR emp_cursor IS

SELECT name,sal FROM emp;

BEGIN

OPEN emp_cursor;

LOOP

FETCH emp_cursor INTO name,sal;

EXIT WHEN emp_cursor%NOTFOUND;

dbms_output.put_line('第'||emp_cursor%ROWCOUNT||'个雇员：'||name|| sal);

END LOOP;

CLOSE emp_cursor;

END;

-- 示例. 带参数显式游标

DECLARE

CURSOR emp_cursor(no NUMBER) IS

select name,sal FROM emp WHERE deptno=no;

emp_record emp_cursor%ROWTYPE;

v_sal emp.sal %TYPE;

BEGIN

IF NOT emp_cursor%ISOPEN THEN

OPEN emp_cursor(10);

END IF;

LOOP

FETCH emp_cursor INTO emp_record;

EXIT WHEN emp_cursor%NOTFOUND;

dbms_output.put_line(emp_record.ename||':'||emp_record.sal);

END LOOP;

CLOSE emp_cursor;

END;

--示例. 使用显式游标更新或删除

--单表

DECLARE

CURSOR emp_cursor IS

select name,sal,deptno FROM emp FOR UPDATE;

emp_record emp_cursor%ROWTYPE;

BEGIN

IF NOT emp_cursor%ISOPEN THEN

OPEN emp_cursor;

END IF;

LOOP

FETCH emp_cursor INTO emp_record;

EXIT WHEN emp_cursor%NOTFOUND;

if emp_record.deptno=30 AND emp_record.sal>2500 THEN

DELETE FROM emp WHERE CURRENT OF emp_cursor;

END IF;

END LOOP;

CLOSE emp_cursor;

END;

--多表

DECLARE

CURSOR emp_cursor IS

select name,sal,emp.deptno,name

FROM emp,dept WHERE emp.deptno=dept.deptno

FOR UPDATE of emp.sal; --注意：多表时FOR UPDATE OF 后面明确的某个列的作用是，这个列是属于哪个表的则后面通过游标更新或修改的是哪个表。例如，该例中明确的是emp表的sal列，说明下列语句中通过游标更新或修改的表为emp表，如果下列语句中更新的是dept表，则报错。

emp_record emp_cursor%ROWTYPE;

BEGIN

IF NOT emp_cursor%ISOPEN THEN

OPEN emp_cursor;

END IF;

LOOP

FETCH emp_cursor INTO emp_record;

EXIT WHEN emp_cursor%NOTFOUND;

if emp_record.deptno=30 THEN

UPDATE emp SET sal=sal+100 WHERE CURRENT OF emp_cursor;

END IF;

dbms_output.put_line(emp_record.sal);

END LOOP;

CLOSE emp_cursor;

END;

DECLARE

CURSOR emp_cursor IS

select name,sal,emp.deptno,name

FROM emp,dept WHERE emp.deptno=dept.deptno

FOR UPDATE of dept.dname; --该例中明确的是dept表的dname列，说明下列语句中通过游标更新或修改的表为dept表，如果下列语句中更新的是emp表，则报错。

emp_record emp_cursor%ROWTYPE;

BEGIN

IF NOT emp_cursor%ISOPEN THEN

OPEN emp_cursor;

END IF;

LOOP

FETCH emp_cursor INTO emp_record;

EXIT WHEN emp_cursor%NOTFOUND;

if emp_record.deptno=30 THEN

UPDATE emp SET sal=sal+100 WHERE CURRENT OF emp_cursor;

END IF;

dbms_output.put_line(emp_record.sal);

END LOOP;

CLOSE emp_cursor;

END;

-- 示例. 循环游标

--显示雇员表中所有雇员的姓名和薪水

DECLARE

CURSOR emp_cursor IS

select name,sal FROM emp;

BEGIN

FOR emp_record IN emp_cursor LOOP

dbms_output.put_line('第'||emp_cursor%ROWCOUNT||'个雇员：'||emp_record.name|| emp_record.sal);

END LOOP;

END;

示例. REF游标

--显示雇员表中20号部门的雇员信息和部门表中部分部门信息

DECLARE

--定义一个游标数据类型

TYPE emp_cursor_type IS REF CURSOR;

--声明一个游标变量

c1 EMP_CURSOR_TYPE;

--声明两个记录变量

v_emp_record emp%ROWTYPE;

v_dept_record dept%ROWTYPE;

BEGIN

OPEN c1 FOR SELECT * FROM emp WHERE deptno= 20;

LOOP

FETCH c1 INTO v_emp_record;

EXIT WHEN c1%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(v_emp_record.name||'的工资是'||v_emp_record.sal);

END LOOP;

--end;

--将同一个游标变量对应到另一个SELECT语句

OPEN c1 FOR SELECT * FROM dept WHERE deptno IN(10,20);

LOOP

FETCH c1 INTO v_dept_record;

EXIT WHEN c1%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(v_dept_record.deptno||'表示'

||v_dept_record.deptname);

END LOOP;

CLOSE c1;

END;

Oracle %rowtype与%type

%rowtype

表示该类型为行数据类型，存储的是一行数据，一行数据里可以有多列，类似于表里的一行数据，也可以是游标里的一行数据，

如：vs_row1 表%rowtype;

vs_row2 游标%rowtype;

%type

例如

vempno emp.empno%type;

例如上面的这句话，你的vempno就是你定义的变量，和面的那个emp是你数据库里面存在的表，他的表里面有意个empno字段，然后%type就是empno的数据类型，总体说这句话就是，让vempno与你数据库里面的表字段的属性匹配，这个你就可以很方便的对emp进行操作了，不会因为类型不匹配而报莫名的错误。

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/羊村懒王/article/detail/619390

ORACLE 游标 原理及使用讲解_cursor返回值类型

1.什么是游标？

2.游标有什么作用？

3.为什么避免使用游标？

4.Oracle游标的类型？

5.Oracle游标的状态有哪些，怎么使用游标属性？

6.如何使用显示游标,？如何遍历循环游标？

7.怎样更新和删除显示游标中的记录？

8.什么是带参数的显示游标？

9.ORACLE中的游标汇总

10.其他教程

Oracle %rowtype与%type

ORACLE 游标原理及使用讲解_cursor返回值类型