oracle 游标

1、隐式游标
实例1、用sql％found 相当于sql.found 

create or replace procedure abing
is
--set serveroutput on;
begin
declare
begin
update former_emp set ename='isStrong' where empno='abin';
if sql%found then
dbms_output.put_line('ok');
dbms_output.put_line(sql%rowcount);
end if;
if sql%notfound then
dbms_output.put_line('no');
end if;
exception
when no_data_found then
dbms_output.put_line('no data found');
end;
end;

begin
update former_emp set ename='isStrong' where empno='abin';
if sql%found then
dbms_output.put_line('ok');
dbms_output.put_line(sql%rowcount);
end if;
if sql%notfound then
dbms_output.put_line('no');
end if;
exception
when no_data_found then
dbms_output.put_line('no data found');
end;

create or replace procedure abin_1
is
--set serveroutput on;
begin
declare
begin
update former_emp set ename='isStrong' where empno='abing';
if sql%found then
dbms_output.put_line('yes');
dbms_output.put_line(sql%rowcount);
end if;
if sql%notfound then
dbms_output.put_line('no');
end if;
end;
end;

测试语句：
begin
declare
i number:=0;
begin
for i in 1..5 loop
abin_1;
end loop;
end;
end;

create or replace procedure abin_1
is
--set serveroutput on;
begin
declare name former_emp.empno%type;
begin
select ename into name from former_emp where empno='abin';
if sql%found then
dbms_output.put_line('yes');
dbms_output.put_line(sql%rowcount);
dbms_output.put_line(name);
end if;
if sql%notfound then
dbms_output.put_line('no');
end if;
end;
end;
测试代码：
begin
declare 
i number;
begin
i:=0;
for i in 1..5 loop
abin_1;
end loop;
end;
end;

create or replace procedure abin_1
is
begin
declare
var_userid number(3);
var_salary number(3);
cursor mysor is select userid,salary from userinfo;
begin
open mysor;
loop
fetch mysor into var_userid,var_salary; 
exit when mysor%notfound;
if var_userid=1 then
update userinfo set salary=salary+salary*0.5 where userid=var_userid;
end if;
if var_userid=2 then
update userinfo set salary=salary+salary*0.6 where userid=var_userid;
end if;
if var_userid=3 then
update userinfo set salary=salary+salary*0.7 where userid=var_userid;
end if;
commit;
end loop;
end;
end;

create or replace procedure abin_2
is
begin
declare
cursor mysor is select * from userinfo;
begin
--open mysor;
for a in mysor
loop
dbms_output.put_line(a.username);
end loop;
--close mysor;
end;
end;
测试代码：
begin
abin_2;
end;

create or replace procedure allcursor
is
begin
declare
type mysor is ref cursor;
var_sor mysor;
var_user userinfo%rowtype;
var_person personinfo%rowtype;
begin
open var_sor for select * from userinfo;
loop
fetch var_sor into var_user;
exit when var_sor%notfound;
dbms_output.put_line(var_user.username);
end loop;
close var_sor;

open var_sor for select * from personinfo;
loop
fetch var_sor into var_person;
exit when var_sor%notfound;
dbms_output.put_line(var_person.pname);
end loop;
close var_sor;
end;
end;

create or replace procedure scursor
is
begin
declare
cursor mycur is select ename,salary from emp1;
r_emp mycur%rowtype;
begin
open mycur;
loop
fetch mycur into r_emp;
exit when mycur%notfound;
dbms_output.put_line('ename='||r_emp.ename||'  '||'salary='||r_emp.salary);
end loop;
close mycur;
end;
end;

测试代码：
begin
scursor;
end;

带参数的游标
  与存储过程和函数相似，可以将参数传递给游标并在查询中使用。这对于处理在某种条件下打开游标的情况非常有用。它的语法如下：

CURSOR cursor_name[(parameter[,parameter],...)] IS select_statement;

定义参数的语法如下：
  Parameter_name [IN] data_type[{:=|DEFAULT} value]

