Oracle cursor (STATIC) records

Cursor Definitions:
A cursor is a way of working with data, in order to view or manipulate the data in the result set, the cursor provides the ability to move forward or backward through the data in the result set one line at a time or in multiple rows. A cursor can be used as a pointer that can be used to return the row record it is currently pointing to (one row only) to return multiple rows, you need to scroll continuously to query all the data you want. A cursor can specify any location in the result, and then allow the user to manipulate the data at the specified location.
Advantages of cursors:
1) allows the program to perform the same or different operations on each row in the rowset returned by the query statement select
, rather than performing the same operation on the entire rowset.
2) provides the ability to delete and update rows in a table based on the cursor location.
Disadvantages of cursors:
The most thing to consider when creating cursors is, "is there a way to avoid using cursors?" "Because cursors are inefficient, if the cursor operates on more than 10,000 rows of data, it should be rewritten, and if a cursor is used, try to avoid table joins in the cursor loop."
static cursors are divided into:
Explicit and implicit cursors
Show cursor Section:-->
1. Syntax for cursors:
CURSOR cursor_name (parameter_name datatype) is select ...
2. Display the use steps of the cursor:
1. Declaring cursors 2. Open cursor 3. read CURSOR 4. Close cursor
3. Display four properties of a cursor:
1.cursorname%found
2.cursorname%notfound
3.cursorname%isopen
4.cursorname%rowcount
4. Display the example of the tour indicator:
Example 1:
Declare
Cursor C is a select * from DJ_DJB where slbh like ' 201,708% ';
V_DJB Dj_djb%rowtype;
Begin
Open C;
Loop
--exit when C%notfound;
If C%isopen Then
Dbms_output.put_line (' Cursor already open! ‘);
Fetch C into V_DJB;
Exit when C%notfound;
Dbms_output.put_line (V_DJB.SLBH);

dbms_output.enable(buffer_size=>null);dbms_output.put_line(c%rowcount);else  dbms_output.put_line(‘游标未打开！‘);  end if;  end loop;

End
Example 2
Use: Cursor for loop ...
Declare
Cursor C is a select * from DJ_DJB where slbh like ' 201,708% ';
V_DJB Dj_djb%rowtype;
Begin
For I in C loop
Dbms_output.put_line (I.SLBH);
Dbms_output.put_line (C%rowcount);
End Loop;
End

Use bulk collect into .... To extract data in batches

Declare
Cursor C is a select * from DJ_DJB where slbh like ' 201,708% ';
Type v_djb_tmp is table of Dj_djb%rowtype index by Binary_integer;
V_DJB v_djb_tmp;
Begin
Open C;
Loop
Fetch c Bulk collect into V_DJB limit 10;
For I in 1..v_djb.count loop
Dbms_output.enable (Buffer_size=>null);
Dbms_output.put_line (V_DJB (i). slbh| | ' corresponding line number: ' | | i);
End Loop;
Dbms_output.put_line (C%rowcount);
Exit when C%notfound;
End Loop;
Close C;
End
implicit cursor section--
Implicit cursors are automatically defined by the system, and DML statements that do not display a defined cursor are given an implicit cursor property. The process is controlled by Oracle and fully automated. The name of the implicit cursor is SQL, and you cannot execute the open,fetch,close statement on SQL Leng.
properties of an implicit cursor:
Similar to displaying cursors, implicit cursors also have four properties, except that implicit cursors start with sql% and display cursors start with cursor_name%. By sql%, you can always only access the cursor properties of the previous DML operation or single-row select operation, which is used to determine the status and results of DML execution, and thus control the process.
Sql%isopen:
Whether the cursor is open. When you execute SELECT INTO, insert Update,delete, Oracle implicitly opens the cursor, and when the statement executes or implicitly closes the cursor, because it is an implicit cursor, Sql%isopen always False
Sql%found:
Determine if the SQL statement was executed successfully. True if the row is functioning successfully, otherwise false. Sql%notfound:
Determine if the SQL statement was executed successfully. No value is false when there is a function, otherwise its value is true.
Sql%rowcount:
Before any DML statements are executed, the value of Sql%rowcount is null.

                                                                                              by wolihaito  2018.03.26

Oracle cursor (STATIC) records