一、显示游标
1.1、游标属性
%ISOPEN用于确定游标是否打开,如果打开返回TRUE,否则FLASE。
%FOUND用于检查是否从结果集中提取到了数据,如果取到数据返回TRUE,否则FALSE。
%NOTFOUND用于检查是否从结果集中提取到了数据,如果取到数据返回FALSE,否则TRUE。
%ROWCOUNT该属性用于返回当前行为止已经提取到的实际行数。
2、显示使用游标
2、1在显示游标中使用FETCH...INTO语句
declare cursor emp_cursor is select ename,sal from scott.emp;
v_ename scott.emp.ename%type;
v_sal scott.emp.sal%type;
begin
open emp_cursor;
loop
Fetchemp_cursor into v_ename,v_sal;
exit when emp_cursor%notfound;
dbms_output.put_line(v_ename||' '||v_sal);
end loop;
close emp_cursor;
end;
2.2、在显示游标中,使用FECTH..BULK COLLECT INTO语句提取所有数据
declare type ename_table_type is table of scott.emp.ename%typeindex by pls_integer;
ename_table ename_table_type;
cursor emp_cursor is select ename from scott.emp;
begin
open emp_cursor;
fetch emp_cursor BULK COLLECTinto ename_table;
dbms_output.put_line(emp_cursor%rowcount);
for i in 1..ename_table.count loop
dbms_output.put_line(ename_table(i));
end loop;
close emp_cursor;
end;
2.3、在显示游标中,使用FECTH..BULK COLLECT INTO...LIMIT语句提取部分数据
declare type ename_table_type is table of scott.emp.ename%typeindex by pls_integer;
ename_table ename_table_type;
cursor emp_cursor is select ename from scott.emp;
v_rows int:=5;
v_count int:=0;
begin
open emp_cursor;
loop
fetch emp_cursor bulk collect into ename_table LIMITv_rows;
dbms_output.put_line(emp_cursor%rowcount);
for i in 1..(emp_cursor%rowcount-v_count) loop
dbms_output.put_line(ename_table(i));
end loop;
dbms_output.put_line('----------------');
v_count:=emp_cursor%rowcount;
exit when emp_cursor%NOTFOUND;
end loop;
close emp_cursor;
end;
2.4、使用游标属性
declare cursor ename_cursor is select ename from scott.emp;
type ename_table_type is table of scott.emp.ename%type index bypls_integer;
ename_table ename_table_type;
begin
if not ename_cursor%isopen
then open ename_cursor;
end if;
fetch ename_cursor into ename_table(1);
if ename_cursor%found then dbms_output.put_line('有数据');endif;
if ename_cursor%notfound then dbms_output.put_line('没有取得数据');endif;
if ename_cursor%isopen
then close ename_cursor;
end if;
dbms_output.put_line(ename_table.count);
end;
2.5、基于游标定义记录变量
declare cursor emp_cursor is select ename,sal from scott.emp;
type emp_record is record(r_ename varchar(50),r_salnumber(11,0));
type emp_table_type is table of emp_record index bypls_integer;
emp_table emp_table_type;
begin
open emp_cursor;
fetch emp_cursor bulk collect into emp_table;
dbms_output.put_line(emp_table.count);
close emp_cursor;
end;
declare cursor emp_cursor(c_deptnonumber) is select * from scott.emp wheredeptno=c_deptno;
type emp_table_type is table of emp_cursor%rowtype index bypls_integer;
emp_table emp_table_type;
begin
open emp_cursor(&deptno);
fetch emp_cursor bulk collect into emp_table;
dbms_output.put_line(emp_table.count);
close emp_cursor;
end;
CURSOR cursor_name(parameter_name datatype)
IS select_statment FOR UPDATE [OF column_reference][NOWAIT]
FORUPDATE子句用于在游标结果集数据上加行共享锁,OF子句可以确定哪些表要加锁,如果没有OF子句,则会在SELECT语句所应用的全部表上加锁。NOWAIT子句用于指定不等待锁。
为了更新或删除当前游标行数据,必须在UPDATE或DELETE语句中引用WHERE CURRENT OF子句
3.1、使用游标更新数据
declare cursor emp_cursor is select ename,sal from scott.empFORUPDATE;
c_emp emp_cursor%rowtype;
begin
open emp_cursor;
loop
fetch emp_cursor into c_emp;
exit when emp_cursor%notfound;
if emp_cursor%found then dbms_output.put_line('有数据');end if;
if emp_cursor%notfound then dbms_output.put_line('无数据');endif;
if c_emp.sal<2000 then
update scott.emp set sal=sal+100 where CURRENT OF emp_cursor;
end if;
end loop;
close emp_cursor;
end;
3.2、使用游标删除数据
declare cursor emp_cursor is select * from scott.emp FORUPDATE;
c_emp emp_cursor%rowtype;
begin
open emp_cursor;
loop
fetch emp_cursor into c_emp;
exit when emp_cursor%notfound;
if c_emp.deptno=10 then
delete scott.emp where CURRENTOF emp_cursor;
end if;
end loop;
dbms_output.put_line(sql%rowcount);
close emp_cursor;
end;
3.3、使用OF子句在特定表上加行共享锁
declare cursor emp_cursor is select e.deptno,e.ename,e.sal,d.dnamefrom scott.emp e,scott.dept d where e.deptno=d.deptno FOR UPDATEOFe.deptno;
c_emp emp_cursor%rowtype;
begin
open emp_cursor;
loop
fetch emp_cursor into c_emp;
exit when emp_cursor%notfound;
if c_emp.deptno=10 then
update scott.emp set sal=sal+100 where current of emp_cursor;
end if;
dbms_output.put_line('雇员名:'||c_emp.ename||' 工资:'||c_emp.sal||' 部门:'||c_emp.dname);
end loop;
dbms_output.put_line(sql%rowcount);
close emp_cursor;
end;
3.4、使用NOWAIT子句
declare cursor emp_cursor is select * from scott.emp FOR UPDATENOWAIT;
c_emp emp_cursor%rowtype;
begin
open emp_cursor;
loop
fetch emp_cursor into c_emp;
exit when emp_cursor%notfound;
if c_emp.sal<2000 then
update scott.emp set sal=sal+100 where current of emp_cursor;
end if;
end loop;
dbms_output.put_line(sql%rowcount);
close emp_cursor;
end;
declare cursor emp_cursor is select * from scott.emp;
begin
for emp in emp_cursor loop
dbms_output.put_line(emp_cursor%rowcount||emp.ename);
end loop;
end;
4.2、在游标FOR循环中直接使用子查询
当不需要游标属性时可以直接使用子查询
begin
for emp in (select * from scott.emp) loop
dbms_output.put_line(emp.ename);
end loop;
end;
declare type emp_cursor_type is ref cursor;
emp_cursor emp_cursor_type;
type emp_table_type is table of scott.emp%rowtype index bypls_integer;
emp_tyoe scott.emp%rowtype;
emp_table emp_table_type;
begin
open emp_cursor for select * from scott.emp;
loop
fetch emp_cursor into emp_tyoe;
dbms_output.put_line(emp_tyoe.ename);
exit when emp_cursor%notfound;
end loop;
close emp_cursor;
end;
5.2、在定义REF CURSOR类型是指定RETURN子句
declare type emp_record_type is record(name varchar2(50),salarynumber(6,2));
type emp_cursor_type is ref cursor RETURNemp_record_type;
emp_record emp_record_type;
emp_cursor emp_cursor_type;
begin
open emp_cursor for select ename,sal from scott.emp;
loop
fetch emp_cursor into emp_record;
dbms_output.put_line(emp_record.name||' '||emp_record.salary);
exit when emp_cursor%notfound;
end loop;
close emp_cursor;
end;
当定义REF CURSOR类型时指定了RETURN子句,所以游标的查询语句返回的结果必须与记录类型匹配
1.1、游标属性
%ISOPEN用于确定游标是否打开,如果打开返回TRUE,否则FLASE。
%FOUND用于检查是否从结果集中提取到了数据,如果取到数据返回TRUE,否则FALSE。
%NOTFOUND用于检查是否从结果集中提取到了数据,如果取到数据返回FALSE,否则TRUE。
%ROWCOUNT该属性用于返回当前行为止已经提取到的实际行数。
2、显示使用游标
2、1在显示游标中使用FETCH...INTO语句
declare cursor emp_cursor is select ename,sal from scott.emp;
v_ename scott.emp.ename%type;
v_sal scott.emp.sal%type;
begin
open emp_cursor;
loop
Fetchemp_cursor into v_ename,v_sal;
exit when emp_cursor%notfound;
dbms_output.put_line(v_ename||' '||v_sal);
end loop;
close emp_cursor;
end;
2.2、在显示游标中,使用FECTH..BULK COLLECT INTO语句提取所有数据
declare type ename_table_type is table of scott.emp.ename%typeindex by pls_integer;
ename_table ename_table_type;
cursor emp_cursor is select ename from scott.emp;
begin
open emp_cursor;
fetch emp_cursor BULK COLLECTinto ename_table;
dbms_output.put_line(emp_cursor%rowcount);
for i in 1..ename_table.count loop
dbms_output.put_line(ename_table(i));
end loop;
close emp_cursor;
end;
2.3、在显示游标中,使用FECTH..BULK COLLECT INTO...LIMIT语句提取部分数据
declare type ename_table_type is table of scott.emp.ename%typeindex by pls_integer;
ename_table ename_table_type;
cursor emp_cursor is select ename from scott.emp;
v_rows int:=5;
v_count int:=0;
begin
open emp_cursor;
loop
fetch emp_cursor bulk collect into ename_table LIMITv_rows;
dbms_output.put_line(emp_cursor%rowcount);
for i in 1..(emp_cursor%rowcount-v_count) loop
dbms_output.put_line(ename_table(i));
end loop;
dbms_output.put_line('----------------');
v_count:=emp_cursor%rowcount;
exit when emp_cursor%NOTFOUND;
end loop;
close emp_cursor;
end;
2.4、使用游标属性
declare cursor ename_cursor is select ename from scott.emp;
type ename_table_type is table of scott.emp.ename%type index bypls_integer;
ename_table ename_table_type;
begin
if not ename_cursor%isopen
then open ename_cursor;
end if;
fetch ename_cursor into ename_table(1);
if ename_cursor%found then dbms_output.put_line('有数据');endif;
if ename_cursor%notfound then dbms_output.put_line('没有取得数据');endif;
if ename_cursor%isopen
then close ename_cursor;
end if;
dbms_output.put_line(ename_table.count);
end;
2.5、基于游标定义记录变量
declare cursor emp_cursor is select ename,sal from scott.emp;
type emp_record is record(r_ename varchar(50),r_salnumber(11,0));
type emp_table_type is table of emp_record index bypls_integer;
emp_table emp_table_type;
begin
open emp_cursor;
fetch emp_cursor bulk collect into emp_table;
dbms_output.put_line(emp_table.count);
close emp_cursor;
end;
二、参数游标
当定义参数游标时,需要指定参数名及其数据类型。只能指定数据类型而不能指定长度,定义参数游标时,一定要在游标子查询的WHERE子句中引用该参数,否则失去了定义游标参数的意义。declare cursor emp_cursor(c_deptnonumber) is select * from scott.emp wheredeptno=c_deptno;
type emp_table_type is table of emp_cursor%rowtype index bypls_integer;
emp_table emp_table_type;
begin
open emp_cursor(&deptno);
fetch emp_cursor bulk collect into emp_table;
dbms_output.put_line(emp_table.count);
close emp_cursor;
end;
三、使用游标更新或删除数据
语法:CURSOR cursor_name(parameter_name datatype)
IS select_statment FOR UPDATE [OF column_reference][NOWAIT]
FORUPDATE子句用于在游标结果集数据上加行共享锁,OF子句可以确定哪些表要加锁,如果没有OF子句,则会在SELECT语句所应用的全部表上加锁。NOWAIT子句用于指定不等待锁。
为了更新或删除当前游标行数据,必须在UPDATE或DELETE语句中引用WHERE CURRENT OF子句
UPDATE table_name SET column=.. WHERE CURRENT OFcursor_name
DELETEtable_name WHERE CURRENT OF cursor_name
DELETEtable_name WHERE CURRENT OF cursor_name
3.1、使用游标更新数据
declare cursor emp_cursor is select ename,sal from scott.empFORUPDATE;
c_emp emp_cursor%rowtype;
begin
open emp_cursor;
loop
fetch emp_cursor into c_emp;
exit when emp_cursor%notfound;
if emp_cursor%found then dbms_output.put_line('有数据');end if;
if emp_cursor%notfound then dbms_output.put_line('无数据');endif;
if c_emp.sal<2000 then
update scott.emp set sal=sal+100 where CURRENT OF emp_cursor;
end if;
end loop;
close emp_cursor;
end;
3.2、使用游标删除数据
declare cursor emp_cursor is select * from scott.emp FORUPDATE;
c_emp emp_cursor%rowtype;
begin
open emp_cursor;
loop
fetch emp_cursor into c_emp;
exit when emp_cursor%notfound;
if c_emp.deptno=10 then
delete scott.emp where CURRENTOF emp_cursor;
end if;
end loop;
dbms_output.put_line(sql%rowcount);
close emp_cursor;
end;
3.3、使用OF子句在特定表上加行共享锁
declare cursor emp_cursor is select e.deptno,e.ename,e.sal,d.dnamefrom scott.emp e,scott.dept d where e.deptno=d.deptno FOR UPDATEOFe.deptno;
c_emp emp_cursor%rowtype;
begin
open emp_cursor;
loop
fetch emp_cursor into c_emp;
exit when emp_cursor%notfound;
if c_emp.deptno=10 then
update scott.emp set sal=sal+100 where current of emp_cursor;
end if;
dbms_output.put_line('雇员名:'||c_emp.ename||' 工资:'||c_emp.sal||' 部门:'||c_emp.dname);
end loop;
dbms_output.put_line(sql%rowcount);
close emp_cursor;
end;
3.4、使用NOWAIT子句
declare cursor emp_cursor is select * from scott.emp FOR UPDATENOWAIT;
c_emp emp_cursor%rowtype;
begin
open emp_cursor;
loop
fetch emp_cursor into c_emp;
exit when emp_cursor%notfound;
if c_emp.sal<2000 then
update scott.emp set sal=sal+100 where current of emp_cursor;
end if;
end loop;
dbms_output.put_line(sql%rowcount);
close emp_cursor;
end;
四、游标FOR循环
4.1、使用游标FOR循环declare cursor emp_cursor is select * from scott.emp;
begin
for emp in emp_cursor loop
dbms_output.put_line(emp_cursor%rowcount||emp.ename);
end loop;
end;
4.2、在游标FOR循环中直接使用子查询
当不需要游标属性时可以直接使用子查询
begin
for emp in (select * from scott.emp) loop
dbms_output.put_line(emp.ename);
end loop;
end;
五、使用游标变量
定义:
TYPE ref_type_name IS REF CURSOR [RETURN return_type];
ref_type ref_type_name;
打开:
OPEN cursor_varible FOR select_statement;
提取:
FETCH cursor_varible into varible1,varible2....;
FETCH cursor_varible BULK COLLECT into collect1,collect2....[LIMITrows];
关闭:CLOSEcursor_varible;
5.1、在定义REF CURSOR类型是不指定RETURN子句定义:
TYPE ref_type_name IS REF CURSOR [RETURN return_type];
ref_type ref_type_name;
打开:
OPEN cursor_varible FOR select_statement;
提取:
FETCH cursor_varible into varible1,varible2....;
FETCH cursor_varible BULK COLLECT into collect1,collect2....[LIMITrows];
关闭:CLOSEcursor_varible;
declare type emp_cursor_type is ref cursor;
emp_cursor emp_cursor_type;
type emp_table_type is table of scott.emp%rowtype index bypls_integer;
emp_tyoe scott.emp%rowtype;
emp_table emp_table_type;
begin
open emp_cursor for select * from scott.emp;
loop
fetch emp_cursor into emp_tyoe;
dbms_output.put_line(emp_tyoe.ename);
exit when emp_cursor%notfound;
end loop;
close emp_cursor;
end;
5.2、在定义REF CURSOR类型是指定RETURN子句
declare type emp_record_type is record(name varchar2(50),salarynumber(6,2));
type emp_cursor_type is ref cursor RETURNemp_record_type;
emp_record emp_record_type;
emp_cursor emp_cursor_type;
begin
open emp_cursor for select ename,sal from scott.emp;
loop
fetch emp_cursor into emp_record;
dbms_output.put_line(emp_record.name||' '||emp_record.salary);
exit when emp_cursor%notfound;
end loop;
close emp_cursor;
end;
当定义REF CURSOR类型时指定了RETURN子句,所以游标的查询语句返回的结果必须与记录类型匹配
六、使用CURSOR表达式
declare type emp_cursor_type is ref cursor;
emp_cursor emp_cursor_type;
cursor dept_cursor(c_deptno number) is select d.dname,CURSOR(selecte.ename,e.sal from scott.emp e where d.deptno=e.deptno) fromscott.dept d where d.deptno=c_deptno;
type myemp_record_type is record(r_ename varchar2(50),r_sal number(11));
myemp_record myemp_record_type;
v_dname varchar2(50);
begin
open dept_cursor(&no);
loop
fetch dept_cursor into v_dname,emp_cursor;
exit when dept_cursor%notfound;
dbms_output.put_line('部门名:'||v_dname);
loop
fetch emp_cursor into myemp_record;
exit when emp_cursor%notfound;
dbms_output.put_line(' 姓名:'||myemp_record.r_ename||'工资:'||myemp_record.r_sal);
end loop;
end loop;
close dept_cursor;
end;
declare type emp_cursor_type is ref cursor;
emp_cursor emp_cursor_type;
cursor dept_cursor(c_deptno number) is select d.dname,CURSOR(selecte.ename,e.sal from scott.emp e where d.deptno=e.deptno) fromscott.dept d where d.deptno=c_deptno;
type myemp_record_type is record(r_ename varchar2(50),r_sal number(11));
myemp_record myemp_record_type;
v_dname varchar2(50);
begin
open dept_cursor(&no);
loop
fetch dept_cursor into v_dname,emp_cursor;
exit when dept_cursor%notfound;
dbms_output.put_line('部门名:'||v_dname);
loop
fetch emp_cursor into myemp_record;
exit when emp_cursor%notfound;
dbms_output.put_line(' 姓名:'||myemp_record.r_ename||'工资:'||myemp_record.r_sal);
end loop;
end loop;
close dept_cursor;
end;