oracle 游标
1.自定义（显式）游标
set serverout on;
declare cursor cu_emp is select empno,ename,sal from emp;
e_no number;
e_name varchar2(10);
e_sal number;
begin
open cu_emp;
fetch cu_emp into e_no,e_name,e_sal;
while cu_emp%found loop --- 游标存在时
dbms_output.put_line('编号：'||e_no||'，姓名：'||e_name||'，基本薪资：'||e_sal);
fetch cu_emp into e_no,e_name,e_sal;
end loop;
close cu_emp;
end;
2.应用被遍历的的表的列类型
set serverout on;
declare cursor cu_emp is select empno,ename,sal from emp;
e_no emp.empno%type; -- empno列的类型
e_name emp.ename%type;
e_sal emp.sal%type;
begin
open cu_emp;
fetch cu_emp into e_no,e_name,e_sal;
while cu_emp%found loop
dbms_output.put_line('编号：'||e_no||'，姓名：'||e_name||'，基本薪资：'||e_sal);
fetch cu_emp into e_no,e_name,e_sal;
end loop;
close cu_emp;
end;
3.应用被遍历表的行属性
set serverout on;
declare cursor cu_emp is select * from emp; -- declare 可以同时声明多个变量，用分号隔开。
e emp%rowtype;
begin
open cu_emp;
fetch cu_emp into e;
while cu_emp%found loop
dbms_output.put_line('编号：'||e.empno||'，姓名：'||e.ename||'，基本薪资：'||e.sal);
fetch cu_emp into e;
end loop;
close cu_emp;
end;
4.游标的遍历可以加入条件
set serverout on;
declare cursor cu_emp is select * from emp where sal>2000 and sal<3000;
e emp%rowtype;
begin
open cu_emp;
fetch cu_emp into e;
while cu_emp%found loop
dbms_output.put_line('编号：'||e.empno||'，姓名：'||e.ename||'，基本薪资：'||e.sal);
fetch cu_emp into e;
end loop;
close cu_emp;
end;
5.隐式游标（系统声明好的游标，取名为sql）
隐式游标的属性 返回值类型 意 义
SQL%ROWCOUNT 整型 代表 DML 语句成功执行的数据行数
SQL%FOUND 布尔型 值为 TRUE 代表插入、删除、更新或单行查询操作成功
SQL%NOTFOUND 布尔型 与 SQL%FOUND 属性返回值相反
SQL%ISOPEN 布尔型 DML 执行过程中为真，结束后为假
begin
if sql%isopen then
dbms_output.put_line('sql游标已打开');
else
dbms_output.put_line('sql游标未打开');
end if;
end;
6.隐式游标的一些方法
declare e_count number;
begin
select count(*) into e_count from emp;
dbms_output.put_line('游标捕获的记录数：'||sql%rowcount);
end;
declare e_count number;
begin
select count(*) into e_count from emp;
dbms_output.put_line('游标捕获的记录数：'||sql%rowcount);
end;
begin
update emp set ename='sb3' where empno=111;
if sql%rowcount=1 then
dbms_output.put_line('已更新');
else
dbms_output.put_line('未更新');
end if;
end;
begin
update emp set ename='sb3' where empno=111;
if sql%found then
dbms_output.put_line('已更新');
else
dbms_output.put_line('未更新');
end if;
end;
7.强类型动态游标
declare type emptype is ref cursor return emp%rowtype;
cu_emp emptype;
e_count number;
e emp%rowtype;
begin
select count(*) into e_count from emp where job='PRESIDENT1';
if e_count=0 then
open cu_emp for select * from emp;
else
open cu_emp for select * from emp where job='PRESIDENT';
end if;
fetch cu_emp into e;
while cu_emp%found loop
dbms_output.put_line('编号：'||e.empno||'，姓名：'||e.ename||'，基本薪资：'||e.sal);
fetch cu_emp into e;
end loop;
close cu_emp;
end;
8.弱类型动态游标（更强大）
declare type customType is ref cursor;
e_count number;
e emp%rowtype;
s salgrade%rowType;
cType customType;
begin
select count(*) into e_count from emp where job='PRESIDENT1';
if e_count=0 then
open cType for select * from salgrade;
fetch cType into s;
while cType%found loop
dbms_output.put_line('等级：'||s.grade||'，最低薪资：'||s.losal||'，最高薪资：'||s.hisal);
fetch cType into s;
end loop;
close cType;
else
open cType for select * from emp where job='PRESIDENT';
fetch cType into e;
while cType%found loop
dbms_output.put_line('编号：'||e.empno||'，姓名：'||e.ename||'，基本薪资：'||e.sal);
fetch cType into e;
end loop;
close cType;
end if;
end;
