oracle中游标与数据,Oracle PL/SQL中游标和游标变量的使用

游标是什么:

游标字面理解就是游动的光标。

用数据库语言来描述:游标是映射在结果集中一行数据上的位置实体,有了游标用户就可以访问结果集中的任意一行数据了,将游标放置到某行后,即可对该行数据进行操作,例如提取当前行的数据等等。

在Oracle9i之前,使用FETCH语句每次只能提取一行数据;从Oracle9i开始,通过使用FETCH…BULK COLLECT INTO语句,每次可以提取多行数据。语法如下:

(1) FETCH cursor_name INTO variable1,variable2,…;

此方法必须要使用循环语句处理结果集的所有数据。

(2) FETCH cursor_name BULK COLLECT INTO collect1,collect2,…[LIMIT rows]

[LIMIT rows]可用来限制每次游标每次提取的行数。

游标的分类: 显式游标和隐式游标

(1)显示游标的使用:

1.声明游标

CURSOR mycur(vartype number) is

select emp_no,emp_zc from cus_emp_basic

where com_no = vartype;

2.打开游标

open mycur(000627) 注:000627:参数

3.读取数据

fetch mycur into varno,varprice;

4.关闭游标

close mycur;

游标的属性

oracle 游标有4个属性: %ISOPEN , %FOUND , %NOTFOUND, %ROWCOUNT

%ISOPEN 判断游标是否被打开,如果打开%ISOPEN 等于true,否则等于false

%FOUND %NOTFOUND 判断游标所在的行是否有效,如果有效,则%FOUNDD等于true,否则等于false

%ROWCOUNT 返回当前位置为止游标读取的记录行数。

示例:

set serveroutput on;

declare

varno varchar2(20);

varprice varchar2(20);

CURSOR mycur(vartype number) is

select emp_no,emp_zc from cus_emp_basic

where com_no = vartype;

begin

if mycur%isopen = false then

open mycur(000627);

end if;

fetch mycur into varno,varprice;

while mycur%found

loop

dbms_output.put_line(varno||','||varprice);

if mycur%rowcount=2 then

exit;

end if;

fetch mycur into varno,varprice;

end loop;

close mycur;

end;

(2)基于游标定义记录变量

使用%ROWTYPE属性不紧可以基于表和视图定义记录变量,也可以基于游标定义记录变量。当基于游标定义记录变量时,记录成员名实际就是SELECT语句的列名和列别名。为了简化显示游标的数据处理,建議开发人员使用记录变量存放游标数据。

For example:

DECLARE

CURSOR emp_cursor IS SELECT ename,sal FROM emp;

emp_reocrd emp_cursor%ROWTYPE;

BEGIN

OPEN emp_cursor;

LOOP

FETCH emp_cursor INTO emp_record;

EXIT WHEN emp_record%NOTFOUND;

dbms_ouput.put_line(‘雇员名:’||emp_record.ename||’,雇员工资:’||emp_record.sal);

END LOOP;

CLOSE emp_cursor;

END;

(3)隐式游标的使用:

如果在pl/sql程序中使用了select语句进行操作,pl/sql会隐含处理游标定义,即称做隐式游标。这种游标不需要声明、打开和关闭。

例:

Create or replace procedure cx_xm

(in_xh in char,out_num out char)

As

Begin

Select xm into out_xm from xs where xh=in_xh;   /*隐式游标必须使用into*/

Dbms_output.put_line(out_xm);

End

使用隐式游标时要注意以下几点:

A、每一个隐式游标必须有一个into;

B、和显示游标一样,带有关键字into接收数据的变量时数据类型要与列表一致。

C、隐式游标一次只能返回移行数据。

典型游标for 循环

游标for循环和显示游标的一种快捷使用方式,它使用for循环依次读取结果集中的行数据,当for循环开始时,游标自动打开(不需要open),每循环一次系统自动读取游标当前行的数据(不需要fetch),当退出for循环时,游标被自动关闭(不需要使用close)使用游标for循环的时候不能使用open语句,fetch语句和close语句,否则会产生错误。

set serveroutput on;

declare

cursor mycur(vartype number)is

select emp_no,emp_zc from cus_emp_basic

where com_no=vartype;

begin

for person in mycur(000627) loop

dbms_output.put_line('雇员编号:'||person.emp_no||',地址:'||person.emp_zc);

end loop;

end;

在游标FOR循环中使用查询

在游标FOR循环中可以定义查询,由于没有显式声明所以游标没有名字,记录名通过游标查询来定义。

DECALRE

v_tot_salary EMP.SALARY%TYPE;

BEGIN

FOR r_dept IN (SELECT deptno,dname FROM dept ORDER BY deptno) LOOP

