使用游标
(一)显式游标
1、PL/SQL包含隐式游标、显式游标。隐式游标用于处理SELECT...INTO...和DML语句,显示游标专门用于处理SELECT语句返回的多行数据。
2、使用显式游标包含定义游标、打开游标、提取数据、关闭游标等四个阶段。
-
定义游标
语法:CURSOR 游标名 IS select_statemant;
- 打开游标
语法:OPEN 游标名;
- 提取数据
语法:|-FETCH 游标名 INTO variable1,.....; --每次只能提取一行数据,variable用于指定接收游标数据的变量。
|-FETCH 游标名 BULK COLLECT INTO collect1,......; --每次可以提取多行数据。collect用于指定接收游标结果的集合变量。默认情况下,一次可以提取结果集中的所有数据。
- 关闭游标
语法:CLOSE 游标名;
3、显式游标属性 --使用方法:游标名.属性名
-
%ISOPEN --该属性用于确定游标是否已经打开,如果打开则返回值为TRUE,否则返回值为FALSE。
-
%FOUND --该属性用于检查是否从结果集中提取到了数据(规定行数)。如果提取到了数据(规定行数)返回TRUE,否则返回FALSE。
-
%NOTFOUND --与%FOUND属性恰好相反。
-
%ROWCOUNT --该属性返回到当前行为止已经提取到的实际行数。
4、显式游标使用示例
例1:在显式游标中使用fetch...into...
DECLARE
v dept.dname%type;
v_long varchar2(200);
cursor abc is select dname from dept;
begin
open abc;
loop
fetch abc into v;
exit when abc%notfound;
v_long:=v_long||' '||v;
end loop;
close abc;
dbms_output.put_line(v_long);
end;
输出: ACCOUNTING RESEARCH SALES OPERATIONS
例2: 在显式游标中使用fetch...bulk collect into...语句提取所有数据
declare
cursor emp_cursor is select ename from emp where deptno=10;
type emp_table is table of emp.ename%type;
emp_identifier emp_table;
i number not null default 1;
begin
open emp_cursor;
fetch emp_cursor bulk collect into emp_identifier;
close emp_cursor;
while i<=emp_identifier.count loop
dbms_output.put_line(emp_identifier(i));
i:=i+1;
end loop;
end;
输出:
CLARK
KING
MILLER
例3:使用fetch...bulk collect into ...limit n 语句提取数据 -- n为一整数,表示每次提取多少行
declare
cursor emp_cursor_type is select ename from emp;
type emp_table_type is table of emp.ename%type;
emp_identifier emp_table_type;
k number not null default 0;
begin
open emp_cursor_type;
while 1>0 loop
fetch emp_cursor_type bulk collect into emp_identifier limit 5;
dbms_output.put(' 雇员名: ');
for i in 1..(emp_cursor_type%rowcount -k)loop
dbms_output.put(emp_identifier(i)||' ');
end loop;
dbms_output.put(' '||emp_cursor_type%rowcount);
dbms_output.new_line;
k:=emp_cursor_type%rowcount;
exit when not emp_cursor_type%found;
end loop;
close emp_cursor_type;
end;
输出:
雇员名: SMITH ALLEN WARD JONES MARTIN 5
雇员名: BLAKE CLARK SCOTT KING TURNER 10
雇员名: ADAMS JAMES FORD MILLER 14
例4:使用游标属性
declare
cursor emp_cursor_type is select ename from emp;
type emp_table_type is table of emp.ename%type;
emp_table_type_identifier emp_table_type;
begin
if not emp_cursor_type%isopen then
open emp_cursor_type;
end if;
fetch emp_cursor_type bulk collect into emp_table_type_identifier;
dbms_output.put_line('提取的总行数: '||emp_cursor_type%rowcount);
dbms_output.put_line('雇员名: ');
for i in 1..(emp_cursor_type%rowcount) loop
dbms_output.put_line(emp_table_type_identifier(i)||' ');
end loop;
close emp_cursor_type;
end;
例5:基于游标定义记录变量 --使用%ROWTYPE属性不仅可以基于表和视图定义记录变量,也可以基于游标定义记录变量。当基于游标定义记录变量时,记录成员名实际就是SELECT语句的列名或列别名。语法:游标名%ROWTYPE
declare
cursor dept_cursor is select deptno,dname,loc from dept;
v_dept dept_cursor%rowtype;
begin
if not dept_cursor%isopen then
open dept_cursor;
end if;
loop
fetch dept_cursor into v_dept.deptno,v_dept.dname,v_dept.loc;
exit when not dept_cursor%found;
dbms_output.put_line('部门号:'||v_dept.deptno||' 部门名:'||v_dept.dname||' 部门位置:'||v_dept.loc);
end loop;
close dept_cursor;
end;
输出:
部门号:10 部门名:ACCOUNTING 部门位置:NEW YORK
部门号:20 部门名:RESEARCH 部门位置:DALLAS
部门号:30 部门名:SALES 部门位置:CHICAGO
部门号:40 部门名:OPERATIONS 部门位置:BOSTON
(二)参数游标
-
定义参数游标语法
CURSOR cursor_name(parameter_name datatype) IS select_statement;
注:定义参数游标时,游标参数只能指定数据类型,而不能指定长度。
例1:参数游标使用示例
declare
cursor emp_cur(no number) is select ename from emp where deptno=no;
type emp_tbl_type is table of emp.ename%type;
emp_tbl emp_tbl_type;
begin
if not emp_cur%isopen then
open emp_cur(10);
end if;
fetch emp_cur bulk collect into emp_tbl;
dbms_output.put_line('雇员名:');
for i in 1..emp_cur%rowcount loop
dbms_output.put_line(emp_tbl(i)||' ');
end loop;
close emp_cur;
END;
输出:
雇员名:
CLARK
KING
MILLER
(三)使用游标更新或删除数据
1、如果要通过游标更新或删除数据,在定义游标时必须要带有FOR UPDATE子句。定义语法如下:
CURSOR cursor_name(paratmeter_name datatype)
IS select_statement FOR UPDATE [of column_reference] [nowait];
其中:①FOR UPDATE子句用于在游标结果集数据上加行共享锁,以防止其他用户在相应行上执行DML操作。
②当SELECT语句引用到多张表时,使用OF子句可以确定哪些表要加锁,如果没有OF子句,则会在SELECT语句所引用的全部表上加锁。
③nowait子句用于指定不等待锁。
2、在提取了游标数据之后,为了更新或删除当前游标行数据,必须在update或delete语句中引用where current of子句。
语法如下:
update table_name set column=... where current of cursor_name;
delete table_name where current of cursor_name;
例1:使用游标更新数据
declare
cursor v_cursor(n number) is select deptno,dname from v_dept where deptno<=n for update;
v1 v_dept.deptno%type;
v2 v_dept.dname%type;
begin
if not v_cursor%isopen then
open v_cursor(30);
end if;
loop
fetch v_cursor into v1,v2;
exit when v_cursor%notfound;
if v1<=20 then
update v_dept set deptno=deptno+1 where current of v_cursor;
end if;
end loop;
close v_cursor;
end;
结果:
SQL> select * from v_dept;
DEPTNO DNAME LOC
------ -------------- -------------
11 ACCOUNTING NEW YORK
21 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
例2:使用游标删除数据
declare
cursor v_cursor(n number) is select * from v_emp where deptno<=n for update;
v v_emp%rowtype;
begin
if not v_cursor%isopen then
open v_cursor(40);
end if;
loop
fetch v_cursor into v;--直接使用记录变量接收游标数据
exit when v_cursor%notfound;
if v.deptno=30 then
delete v_emp where current of v_cursor;
end if;
end loop;
close v_cursor;
end;
结果:
SQL> select * from v_emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7566 JONES MANAGER 7839 1981-4-2 2975.00 20
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20
7839 KING PRESIDENT 1981-11-17 5000.00 10
7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
7902 FORD ANALYST 7566 1981-12-3 3000.00 20
7934 MILLER CLERK 7782 1982-1-23 1300.00 10
8 rows selected