Oracle游标管理

游标提供一种逐条逐条取结果集里面数据的一种机制

--游标的优点：能逐条逐条的处理数据
--缺点：速度慢

--游标的分类：
（1）隐士游标
（2）显示游标
（3）ref游标

--静态游标（隐式游标和显示游标）
--隐式游标
（1）隐士游标的名称就是SQL
（2）游标的属性
--SQL%FOUND的用法：表示当游标影响记录行的时候返回true
--SQL%NOTFOUND的用法:表示当游标没有影响记录行的时候返回true
--SQL%ROWCOUNT的用法：表示游标影响记录行数
--SQL%ISOPEN的用法:对于隐似游标都是false


begin
    --insert into dept values(4,'测试部','长沙');
    DELETE FROM DEPT where deptno in (33,1,2,60,80);--删除部门记录
    if SQL%FOUND THEN --如果上面这个条语句执行成功，那么SQL%FOUND 就会返回TRUE;
       dbms_output.put_line('删除数据成功，影响行数为：'|| SQL%ROWCOUNT);
    elsif SQL%NOTFOUND then
       dbms_output.put_line('没有删除数据');
    end if;
end;




select * from dept;
--SQL%ISOPEN的使用
declare
   x number(10);
begin
   if SQL%ISOPEN THEN  --SQL%ISOPEN 隐似游标中都是false
      x:=1;
   else
      x:=2;
   end if;
   dbms_output.put_line(x);
end;




--sqlerrm 关键字，返回上面语句执行过程中的错误信息
declare          
   varename emp.ename%type;
begin
   select ename into varename from emp;
   exception
         when others then
               dbms_output.put_line(sqlerrm);--sqlerrm 关键字，记录上面语句中的错误信息
end;


SELECT * FROM DEPT;
--隐式游标属性（利用的是上次发给大家的‘员工表.sql’）
set serveroutput on;
BEGIN
UPDATE tsm_employee SET salary=salary+2000
WHERE dept_no='87';
IF SQL%FOUND THEN--SQL%FOUND
DBMS_OUTPUT.PUT_LINE('表已更新'||SQL%ROWCOUNT||'行'); 
  else
  DBMS_OUTPUT.PUT_LINE('没有符合条件的数据');
END IF;
  commit;--提交事务
  exception 
      when others then
        rollback;--回滚事务
        DBMS_OUTPUT.PUT_LINE(sqlerrm);
END;
/


--未找到数据异常NO_DATA_FOUND
DECLARE
  vn_emp_no tsm_employee.emp_no%type;
  vn_name tsm_employee.name%type;
BEGIN
  vn_name:= '&input_name';
  select emp_no into vn_emp_no
  from tsm_employee where name =vn_name;
  DBMS_OUTPUT.PUT_LINE( vn_name||'的雇员编码为'||vn_emp_no);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('没有'||vn_name||'这位雇员');
  when others then
   DBMS_OUTPUT.PUT_LINE(sqlerrm);
END;
/




--select into 时错误TOO_MANY_ROWS
insert into tsm_employee(emp_no,name,position)
values(s_emp_no.nextval, '&input_name','软件开发工程师');


DECLARE
  vn_emp_no tsm_employee.emp_no%type;
  vn_name tsm_employee.name%type;
BEGIN
  vn_name:= '&input_name';
  select emp_no into vn_emp_no
  from tsm_employee where name =vn_name;
  DBMS_OUTPUT.PUT_LINE( vn_name||'的雇员编码为'||vn_emp_no);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('没有'||vn_name||'这位雇员');
  when too_many_rows then
      DBMS_OUTPUT.PUT_LINE('存在多个'||vn_name);
  when others then
   DBMS_OUTPUT.PUT_LINE(sqlerrm);
END;
/


--使用隐士游标注意：
（1）隐士游标的名称SQL
（2）掌握游标的状态 %FOUND %NOTFOUND %ROWCOUNT %ISOPEN
（3）sqlerrm 关键字的使用




--显示游标
declare
    --定义存储游标变量
    vr_emp tsm_employee%rowtype;--记录类型变量，用来保存一个员工的信息
    --定义游标
    cursor cur is select * from tsm_employee where position='部门经理';