DBMS_OUTPUT.PUT_LINE('Department:'|| r_dept.deptno||'-'||r_dept.dname);

v_tot_salary:=0;

FOR r_emp IN (SELECT ename,salary

FROM emp

WHERE deptno=p_dept

ORDER BY ename) LOOP

DBMS_OUTPUT.PUT_LINE('Name:'|| v_ename||' salary:'||v_salary);

v_tot_salary:=v_tot_salary+v_salary;

END LOOP;

DBMS_OUTPUT.PUT_LINE('Toltal Salary for dept:'|| v_tot_salary);

END LOOP;

END;

游标中的子查询

语法如下:

CURSOR C1 IS SELECT * FROM emp

WHERE deptno NOT IN (SELECT deptno

FROM dept

WHERE dname!='ACCOUNTING');

可以看出与SQL中的子查询没有什么区别。

游标中的更新和删除

在PL/SQL中依然可以使用UPDATE和DELETE语句更新或删除数据行。显式游标只有在需要获得多行数据的情况下使用。PL/SQL提供了仅仅使用游标就可以执行删除或更新记录的方法。

UPDATE或DELETE语句中的WHERE CURRENT OF子串专门处理要执行UPDATE或DELETE操作的表中取出的最近的数据。要使用这个方法,在声明游标时必须使用FOR UPDATE子串,当对话使用FOR UPDATE子串打开一个游标时,所有返回集中的数据行都将处于行级(ROW-LEVEL)独占式锁定,其他对象只能查询这些数据行,不能进行UPDATE、DELETE或SELECT...FOR UPDATE操作。

语法:

FOR UPDATE [OF [schema.]table.column[,[schema.]table.column]..

[nowait]

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

在UPDATE和DELETE中使用WHERE CURRENT OF子串的语法如下:

WHERE{CURRENT OF cursor_name|search_condition}

例:

DELCARE

CURSOR c1 IS SELECT empno,salary

FROM emp

WHERE comm IS NULL

FOR UPDATE OF comm;

v_comm NUMBER(10,2);

BEGIN

FOR r1 IN c1 LOOP

IF r1.salary<500 THEN

v_comm:=r1.salary*0.25;

ELSEIF r1.salary<1000 THEN

v_comm:=r1.salary*0.20;

ELSEIF r1.salary<3000 THEN

v_comm:=r1.salary*0.15;

ELSE

v_comm:=r1.salary*0.12;

END IF;

UPDATE emp;

SET comm=v_comm

WHERE CURRENT OF c1l;

END LOOP;

END

游标变量(REF CURSOR游标):

l          强类型(限制)(Strong REF CURSOR),规定返回类型

l          弱类型(非限制)(Weak REF CURSOR),不规定返回类型,可以获取任何结果集。

与游标类似,游标变量指向多行查询的结果集的当前行。但是,游标与游标变量是不同的,就像常量和变量的关系一样。游标是静态的,游标变量是动态的,因为它不与特定的查询绑定在一起。

(1)、用法1:

declare

type cur_tab is ref cursor;

sqlcur cur_tab;

v_T3100101 char(2);

v_T3100102 char(10);

begin

open sqlcur for select T3100101,T3100102 from T31001;

loop

fetch sqlcur into v_T3100101,v_T3100102;

exit when sqlcur%notfound;

dbms_output.put_line(v_T3100101||v_T3100102);

end loop;

close sqlcur;

end;

(2)、用法2:用于存储过程返回结果集

create or replace package selecttable

is

type cur_T31001 is ref cursor return T31001%rowtype;  --注意,这里没有begin

end selecttable;

create or replace procedure T31001_select

(

cur out selecttable.cur_T31001       --参数类型定义为先前定义好的T31001

)

is

begin

open cur for

select * from T31001;

end T31001_select;

----------------------------------------------------------------------------------------------------------------------

通过从游标工作区中抽取出来的数据,可以对数据库中的数据进行操纵,包括修改与删除操作。

要想通过游标操纵数据库,在定义游标的时候,必须加上FOR UPDATE OF子句;

而且在UPDATE或DELETE时,必须加上WHERE CURRENT OF子句,则游标所在行被更新或者删除。

一个FOR UPDATE子句将使所在行获得一个行级排他锁。

cursor 和 ref cursor的区别

从技术底层看,两者是相同的。普通plsql cursor在定义时是“静态”的。而Ref cursors可以动态打开。

例如下面例子:

Declare

type rc 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;

/

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

l          ref cursor可以返回给客户端,cursor则不行。

l          cursor可以是全局的global ,ref cursor则必须定义在过程或函数中。

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

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

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值