oracle 在执行sql 语句时，总是需要创建一快内存区域，在这块内存区域称为上下文区域。早上下文区域中包含了出路语句的信息，这些信息包含当前语句已经处理了多少行，指向被分析语句的指针和查询语句返回的数据行集。当在pl/sql 中执行select 和dml 语句时，如果只查询单行数据，比如使用select into 语句或执行dml 语句时，oracle 会分配隐含的游标。吐过吃药处理select 语句返回的多行记录，必须要显式地定义游标。
1. 在plsql 中使用游标 在显式使用游标时，总是要先在declare 区对游标进行定义，然后打开游标，从游标中提取数据，在所有的操作完成之后关闭游标。通过灵活地对游标进行控制，可以比隐式游标具有更多的编程能力，也可以具有更高的效率。 declare empow emp%rowtype; -- 定义保存游标检索结果行的记录变量 cursor emp_cur is select * from emp where deptno is not null; begin open emp_cur; -- 打开游标 loop -- 循环检索游标 fetch emp_cur into emprow; -- 提取游标内容 -- 输出检索到的游标行的信息 dbms_output.put_line( '员工编号：' || emprow.empno ||'' ||'员工名称：' ||emprow.ename ); exit when emp_cur%notfound; --当游标数据检索完成后退出循环 end loop; close emp_cur; -- 关闭游标 end;
2.隐式游标 begin update emp set comm = comm* 1.12 where empno = 7369; -- 更新员工编号魏7369的员工信息 -- 使用隐式游标属性判断已更新的行数 dbms_output.put_line(sql%rowcount||'行被更新'); -- 如果没有任何更新 if sql%notfound then --- 显示未更新的信息 dbms_output.put_line('不能更新员工号为7369的员工！'); end if; -- 向数据库提交更改 commit; exception when others then dbms_output.put_line(sqlerrm); -- 如果出现异常，显示异常信息 end;
3.简单的游标定义语句 declare cursor emp_cursor --定义一个查询emp表中部门编号为20的游标 is select * from emp where deptno =20; begin null; end;
4.根据变量查询emp表 declare v_deptno number; cursor emp_cursor -- 定义一个查询emp表中部门编号为20的游标 is select * from emp where deptno =v_deptno; begin v_deptno :=20; open emp_cursor; --打开游标 if emp_cursor%isopen then dbms_output.put_line('游标已经被打开'); end if; end;
5. 声明游标参数 declare cursor emp_cursor(p_deptno in number) -- 定义游标并执行游标参数 is select * from emp where deptno =p_pdeno; begin open emp_cursor(20); end;
6. 指定游标的返回类型 declare -- 声明游标并指定游标返回值类型 cursor emp_cursor(p_deptno in number) return emp%rowtype is select * from emp where deptno= p_deptno; begin open emp_cursor(20); -- 打开游标 end;
7.%isopen 游标属性使用示例 declare cursor emp_cursor (p_deptno in number) -- 定义游标并执行游标参数 is select * from emp where deptno=p_deptno; begin if not emp_cursor%isopen then -- 如果游标还没有被打开 open emp_cursor(20); -- 打开游标 end if; if emp_cursor%isopen then -- 判断游标状态，显示状态信息 dbms_output.put_line('游标已经被打开'); else dbms_output.put_line('游标还没有被打开')； end if; close emp_cursor; end;
8.%found 游标属性使用示例 declare emp_row emp%rowtype; --定义游标值存储变量 cursor emp_cursor(p_deptno in number) -- 定义游标并指定游标参数 is select * from emp where deptno =p_deptno; begin if not emp_cursor%isopen; then open emp_cursor(20); --打开游标 end if; if emp_cursor%found is null --在使用fetch提取游标数据之前，值为null then dbms_output.put_line('%found 属性为null'); --- 输出提示信息 end if; loop -- 循环提取游标数据 fetch emp_cursor into emp_row; -- 使用fetch 语句提取游标数据 -- 每循环一次判断%found 属性值，如果该值为false，表示提取完成，将退出循环 exit when not emp_cursor%found; end loop; close emp_cursor; end;
9.%notfound 游标属性使用示例 declare emp_row emp%rowtype; -- 定义游标值存储变量 cursor emp_cursor(p_deptno in number) --定义游标并指定游标参数 is select * from emp where deptno =p_deptno; begin open emp_cursor(20); -- 打开游标 if emp_cursor%notfound is null -- 在使用fetch提取游标数据之前，值为null then dbms_output.put_line('%notfound属性为null'); --输出提示信息 end if; loop -- 循环提取游标数据 fetch emp_cursor into emp_row; --使用fetch 语句提取游标数据 --每循环一次判断%found 属性值，如果该值为false,表示提取完成，将退出循环 exit when emp_cursor%notfound; end loop; close emp_cursor; end;
10 %rowtype 游标属性使用示例 declare emp_row emp%rowtype; --定义游标值存储变量 cursor emp_cursor(p_deptno in number) -- 定义游标并指定游标参数 is select * from emp where deptno =p_deptno; begin open emp_cursor(20); -- 打开游标 loop -- 循环提取游标数据 fetch emp_cursor into emp_row; -- 使用fetch 语句提取游标数据 -- 每循环一次判断%found 属性值，如果该值为false,表示提取完成，将退出循环 exit when emp_cursor%notfound; dbms_output.put_line('当前已提取的行数为'||emp_cursor%rowcount||'行！'); end loop; close emp_cursor; end;
11.使用fetch语句提取游标数据 declare deptno dept.deptno%type; --定义保存游标数据的变量 dname dept.dname%type; loc dept.loc%type; dept_row dept%rowtype; --定义记录变量 cursor dept_cur is select * from dept; -- 定义游标 begin open dept_cur; --打开游标 loop if dept_cur%rowcount<=4 then -- 判断如何当前提取的游标小于等于4行 fetch dept_cur into depty_row; --提取游标数据到记录类型 if dept_cur%found then -- 如果fetch 到数据，则进行显示 dbms_output.put_line(dept_row.deptno||''||dept_row.dname||''||dept_row.loc); end if; else fetch dept_cur into deptno,dname,loc; --否则提取记录到变量列表中 if dept_cur%found then -- 如果提取到数据进行显示 dbms_output.put_line(deptno||''||dname||''||loc); end if; end if; exit when dept_cur%notfound; -- 判断是提取完成 end loop; close dept_cur end;
12 使用bulkcollect 语句批量提取游标数据 declare type depttab_type is table of dept%rowtype; --定义dept行类型的嵌套表类型 depttab depttab_type; --定义嵌套表变量 cursor deptcur is select * from dept; --定义游标 begin open deptcur; fetch deptcur bulk collect into depttab; -- 使用bulk collect into 子句批次插入 close deptcur; -- 关闭游标 for i in 1 .. depttab.count -- 循环嵌套表变量中的数据 loop dbms_output.put_line( depttab(i).deptno || '' ||depttab(i).dname ||'' ||depttab(i).loc ); end loop; close deptcur; --关闭游标 end;
13 使用 bulk collect limit 语句批量提取游标数据 declare type dept_type is varray(4) of dept%rowtype; --定义变长数组类型 depttab dept_type ; -- 定义变长数组变量 cursor dept_cursor is select * from dept; v_rows int :=4; --使用limit 限制的行数 v_count int :=0; --保存游标提取过的数据 begin open dept_cursor; --打开游标 loop --循环提取游标 --每次提取4行数据到边长数组中 fetch dept_cursor bulk collect into depttab limit v_rows; exit when dept_cursor%notfound; -- 没有游标数据时退出 dbms_output.put('部门名称：'); -- 输出部门名称 --循环提取变长数组数据，因为边长数组只能存放4个元素，因此不能越界读取 for i in 1 .. (dept_cursor%rowcount - v_count) loop dbms_output.put_line(depttab(i).dname||''); -- 输出部门名称 end loop; close dept_cursor; end;
14.基本的loop循环 declare dept_row dept%rowtype; --定义游标结果记录类型 cursor dept_cursor is select * form dept; -- 定义游标变量 begin open dept_cursor; -- 打开游标 loop fetch dpet_cursor into dept_row; -- 提取游标数据 exit when dept_cursor%notfound; -- 退出循环的控制语句 dbms_output.put_line('部门名称：'||dept_row.dname); end loop; close dept_cursor;--关闭游标 end;
15 使用while 循环检索游标数据 declare dept dept_row dept%rowtype; -- 定义游标结果记录变量 cursor dept_cursor is select * from dept; -- 定义游标变量 begin open dept_cursor; -- 卡开游标 fetch dept_cursor into dept_row;-- 提取游标数据 while dept_cursor%found loop dbms_output.put_line('部门名称：'||dept_row.dname); fetch dept_cursor into dept_row; --提取游标数据 end loop; close dept_cursor; end;
16.使用游标for 循环检索数据 declare cursor dept_cursor is select * from dept; -- 定义游标变量 begin for dept_row in dept_cursor loop dbms_output.put_line('部门名称：'||dept_row.dname); end loop; end;
17 游标for 循环子查询语句 begin for dept_row in (select * from dept) loop -- 在游标for 循环中检索数据 dbms_output.put_line('部门名称：'||dept_row.dname); end loop; end;
18.使用游标哦更新数据 declare cursor emp_cursor(p_deptno in number) is select * from emp where deptno=p_deptno for update; -- 使用for update 子句添加互斥锁 begin for emp_row in emp_cursor(20) -- 使用游标for循环检索游标 loop update emp set comm =comm *1.12 where current of emp_cursor; -- 使用where current of 更新游标数据 end loop; commit; -- 提交更改 end;
19.使用游标删除数据 declare cursor emp_cursor(p_empno in number) is select * from emp where emp=p_empno for update; -- 使用for update 子句添加互斥锁 begin for emp_row in emp_cursor(7369) -- 使用游标for 循环 检索游标 loop delete from emp where current of emp_cursor; -- 使用where current of 删除游标数据 end loop; end;
20 游标变量使用示例 declare type emp_type is ref cursor return emp%rowtype; --定义游标变量类型 emp_cur emp_type; -- 声明游标变量 emp_row emp%rowtype; --定义游标结果值变量 begin open emp_cur for select * from emp; -- 打开游标 loop fetch emp_cur into emp_row; -- 循环提取游标变量 exit when emp_cur%notfound; -- 循环退出检测 dbms_output.put_line('员工名称：'||emp_row.ename); end loop; end;
21. 定义游标变量 declare type emp_type is ref cursor return emp%rowtype; -- 定义游标类型 type gen_type is ref cursor; emp_cur tmp_type; gen_cur gen_type; begin open mep_cur for select * from emp where deptno=20; end;
22.打开游标变量 declare type emp_curtype is ref cursor; -- 定义游标类型 emp_cur emp_curtype; -- 声明游标类型的变量 begin open emp_cur for select * from emp; -- 打开游标，查询emp 所有列 open emp_cur for select empno from emp;--打开游标，查询emp表empno 列 open emp_cur for select deptno from dept;-- 打开游标，查询dept表deptno 列 end;
23.使用fetch 语句提取游标变量数据 declare type emp_type is ref cursor return emp%rowtype; -- 定义游标类型 emp_cur emp_type; -- 声明游标变量 emp_row emp%rowtype; begin if not emp_cur%isopen then open emp_cur for select * from emp where deptno=20; -- 打开游标变量 end if; loop fetch emp_cur into emp_row; -- 提取游标变量 exit when emp_cur%notfound; -- 如果提取完成则退出循环 dbms_ourput.put_line('员工名称：'++emp_row.ename||'员工职位：'||emp_row.job); --输出员工信息 end loop; end;
24. 使用colse语句关闭游标数据 declare type emp_type is ref cursor return emp%rowtype; --定义游标类型 emp_cur emp_type; -- 声明游标变量 emp_row emp%rowtype; begin open emp_cur for select * from emp where deptno=20; -- 打开游标 fetch emp_cur into emp_row; -- 提取游标 while emp_cur%found loop dbms_output.put_line('员工名称：'||emp_row.ename); fetch emp_cur into emp_row; end loop; close emp_cur; --关闭游标 end;
25.处理invalid_cursor异常 declare type emp_curtype is ref cursor; --定义游标类型 emp_cur1 emp_curtype; -- 声明游标类型的变量 emp_cur2 emp_curtype; emp_row emp%rowtype; -- 定义保存游标数据的记录类型 begin open emp_cur1 for select * from emp where deptno=20; -- 打开第一个游标 fetch emp_cur1 into emp_row; -- 提取并显示游标信息 dbms_output.put_line('员工名称：'||emp_row.ename||'部门编号：'||emp_row.deptno); fetch emp_cur2 into emp_row; -- 提取第二个游标变量讲引发异常 exception when invalid_cursor then -- 异常处理 emp_cur2:emp_cur1; -- 将emp_cur1 指向的查询区域赋给emp_cur2 fetch emp_cur2 into emp_row; -- 现在emp_cur1 与emp_cur2 指向相同的查询 dbms_output.put_line('员工名称：'emp_row.ename||'部门编号'||emp_row.deptno); -- 重新打开emp_cur2 游标变量，利用相同的查询区域 open emp_cur2 for select * from emp where deptno=30; fetch emp_cur1 into emp_row;-- 由于emp_cur1与emp_cur2 共享相同的查询区域，因此结果相同 dbms_output.put_line('员工名称：'||emp_row.ename||'部门编号：'||emp_row.deptno); end;
26 处理rowtype_mismatch 异常 declare type emp_curtype is ref cursor; -- 定义游标类型吧 emp_cur emp_curtype; -- 声明游标类型的变量 rmp_row emp%rowtype; -- 声明游标数据结果类型 dept_row dept%rowtype; begin open emp_cur for select * from emp where deptno=20; -- 打开游标变量 fetch emp_cur into dept_row; -- 提取到一个不匹配的类型中 exception when rowtype_mismatch then -- 处理rowtype_mismatch异常 fetch emp_cur into emp_row; -- 再次提取游标变量数据，输出结果 dbms-output.put_line('员工名称：'||emp_row.ename||emp_ename||'部门编号'||emp_row.deptno); end;
27 使用sys_refcursor类型 declare emp_cur sys_refcursor; --定义弱类型游标变量 emp_row emp%rowtype; dept_row dept%rowtype; begin open emp_cur for select * from emp where deptno=20; -- 打开游标数据 fetch emp_cur into dept_row; exception when rowtype_mismatch then --处理rowtype_mismatch 异常 fetch emp_cur into emp_row; -- 重新提取并输出异常结果 dbms_output.put_line('员工名称：'||emp_row.ename||'部门编号'||emp_row.deptno); end;
28.在包中使用游标变量 -- 创建包规范 create or replace package emp_data_action as type emp_type is ref cursor return emp%rowtype; --定义强类型游标类型 --定义使用游标变量的子程序 procedure getempbydeptno(emp_cur in out emp_type,p_deptno number); end emp_data_action; -- 实现包体 create or replace package body emp_data_action as -- 创建在包规范定义的过程 procedure getempbydeptno(emp_cur in out emp_type,p_deptno number) is emp_row emp%rowtype; begin open emp_cur for select * from emp where deptno=p_deptno; loop fetch emp_cur into emp_row; --提取数据 exit when emp_cur%notfound; --输出游标数据 dbms_output.put_line('员工名称：'||emp_row.ename||'部门编号：'||emp_row.deptno); end loop; ccose emp_cur; end; end emp-data_action;