Database cursor (Oracle) Analysis

Database cursor (Oracle) the concept of cursor is a memory Workspace of SQL, which is defined by the system or user in the form of variables. A cursor is used to temporarily store data blocks extracted from the database. Why use a cursor? The data in the database is stored in the disk. The cursor transfers the data from the disk to the computer memory for processing. The processing result is displayed or written back to the database, this can improve data processing efficiency because frequent disk data exchange will reduce the efficiency. Two types of cursors are available: Implicit cursors and display cursors. Implicit cursor: for SELECT... INTO... statements (only one row of records can be extracted from the database at a time) and DML (data manipulation language, that is, INSERT statements, UPDATE statements, and DELETE statements), the system uses an implicit cursor. Display cursor: for SELECT statements with more than one record in the result set, the programmer needs to define a display cursor.
The value of % FOUNT is true for the four attributes of the implicit cursor. true indicates that the single-row query statement or DML statement has been successfully operated. % NOTFOUNT is opposite to % Found in the execution of www.2cto.com % isopen dml, false % ROWCOUNT indicates the number of data rows successfully executed by the DML statement.
An example of using a hidden cursor: [SQL] set serveroutput on begin update emp SET sal = sal + 100 WHERE empno = 1234; if SQL % FOUND THEN DBMS_OUTPUT.PUT_LINE ('employee salary modified successfully! '); COMMIT; ELSE DBMS_OUTPUT.PUT_LINE (' failed to modify employee salary! '); End if; END; display the four attributes of the cursor. % FOUNT the latest FETCH statement returns a row of records. true, otherwise, false % NOTFOUNT is the opposite of % Found. If the www.2cto.com % ISOPEN cursor is opened, true is used. Otherwise, false % ROWCOUNT is used to obtain the number of rows returned by the FETCH statement. The cursor is displayed in the following four steps: declare CURSOR open CURSOR extract data close CURSOR declare CURSOR: CURSOR name [(parameter 1 Data Type [, parameter 2 data type...])] is select statement; parameter IS optional
OPEN cursor: OPEN cursor name [(actual parameter 1 [, actual parameter 2...])]; the parameter is an optional part. When a cursor is opened, the result set is sent to the cursor workspace to extract data: FETCH cursor name INTO variable name 1 [, variable name 2...]; or FETCH cursor name INTO record the variable. When the cursor is opened, a pointer points to the data area. If the FETCH statement returns the data of a row indicated by the pointer at a time, multiple rows must be returned and executed repeatedly, you can use loop statements. The control loop can be performed by judging the attributes of the cursor. The first FETCH format: the variable name is used to receive data from the cursor, which needs to be defined in advance. The number and type of variables must be the same as the number and type of field variables in the SELECT statement. The second FETCH format: To extract a row of data to the record variable at a time, you need to use % ROWTYPE to define the record variable in advance. This method is convenient to use and does not have to define or use multiple variables separately. Method for defining record variables: variable name table name | cursor name % ROWTYPE; the table must exist and the cursor name must be defined first.
CLOSE cursor: CLOSE the cursor name. After an explicit cursor is opened, it must be closed explicitly. Once the cursor is closed, the resources occupied by the cursor are released. The cursor becomes invalid and must be re-opened before it can be used. The following is an example of how to use a display cursor: [Example 1] use a cursor to extract the names and positions of 7788 employees in the emp table (the first type of FETCH) [SQL] SET SERVEROUTPUT ON DECLARE www.2cto.com v_ename VARCHAR2 (10); v_job VARCHAR2 (10); CURSOR emp_cursor IS SELECT ename, job FROM emp WHERE empno = 7788; BEGIN OPEN emp_cursor; FETCH emp_cursor INTO v_ename, v_job; DBMS_OUTPUT.PUT_LINE (v_ename | ',' | v_job); CLOSE emp_cursor; END; [Example 2] use a CURSOR to extract the names, positions, and salaries of the 7788 employees in the emp table (Type 2 FETCH) [SQL] SET SERVEROUTPUT ON DECLARE CURSOR emp_cursor IS SELECT ename, job, sal FROM emp WHERE empno = 7788; emp_record emp_cursor % ROWTYPE; begin open emp_cursor; FETCH emp_cursor INTO emp_record; values (emp_record.ename | ',' | emp_record.job | ', '| emp_record.sal); www.2cto.com CLOSE emp_cursor; END; [Example 3] use a cursor to extract the title (LOOP) of all books of type 1) [SQL] DECLARE CURSOR BOOKS_CURSOR IS SELECT TITLE FROM BOOKS WHERE CATEGORY_ID = 1; V_TITLE BOOKS. TITLE % TYPE; V_ONE_BOOK BOOKS % ROWTYPE; begin open BOOKS_CURSOR; loop fetch BOOKS_CURSOR INTO V_TITLE; exit when BOOKS_CURSOR % NOTFOUND; DBMS_OUTPUT.PUT_LINE (BOOKS_CURSOR % ROWCOUNT | '. '| V_TITLE); end loop; CLOSE BOOKS_CURSOR; END; [Example 4] use a cursor to extract the titles of all books of type 1 () [SQL] DECLARE CURSOR BOOKS_CURSOR IS SELECT TITLE FROM BOOKS WHERE CATEGORY_ID = 1; V_ONE_BOOK BOOKS % ROWTYPE; www.2cto.com V_COUNT NUMBER (2): = 0; BEGIN FOR V_ONE_BOOK IN BOOKS_CURSOR LOOP V_COUNT: = V_COUNT + 1; DBMS_OUTPUT.PUT_LINE (V_COUNT | '. '| V_ONE_BOOK.TITLE); end loop; END; from the 的