Oracle Base Cursors

One: The basic principle of cursors

cursors are used to process multiple rows of records retrieved from the database (using the SELECT statement). Cursors allow the program to process and iterate through the entire set of records returned one at a time. 　　

To handle SQL statements, Oracle allocates an area in memory, which is the context area. This area contains the number of rows already processed, a pointer to the parsed statement, and the entire area is the set of data rows returned by the query statement. A cursor is a pointer to a context area handle.

II: Classification of cursors

1. Static cursor: A cursor that knows its SELECT statement at compile time.

　　　　(1). Display Cursors

　　　　(2). An implicit cursor

2. Dynamic cursor: The query that the user uses for the cursor is not determined until run time, and the cursor variable must be declared.

(1): Strongly-typed ref cursors

(2): weakly-typed REF CURSOR.

Three: Show the use of cursors

　　The display cursor is used to process a SELECT statement that returns multiple rows of data, and the cursor name is assigned to the SELECT statement by the cursor....is statement.

(a): Use steps

1) Declaring a cursor:

Cursor cursor_name (cursor name) is a SELECT statement;

2) Open the cursor for the query:

OPEN cursor_name (cursor name)

3) Obtain the result into the PL/SQL variable;

FETCH cursor_name (cursor name) into list_of_variables (variable);

FETCH cursor_name (cursor name) into Pl/sql_record (variable list);

4) Close the cursor.

CLOSE cursor_name Cursor Name

Note: When declaring a cursor,select_statement cannot include an into clause. When you use the display cursor, the INTO clause is part of the FETCH statement.

　　Example: Show employee's name and salary

Method One:

--Traversing cursors with loopsDECLAREV_name Emp.ename%TYPE; V_sal Emp.sal%TYPE;CURSOR cus_empIsSELECT Ename,salfrom EMP;--declaring cursorsBEGINOPEN cus_emp;--Open cursorLOOPFETCH cus_empinto V_name,v_sal;--Extracting cursorsEXITWhen Cus_emp%NOTFOUND; Dbms_output.put_line ( '   || Cus_emp%rowcount| |  '  user: Name: "|| V_name| |  '  Sal: || v_sal); end LOOP; close cus_emp; -- close cursor end;     

Displaying the properties of a cursor

　　　　%isopen: Whether the cursor is open

%found: Whether the cursor points to a valid row

%NOTFOUNP: Whether the cursor does not point to a valid row

%rowcount: Number of rows drawn by the cursor

Syntax: Cursor Name Property name

　　　　

Method Two: Use cursors to simplify cursor reading

For custom type in cursor name loop;

--manipulate data for each row

End Loop;

DECLARE CURSOR cus_empIs SELECT Ename,salFromEmp BEGIN For Record_empInchCus_emp LOOP Dbms_output.put_line (' first '| | Cus_emp%ROWCOUNT| | ' User: Name:'| | Record_emp.ename| | ' sal:'| | record_emp.sal); END LOOP; END;

Four, implicit cursors:

　　All implicit cursors are assumed to return only one record.

When using implicit cursors, users do not need to declare, open, and close. PL/SQL implicitly opens, processes, and then turns off the cursor. Multiple SQL statements implicit cursor SQL always refers to the result of the last SQL statement, mainly used on update and DELETE statements.

Four properties of an implicit cursor:

Property	Description
Sql%rowcount	An integer that affects the number of rows of records (used to determine whether an INSERT, update modification succeeds, must precede comit, otherwise the result is 0.)
Sql%found	The record is affected by true ()
Sql%notfound	No impact to record true
Sql%isopen	Whether to open the Boolean value is always False

For example:

DECLARERow_emp EMP%ROWTYPE;BEGINSELECT Ename,salIntoRow_emp.ename,row_emp.salFrom EMPWHERE Emp.empno=7369;--Determine if the data is foundif (SQL%ROWCOUNT=1)ThenDbms_output.put_line (‘Got it‘);ENDIF;-- Another way to judge if (Sql%found) then Dbms_output.put_line (  '  found  ); end if ' ename:  ' | | Row_emp.ename| |  '  Sal: || row_emp.sal); end;             

V: Dynamic Cursors

Oracle Base Cursors