语法:
CURSOR 光标名 [(参数名 数据类型[ 参数名 数据类型]…)] IS SELECT 语句;
光标用于存储一个查询返回的多行数据。
例如:
Cursor c1 is select ename from emp; //c1这个光标代表了所有员工姓名的集合
光标使用之前需要open,
Open c1; //打开光标执行查询。
使用fetch从光标中取值。光标在结果集中也维护一个指针,Java的结果集默认情况下是指向第一个元素前面的位置,但是光标不是这样的,初始状态下,光标指向第一个位置,fetch的作用就是将指针指向的这个元素的值取出,并将光标的位置向后移动。返回来的值,通过into关键字赋给后面的变量。
使用完后需要close c1
--例如:使用游标查询员工的姓名和工资,并打印。
/*
光标的属性:
%isopen是否打开
%rowcount记录数
%notfound没有找到记录,如果没有找到记录就是true,如果找到了就是false
*/
Set serveroutput on
Declare
--定义光标。代表员工的姓名和工资。
Cursorcemp is select ename,sal from emp;
--定义好后,最好养成一个习惯,因为要从光标中取值赋给变量,所以紧接着要为光标定义变量,
--这里需要姓名和薪水。
Penameemp.ename%type;
Psalemp.sal%type;
Begin
Opencemp;
loop
--取一条记录
Fetchemp into pename,psal;
--退出条件:光标中没有值了。使用%notfound判断有没有值。
Exitwhen cemp%notfound;
--没有退出就是去到了值,打印。
dbms_output.put_line(pename||’的薪水是’||psal);
Endloop;
Close cemp;
End;
/
--给员工涨工资,总裁1000经历800 其他400
Set serveroutput on
Declare
--定义光标
Cursoremp is select empno,empjob from emp;
--定义变量保存员工号和职位
Pempnoemp.empno%type;
Pjobemp.empjob%type;
Begin
Opencemp;
Loop
--获取一个员工信息
Fetchcemp into pempno,pjob;
Exitwhen cemp%notfound;
--判断职位
Ifpjob = ‘PRESIDENT’ then update emp set sal = sal + 1000 where empno = pempno;
Elseifpjob = ‘MANAGER’ then update emp set sal = sal + 800 where empno = pempno;
Elseifupdate emp set sal = sal + 400 where empno = pempno;
Endif;
Endloop;
Closecemp;
--注意:这里必须提交一下,否则数据无法更新,因为Oracle默认开启事务,Oracle的默认事务隔离级别是
--read commited,如果我们使用命令行观察更新后的数据,因为命令行和SQL Developer是两个进程,所以由于
--事务的隔离级别,必须提交后,命令行才可以读取到更新后的数据。
Commit;
Dbms_output.put_line(‘done’);
End;
/
如果不close光标,没有任何问题,但是Oracle在这个地方做了一个限制,不允许无限制的打开光标而不close,只允许一次打开300个光标。以管理员登录,使用命令:show parameters cursor。可以将光标的参数都列出来:
对于小系统来讲,300个就够用了,但是大系统就不行了,所以这个300是可以改的。
通过 alter system setopen_cursors=400 scope=both;就可以修改。
定义一个带参数的光标:
--查询某个部门中员工的姓名
Set serveroutput on
Declare
--定义一个带参数的光标,形参
Cursorcemp(dno number) is select ename from emp where deptno=dno;
Penameemp.ename%type;
Begin
--给光标传入实参,带参数的和不带参数的就在定义的时候和开启的时候有区别,其他的完全一样
Opencemp(10);
Loop
Fetchcemp into pename;
--退出
Exit when cemp%notfound;
--打印
Dbms_output.put_line(pename);
Closeemp;
End;
/