Oracle游标.docx

1、Oracle游标本篇主要内容如下：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），或是一种结构化数据类型。它为应用等量齐

2、观提供了一种对具有多行数据查询结果集中的每一行数据分别进行单独处理的方法，是设计嵌入式SQL语句的应用程序的常用编程方式。在每个用户会话中，可以同时打开多个游标，其数量由数据库初始化参数文件中的OPEN_CURSORS参数定义。对于不同的SQL语句，游标的使用情况不同：SQL语句游标非查询语句隐式的结果是单行的查询语句隐式的或显示的结果是多行的查询语句显示的4.1.1处理显式游标1.?显式游标处理显式游标处理需四个PL/SQL步骤:定义/声明游标：就是定义一个游标名，以及与其相对应的SELECT语句。格式：CURSORcursor_name(parameter,parameter)RETURN

3、datatypeISselect_statement;复制代码游标参数只能为输入参数，其格式为：parameter_nameINdatatype:=|DEFAULTexpression在指定数据类型时，不能使用长度约束。如NUMBER(4),CHAR(10)等都是错误的。RETURNdatatype是可选的，表示游标返回数据的数据。如果选择，则应该严格与select_statement中的选择列表在次序和数据类型上匹配。一般是记录数据类型或带“%ROWTYPE”的数据。打开游标：就是执行游标所对应的SELECT语句，将其查询结果放入工作区，并且指针指向工作区的首部，标识游标结果集合。如果游标查

4、询语句中带有FORUPDATE选项，OPEN语句还将锁定数据库表中游标结果集合对应的数据行。格式：OPENcursor_name(parameter=value,parameter=value);在向游标传递参数时，可以使用与函数参数相同的传值方法，即位置表示法和名称表示法。PL/SQL程序不能用OPEN语句重复打开一个游标。提取游标数据：就是检索结果集合中的数据行，放入指定的输出变量中。格式：FETCHcursor_nameINTOvariable_list|record_variable;执行FETCH语句时，每次返回一个数据行，然后自动将游标移动指向下一个数据行。当检索到最后一行数据时，

5、如果再次执行FETCH语句，将操作失败，并将游标属性%NOTFOUND置为TRUE。所以每次执行完FETCH语句后，检查游标属性%NOTFOUND就可以判断FETCH语句是否执行成功并返回一个数据行，以便确定是否给对应的变量赋了值。对该记录进行处理；继续处理，直到活动集合中没有记录；关闭游标：当提取和处理完游标结果集合数据后，应及时关闭游标，以释放该游标所占用的系统资源，并使该游标的工作区变成无效，不能再使用FETCH语句取其中数据。关闭后的游标可以使用OPEN语句重新打开。格式：CLOSEcursor_name;注：定义的游标不能有INTO子句。例1.查询前10名员工的信息。DECLAREC

6、URSORc_cursorISSELECTfirst_name|last_name,SalaryFROMEMPLOYEESWHERErownum11;v_enameEMPLOYEES.first_name%TYPE;v_salEMPLOYEES.Salary%TYPE;BEGINOPENc_cursor;FETCHc_cursorINTOv_ename,v_sal;WHILEc_cursor%FOUNDLOOPDBMS_OUTPUT.PUT_LINE(v_ename|-|to_char(v_sal);FETCHc_cursorINTOv_ename,v_sal;ENDLOOP;CLOSEc_c

7、ursor;END;复制代码例2.游标参数的传递方法。DECLAREDeptRecDEPARTMENTS%ROWTYPE;Dept_nameDEPARTMENTS.DEPARTMENT_NAME%TYPE;Dept_locDEPARTMENTS.LOCATION_ID%TYPE;CURSORc1ISSELECTDEPARTMENT_NAME,LOCATION_IDFROMDEPARTMENTSWHEREDEPARTMENT_ID=30;CURSORc2(dept_noNUMBERDEFAULT10)ISSELECTDEPARTMENT_NAME,LOCATION_IDFROMDEPARTMEN

8、TSWHEREDEPARTMENT_ID=dept_no;CURSORc3(dept_noNUMBERDEFAULT10)ISSELECT*FROMDEPARTMENTSWHEREDEPARTMENTS.DEPARTMENT_ID20);LOOPFETCHc3INTOdeptrec;EXITWHENc3%NOTFOUND;DBMS_OUTPUT.PUT_LINE(deptrec.DEPARTMENT_ID|-|deptrec.DEPARTMENT_NAME|-|deptrec.LOCATION_ID);ENDLOOP;CLOSEc3;END;复制代码2.游标属性Cursor_name%FOUN

9、D布尔型属性，当最近一次提取游标操作FETCH成功则为TRUE,否则为FALSE；Cursor_name%NOTFOUND布尔型属性，与%FOUND相反；Cursor_name%ISOPEN布尔型属性，当游标已打开时返回TRUE；Cursor_name%ROWCOUNT数字型属性，返回已从游标中读取的记录数。例3：给工资低于1200的员工增加工资50。DECLAREv_empnoEMPLOYEES.EMPLOYEE_ID%TYPE;v_salEMPLOYEES.Salary%TYPE;CURSORc_cursorISSELECTEMPLOYEE_ID,SalaryFROMEMPLOYEES;B

10、EGINOPENc_cursor;LOOPFETCHc_cursorINTOv_empno,v_sal;EXITWHENc_cursor%NOTFOUND;IFv_sal=1200THENUPDATEEMPLOYEESSETSalary=Salary+50WHEREEMPLOYEE_ID=v_empno;DBMS_OUTPUT.PUT_LINE(编码为|v_empno|工资已更新!);ENDIF;DBMS_OUTPUT.PUT_LINE(记录数:|c_cursor%ROWCOUNT);ENDLOOP;CLOSEc_cursor;END;复制代码例4：没有参数且没有返回值的游标。DECLAREv

11、_f_nameemployees.first_name%TYPE;v_j_idemployees.job_id%TYPE;CURSORc1-声明游标,没有参数没有返回值ISSELECTfirst_name,job_idFROMemployeesWHEREdepartment_id=20;BEGINOPENc1;-打开游标LOOPFETCHc1INTOv_f_name,v_j_id;-提取游标IFc1%FOUNDTHENDBMS_OUTPUT.PUT_LINE(v_f_name|的岗位是|v_j_id);ELSEDBMS_OUTPUT.PUT_LINE(已经处理完结果集了);EXIT;ENDIF;ENDLOOP;CLOSEc1;-关闭游标END;复制代码例5：有参数且没有返回值的游标。DECLAREv_f_nameemployees.first_name%TYPE;v_h_dateemployees.hire_date%TYPE;CURSORc2(dept_idNUMBER,j_idVARCHAR2)-声明游标,有参数没有返回值ISSELECTfirst_name,hire_dateFROMemployeesWHEREdepartment_id=dept_idANDjob_id=