游标
游标管理技巧
游标类型（重点）：
1、隐式游标：在pl/sql程序中执行DML SQL语句时，自动创建隐式游标，不被程序打开或关闭。
|
隐式游标的属性： 1.%FOUND-sql语句影响一行或者多行为true 2.%NORFOUND-sql语句没有影响任何行的时候返回值true 3.%ROWCOUNT-sql语句影响的行数 4.%ISOPEN-游标是否打开，隐式游标时钟为false |
案列：编写程序判断游标是否有数据，如果有数据，就输出数据，且输出“发现数据”；如果没有数据，就提示用户“输出没有数据”
|
declare i number;--相当于int i； begin i := 2;--相当于i=2; if sql%found then dbms_output.put_line('发现数据'); else dbms_output.put_line('没有数据'); end if; end; |
2、显式游标：处理返回多条语句的查询
如果要使用创建好的游标，需要先打开游标，语法结构：
|
Open游标名； |
Close游标名； |
提取游标中的数据，需要使用fetch语句，语法结构如下：
|
Fetch 游标名 into 变量名1,变量名2... 或fetch 游标明 into 记录型变量名 |
案列：写一个游标来显示部门为10的所有雇员名及工资
|
declare cursor mc is select ename,sal from emp where deptno=10; v_ename emp.ename%TYPE; v_sal emp.sal%TYPE; begin open mc; -通过循环逐行提取数据 loop fetch mc into v_ename,v_sal; exit when mc%NOTFOUND; dbms_output.put_line('姓名：'||v_ename||' 薪水：'||v_sal); end loop; close mc; end; |
使用fetch...bulk collect into提取所有数据
案列：显示emp表中10部门员工的姓名
|
declare cursor mc is select * from emp where deptno=&deptno; type emp_table_emp is table of emp%ROWTYPE;--rowtype是逐行提取 emp_table emp_table_emp; begin open mc; --从游标中一次性将数据提取 fetch mc bulk collect into emp_table; close mc; for i in 1..emp_table.count loop dbms_output.put_line('姓名:'||emp_table(i).ename); end loop; end; |
为了限制提取的行数可以使用limit语句 fetch 游标 bulk connect into...limit 行数
案列：
|
declare cursor mc is select * from emp; type emp_array_type is array(6) of emp%ROWTYPE; emp_array emp_array_type; begin open mc; loop fetch mc bulk collect into emp_array limit &rows; for i in 1..emp_array.count loop dbms_output.put_line('姓名:'||emp_array(i).ename||' ,工资:'||emp_array(i).sal); end loop; dbms_output.new_line; exit when mc%NOTFOUND; end loop; close mc; end; |
案列：基于游标定义的记录变量
|
declare cursor mc is select * from emp; --定义记录变量 v_emp emp%ROWTYPE; begin open mc; loop fetch mc into v_emp; exit when mc%NOTFOUND; dbms_output.put_line('姓名:'||v_emp.ename); end loop; close mc; end; |
案列：for循环游标（不需要打开和关闭游标）
|
declare cursor mc is select * from emp; type emp_table_type is table of emp%ROWTYPE; emp_table emp_table_type; begin for emp_table in mc loop dbms_output.put_line(‘姓名:’||emp_table.ename); end loop; end; |
案列：带参数的游标（接受用户输入的部门编号，用for循环游标打印出所有部门的雇员信）
|
declare cursor mc(p_deptno number :=&deptno) is select * from emp where deptno=p_deptno; v_emp emp%ROWTYPE; begin open mc; loop fetch mc into v_emp; exit when mc%NOTFOUND; dbms_output.put_line('姓名:'||v_emp.ename||' ,薪水:'||v_emp.sal); end loop; close mc; end; |
使用游标更新数据
案例：给工资高于5000的员工减薪500，显示员工的全部信息（包括薪水前，减薪后）
|
declare cursor mc is select ename,sal from emp where sal>=5000 for update; v_ename emp.ename%type; v_sal emp.sal%type; v_new number; begin open mc; loop fetch mc into v_ename,v_sal; exit when mc%NOTFOUND; if v_sal>=5000 then update emp set sal=v_sal+500 where current of mc; v_new := v_sal+500; dbms_output.put_line('姓名:'||v_ename||' ,原薪水:'||v_sal||'，减薪后:'||v_new); else dbms_output.put_line('姓名:'||v_ename||' ,原薪水:'||v_sal); end if; end loop; close mc; end; |
案列：删除数据，删除部门20的员工
|
declare cursor mc is select * from emp where deptno=20 for update; v_emp emp%ROWTYPE; begin open mc; loop fetch mc into v_emp; exit when mc%NOTFOUND; if v_emp.deptno=20 then delete emp where current of mc; end if; end loop; close mc; end; |
3、Ref游标：用于处理运行时才能确定的动态 sql查询的结果
案列：使用游标更新数据,给工资低于2000的员工加200的工资
|
declare type emp_mc is ref cursor; mc emp_mc; v_emp emp%rowtype; begin open mc for select * from emp for update; loop fetch mc into v_emp; exit when mc%NOTFOUND; if v_emp.sal>4500 then update emp set sal=v_emp.sal+200 where empno=v_emp.empno;--注意:此处不能用current of mc dbms_output.put_line('姓名:'||v_emp.ename||'，薪水:'||v_emp.sal); end if; end loop; close mc; end; |
注意：where current of 游标名只能用在静态游标中
