PL/SQL光标的定义及运用实例讲解

PL/SQL中光标或游标代表一个集合。

定义光标：

cursor 光标名[(参数名 数据类型[,参数名 数据类型]… )] is select 语句;

例子 一：(查询出所有员工姓名和薪资)

declare

–定义一个光标

cursor cemp is select ename,sal from emp;

–定义光标变量(引用型变量)

pname emp.ename%type;

psal emp.sal%type;

begin

–打开光标

open cemp;

–循环打印

loop

–取出一条记录

fetch cemp into pname,psal;

–符合条件退出

exit when cemp%notfound

dbms_output.put_line(pname||’的薪水是’||psal);

end loop;

–关闭光标

close cemp;

end;

注意：

1.光标的属性(以%开头)

%found：如果fetch语句取到了记录，它的值就是true否则为false;(找到)

%notfound: 如果fetch语句未取到记录，它的值为true，否则为false。与%found相反。(未找到)

2.fetch的作用：

a.把当前指针指向的记录返回;

b.将指针指向下条记录。

3.into后面变量循序要与select查询语句中变量顺序一致。

例子 二：(查询某个部门中员工的姓名)

declare

–定义带参数的光标(dno 为形参)

cursor cemp(dno number) is select ename from emp where deptno=dno;

pename emp.ename%type;

begin

–打开光标(10为实参)

open cemp(10);

loop

–取出每个员工的姓名

fetch cemp into pename;

exit when cemp%notfound;

dbms_output.put_line(pename);

end loop;

–关闭光标

close cemp;

end;

例子 三：(给员工涨工资：总裁1000，经理800，其他400)

declare

–定义光标代表给那些员工涨工资

–alter table emp rename column “JOB” to empjob (job为关键字，更改其名)

cursor cemp is select empno,empjob from emp;

pempno emp.empno%type;

pjob emp.empjob%type;

begin

–打开光标

open cemp;

loop

–取出一个员工

fetch cemp into pempno,pjob;

exit when cemp%notfound;

–判断员工的职位

if pjob = ‘PRESIDENT’ then update emp set sal = sal + 1000 where empno = pempno;

elsif pjob = ‘MANAGER’ then update emp set sal = sal + 800 where empno = pempno;

else update emp set sal = sal + 400 where empno = pempno;

end if;

end loop;

–关闭光标

close cemp;

–对于oracle,默认的事务隔离级别是 read committed

–事务的ACID(原子性、一致性、隔离型、持久性)

commit;

dbms_output.put_line(‘涨工资完成’);

end;

光标的属性和限制：

1.光标的属性

%found %notfound

%isopen:判断光标是否打开

%rowcount:影响的行数

例子 一：(%isopen:判断光标是否打开)

declare

–定义光标

cursor cemp is select empno,empjob from emp;

pempno emp.empno%type;

pjob emp.empjob%type;

begin

–打开光标

open cemp;

if cemp%isopen then

dbms_output.put_line(‘光标已经打开’);

else

dbms_output.put_line(‘光标没有打开’);

end if;

–关闭光标

close cemp;

end;

例子 二：( %rowcount:影响的行数)

declare

–定义光标

cursor cemp is select empno,empjob from emp;

pempno emp.empno%type;

pjob emp.empjob%type;

begin

–打开光标

open cemp;

loop

–取出一条记录

fetch cemp into pempno,pjob;

exit when cemp%notfound;

–打印rowcount的值

dbms_output.put_line(‘rowcount:’||cemp%rowcount);

end loop;

–关闭光标

close cemp;

end;

2.光标的限制：

默认情况下，Oracle数据库只允许在同一个会话中，打开300个光标。

修改光标数的限制:

alter system set open_cursors=400 scope=both;

scope(范围)的取值:

both:既改变实例也改变SPFILE。

memory:只改变当前实例运行，即初始化参数改变了只对当前实例有效，当实例重启之后，初始化参数值 还原。

spfile(数据库需要重启):只改变SPFILE的设置。即改变初始化参数文件内容。实例重启后参数生效。