A Brief Analysis of Database Cursors (Oracle)

A Brief Analysis of Database Cursors (Oracle)
the
Cursor concept
The cursor is a memory work area of ​​SQL, which is defined by the system or the user in the form of variables.
The function of the cursor is to temporarily store the data blocks extracted from the database.
Why use a cursor? The data of the database is stored in the disk, and the cursor transfers the data from the disk to the computer memory for processing, and finally displays the processing results or finally writes them back to the database, which can improve the efficiency of data processing, because frequent disk data Swapping reduces efficiency.
There are two types of cursors: implicit cursors and explicit 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 will use implicit cursors .
Display cursor: For a SELECT statement with more than one record in the result set, the programmer needs to define a display cursor.
the
implicit cursor
Four properties of implicit cursors
%FOUNT The value true indicates that the single-line query statement or DML statement operation is successful
%NOTFOUNT is the opposite of %Found www.2cto.com
%ISOPEN true during DML execution, false after execution
%ROWCOUNT represents the number of data rows successfully executed by the DML statement
the
An example using implicit cursors:
[sql]
SET SERVEROUTPUT ON
    BEGIN  
    UPDATE emp SET sal=sal+100 WHERE empno=1234;  
  IF SQL%FOUND THEN  
  DBMS_OUTPUT.PUT_LINE('Successfully modified employee salary!');  
  COMMIT;  
           
  DBMS_OUTPUT.PUT_LINE('Failed to modify employee salary!& #39;);  
      END IF;  
             
the
show cursor
Display the four properties of the cursor
%FOUNT If the most recent FETCH statement returns a record, it is true, otherwise it is false
%NOTFOUNT is the opposite of %Found www.2cto.com
%ISOPEN is true when the cursor is open, otherwise it is false
%ROWCOUNT Get the number of rows returned by the FETCH statement
The use of the display cursor is divided into the following 4 steps:
declare cursor
open cursor
Extract data
close cursor
Declare the cursor:
CURSOR cursor name [(parameter 1 data type [, parameter 2 data type…])] IS SELECT statement;
Parameters are optional
the
Open cursor:
OPEN cursor name [(actual parameter 1[, actual parameter 2…])];
The parameter is an optional part. When the cursor is opened, the result set is sent to the cursor work area
Extract data:
FETCH cursor_name INTO variable_name1[, variable_name2…];
or
FETCH cursor_name INTO record variable;
After the cursor is opened, there is a pointer pointing to the data area. The FETCH statement returns a row of data pointed to by the pointer at a time. To return multiple rows, it needs to be executed repeatedly, which can be realized by using a loop statement. The control loop can be performed by determining the properties of the cursor.
The first FETCH format: the variable name is a variable used to receive data from the cursor, which needs to be defined in advance. The number and type of variables should be consistent with the number and type of field variables in the SELECT statement.
The second FETCH format: to extract one row of data into the record variable at a time, you need to use %ROWTYPE to define the record variable in advance. This form is more convenient to use, and it is not necessary to define and use multiple variables separately. The method of defining record variables: variable name table name|cursor name%ROWTYPE; the table must exist, and the cursor name must be defined first.
the
Close the cursor:
CLOSE cursor name;
After an explicit cursor is opened, it must be explicitly closed. Once the cursor is closed, the resources occupied by the cursor are released, and the cursor becomes invalid and must be reopened to be used.
Here are a few examples of how to use display cursors:
[Example 1] Use a cursor to extract the names and titles 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;
the
【Example 2] Use a cursor to extract the name, title and salary of 7788 employees in the emp table (the second 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;
DBMS_OUTPUT.PUT_LINE(emp_record.ename||','|| emp_record.job||','|| emp_record.sal); www.2cto.com
CLOSE emp_cursor;
END;
the
[Example 3] Use a cursor to extract the titles of all books whose category is 1 (LOOP)
[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;
the
[Example 4] Use the cursor to extract the titles of all books whose category is 1 (FOR)
[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;
the
the
Excerpted from Shang?’s column