Oracle开发学习篇之游标

declare
        v_count NUMBER;
begin
    select count(*) into v_count from emp;
    dbms_output.put_line('sql%rowcount ='  || sql%rowcount);
end;
/


declare
begin
    INSERT into DEPT(DEPTNO,DNAME,LOC) VALUES(80,'firefox','shanghai');
    dbms_output.put_line('sql%rowcount =' || sql%rowcount);
end;
/


declare
begin
    update DEPT SET DNAME = 'firefox.COMMIT';
    dbms_output.put_line('sql%rowcount' || sql%rowcount);
end;
/
--通过以上几个简单的程序可以发现隐式游标一直存在，但是隐式游标有很多种;



declare
 v_empRow emp%ROWTYPE ; --保存emp一行数据;
begin
select * into v_empRow from emp where EMPNO = 7369;
if SQL%FOUND THEN
    dbms_output.put_line('name is :' || v_empRow.ENAME || ',JOB is :' ||
        v_empRow.JOB);
end if;
end;
/

--单行隐士游标
declare
begin
    update emp SET SAL=SAL*1.2 where 1=2;
        if sql%FOUND THEN
            dbms_output.put_line('update rowcount:' || sql%rowcount);
        else
            dbms_output.put_line('NOT FOUND data update');
        end if;
    end;
/
--多行隐士游标;


--范例:定义显示游标;
declare
    cursor cur_emp is select * from emp;
    v_empRow emp%ROWTYPE;
begin
    --游标如果要操作一定要保证其已经打开
    if cur_emp%isopen THEN
        NULL; --什么都不做
    else
        open cur_emp; --打开游标
    end if;
    --默认情况下游标在第一行记录上
    fetch cur_emp into v_empRow; --取得当前行数据
    while cur_emp%FOUND loop
    dbms_output.put_line('rowmeber is :' || cur_emp%rowcount ||',name is : ' || v_empRow.ename ||
', JOB is :'|| v_empRow.JOB || ',sal is : ' || v_empRow.sal    );
    fetch cur_emp into v_empRow; --把游标指向下一行;
    end loop;
    close cur_emp;
end;
/


--使用loop循环
declare
    cursor cur_emp is select * from emp;
    v_empRow emp%ROWTYPE;
begin
    --游标如果要操作一定要保证其已经打开
    if cur_emp%isopen THEN
        NULL; --什么都不做
    else
        open cur_emp; --打开游标
    end if;
    --默认情况下游标在第一行记录上
    loop
        fetch cur_emp into v_empRow;
        exit when cur_emp%NOTFOUND; --没有数据推出循环
    dbms_output.put_line('rowmeber is :' || cur_emp%rowcount ||',name is : ' || v_empRow.ename ||
', JOB is :'|| v_empRow.JOB || ',sal is : ' || v_empRow.sal    );
    end loop;
    close cur_emp;
end;
/

--使用for循环进行游标操作：
declare
    cursor cur_emp return emp%ROWTYPE is select * from emp;
    v_empRow emp%ROWTYPE;
begin
    for v_empRow in cur_emp loop
        exit when cur_emp%NOTFOUND; --没有数据推出循环
    dbms_output.put_line('rowmeber is :' || cur_emp%rowcount ||',name is : ' || v_empRow.ename ||
', JOB is :'|| v_empRow.JOB || ',sal is : ' || v_empRow.sal    );
    end loop;
end;
/

--偷懒for循环进行游标操作;
declare
begin
    for v_empRow in (select * from emp)loop
    dbms_output.put_line(',name is : ' || v_empRow.ename ||
', JOB is :'|| v_empRow.JOB || ',sal is : ' || v_empRow.sal    );
    end loop;
end;
/

--在动态select中使用游标;
declare 
    v_lowsal emp.sal%TYPE := &inputlowsal;
    v_highsal emp.sal%TYPE := &inputhighsal;
    cursor cur_emp is select * from emp where sal between v_lowsal and
v_highsal;
begin
    for emp_row in cur_emp loop
    dbms_output.put_line(cur_emp%rowcount || ',name is : ' ||
        emp_row.ename || ', JOB is : ' || emp_row.JOB || ', sal : '
        || emp_row.sal);
    end loop;
end;
/

--
declare
    cursor cur_emp(p_dno emp.DEPTNO%TYPE) is select * from
    emp where DEPTNO=p_dno;
begin
    for emp_row in cur_emp(&inputDeptno) loop
    dbms_output.put_line(cur_emp%rowcount || ', name is : ' || 
    emp_row.ename || ', JOB is : ' || emp_row.JOB || 
    'sal is :' || emp_row.sal);
    end loop;
end;
/


范例：使用嵌套表接收游标数据
declare
    TYPE dept_nested is table of dept%ROWTYPE;
    v_dept dept_nested;
    cursor cur_dept is select * from DEPT;
begin
    if cur_dept%isopen THEN
        null;
    else
        open cur_dept;
end if;
    fetch cur_dept bulk collect into v_dept;
    for x in v_dept.first ..  v_dept.last loop
    dbms_output.put_line('member is :' || v_dept(x).DEPTNO || ', name is : ' ||
        v_dept(x).DNAME || ',localtion is : '|| v_dept(x).loc);
    end loop;
