ORACLE PLSQL编程之四把游标说透.docx
《ORACLE PLSQL编程之四把游标说透.docx》由会员分享，可在线阅读，更多相关《ORACLE PLSQL编程之四把游标说透.docx（15页珍藏版）》请在冰豆网上搜索。
ORACLEPLSQL编程之四把游标说透
[推荐]ORACLEPL/SQL编程之四：
把游标说透（不怕做不到，只怕想不到）
继上两篇：
ORACLEPL/SQL编程之八：
把触发器说透
ORACLEPL/SQL编程之六：
把过程与函数说透（穷追猛打，把根儿都拔起!
）
得到了大家的强力支持，感谢。
接下来再下猛药，介绍下一篇，大家一定要支持与推荐呀~！
我也才有动力写后面的。
本篇主要内容如下：
4.1游标概念
4.1.1处理显式游标
4.1.2处理隐式游标
4.1.3关于NO_DATA_FOUND和%NOTFOUND的区别
4.1.4使用游标更新和删除数据
4.2游标变量
4.2.1声明游标变量
4.2.2游标变量操作
游标的使用
在PL/SQL程序中，对于处理多行记录的事务经常使用游标来实现。
4.1游标概念
在PL/SQL块中执行SELECT、INSERT、DELETE和UPDATE语句时，ORACLE会在内存中为其分配上下文区（ContextArea），即缓冲区。
游标是指向该区的一个指针，或是命名一个工作区（WorkArea），或是一种结构化数据类型。
它为应用等量齐观提供了一种对具有多行数据查询结果集中的每一行数据分别进行单独处理的方法，是设计嵌入式SQL语句的应用程序的常用编程方式。
在每个用户会话中，可以同时打开多个游标，其数量由数据库初始化参数文件中的OPEN_CURSORS参数定义。
对于不同的SQL语句，游标的使用情况不同：
SQL语句
游标
非查询语句
隐式的
结果是单行的查询语句
隐式的或显示的
结果是多行的查询语句
显示的
4.1.1处理显式游标
1.显式游标处理
显式游标处理需四个PL/SQL步骤:
●定义/声明游标：
就是定义一个游标名，以及与其相对应的SELECT语句。
格式：
CURSORcursor_name[（parameter[,parameter]…）]
[RETURNdatatype]
IS
select_statement;
游标参数只能为输入参数，其格式为：
parameter_name[IN]datatype[{:
=|DEFAULT}expression]
在指定数据类型时，不能使用长度约束。
如NUMBER（4）,CHAR（10）等都是错误的。
[RETURNdatatype]是可选的，表示游标返回数据的数据。
如果选择，则应该严格与select_statement中的选择列表在次序和数据类型上匹配。
一般是记录数据类型或带“%ROWTYPE”的数据。
●打开游标：
就是执行游标所对应的SELECT语句，将其查询结果放入工作区，并且指针指向工作区的首部，标识游标结果集合。
如果游标查询语句中带有FORUPDATE选项，OPEN语句还将锁定数据库表中游标结果集合对应的数据行。
格式：
OPENcursor_name[（[parameter=>]value[,[parameter=>]value]…）];
在向游标传递参数时，可以使用与函数参数相同的传值方法，即位置表示法和名称表示法。
PL/SQL程序不能用OPEN语句重复打开一个游标。
●提取游标数据：
就是检索结果集合中的数据行，放入指定的输出变量中。
格式：
FETCHcursor_nameINTO{variable_list|record_variable};
执行FETCH语句时，每次返回一个数据行，然后自动将游标移动指向下一个数据行。
当检索到最后一行数据时，如果再次执行FETCH语句，将操作失败，并将游标属性%NOTFOUND置为TRUE。
所以每次执行完FETCH语句后，检查游标属性%NOTFOUND就可以判断FETCH语句是否执行成功并返回一个数据行，以便确定是否给对应的变量赋了值。
●对该记录进行处理；
●继续处理，直到活动集合中没有记录；
●关闭游标：
当提取和处理完游标结果集合数据后，应及时关闭游标，以释放该游标所占用的系统资源，并使该游标的工作区变成无效，不能再使用FETCH语句取其中数据。
关闭后的游标可以使用OPEN语句重新打开。
格式：
CLOSEcursor_name;
注：
定义的游标不能有INTO子句。
例1.查询前10名员工的信息。
DECLARE
CURSORc_cursor
ISSELECTfirst_name||last_name,Salary
FROMEMPLOYEES
WHERErownum<11;
v_enameEMPLOYEES.first_name%TYPE;
v_salEMPLOYEES.Salary%TYPE;
BEGIN
OPENc_cursor;
FETCHc_cursorINTOv_ename,v_sal;
WHILEc_cursor%FOUNDLOOP
DBMS_OUTPUT.PUT_LINE（v_ename||'---'||to_char（v_sal））;
FETCHc_cursorINTOv_ename,v_sal;
ENDLOOP;
CLOSEc_cursor;
END;
例2.游标参数的传递方法。
DECLARE
DeptRecDEPARTMENTS%ROWTYPE;
Dept_nameDEPARTMENTS.DEPARTMENT_NAME%TYPE;
Dept_locDEPARTMENTS.LOCATION_ID%TYPE;
CURSORc1IS
SELECTDEPARTMENT_NAME,LOCATION_IDFROMDEPARTMENTS
WHEREDEPARTMENT_ID<=30;
CURSORc2（dept_noNUMBERDEFAULT10）IS
SELECTDEPARTMENT_NAME,LOCATION_IDFROMDEPARTMENTS
WHEREDEPARTMENT_ID<=dept_no;
CURSORc3（dept_noNUMBERDEFAULT10）IS
SELECT*FROMDEPARTMENTS
WHEREDEPARTMENTS.DEPARTMENT_ID<=dept_no;
BEGIN
OPENc1;
LOOP
FETCHc1INTOdept_name,dept_loc;
EXITWHENc1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE（dept_name||'---'||dept_loc）;
ENDLOOP;
CLOSEc1;
OPENc2;
LOOP
FETCHc2INTOdept_name,dept_loc;
EXITWHENc2%NOTFOUND;
DBMS_OUTPUT.PUT_LINE（dept_name||'---'||dept_loc）;
ENDLOOP;
CLOSEc2;
OPENc3（dept_no=>20）;
LOOP
FETCHc3INTOdeptrec;
EXITWHENc3%NOTFOUND;
DBMS_OUTPUT.PUT_LINE（deptrec.DEPARTMENT_ID||'---'||deptrec.DEPARTMENT_NAME||'---'||deptrec.LOCATION_ID）;
ENDLOOP;
CLOSEc3;
END;
2.游标属性
Cursor_name%FOUND布尔型属性，当最近一次提取游标操作FETCH成功则为TRUE,否则为FALSE；
Cursor_name%NOTFOUND布尔型属性，与%FOUND相反；
Cursor_name%ISOPEN布尔型属性，当游标已打开时返回TRUE；
Cursor_name%ROWCOUNT数字型属性，返回已从游标中读取的记录数。
例3：
给工资低于1200的员工增加工资50。
DECLARE
v_empnoEMPLOYEES.EMPLOYEE_ID%TYPE;
v_salEMPLOYEES.Salary%TYPE;
CURSORc_cursorISSELECTEMPLOYEE_ID,SalaryFROMEMPLOYEES;
BEGIN
OPENc_cursor;
LOOP
FETCHc_cursorINTOv_empno,v_sal;
EXITWHENc_cursor%NOTFOUND;
IFv_sal<=1200THEN
UPDATEEMPLOYEESSETSalary=Salary+50WHEREEMPLOYEE_ID=v_empno;
DBMS_OUTPUT.PUT_LINE（'编码为'||v_empno||'工资已更新!
'）;
ENDIF;
DBMS_OUTPUT.PUT_LINE（'记录数:
'||c_cursor%ROWCOUNT）;
ENDLOOP;
CLOSEc_cursor;
END;
例4：
没有参数且没有返回值的游标。
DECLARE
v_f_nameemployees.first_name%TYPE;
v_j_idemployees.job_id%TYPE;
CURSORc1--声明游标,没有参数没有返回值
IS
SELECTfirst_name,job_idFROMemployees
WHEREdepartment_id=20;
BEGIN
OPENc1;--打开游标
LOOP
FETCHc1INTOv_f_name,v_j_id;--提取游标
IFc1%FOUNDTHEN
DBMS_OUTPUT.PUT_LINE（v_f_name||'的岗位是'||v_j_id）;
ELSE
DBMS_OUTPUT.PUT_LINE（'已经处理完结果集了'）;
EXIT;
ENDIF;
ENDLOOP;
CLOSEc1;--关闭游标
END;
例5：
有参数且没有返回值的游标。
DECLARE
v_f_nameemployees.first_name%TYPE;
v_h_dateemployees.hire_date%TYPE;
CURSORc2（dept_idNUMBER,j_idVARCHAR2）--声明游标,有参数没有返回值
IS
SELECTfirst_name,hire_dateFROMemployees
WHEREdepartment_id=dept_idANDjob_id=j_id;
BEGIN
OPENc2（90,'AD_VP'）;--打开游标,传递参数值
LOOP
FETCHc2INTOv_f_name,v_h_date;--提取游标
IFc2%FOUNDTHEN
DBMS_OUTPUT.PUT_LINE（v_f_name||'的雇佣日期是'||v_h_date）;
ELSE
DBMS_OUTPUT.PUT_LINE（'已经处理完结果集了'）;
EXIT;
ENDIF;
ENDLOOP;
CLOSEc2;--关闭游标
END;
例6：
有参数且有返回值的游标。
DECLARE
TYPEemp_record_typeISRECORD（
f_nameemployees.first_name%TYPE,
h_dateemployees.hire_date%TYPE）;
v_emp_recordEMP_RECORD_TYPE;
CURSORc3（dept_idNUMBER,j_idVARCHAR2）--声明游标,有参数有返回值
RETURNEMP_RECORD_TYPE
IS
SELECTfirst_name,hire_dateFROMemployees
WHEREdepartment_id=dept_idANDjob_id=j_id;
BEGIN
OPENc3（j_id=>'AD_VP',dept_id=>90）;--打开游标,传递参数值
LOOP
FETCHc3INTOv_emp_record;--提取游标
IFc3%FOUNDTHEN
DBMS_OUTPUT.PUT_LINE（v_emp_record.f_name||'的雇佣日期是'
||v_emp_record.h_date）;
ELSE
DBMS_OUTPUT.PUT_LINE（'已经处理完结果集了'）;
EXIT;
ENDIF;
ENDLOOP;
CLOSEc3;--关闭游标
END;
例7：
基于游标定义记录变量。
DECLARE
CURSORc4（dept_idNUMBER,j_idVARCHAR2）--声明游标,有参数没有返回值
IS
SELECTfirst_namef_name,hire_dateFROMemployees
WHEREdepartment_id=dept_idANDjob_id=j_id;
--基于游标定义记录变量，比声明记录类型变量要方便，不容易出错
v_emp_recordc4%ROWTYPE;
BEGIN
OPENc4（90,'AD_VP'）;--打开游标,传递参数值
LOOP
FETCHc4INTOv_emp_record;--提取游标
IFc4%FOUNDTHEN
DBMS_OUTPUT.PUT_LINE（v_emp_record.f_name||'的雇佣日期是'
||v_emp_record.hire_date）;
ELSE
DBMS_OUTPUT.PUT_LINE（'已经处理完结果集了'）;
EXIT;
ENDIF;
ENDLOOP;
CLOSEc4;--关闭游标
END;
3.游标的FOR循环
PL/SQL语言提供了游标FOR循环语句，自动执行游标的OPEN、FETCH、CLOSE语句和循环语句的功能；当进入循环时，游标FOR循环语句自动打开游标，并提取第一行游标数据，当程序处理完当前所提取的数据而进入下一次循环时，游标FOR循环语句自动提取下一行数据供程序处理，当提取完结果集合中的所有数据行后结束循环，并自动关闭游标。
格式：
FORindex_variableINcursor_name[（value[,value]…）]LOOP
--游标数据处理代码
ENDLOOP;
其中：
index_variable为游标FOR循环语句隐含声明的索引变量，该变量为记录变量，其结构与游标查询语句返回的结构集合的结构相同。
在程序中可以通过引用该索引记录变量元素来读取所提取的游标数据，index_variable中各元素的名称与游标查询语句选择列表中所制定的列名相同。
如果在游标查询语句的选择列表中存在计算列，则必须为这些计算列指定别名后才能通过游标FOR循环语句中的索引变量来访问这些列数据。
注：
不要在程序中对游标进行人工操作；不要在程序中定义用于控制FOR循环的记录。
例8：
DECLARE
CURSORc_salISSELECTemployee_id,first_name||last_nameename,salary
FROMemployees;
BEGIN
--隐含打开游标
FORv_salINc_salLOOP
--隐含执行一个FETCH语句
DBMS_OUTPUT.PUT_LINE（to_char（v_sal.employee_id）||'---'||v_sal.ename||'---'||to_char（v_sal.salary））;
--隐含监测c_sal%NOTFOUND
ENDLOOP;
--隐含关闭游标
END;
例9：
当所声明的游标带有参数时，通过游标FOR循环语句为游标传递参数。
DECLARE
CURSORc_cursor（dept_noNUMBERDEFAULT10）
IS
SELECTdepartment_name,location_idFROMdepartmentsWHEREdepartment_id<=dept_no;
BEGIN
DBMS_OUTPUT.PUT_LINE（'当dept_no参数值为30：
'）;
FORc1_recINc_cursor（30）LOOPDBMS_OUTPUT.PUT_LINE（c1_rec.department_name||'---'||c1_rec.location_id）;
ENDLOOP;
DBMS_OUTPUT.PUT_LINE（CHR（10）||'使用默认的dept_no参数值10：
'）;
FORc1_recINc_cursorLOOPDBMS_OUTPUT.PUT_LINE（c1_rec.department_name||'---'||c1_rec.location_id）;
ENDLOOP;
END;
例10：
PL/SQL还允许在游标FOR循环语句中使用子查询来实现游标的功能。
BEGIN
FORc1_recIN（SELECTdepartment_name,location_idFROMdepartments）LOOPDBMS_OUTPUT.PUT_LINE（c1_rec.department_name||'---'||c1_rec.location_id）;
ENDLOOP;
END;
4.1.2处理隐式游标
显式游标主要是用于对查询语句的处理，尤其是在查询结果为多条记录的情况下；而对于非查询语句，如修改、删除操作，则由ORACLE系统自动地为这些操作设置游标并创建其工作区，这些由系统隐含创建的游标称为隐式游标，隐式游标的名字为SQL，这是由ORACLE系统定义的。
对于隐式游标的操作，如定义、打开、取值及关闭操作，都由ORACLE系统自动地完成，无需用户进行处理。
用户只能通过隐式游标的相关属性，来完成相应的操作。
在隐式游标的工作区中，所存放的数据是与用户自定义的显示游标无关的、最新处理的一条SQL语句所包含的数据。
格式调用为：
SQL%
注：
INSERT,UPDATE,DELETE,SELECT语句中不必明确定义游标。
隐式游标属性
属性
值
SELECT
INSERT
UPDATE
DELETE
SQL%ISOPEN
FALSE
FALSE
FALSE
FALSE
SQL%FOUND
TRUE
有结果
成功
成功
SQL%FOUND
FALSE
没结果
失败
失败
SQL%NOTFUOND
TRUE
没结果
失败
失败
SQL%NOTFOUND
FALSE
有结果
成功
失败
SQL%ROWCOUNT
返回行数，只为1
插入的行数
修改的行数
删除的行数
例11:
删除EMPLOYEES表中某部门的所有员工，如果该部门中已没有员工，则在DEPARTMENT表中删除该部门。
DECLARE
V_deptnodepartment_id%TYPE:
=&p_deptno;
BEGIN
DELETEFROMemployeesWHEREdepartment_id=v_deptno;
IFSQL%NOTFOUNDTHEN
DELETEFROMdepartmentsWHEREdepartment_id=v_deptno;
ENDIF;
END;
例12:
通过隐式游标SQL的%ROWCOUNT属性来了解修改了多少行。
DECLARE
v_rowsNUMBER;
BEGIN
--更新数据
UPDATEemployeesSETsalary=30000
WHEREdepartment_id=90ANDjob_id='AD_VP';
--获取默认游标的属性值
v_rows:
=SQL%ROWCOUNT;
DBMS_OUTPUT.PUT_LINE（'更新了'||v_rows||'个雇员的工资'）;
--回退更新，以便使数据库的数据保持原样
ROLLBACK;
END;
4.1.3关于NO_DATA_FOUND和%NOTFOUND的区别
SELECT…INTO语句触发NO_DATA_FOUND；
当一个显式游标的WHERE子句未找到时触发%NOTFOUND；
当UPDATE或DELETE语句的WHERE子句未找到时触发SQL%NOTFOUND；在提取循环中要用%NOTFOUND或%FOUND来确定循环的退出条件，不要用NO_DATA_FOUND.4.1.4使用游标更新和删除数据
游标修改和删除操作是指在游标定位下，修改或删除表中指定的数据行。
这时，要求游标查询语句中必须使用FORUPDATE选项，以便在打开游标时锁定游标结果集合在表中对应数据行的所有列和部分列。
为了对正在处理（查询）的行不被另外的用户改动，ORACLE提供一个FORUPDATE子句来对所选择的行进行锁住。
该需求迫使ORACLE锁定游标结果集合的行，可以防止其他事务处理更新或删除相同的行，直到您的事务处理提交或回退为止。
语法：
SELECTcolumn_listFROMtable_listFORUPDATE[OFcolumn[,column]…][NOWAIT]
如果另一个会话已对活动集中的行加了锁，那么SELECTFORUPDATE操作一直等待到其它的会话释放这些锁后才继续自己的操作，对于这种情况，当加上NOWAIT子句时，如果这些行真的被另一个会话锁定，则OPEN立即返回并给出：
ORA-0054：
resourcebusyandacquirewithnowaitspecified.
如果使用FORUPDATE声明游标，则可在DELETE和UPDATE语句中使用
WHERECURRENTOFcursor_name子句，修改或删除游标结果集合当前行对应的数据库表中的数据行。
例13：
从EMPLOYEES表中查询某部门的员工情况，将其工资最低定为1500；
DECLARE
V_deptnoemployees.department_id%TYPE:
=&p_deptno;
CURSORemp_cursor
IS
SELECTemployees.employee_id,employees.salary
FROMemployeesWHEREemployees.department_id=v_deptno
FORUPDATENOWAIT;
BEGIN
FORemp_recordINemp_cursorLOOP
IFemp_record.salary<1500THEN
UPDATEemployeesSETsalary=1500
WHERECURRENTOFemp_cursor
升级会员 