begin
     open cur;--打开
     loop--循环
         fetch cur into vr_emp;--提取
         exit when cur%notfound;--退出
         dbms_output.put_line
         ('部门编码:'||vr_emp.dept_no||'员工姓名：'||vr_emp.name||' 职位:'||vr_emp.position);--从游标变量中取某个字段值
     end loop;
     close cur;--关闭
end;
/
 
--带参数的显示游标  
declare
  --定义存储游标变量
  vr_emp tsm_employee%rowtype;
  vn_position tsm_employee.position%type;
  --定义游标
  cursor cur(ip_position tsm_employee.position%type) --参数，特别注意参数的类型不能指定长度
  is select * from tsm_employee where position=ip_position;
  begin
     vn_position:='&vn_position';
     open cur(vn_position);--打开
       loop--循环     
       fetch cur into vr_emp;--提取
       exit when cur%notfound;--退出
       dbms_output.put_line('部门编码:'||vr_emp.dept_no||'员工姓名：'||vr_emp.name||' 职位:'||vr_emp.position);--从游标变量中取某个字段值
       end loop;
     close cur;--关闭
     exception when others then
     dbms_output.put_line(sqlerrm);
  end;
  /
  
  
--使用带参数的游标
--根据输入进来的部门编号，查询此部门中员工的姓名
declare
     varename emp.ename%type;--用来保存员工姓名
     vardeptno1 emp.deptno%type;--用来接收从键盘上输入的一个部门编号
     --声明一个带参数的游标
     cursor mycur(vardeptno emp.deptno%type)
     is select ename from emp where deptno=vardeptno;
begin
     vardeptno1 :='&dno';
     --打开游标
     open mycur(vardeptno1);
     loop 
          --提取游标中的值
          fetch mycur into varename;
          exit when mycur%notfound;--退出循环
          dbms_output.put_Line(varename); 
     end loop;
     --关闭游标
     close mycur;
end; 




--带参数的游标使用
（1）声明游标变量，指向要操作的结果集 cursor mycur(参数 类型) is SQL语句; 
（2）打开游标，必须给定参数值 open mycur(值)
（3）循环迭代取游标指向的值 fetch mycur into 变量;
（4）关闭游标  close mycur;




select * from emp where deptno=20;
  
  
--使用显示游标更新记录
declare
new_sal tsm_salary_records.salary%type;
cursor cur is select salary from tsm_salary_records
 where salary<7000 for update of salary;
begin
    open cur;
       loop
         fetch cur into new_sal;
         exit when cur%notfound;
         update tsm_salary_records set salary=1.1*new_sal  where current of cur;
         dbms_output.put_line('记录已经更新'||1.1*new_sal);
       end loop;
    close cur;
 exception when others then
     dbms_output.put_line(sqlerrm);   
end;
/


select * from emp;
--更新当前游标指向的数据行（更新员工薪水信息，将低于1500的员工薪水调整为1500）
declare
       varsal emp.sal%type;
       cursor mycur is select sal from emp for update;--for update 表示可以更新
begin
       --打开游标
       open mycur;
       loop
            --提取游标值
            fetch mycur into varsal;
            --退出循环
            exit when mycur%notfound;
            if varsal < 1500 then
               update emp set sal=1500 where current of mycur;--更新当前游标指向的数据行
            end if;
       end loop;
       dbms_output.put_line('更行数据完毕，一共影响记录数为：'|| mycur%rowcount);
       exception
             when others then
                  dbms_output.put_line(sqlerrm);
end;






--循环游标
declare
cursor cur is select name,position,dept_no from tsm_employee where dept_no='&input_dept';
begin
        for new_cur in cur
        loop
           dbms_output.put_line('员工：'||new_cur.name||'的职位是：'||new_cur.position);
        end loop;  
 exception when others then
     dbms_output.put_line(sqlerrm);           
end;
/




--循环游标实例
declare
     cursor mycur is select * from emp;
     --emprecord emp%rowtype;
begin
     --打开游标
     --open mycur;
     for emprecord in mycur 
     loop
          --fetch mycur into emprecord;
          --exit when mycur%notfound;--退出循环
          dbms_output.put_line(emprecord.ename||','||emprecord.sal||','||emprecord.job);
     end loop;
     --关闭游标
     --close mycur;
end;
--使用循环游标,不需要显示的打开游标，取值，关闭游标


declare
     cursor mycur is select deptno from dept;
begin
     for deptrecord in mycur 
     loop
          dbms_output.put_line(deptrecord.deptno);
     end loop;
end;


