oracle游标

--fetch ... into
declare
cursor emp_cursor is select ename,sal from emp where deptno=10;
v_ename emp.ename%type;
v_sal emp.sal%type;
begin

open emp_cursor;
loop
fetch emp_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;

--fetch ... bulk collect into
declare
cursor emp_cursor is
select ename from emp where deptno=10;
type ename_table_type is table of varchar2(10);
ename_table ename_table_type;
begin
open emp_cursor;
fetch emp_cursor bulk collect into ename_table ;
for i in 1.. ename_table.count loop
dbms_output.put_line(ename_table(i));
end loop;
close emp_cursor;
end;

--fetch ... bulk collect into...limit
declare
type name_array_type is varray(5) of varchar2(10);
name_array name_array_type ;
cursor emp_cursor is select ename from emp;
rows int:=5;
v_count int:=0;
begin
open emp_cursor;
loop
fetch emp_cursor bulk collect into name_array limit rows;
dbms_output.put('雇员名：');
for i in 1..(emp_cursor%rowcount-v_count) loop
dbms_output.put(name_array(i)||' ');

end loop;
dbms_output.new_line;
v_count:=emp_cursor%rowcount;
exit when emp_cursor%notfound;
end loop;
close emp_cursor;
end;

--使用游标属性 %isopen %rowcount
declare
cursor emp_cursor is select ename from emp where deptno=10;
type ename_table_type is table of varchar2(20);
ename_table ename_table_type  ;
begin

  if not emp_cursor%isopen then
open emp_cursor;
  end if;
  fetch emp_cursor bulk collect into ename_table;
  for i in 1.. ename_table.count loop
dbms_output.put_line(ename_table(i));
  end loop;
  dbms_output.put_line('提取的总计行数：'||emp_cursor%rowcount);
  close emp_cursor;
end;

--基于游标 定义记录变量
declare
cursor emp_cursor is select ename,sal from emp;
emp_record emp_cursor%rowtype;
begin
open emp_cursor;
loop
   fetch emp_cursor into emp_record;
   dbms_output.put_line('name:'||emp_record.ename||' sal:'||emp_record.sal);
   exit when emp_cursor%notfound ;

end loop;
close emp_cursor;

end;

--带参数游标
declare
cursor emp_cursor(no number) is select ename from emp where deptno=no;
v_ename emp.ename%type;
begin
  open emp_cursor(10);
  loop
    fetch emp_cursor into v_ename;
    dbms_output.put_line(v_ename);
    exit when emp_cursor%notfound;
  end loop;
end;

/*
游标应用
*/
--1使用游标更新数据
declare
cursor emp_cursor is select ename,sal from emp for update;
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
   open emp_cursor;
   loop
fetch emp_cursor into v_ename,v_sal;
exit when emp_cursor%notfound;
if v_sal<2000 then
  update emp set sal=sal+200 where current of emp_cursor;
      end if;
   end loop;
   close emp_cursor;
  
end;

--2使用游标删除数据

declare
cursor emp_cursor is select ename,sal,deptno from emp for update;
v_ename emp.ename%type;
v_oldsal emp.sal%type;
v_deptno emp.deptno%type;
begin
open emp_cursor;
loop
fetch emp_cursor into v_ename ,v_oldsal,v_deptno;
exit when emp_cursor%notfound;
if v_deptno=30 then
delete from emp where current of emp_cursor;
end if;
end loop;
close emp_cursor;
end;

---使用Of子句在特定表上加共享锁

declare
cursor emp_cursor is select ename,sal,dname,emp.deptno from emp,dept
where emp.deptno=dept.deptno for update of emp.deptno;
emp_record emp_cursor%rowtype;
begin
open emp_cursor;
loop
fetch emp_cursor into emp_record;
exit when emp_cursor%notfound;
if emp_record.deptno=30 then
update emp set sal=sal+100 where current of emp_cursor;
end if;
dbms_output.put_line('雇员名：'||emp_record.ename||' 工资：'||emp_record.sal||' 部门：'||emp_record.dname);
end loop;
end;

--使用游标for 循环
declare
cursor emp_cursor is select ename ,sal from emp ;
begin
  for emp_record in emp_cursor loop
    dbms_output.put_line('雇员名：'||emp_record.ename||' 薪资：'||emp_record.sal);
  end loop;
end;

--for 循环中直接使用子查询
begin
for emp_record in(
  select ename,sal from emp ) loop
dbms_output.put_line('雇员名：'||emp_record.ename||' 薪资：'||emp_record.sal);
  end loop;
end;