oracle sql 能在声明中只创建游标_右面再赋值吗,Oracle数据库--实用操作(4)游标和函数、过程...

游标简介

原理：逐行处理查询结果，以编程的方式访问数据

应用场景：在代码中需要接收多行查询出来的记录，借助游标遍历结果集。常与过程一起使用。

游标的类型：

一、隐式游标

在PL/SQL中使用DML语句时自动创建隐式游标,即：所有的SQL 语句在上下文区内部都是可执行的，因此都有一个游标指向上下文区，此游标就是所谓的SQL游标(SQL cursor)，与显式游标不同，SQL游标不被程序打开和关闭，通过检查隐式游标的属性可以获得最近执行的DML 语句的信息

隐式游标的属性有：SQL+属性

1.       %FOUND – SQL 语句影响了一行或多行时为 TRUE

2.       %NOTFOUND – SQL 语句没有影响任何行时为TRUE

3.       %ROWCOUNT – SQL 语句影响的行数

4.       %ISOPEN  - 游标是否打开，始终为FALSE

SQL> SETSERVEROUTPUT ON

SQL> BEGIN

UPDATE toys SET toyprice=270

WHERE toyid= 'P005';

IF SQL%FOUND THEN

DBMS_OUTPUT.PUT_LINE(‘表已更新');

END IF;

END;       --只有在DML 语句影响一行或多行时，才返回True

隐式游标用的很少，而且Oracle数据库自动创建。

二、显式游标

显式游标在PL/SQL 块的声明部分定义查询，该查询可以返回多行

显式游标的使用：

如果要使用创建好的游标，需要先打开游标，语法结构如下：

open 游标名;

打开游标的过程有以下两个步骤：

(1)将符合条件的记录送入内存。

(2)将指针指向第一条记录。

游标打开了要关闭：CLOSE 游标名；

游标提取数据的操作(每次只能提取一行)

如果要提取游标中的数据，需要使用fetch命令，语法形式如下。

fetch 游标名 into 变量名1, 变量名2,……;

或fetch 游标名into 记录型变量名;

示例代码

set serveroutput on

declare   tempsalscott.emp.sal%type;

cursormycursor is

select* from scott.emp

wheresal>tempsal;

cursorrecordmycursor%rowtype;

begin       tempsal:=800;

openmycursor;

fetchmycursor into cursorrecord;   --抓取每行数据into到变量cursorrecord

dbms_output.put_line(cursorrecord.deptno);

end;

带参数的显式游标

SQL> SET SERVEROUTPUT ON

SQL> DECLARE

desig    VARCHAR2(20);

emp_codeVARCHAR2(5);

empnm    VARCHAR2(20);

CURSORemp_cur(desigparam VARCHAR2) IS  --参数desigparam

SELECT empno, ename FROM employee

WHERE designation=desigparam;

BEGIN

desig:='&desig';

OPENemp_cur(desig);    --open时，带参数

LOOP

FETCHemp_cur INTO emp_code,empnm;

EXITWHEN emp_cur%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(emp_code||''||empnm);

END LOOP;

CLOSEemp_cur;

END;

循环游标

循环游标用于简化游标处理代码

当用户需要从游标中提取所有记录时使用

不需要显示打开和关闭游标

循环游标的语法如下：

FOR IN

LOOP

END LOOP;

注意： 名字可以不需要定义，直接使用

因为是Oracle隐含定义的变量名

SQL> SET SERVER OUTPUT ON

SQL> DECLARE

CURSOR mytoy_cur IS

SELECT toyid, toyname, toyprice

FROM toys;

BEGIN

FOR toy_rec IN mytoy_cur

LOOP

DBMS_OUTPUT.PUT_LINE(

‘玩具编号：'||' '||toy_rec.toyid||' '

||‘玩具名称：'||' '||toy_rec.toyname||' '

||‘玩具单价：'||' '||toy_rec.toyprice);

END LOOP;

END;

三、REF游标

REF 游标和游标变量用于处理运行时动态执行的 SQL 查询

创建游标变量需要两个步骤：

1.       声明 REF 游标类型

2.       声明 REF 游标类型的变量

用于声明 REF 游标类型的语法为：

TYPE IS REF CURSOR

[RETURN];

DECLARE

TYPE toys_curtype IS REF CURSOR  --声明ref游标

RETURN toys%ROWTYPE;

toys_curvar toys_curtype;

toys_rec toys%ROWTYPE;

BEGIN

OPEN toys_curvar FOR

SELECT * FROM toys;

FETCH toys_curvar INTO toys_rec;

...

CLOSE toys_curvar;

END;

例：

...................................................................................................................................

CREATE OR REPLACE PROCEDUREUPDATE_SAL99  --创建  存储过程。

(

P_MIN IN NUMBER       --参数

, P_MAX IN NUMBER

, P_dno IN NUMBER

)

AS

V_average number;   --声明变量

V_sal emp.sal%type;

cursor C_emp(n number) is     --声明游标

select sal from emp where deptno=n for update;

BEGIN

select avg(sal) into V_average from emp whereDEPTNO=P_dno;  --查出员工平均工资，赋给变量V_average

open c_emp(P_dno);  --打开游标

loop                                  --利用游标，循环读取每行数据

fetch c_emp into  V_sal;

exit when c_emp%notfound;   --当游标为空，则退出循环

ifv_sal > V_average  then                                   --如果>平均工资，给员工工资增长P_max

update emp set sal=sal+p_max where current of c_emp;

else

update emp set sal=sal+p_min where current of c_emp;

endif;

endloop;

closec_emp;

commit;   --提交

exception

when others then

rollback;       --异常处理：任何异常都  回滚

NULL;

END UPDATE_SAL99;

...........................................................................................

--调用

execute UPDATE_SAL99(20,10000,20000);  --调用过程，传递实参

select ename, sal from emp where deptno = 20;

游标变量的优点和限制

游标变量的功能强大，可以简化数据处理。

游标变量的优点有：

1.       可从不同的 SELECT 语句中提取结果集

2.       可以作为过程的参数进行传递

3.       可以引用游标的所有属性

4.       可以进行赋值运算

使用游标变量的限制：

1.       不能在程序包中声明游标变量

2.       FOR UPDATE子句不能与游标变量一起使用

3.       不能使用比较运算符

总结：

游标用于处理查询结果集中的数据

游标类型有：隐式游标、显式游标和REF游标

隐式游标由PL/SQL自动定义、打开和关闭

显式游标用于处理返回多行的查询

显式游标可以删除和更新活动集中的行

要处理结果集中所有记录时，可使用循环游标

在声明REF游标时，不需要将SELECT 语句与其关联

函数：

系统定义的

自定义函数[把常用的计算式，定义成函数，可在多处调用]

函数

有return,返回单一的值。有参数。不能修改更新[update]数据。

函数写好，需要编译[保存]，才可调用。在SQL语句中直接调用。

过程(存储过程)

参数(IN,OUT,INOUT)

没有返回值。

封装多个SQL操作

独立(直接)运行

过程--可修改数据

createor replace FUNCTION FUNCTION_REVENUE --若已有函数，则替代。

(

SAL IN NUMBER,     --声明的参数变量

n number

)RETURN NUMBER AS

temp number;  --函数 局部变量

BEGIN

if sal> n then

temp:= SAL*18;    /*赋值  ：=*/

else

temp:= SAL*12;

End if;

RETURN temp;     --返回值

ENDFUNCTION_REVENUE;

存储过程-的例子在前页和游标一起给出了。