--显示游标：
（1）使用步骤
（2）带参数的游标
（3）使用游标更新数据行
（4）循环游标


select * from emp where deptno=30;
--编写存储过程，从键盘上输入部门编号，采用带参数游标，
--如果部门编号为10，将部门编号为10所有的员工的工资都上调500，
--如果编号为20，将部门编号为20所有的员工的工资都上调800
--其他的都上调1000
create or replace procedure proc_emp(deptno emp.deptno%type)
as     --不能写declare
        --变量的声明
        cursor mycur(dno emp.deptno%type)
        is select sal from emp where deptno=dno for update;--for update 更新
        varsal emp.sal%type;--用来保存薪水
begin
       --处理过程
       --打开游标
       open mycur(deptno);
       --循环迭代取游标值
       loop
            --提取游标值
            fetch mycur into varsal;
            --退出循环
            exit when mycur%notfound;
            --如果部门编号为10，将部门编号为10所有的员工的工资都上调500
            if deptno =10  then
               update emp set sal = sal + 500 where current of mycur;
            elsif deptno=20  then
            --如果编号为20，将部门编号为20所有的员工的工资都上调800
               update emp set sal = sal + 800 where current of mycur;
            else
            --其他的都上调1000
               update emp set sal = sal + 1000 where current of mycur; 
            end if;
       end loop;
       --关闭游标
       close mycur;
       exception 
             when others then
                  dbms_output.put_line(sqlerrm);
end;


--执行存储过程
begin
       proc_emp(30);
end;




--REF游标(动态游标)
 accept tab prompt '你想查看什么信息? 员工信息(E)或部门信息(D)：';--使用对话框输入参数
 --上面语句只能在命令窗口中执行
 
declare
    type cur is ref cursor;--声明游标类型
    /*如果不指定返回类型为弱类型游标，如果指定了返回类型则为强类型，如下所示：*/
    --return emp%rowtype;
    new_cur cur;--定义游标变量 
    p_name varchar2(200);
    selection varchar2(1) := upper(substr('&tab',1,1));--接收对话框输入参数
begin
     if selection ='E' then
       open new_cur for select name from tsm_employee where dept_no='72';
       dbms_output.put_line('=====员工信息表=====');
    elsif selection='D' then
       open new_cur for select dept_name name from tsm_department;
       dbms_output.put_line('=====部门信息表=====');
    else
       dbms_output.put_line('请输入员工信息(E)或部门信息(D)');
       return;
    end if;
    loop--循环提取游标数据方法
        fetch new_cur into  p_name;
        exit when new_cur%notfound;
        dbms_output.put_line( p_name);
    end loop;
    --关闭游标
    close new_cur;
    --异常处理部分
    exception 
          when others then
               dbms_output.put_line(sqlerrm);       
end;
/




--ref游标的使用
（1） 强类型的ref游标
（2） 弱类型的ref游标


--使用步骤：
（1）声明ref游标类型
     type ref_cursor_type is ref cursor [return 类型]; 
（2）声明ref游标类型的变量
     cursor_name ref_cursor_type;
（3）打开游标的时候，确定游标操作的结果集
     open cursor_name for 结果集;
（4）提取游标中的值，放置到变量中
     fetch cursor_name into 变量;
（5）关闭游标
     close cursor_name;
     
--使用ref游标的示例：
declare
     tempStr varchar2(20);--用来从键盘上接收一个字符
     --声明ref游标类型
     type myrefType is ref cursor;--弱类型的ref游标
     --声明ref游标类型的变量
     mycur myrefType;
     t_name varchar2(50);--这个变量，可能保存的是员工的姓名，也有可能保存部门名称
begin
     tempStr := upper(substr('&tempStr',1,1));--从键盘上接收一个字符
     --如果输入进来的是E，那么查询员工姓名，打印出来
     if tempStr = 'E' then
        open mycur for select ename from emp;
     --如果输入进来的是D，那么查询部门名称，打印出来
     elsif tempStr = 'D' then
        open mycur for select dname from dept; 
     else 
        dbms_output.put_line('您输入的不是E或者D，请重新输入');
     end if;    
     loop
         --提取游标值
         fetch mycur into t_name;
         exit when mycur%notfound;--退出循环
         dbms_output.put_line(t_name);
     end loop;  
     --关闭游标
     close mycur;
end;


