pl/sql编程-游标

一，游标

游标是一种从表中检索数据并每次指向其中一条记录进行交互的机制，因其总是游动性地指向结果集中的某一行，就像计算机屏幕上的光标指示当前位置一样，“游标”因此而得名。

二，游标的的属性

无论是显式游标还是隐式游标，都具有%found、%notfound、%isopen、%rowcount这4个属性，
通过这4个属性可以获知SQL语句的执行结果、游标的状态信息，以及配合循环语句对游标反复读取。
%found：布尔型属性，如果SQL语句能影响到至少一行数据，则该属性为true，否则为false。
%notfound：布尔型属性，与%found相反。
%isopen：布尔型属性，当游标处于打开状态时该属性为true，否则为false。
%rowcount：数值型属性，返回受SQL影响的数据的行数。

三，游标的分类

1）按照是由用户声明创建还是由ORACLE自行声明创建，游标被分为显式游标和隐式游标，这两种游标也是使用相对频繁的两种游标，除此之外还有一种具有引用特性的REF游标。
2）显式游标是由用户声明和操作的一种游标，通常用于解决将查询结果集中的数据反复获取（如将结果集中的多行数据赋予给同一变量中），它的操作步骤包括：声明游标、打开游标、读取游标和关闭游标4个步骤，其中读取游标可能是个反复操作的步骤，因为游标每次只能读取一行数据，对于多条记录，需要配合循环语句反复读取，直到游标读取不到数据为止。
3）也将游标分为静态游标和动态游标，静态游标包括显式游标和隐式游标，而动态游标则是CURSOR REF;

1.静态游标

1.1显示游标

静态游标分为显示游标和隐式游标；显示游标的操作步骤分为：
1.声明游标
2.打开游标
3.读取游标
4.关闭游标
这里就以在游标中读取循环为例来说明显示游标的用法
语法：
DECLARE
CURSOR CUR_NAME[(IN_PARA1,IN_PARA2…)] --参数的作用 与 ：=&请输入一个数 类似
IS SELECT_SENTENCE;
BEGIN
END;
注意：
其中【in_para】为输入参数，其格式包括参数名字、表示输入方向的IN（可省略）、参数的数据类型（不可指定长度）和起始值（可省略）。
PARA_NAME [IN] DATE_TYPE [:=DEF_VALUE]
1）读取游标中的循环-loop**（无参数输入）**
DECLARE
CURSOR A IS SELECT EMPNO,JOB,ENAME FROM EMP; --声明游标A
B A%ROWTYPE; --声明一个变量B,B的数据类型与A一样
BEGIN
OPEN A; --打开游标A
LOOP --设置循环
FETCH A INTO B; --将游标A中的数据读取到变量B中
EXIT WHEN A%NOTFOUND; --循环的退出条件是在游标中找不到数据(数据为空)时
DBMS_OUTPUT.PUT_LINE(B.EMPNO||B.JOB||B.ENAME); --打印变量B中的数据
END LOOP; --结束循环
CLOSE A;–关闭游标
END;
2）读取游标中的循环-while（有参数输入）
根据输入参数打印部门员工姓名
DECLARE
–声明游标，在声明的同时定义一个输入参数
CURSOR A(I_DEPTNO NUMBER:=‘&请输入一个部门编号’) IS SELECT ENAME FROM EMP WHERE DEPTNO=I_DEPTNO;
–定义变量
B A%ROWTYPE;
BEGIN
–打开游标
OPEN A;
–先读取一次游标，再进入循环，因为while循环的执行条件是条件满足时才会进入游标。
FETCH A INTO B;
WHILE A%FOUND LOOP
DBMS_OUTPUT.PUT_LINE(B.ENAME);
FETCH A INTO B;
END LOOP;
–关闭游标
CLOSE A;
END;
3）读取游标中的循环-for
for循环中省略游标的打开，读取，关闭；仅需要声明游标就行了。
DECLARE
–声明游标的同时定义一个输入参数
CURSOR A(I_DEPTNO NUMBER:=‘&请输入你的部门编号’) IS SELECT ENAME FROM EMP WHERE DEPTNO=I_DEPTNO;
BEGIN
不需要打开游标和读取游标
FOR C IN A LOOP
DBMS_OUTPUT.PUT_LINE(C.ENAME);
END LOOP;
END;

1.2隐式游标

在执行一个SQL语句时，ORALCE会自动创建一个隐式游标。这个游标是内存中处理该语句的工作区域。
隐式游标主要是处理数据操纵语句的执行结果，有些情况下也可以处理SELECT语句的查询结果。
隐式游标也有着自己的属性，在使用隐式游标的属性时需要加上隐式游标的默认名称——SQL。
在PL/SQL编程中，隐式游标常用来判断数据的DML操作结果。
–把隐式游标对应的属性的内容紧贴着要查看的数据属性的后面
–隐式游标（不需要声明）(SQL) 隐式游标就是SQL
–(显式游标一般不用%ROWCOUNT,而隐式游标一般只用%ROWCOUNT)
–打印多少条数据发生了变化
DECLARE
BEGIN
UPDATE EMP SET SAL=2SAL WHERE DEPTNO=10;
/ COMMIT;*/ --commit如果放在这里的话，会将之前变更的数据直接提交，这样接下来就没有数据变更了，下面的打印数据变更数会是0；
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);
COMMIT; --COMMIT放在这里，会先统计变更数据再将数据的变更提交，会有变更数打印出来。
END;

2.动态游标

–输入1，打印emp表信息；输入0，打印dept表信息
DECLARE
TYPE AAA IS REF CURSOR; --定义游标类型为动态游标
CUR_A AAA; --声明游标CUR_A为动态游标
E EMP%ROWTYPE;
D DEPT%ROWTYPE;
N NUMBER:=&请输入1或0;
BEGIN
IF N=1 THEN
OPEN CUR_A FOR SELECT * FROM EMP; --打开游标的同时将查询结果集放入游标
LOOP
FETCH CUR_A INTO E;
EXIT WHEN CUR_A%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(E.ENAME);
END LOOP;
CLOSE CUR_A;
ELSE
/* DBMS_OUTPUT.PUT_LINE(‘========================’);*/
OPEN CUR_A FOR SELECT * FROM DEPT;
LOOP
FETCH CUR_A INTO D;
EXIT WHEN CUR_A%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(‘部门名称：’||D.DNAME||’ 工作地点：‘||D.LOC||’ '||‘部门编号：’||D.DEPTNO);
END LOOP;
CLOSE CUR_A;
END IF;
END;
注：动态游标和动态SQL差不多，就是不需要直接将游标声明死，而是可以灵活变动的。

四，游标的注意点

1.游标的LOOP循环中，读取游标后，要先判断退出条件，再执行PL/SQL语句，否则最后会多执行一次；
2.游标的WHILE循环中，循环之前，要先读取一次游标，否则指针指不到数据，判定循环条件时进不到循环体中；
3.游标的FOR循环中，计数器变量不用声明，引用游标中的数据时，要指明是游标中的哪个字段；
4.游标的FOR循环中，可以声明游标，但不用打开游标、读取游标和关闭游标，这些操作由ORACLE系统内部自动完成。