PL/SQL language development Reference Manual: PL/SQL cursor

PL/SQL language development Reference Manual: PL/SQL cursor

PL/SQL language development Reference Manual
1. cursor)
The cursor is the data extracted from the data table. It is stored in the memory as a temporary table. There is a data pointer in the cursor. In the initial state, it points to the first record, the fetch statement can be used to move the pointer, perform various operations on the data in the cursor, and then write the operation results back to the data table.
1) category:
A) Explicit cursor: the programmer can control the operation and direct it to the end set of the SELECT statement;
B) Implicit cursor: applications that are uncontrollable but restricted by programmers;
2) function: it is used to process the query statement results and extract multi-row datasets;
3) steps:
A) declared cursor:
A. General statement:

Delcare <br/> v_emp s_emp % rowtype; <br/> v_did s_dept.id % type; <br/> cursor cur_emp is select * From s_emp where dept_id = v_did; <br/>/* the content of cursor must be a query statement */ 
B. Parameter declaration:

Delcare <br/> cursor c_emp (v_did s_emp.id % Type) Select * From s_emp where dept_id = v_did; 
B) Open the cursor for the query:

Open cur_emp;/* is equivalent to executing the SELECT statement and saving the execution result to cursor; 
C) obtain the result from the cursor and store it in PL/SQL variables:
A. Fetch cur_emp into var1, var2 ,... (variable name 1, variable name 2 ,.......); /* The number, type, and sequence of the variables must be consistent with the fields in the Table ;*/
B. Fetch cur_emp into record_var (record variable name );
Note: extract the value from the cursor and put it into the variable. One record is obtained every fetch;
D) Close the cursor: Close cur_emp;
Note:
A. the cursor should be closed after use;
B. The closed cursor cannot be fetch or closed again;
C. Closing the cursor is equivalent to clearing the contents of the cursor in the memory;
2. cursor attributes
Cursor name % attribute name
1) % found: Indicates whether a value exists. If yes, true is returned; otherwise, false is returned;
2) % notfound: whether there is no value. If there is no value, true is returned; otherwise, false is returned;
3) % isopen: whether it is in the open state; true is returned if it is in the open state; otherwise, false is returned;
4) % rowcount: the current record number of the cursor (the pointer displacement of the current cursor, indicating the amount of data processed by the cursor (How many records) before that ));
3. The fetch loop of the cursor
1)

Variable defined by loop fetch cursor name into <br/> exit when cursor name % notfound; /* When the cursor does not obtain the track record, exit and make a timely judgment */<br/> end loop; 
2)

While cursor name % found loop fetch variable defined by cursor name into; <br/> end loop; 
3)

For variables that store data (not defined in advance, determined by the Select Type when the cursor is declared) <br/> In cursor name loop (fetch cursor name ...) <br/> (fetch statements are not required) <br/> end loop; 
4. cursor with Parameters

Declare <br/> v_emp s_emp % rowtype <br/> cursor cur_emp (p_did s_emp.dept_id % type) is/* defines the type of the parameter p_did */<br/> select * From s_emp where dept_id = p_did <br/> for update of salary;/* for update of salary; apply row-level locks. When I modify the data, <br/> * others cannot modify the data, until the cursor is closed */<br/> begin <br/> open cur_emp (31 ); /* The parameter value 31 is the */<br/> loop <br/> fetch cur_emp into v_emp assigned to the parameter p_did; <br/> exit when cur_emp % notfound; </P> <p> dbms_output.put_line (v_emp.id | ''| v_emp.first_name); <br/> end loop; <br/> close cur_emp; <br/> end 
5. explicit and implicit cursors
Explicit cursors are mainly used to process query statements, especially when multiple records are queried;
For non-query statements, such as modification and deletion operations, the Oracle system automatically sets a cursor for these operations and creates its workspace. These implicitly created cursors are called implicit cursors, the implicit cursor name is SQL, which is defined by the Oracle system.
For implicit cursor operations, such as definition, opening, value setting, and closing operations, the Oracle system automatically completes the operation without user processing.
You can perform operations only through the properties of implicit cursors.
In the workspace of an implicit cursor, the stored data is the data contained in an SQL statement that is not related to the custom display cursor.
Format call: SQL %
Note: you do not need to define the cursor in insert, update, delete, and select statements.
Implicit cursor property
SQL % found: Boolean attribute. The value is true if the last read record is returned successfully;
SQL % notfound Boolean attribute, opposite to % found;
SQL % rowcount numeric attribute, returns the number of records read from the cursor;
SQL % isopen Boolean attribute. The value is always false. Close the implicit cursor immediately after the SQL command is executed.