　　与存储过程不同的是，游标只能接受传递的值，而不能返回值。参数只定义数据类型，没有大小。
　　另外可以给参数设定一个缺省值，当没有参数值传递给游标时，就使用缺省值。游标中定义的参数只是一个占位符，在别处引用该参数不一定可靠。

在打开游标时给参数赋值，语法如下：

OPEN cursor_name[value[,value]....];
参数值可以是文字或变量。

例： 

create or replace procedure bcursor
is
begin
declare
cursor c_dept is select * from dept order by deptno;
cursor c_emp(p_dept varchar2) is select ename,salary from emp where deptno=p_dept order by ename;
r_dept dept%rowtype;
v_ename emp.ename%type;
v_salary emp.salary%type;
v_tot_salary emp.salary%type;
begin
open c_dept;
loop
fetch c_dept into r_dept;
exit when c_dept%notfound;
dbms_output.put_line('Department='||r_dept.deptno||',dname--'||r_dept.dname);
v_tot_salary:=0;
open c_emp(r_dept.deptno);
loop
fetch c_emp into v_ename,v_salary;
exit when c_emp%notfound;
dbms_output.put_line('Name:'||v_ename||'salary'||v_salary);
v_tot_salary:=v_tot_salary+v_salary;
end loop;
close c_emp;
dbms_output.put_line('Total Salary for dept:'||v_tot_salary);
end loop;
commit;
close c_dept;
end;
end;

下面是直接拷贝过来的，没有自己亲自运行，上面的全部是经过我的运行正确的代码：

游标FOR循环
在大多数时候我们在设计程序的时候都遵循下面的步骤：
1、打开游标
2、开始循环
3、从游标中取值
那一行被返回
5、处理
6、关闭循环
7、关闭游标
  可以简单的把这一类代码称为游标用于循环。但还有一种循环与这种类型不相同，这就是FOR循环，用于FOR循环的游标按照正常的声明方式声明，它的优点在于不需要显式的打开、关闭、取数据，测试数据的存在、定义存放数据的变量等等。游标FOR循环的语法如下：

FOR record_name IN
(corsor_name[(parameter[,parameter]...)]
| (query_difinition)
LOOP
statements
END LOOP;

下面我们用for循环重写上面的例子：
DECALRE
CURSOR c_dept IS SELECT deptno,dname FROM dept ORDER BY deptno;
CURSOR c_emp (p_dept VARACHAR2) IS
SELECT ename,salary
FROM emp
WHERE deptno=p_dept
ORDER BY ename
v_tot_salary EMP.SALARY%TYPE;
BEGIN
  FOR r_dept IN c_dept LOOP
  DBMS_OUTPUT.PUT_LINE('Department:'|| r_dept.deptno||'-'||r_dept.dname);
  v_tot_salary:=0;
  FOR r_emp IN c_emp(r_dept.deptno) LOOP
  DBMS_OUTPUT.PUT_LINE('Name:' || v_ename || 'salary:' || v_salary);  
  v_tot_salary:=v_tot_salary+v_salary;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('Toltal Salary for dept:'|| v_tot_salary);
END LOOP;
END;

在游标FOR循环中使用查询
  在游标FOR循环中可以定义查询，由于没有显式声明所以游标没有名字，记录名通过游标查询来定义。
DECALRE
v_tot_salary EMP.SALARY%TYPE;
BEGIN
FOR r_dept IN (SELECT deptno,dname FROM dept ORDER BY deptno) LOOP
  DBMS_OUTPUT.PUT_LINE('Department:'|| r_dept.deptno||'-'||r_dept.dname);
  v_tot_salary:=0;
  FOR r_emp IN (SELECT ename,salary
        　　　FROM emp
        　　　WHERE deptno=p_dept
        　　　ORDER BY ename) LOOP
    DBMS_OUTPUT.PUT_LINE('Name:'|| v_ename||' salary:'||v_salary);
    v_tot_salary:=v_tot_salary+v_salary;
  END LOOP;
DBMS_OUTPUT.PUT_LINE('Toltal Salary for dept:'|| v_tot_salary);
END LOOP;
END;