--强类型
set serveroutput on;
declare
  r_emp tsm_employee%rowtype;
  d_emp tsm_department%rowtype;
  type c_type is ref cursor return tsm_employee%rowtype;
  v_cur c_type;
  p_salary number;
  vc_sqlstring varchar2(4000);
begin   
  vc_sqlstring:='select * from tsm_employee where position='||''''||'项目经理'||''''||'';
  open v_cur for vc_sqlstring;
   dbms_output.put_line(vc_sqlstring );
  loop
     fetch v_cur into r_emp;
     exit when v_cur%notfound;
     dbms_output.put_line(':编号:'||r_emp.emp_no||'姓名'||r_emp.name );
  end loop;
  close v_cur;
  
  vc_sqlstring:='select * from tsm_department where dept_no='||''''||'72'||'''';
 open v_cur for vc_sqlstring;
   dbms_output.put_line(vc_sqlstring );
  loop
     fetch v_cur into d_emp;
     exit when v_cur%notfound;
     dbms_output.put_line('部门名称:'||d_emp.dept_name );
  end loop;
  close v_cur;
 exception when others then
     dbms_output.put_line(sqlerrm);     
end;
/




declare
  v_no varchar2(20);
  type c_type is ref cursor;
  return tsm_employee%rowtype;
  cur c_type;
  p_salary number;
  vc_sqlstring varchar2(4000);
begin   
  vc_sqlstring:='select position from tsm_employee where rownum<5';
  open cur for vc_sqlstring;
   dbms_output.put_line(vc_sqlstring );
  loop
     fetch cur into v_no;
     exit when cur%notfound;
     dbms_output.put_line('职位:'||v_no );
  end loop;
  close cur;


 exception when others then
     dbms_output.put_line(sqlerrm);     
end;
/




--强类型的ref游标的示例
declare
     --声明ref游标类型
     type myrefType is ref cursor return dept%rowtype;
     --声明ref游标类型的变量
     mycur myrefType;
     --声明一个记录类型的变量
     empRecord emp%rowtype;
     deptRecord dept%rowtype;
begin
     --打开游标
     open mycur for select * from dept;
     --循环取游标值
     loop
          fetch mycur into deptRecord;
          exit when mycur%notfound;
          dbms_output.put_line(deptRecord.dname);
     end loop;
     --关闭游标
     close mycur;
end;






declare
     type myRecordType is record(
          name varchar2(20)
     );
     --声明记录类型变量
     myrec myrecordType;
     --声明ref游标类型
     type myrefType is ref cursor return myrecordType;
     --声明ref游标类型的变量
     mycur myrefType;
     t_name varchar2(50);
begin
     --打开游标
     open mycur for select dname from dept;
     --循环取游标值
     loop
          fetch mycur into t_name;
          exit when mycur%notfound;
          dbms_output.put_line(t_name);
     end loop;
     --关闭游标
     close mycur;
end;




--使用游标变量执行动态SQL
declare
     --声明类型
     type myreftype is ref cursor;
     --声明变量
     mycur myreftype;
     empRecord emp%rowtype;
     empno emp.empno%type;
begin
     empno :='&eno';
     --打开游标
     open mycur for 'select * from emp where empno>:eno' using empno;
     loop
          fetch mycur into empRecord;
          exit when mycur%notfound;
          dbms_output.put_Line(empRecord.ename);
     end loop;
     --关闭游标
     close mycur;
end;




--游标
--静态游标：在声明游标的时候确定了游标操作的结果集
  隐士游标
     （1）隐士游标名称为SQL
     （2）掌握属性（状态）的用法： %found %notfound %rowcount %isopen
     （3）sqlerrm 关键字
     
  显示游标
     （1）使用的四个步骤：声明，打开，提取，关闭
     （2）带参数的游标
     （3）循环游标
     
     
--动态游标：只有在打开游标的时候才能确定操作的结果集
  强类型的ref游标  [return 类型]
  弱类型的ref游标  
  
  重点掌握使用步骤
  --使用步骤：
（1）声明ref游标类型
     type ref_cursor_type is ref cursor [return 类型]; 
（2）声明ref游标类型的变量
     cursor_name ref_cursor_type;
（3）打开游标的时候，确定游标操作的结果集
     open cursor_name for 结果集;
（4）提取游标中的值，放置到变量中
     fetch cursor_name into 变量;
（5）关闭游标
     close cursor_name;