游标的属性简介:
游标分为显示游标和隐式游标.
① %fFOUND 记录获取成功返回true,否则返回false
② %NOTFOUND 记录获取失败返回true,否则返回false
③ %ROWCOUNT 返回已经从游标中获取的记录数
④ %ISOPEN 如果游标式打开的的,返回true,否则返回false(隐式游标是oracle自动打开和关闭的,总是返回false)
⑤ %BULK_ROWCOUNT 返回FORALL语句更改的每个集合元素的记录数
⑥ %BULK_EXCEPTION 返回FORALL更改的每个集合元素的记录的异常信息
显示游标的4个步骤: 声明 , 打开 , 提取记录 , 关闭.
1) 声明显示游标
CURSOR 游标名 [(游标参数列表)] [RETURN 返回值规范]
IS SELECT语句
[FOR UPDATE[OF [列名列表]]];
2) 打开显示游标
OPEN 显示游标名(参数列表);
3) 从显示游标中提取记录
我们使用显示游标的目的就是为了从游标中返回或提取多行数据,然后操纵这些数据;
游标实际就是PL/SQL中的一个虚表,我们需要从这个虚表中提取行,然后操纵行的信息
PL/SQl提供了FETCH语句执行该操作.
FETCH 游标名 into 记录或变量列表;
4) 关闭列表
CLOSE 游标名;
例如:
declare
cursor cur_emp is select ename,sal from scott.emp; --定义游标
row_emp cur_emp%rowtype; --基于游标定义一个记录
begin
open cur_emp; --打开游标
fetch cur_emp into row_emp; --从游标中提取一行添加到记录中
while cur_emp%found --循环检测游标属性,看是否到最后一行
loop
dbms_output.put_line(row_emp.ename||'--'||row_emp.sal);
fetch cur_emp into row_emp; --从游标中提取一行添加到记录中
end loop;
close cur_emp; --关闭游标
end;
结果为:
SMITH--800
ALLEN--1600
WARD--1250
JONES--2975
...
当我们提交一个select…for update语句时,Oracle自动对该select语句所影响的行用行级排他锁锁住,
CURSOR emp_cur IS
Select empno,ename,sal,from scott.emp where job=’manager’ from update;
CURSOR emp_cur IS
Select empno,ename,sal from scott.emp where job=’manager’ for update of sal;
我们还可以在for update后加nowait关键字,用于告诉oracle如果表已经被其他用户锁住,就不需要等待.
PL/SQL对于游标使用过程的UPDATE和DELETE语句提供了WHERE CURRENT OF子句,使我们更容易的修改最近提取的数据行。
语法如下:
Update 表名 set set子句 where current of 游标名;
Delete from 表名 where current of 游标名.
例:
select * from scott.emp where job=('MANAGER')
declare
cursor emp_cur is
select empno,ename,sal from scott.emp where job='MANAGER' for update;
emp_row emp_cur%rowtype;
begin
open emp_cur;
loop
fetch emp_cur into emp_row;
if emp_cur%notfound --如果游标中没有记录,或者到了最后一条记录,就退出
then
exit;
else
update scott.emp set sal=sal+1000 where current of emp_cur; --修改当前记录
end if;
end loop;
commit;
close emp_cur;
end;
游标FOR循环:
FOR 记录 IN 游标名
LOOP
执行语句
END LOOP;
这里的记录不需要我们显式定义的,它根据指定的游标名,用%rowtype属性隐式的定义的,注意,游标FOR循环仅用在处理游标中的每一条记录时.
declare
cursor emp_cur is
select empno,ename,sal from scott.emp where job='MANAGER' for update;
begin
for emp_row in emp_cur
Loop
update scott.emp set sal=sal-1000 where current of emp_cur;
end loop;
commit;
end;
与上比较
*BULK COLLECT
在Oracle 8i以前,要返回多行数据只能使用显式游标.在Oracle 8i中引入了bulk collect子句,可以用对数据库的一个来回,返回多行数据.
… BULK COLLECT INTO 集合名[,集合名]…
规则:
在oracle 9i前,我们只能在静态SQL中使用bulk collect.在oracle9i以后,在静态和动态SQL中都可以使用该子句.
可以在SELECT INTO,FETCH INTO,RETURNING INTO 子句中使用
BULK COLLECT输出到的集合只能存储标量值(字符串,数字,日期等).换句话说,不能把一行数据提取到集合中的一个记录结构的行中.
SQL引擎自动初始化和扩展在BULK COLLECT 子句中引用的集合,它从集合的索引1开始填充,连续的插入元素,并把以前定义的任何元素值覆盖.
不能在FORALL语句中使用select …bulk collect语句.
如果没有查询到行,select..bulk collect 不能抛出NO_DATA_FOUND异常.因此,必须检查集合的内容,看是否有数据
在执行查询之前,bulk collect操作将会清空into子句引用的集合的内容.如果查询没有返回行,那么集合的count方法会返回0.
现在我们用select..bulk collect into 改写上面的显式游标示例
代码如下:
declare
type emp_table_type is table of scott.emp%rowtype index by binary_integer;
emp_table emp_table_type;
begin
select * bulk collect into emp_table from scott.emp;
for i in 1..emp_table.count
loop
dbms_output.put_line(emp_table(i).empno||'--'||emp_table(i).ename);
end loop;
end;
也可以用fetch…into应用bulk collect子句
declare
cursor cur_emp is select * from scott.emp;
type row_emp_type is table of cur_emp%rowtype index by binary_integer;
row_emp row_emp_type;
begin
open cur_emp;
fetch cur_emp bulk collect into row_emp;
for i in 1..row_emp.count
loop
dbms_output.put_line(row_emp(i).empno||'--'||row_emp(i).ename);
end loop;
close cur_emp;
end;
结果如下
7369--SMITH
7499--ALLEN
7521--WARD
7566--JONES
7654--MARTIN
7698--BLAKE
7782--CLARK
7788--SCOTT
7839--KING
7844--TURNER
7876--ADAMS
7900--JAMES
7902--FORD
7934—MILLER
*游标变量和REF CURSOR
游标变量是一个指向或者引用下层游标的变量.显式游标是对PL/SQL 结果集工作区命名,而游标变量则是对该工作区的一个引用,显式游标和隐式游标要绑定到特定的查询,因而是静态的.而游标变量可以为任何查询打开,甚至在单个程序执行期间可以为不同的查询打开.,游标可以在访问游标变量的程序之间共享.
---使用和显式游标基本类似;
定义REF CURSOR类型的游标变量
语法如下:
Return子句是可选的
TYPE cursor_type_name IS REF CURSOR[RETURN return_type];
引用游标名称 return数据规范
弱类型的游标变量:没有将游标类型与记录数据结构相关联,比强类型的游标变量更有灵活性,
从oracle9i数据开始,oracle提供了一种名为SYS_REFCURSOR的预定义的REF CURSOR,
我们可以直接按照如下方法使用,而不需要定义自己的弱类型游标变量:
DECLARE
my_cursor SYS_REFCURSOR;
定义一个游标变量语法如下:
Cursor_name cursor_type_name;
示例:
Declare
/* 为雇员创建一个游标类型*/
TYPE emp_cur_type IS REF CURSOR RETURN emp%ROWTYPE;
/*为雇员创建一个游标变量.*/
emp_cur emp_cur_type;
BEGIN
…
END;
PL/SQL游标
最新推荐文章于 2022-09-28 11:03:32 发布