oracleplsqlcursor使用.docxVIP

When Oracle Database executes a SQL statement, it stores the result set and processing information in an unnamedprivate SQL area. A pointer to this unnamed area, called acursor, lets youretrieve the rows of the result set one at a time.Cursor attributes?return information about the state of the cursor.游标是SQL的一个内存工作区，由系统或用户以变量的形式定义。游标的作用就是用于临时存储从数据库中提取的数据块。Cursor类型包含三种: 静态游标：分为显式（explicit）游标和隐式（implicit）游标；REF游标：是一种引用类型，类似于指针。1．隐式游标?1)Select …INTO…语句，DML语句，使用隐式Cursor。此外，还有一种使用FOR LOOP的Implicit Cursor用法。Every time you run either a SQL?DML statement or a PL/SQLSELECTINTO?statement, PL/SQL opens animplicit cursor. You can get information about this cursor from its attributes, but you cannot control it. After the statement runs, the database closes the cursor; however, its attribute values remain available until another DML orSELECTINTO?statement runs.?2)可以通过隐式Cusor的属性来了解操作的状态和结果。Cursor的属性包含：?SQL%ROWCOUNT 整型代表DML语句成功执行的数据行数?SQL%FOUND? 布尔型? 值为TRUE代表插入、删除、更新或单行查询操作成功?SQL%NOTFOUND 布尔型与SQL%FOUND属性返回值相反?SQL%ISOPEN 布尔型 DML执行过程中为真，结束后为假?3) 隐式Cursor由系统自动打开和关闭.?例如：[sql]?view plaincopyset?serveroutput?on??declare??begin??????update?employees?set?employee_name=Mike?where?employee_id=1001;????if?SQL%FOUND?then????????dbms_output.put_line(Name?is?updated);????else??????dbms_output.put_line(Name?is?not?updated);????end?if;??end;??/???[sql]?view plaincopyset?serveroutput?on??declare??begin??????for?tableInfo?in?(select?*?from?user_tables)?loop??????dbms_output.put_line(tableInfo.table_name);????end?loop;??exception????when?others?then??????dbms_output.put_line(sqlerrm);??end;??/??2．显式游标PL/SQL also lets you declare explicit cursors. An?explicit cursor?has a name and is associated with a query (SQLSELECT?statement)—usually one that returns multiple rows. After declaring an explicit cursor, you must open it (with theOPEN?statement), fetch rows one at a time from the result set (with theFETCH?statement), and close the cursor (with theCLOSE?statement). After closing the cursor, you can neit