close cur_dept;
end;
/

范例：将游标数据保存在可变数组之中;
declare
    TYPE dept_varray  is varray(4) of dept%ROWTYPE; --定义可变数组
    v_dept dept_varray;
    cursor cur_dept is select * from DEPT;
    v_rows  NUMBER := 4; --每次提取2行记录;
    v_count NUMBER := 1; --每次少显示1行记录;
begin
    if cur_dept%isopen THEN
        null;
    else
        open cur_dept;
end if;
    fetch cur_dept bulk collect into v_dept limit v_rows;
    for x in v_dept.first  ..  (v_dept.last - v_count) loop
    dbms_output.put_line('member is :' || v_dept(x).DEPTNO || ', name is : ' ||
        v_dept(x).DNAME || ',localtion is : '|| v_dept(x).loc);
    end loop;
close cur_dept;
end;
/
 

--游标更行操作
declare
cursor cur_emp is select * from emp;
begin
for emp_row in cur_emp loop
if emp_row.deptno = 10 then
if emp_row.sal*1.5 < 5000 then
update emp set sal = sal*1.5
where empno=emp_row.empno;
else
update emp set sal=5000 
where empno=emp_row.empno;
end if;
elsif emp_row.deptno = 20 then
if emp_row.sal*1.22 < 5000 then
update emp set sal = sal*1.22
where empno=emp_row.empno;
else
update emp set sal=5000 
where empno=emp_row.empno;
end if;
elsif emp_row.deptno = 30 then
if emp_row.sal*1.39 < 5000 then
update emp set sal = sal*1.39
where empno=emp_row.empno;
else
update emp set sal=5000 
where empno=emp_row.empno;
end if;
else 
null;
end if;
end loop;
exception
when others then
dbms_output.put_line('SQLCODE =' || SQLCODE);
dbms_output.put_line('SQLERRM =' || SQLERRM);
ROLLBACK;
end;
/

范例:创建一个不等待的游标;
declare
cursor cur_emp is select * from emp where deptno=10 for update nowait;
begin
for emp_row in cur_emp loop
update emp set sal=9999 
where empno=emp_row.empno;
end loop;
end;
/


范例:使用where current of 子句:
declare
cursor cur_emp is select * from emp where deptno=10 for update;
begin
for emp_row in cur_emp loop
update emp set sal=9999 
where current of cur_emp; --表示更新当前行的数据;
end loop;
end;
/


范例:使用游标删除表中数据：
declare
cursor cur_emp is select * from emp where deptno=10 for update
of sal,comm;
begin
for emp_row in cur_emp loop
delete from
emp 
where current of cur_emp; --表示更新当前行的数据;
end loop;
end;
/


范例:创建一个多表查询的操作; --for update
declare 
cursor cur_emp is
select e.ename,e.job,e.sal,d.dname,d.loc
FROM emp e , dept d
where e.deptno=d.deptno
and e.deptno=10 for update;
begin
for emp_row in cur_emp loop
update emp set sal=9999 where current of cur_emp;
end loop;
end;
/

 

declare 
cursor cur_emp is
select e.ename,e.job,e.sal,d.dname,d.loc
FROM emp e , dept d
where e.deptno=d.deptno
and e.deptno=10 for update of sal;
begin
for emp_row in cur_emp loop
update emp set sal=9999 where current of cur_emp;
end loop;
end;
/

 

 

declare
type dept_ref is ref cursor return dept%ROWTYPE; --定义强类型游标
cur_dept dept_ref;    --定义游标变量
v_deptRow dept%ROWTYPE;    --定义行类型
begin
open cur_dept for select * from dept; --打开游标并决定游标类型
loop
fetch cur_dept into v_deptRow; --取得游标类型
exit when cur_dept%NOTFOUND; --如果没有数据就退出
dbms_output.put_line('name is : ' || v_deptRow.dname ||
',localtion : ' || v_deptRow.loc);
end loop;
close cur_dept;
end;
/

 


declare
type cursor_ref is ref cursor; --定义弱类型游标
cur_var cursor_ref;    --定义游标变量
v_deptRow dept%ROWTYPE;    --定义行类型
v_empRow emp%ROWTYPE; --定义行类型;
begin
open cur_var for select * from dept; --打开游标并决定游标类型
loop
fetch cur_var into v_deptRow; --取得游标类型
exit when cur_var%NOTFOUND; --如果没有数据就退出
dbms_output.put_line('1. name is : ' || v_deptRow.dname ||
',localtion : ' || v_deptRow.loc);
end loop;
close cur_var;
open cur_var for select * from emp where deptno = 10; -- 打开游标;
loop
fetch cur_var into v_empRow;
exit when cur_var%NOTFOUND;
dbms_output.put_line('2. name is :' || v_empRow.ename ||
'job is : ' || v_empRow.job);
end loop;
close cur_var;
exception
when ROWTYPE_MISMATCH then
dbms_output.put_line('游标数据类型不匹配异常. SQLCODE' || SQLCODE ||
'SQLERRM' || SQLERRM);
end;
/