在Oracle数据库中,游标分为显式游标与隐式游标,其中隐式游标又称为SQL游标,专门用于处理select into、update、insert、delete语句,而显式游标常用处理多行数据的select语句。
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
一、SQL游标
1、SQL%ISOPEN
当在pl/sql块中使用select into、update、insert、delete语句的时候,Oracle会自动打开游标,并且在语句执行完了之后自动关闭,对于开发人员来说,不用关心该属性。
2、SQL%FOUND
该属性用于确定SQL语句是否执行成功(根据是否作用于行来判断,一个update语句,更新了0行,返回false…………)
begin
declare
v_deptno dept.deptno%type;
begin
v_deptno:=0;
update dept set loc='Hello' where deptno=0;
if SQL%FOUND then
dbms_output.put_line('true查询到了数据');
else
dbms_output.put_line('false没有查询到数据');
end if;
end;
end;
3、SQL%NOTFOUND
该属性用于确定SQL语句是否执行失败(根据是否作用于行来判断,一个update语句,更新了0行,返回true…………)
begin
declare
v_deptno dept.deptno%type;
begin
v_deptno:=0;
update dept set loc='Hello' where deptno=0;
if SQL%NOTFOUND then
dbms_output.put_line('true没有查询到数据');
else
dbms_output.put_line('false查询到数据');
end if;
end;
end;
4、SQL%ROWCOUNT
该属性返回SQL语句作用于行的行数
begin
declare
v_deptno dept.deptno%type;
begin
v_deptno:=0;
update dept set loc='Hello' where deptno=10;
dbms_output.put_line(SQL%ROWCOUNT||'行数据更新');
end;
end;
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
二、显示游标
1、%ISOPEN
判断游标是否已经打开,如果打开,返回true,否则返回false,例如:
if not cursor_name%ISOPEN then
open cursor_name;
end if;
2、%FOUND
用于检测游标结果集是否有数据,如果存在数据,则返回true,否则返回false
begin
declare
v_dname dept.dname%type;
v_loc dept.loc%type;
cursor c_dept is select dname,loc from dept where deptno=10;
begin
if not c_dept%ISOPEN then
open c_dept;
end if;
loop
fetch c_dept into v_dname,v_loc;
exit when not c_dept%FOUND;
dbms_output.put_line('部门名称:'||v_dname||' 部门地址:'||v_loc);
end loop;
close c_dept;
end;
end;
3、%NOTFOUND
用于检测游标结果集是否不存在数据,如果不存在数据,则返回true,否则返回false
begin
declare
v_dname dept.dname%type;
v_loc dept.loc%type;
cursor c_dept is select dname,loc from dept where deptno=10;
begin
if not c_dept%ISOPEN then
open c_dept;
end if;
loop
fetch c_dept into v_dname,v_loc;
exit when c_dept%NOTFOUND;
dbms_output.put_line('部门名称:'||v_dname||' 部门地址:'||v_loc);
end loop;
close c_dept;
end;
end;
4、%ROWCOUNT
返回从游标结果集中提取到的实际行数
begin
declare
v_dname dept.dname%type;
v_loc dept.loc%type;
cursor c_dept is select dname,loc from dept;
begin
if not c_dept%ISOPEN then
open c_dept;
end if;
loop
fetch c_dept into v_dname,v_loc;
exit when c_dept%NOTFOUND;
dbms_output.put_line('当前取得实际行数'||c_dept%ROWCOUNT||' 部门名称:'||v_dname||' 部门地址:'||v_loc);
end loop;
close c_dept;
end;
end;
当前取得实际行数1 部门名称:ACCOUNTING 部门地址:Hello
当前取得实际行数2 部门名称:RESEARCH 部门地址:DALLAS
当前取得实际行数3 部门名称:SALES 部门地址:CHICAGO
当前取得实际行数4 部门名称:OPERATIONS 部门地址:BOSTON
5、用pl/sql记录变量接受游标数据
begin
declare
cursor c_dept is select dname,loc from dept;
v_record c_dept%rowtype;
begin
if not c_dept%ISOPEN then
open c_dept;
end if;
loop
fetch c_dept into v_record;
exit when c_dept%NOTFOUND;
dbms_output.put_line('当前取得实际行数'||c_dept%ROWCOUNT||' 部门名称:'||v_record.dname||' 部门地址:'||v_record.loc);
end loop;
close c_dept;
end;
end;
%rowtype不仅可以基于表、视图定义记录变量,还可以基于游标进行定义,这样可以减少定义变量的个数
6、用pl/sql集合变量接受游标数据
begin
declare
cursor c_dept is select dname,loc from dept;
type tab_type is table of c_dept%rowtype index by binary_integer;
dept_tab tab_type;
i integer;
begin
if not c_dept%ISOPEN then
open c_dept;
end if;
loop
i:=c_dept%rowcount+1;
fetch c_dept into dept_tab(i);
exit when c_dept%NOTFOUND;
dbms_output.put_line('索引值'||i||' 当前取得实际行数'||c_dept%ROWCOUNT||' 部门名称:'||dept_tab(i).dname||' 部门地址:'||dept_tab(i).loc);
end loop;
close c_dept;
end;
end;
7、游标for循环的使用
(1)定义游标,使用游标属性
begin
declare
cursor c_dept is select dname,loc from dept;
begin
for item in c_dept loop
dbms_output.put_line('当前取得实际行数'||c_dept%ROWCOUNT||' 部门名称:'||item.dname||' 部门地址:'||item.loc);
end loop;
end;
end;
采用游标for循环,会自动打开关闭游标,循环完了,自动退出游标
(2)、不定义游标,采用子查询
begin
for item in (select dname,loc from dept) loop
dbms_output.put_line('部门名称:'||item.dname||' 部门地址:'||item.loc);
end loop;
end;
同样会自动打开、关闭游标、并且循环完了自动退出游标
8、参数游标
顾名思义,就是游标加上参数过滤数据,需要注意的地方就是定义参数的时候,只需指定数据类型,不用定义长度。
定义的参数需要用到后面的查询语句中,否则就没有意义了,当然不用也不会报错
多个参数当然也是支持的
begin
declare
cursor c_dept(dno number) is select dname,loc from dept where deptno=dno;
begin
for item in c_dept(10) loop
dbms_output.put_line('当前取得实际行数'||c_dept%ROWCOUNT||' 部门名称:'||item.dname||' 部门地址:'||item.loc);
end loop;
end;
end;
9、更新、删除游标行
语法:cursor cursor_name is select_statement
for update [of 表名.列名] [nowait]
update tab_name set colname=value where current of cursor_name;
delete from tab_name where current of cursor_name;
for update:加行级共享锁,防止其他用户在该行上执行DML操作;
nowait:用于指定不等待锁;
当查询语句涉及到多张表的时候,需指定OF子句,如果不指定会在多张表上面同时加锁,指定了,只在指定的表上面加锁;
(1)、更新游标行
begin
declare
cursor c_dept is select deptno,dname,loc from dept for update;
v_deptno dept.deptno%type;
begin
for item in c_dept loop
if item.deptno=10 then
dbms_output.put_line('部门名称:'||item.dname||' 部门地址:'||item.loc);
update dept set loc='测试地址' where current of c_dept;
end if;
end loop;
end;
end;
(2)、删除游标行
begin
declare
cursor c_dept is select deptno,dname,loc from dept for update;
v_deptno dept.deptno%type;
begin
for item in c_dept loop
if item.deptno=1 then
dbms_output.put_line('删除部门名称:'||item.dname||' 部门地址:'||item.loc);
delete from dept where current of c_dept;
end if;
end loop;
end;
end;
(3)使用OF子句在特定表上面加行共享锁
begin
declare
cursor c_emp is
select emp.empno,emp.ename from emp
inner join dept on dept.deptno=emp.deptno
where dept.dname='ACCOUNTING'
for update OF emp.empno;
v_deptno emp.empno%type;
begin
for item in c_emp loop
if item.empno=7782 then
dbms_output.put_line('更新人员编码:'||item.empno||' 人员名称:'||item.ename);
update emp set sal=1200 where current of c_emp;
end if;
end loop;
end;
end;
10、游标变量
语法:type ref_c_name is ref cursor [return return_type];
cur_name ref_c_name;
open cur_name for select_statement;
fetch cur_name into var1,var2…………;
colse cur_name;
(1)、无返回类型的游标变量
begin
declare
type ref_dept is ref cursor;
c_dept ref_dept;
v_dname dept.dname%type;
v_loc dept.loc%type;
begin
open c_dept for select dname,loc from dept;
loop
fetch c_dept into v_dname,v_loc;
exit when c_dept%NOTFOUND;
dbms_output.put_line('当前取得实际行数'||c_dept%ROWCOUNT||' 部门名称:'||v_dname||' 部门地址:'||v_loc);
end loop;
close c_dept;
end;
end;
(2)、有返回类型的游标变量
如果有返回类型,那么再打开游标时返回的结果数据集类型必须与指定的类型匹配
begin
declare
type ref_dept is ref cursor return dept%rowtype;
c_dept ref_dept;
v_dept dept%rowtype;
begin
open c_dept for select * from dept;
loop
fetch c_dept into v_dept;
exit when c_dept%NOTFOUND;
dbms_output.put_line('当前取得实际行数'||c_dept%ROWCOUNT||' 部门名称:'||v_dept.dname||' 部门地址:'||v_dept.loc);
end loop;
close c_dept;
end;
end;