Oracle Cursor Concept Explanation

1. What is a cursor.

The mechanism by which the result set is retrieved from the table, and each point from which a record is interacted.

The property (result set) that is used to manage the data returned from the data source. These properties include concurrency management, location in the result set, number of rows returned, and whether you can move forward and/or backward in the result set (scrollable).

The cursor tracks the location in the result set and allows multiple operations to be performed row by line on the result set, which may be returned to the original table or not to the original table.

In other words, the cursor returns the result set conceptually from a database based table.

A swimming cursor (pointer) that can point to a result set and fetch the records of each row line by row of the cursor's movement.

Used to query the database, get a pointer to the collection of records (the result set), allowing the developer to access one row of result sets at a time, operating on each result set.

A data structure (result set) used to store multiple query data, which has a pointer to move from top to down to traverse each record.

Because it indicates the current position in the result set, the cursor derives its name as if the cursor on the computer screen indicates the current position. 2. What is the role of cursors?

① Specifies the location of a specific row in the result set.

② retrieves a row or successive rows based on the current result set location.

③ modifies the data in the row at the current position of the result set.

④ defines different sensitivity levels for data changes made by other users.

⑤ can access the database programmatically.

Program language is a record-oriented, a set of variables can only hold a variable or a record at a time, can not directly receive the query result set in the database to introduce a cursor to solve this problem

3. Why to avoid using cursors.

① when creating a cursor, the most thing to consider is, "is there a way to avoid using cursors?" ”

Because the cursor is inefficient, if the cursor operation has more than 10,000 rows of data, it should be overwritten;

If you use a cursor, you should try to avoid the operation of table joins in the cursor loop.

the type of the 4.Oracle cursor.

① static cursor : A cursor that has a true (statically defined) result set. The SQL statement cursor is explicitly known as an implicit and display cursor before execution.

Display Cursors

The user displays the declared cursor, which specifies the result set. When a query returns more than one row, an explicit cursor is required. SQL statements written by the user themselves, and the SQL statements are clearly known at compile time

an implicit cursor

All DML statements (add, delete, modify, query a single record) are implicit cursors, the variable name does not require the user to declare, it is defined by the system, called SQL. You do not need to declare an implicit cursor when you use it, which is defined by the system.

Use of implicit cursors: the use of%rowcour judgments is available.

② dynamic cursors (ref cursors)

The SQL statement cursor is not known until execution, and the cursor of the SQL statement is not known until execution. A temporary object that dynamically associates a result set.

strongly typed cursors

Specify return type

Weak-type cursors

No return type is specified, you can get any result set. 5.Oracle The state of the cursor and how to use the cursor properties.

How to determine whether the end of the result set, in Oracle, for the properties of the cursor, it is judged by the attribute value

the state of the ① cursor is represented by a property.

%found: Used to verify the success of a cursor, usually used before a fetch statement, when a cursor is queried by criteria for a record to return a True,fetch statement (get record) execution true or False.

%notfound: Whether the last record extracts true or False. To the end of the cursor, no record, return true

%isopen: Whether the cursor is open true or False.

%rowcount: The number of rows currently fetched by the cursor, that is, the number of rows that have been affected 1

② uses the properties of the cursor.

Example:

Begin

Update emp Set sal = sal + 0.1 Where JOB = ' clerk ';

If Sql%found Then

Dbms_output. Put_Line (' has been updated. ');

Else

Dbms_output. Put_Line (' update failed. ');

End If;

End;

6. How to use a display cursor. How to traverse a loop cursor.

① using display cursors

One,

⑴ declaration cursor: dividing the storage area, note that the SELECT statement is not executed at this time.

CURSOR cursor name (argument list) [return value type] is Select statement;

⑵ Open cursor: Executes the SELECT statement, obtains the result set to be stored in the cursor, at which point the cursor points to the result set header instead of the first record.

Open cursor name (argument list);

⑶ fetch record : Move cursor fetch a record

Fetch cursor name into temporary record or attribute type variable;

⑷ Closes the cursor : puts the cursor in the buffer pool and does not fully release the resource. can be reopened.

The close cursor name;

Two,

1. Declaring cursors:

Cursor Curname is select ....

2, open the cursor:

Open curname;

3, Extract data: Circular extraction data: The predicate curname%found in the cursor, curname%notfound.

False if the%found value is true when the data in the result set has not been fetched.

%notfound is the opposite of%found. Loops can be controlled with two predicates

Fetch curname into .....

4, close the cursor

Close Curname;

② traversal loop cursor

⑴for Loop Cursor

Loop Leng open cursors, automatically scroll to get a record, and automatically create temporary record type variables to store records. Automatically closes the cursor when the process is finished.

For variable name in cursor name

Loop

Data processing statements;

End Loop;

⑵loop Loop Cursor

。。。

Loop

Fatch cursor into a temporary record or an attribute type variable;

Exit when cursor name%notfound;

End Loop;

。。。

Example 1:

/* Conn Scott/tiger * *

Declare

Cursor mycur is select Empno,ename,sal from EMP;

VNA VARCHAR2 (10);

VNO number (4);

Vsal number (7,2);

Begin

Open mycur;

Fetch mycur into vno,vna,vsal;

Dbms_output.put_line (vno| | ' '||    vna| | ' '|| Vsal);

Close mycur;

End;

/

Example 2: Use the loop to traverse the cursor.

Declare

Cursor mycur is select Ename,job,sal,empno from EMP;

Vare Mycur%rowtype;

Begin

If Mycur%isopen = False Then

Open mycur;

Dbms_output.put_line (' Opening ... ');

End If;

Loop

Fetch mycur into Vare;

Exit when Mycur%notfound;

Dbms_output.put_line (mycur%rowcount| | ' '||    vare.empno| | ' '||    vare.ename| | ' '|| Vare.sal);

End Loop;

If Mycur%isopen Then

Close mycur;

Dbms_output.put_line (' Closing ... ');

End If;

End;

/

Example 3: Using a For loop to traverse a cursor,

/* Conn Scott/tiger * *

Declare

Cursor Mycur is a select * from EMP;

Begin

For VarA in Mycur

Loop

Dbms_output.put_line (mycur%rowcount| | ' '||    vara.empno| | ' '||  vara.ename| | ' '|| Vara.sal);

End Loop;

End;

/

7. How to update and delete the records in the display cursor.

The where current of substring in a ①update or DELETE statement deals specifically with the most recent data that is fetched from the table to perform an update or delete operation .

To use this method, you must use a for update substring when declaring a cursor, and when the dialog uses a for update substring to open a cursor,

All data rows in the returned set will be in row-level (row-level) exclusive locks, and other objects can only query the rows of data.

Cannot make update, delete, or select ... For update operation.

In a multiple-table query, use the OF clause to lock a particular table, and if the of clause is omitted, the rows of data selected in all tables are locked.

If these rows of data are already locked by another session, Oracle will normally wait until the data row unlocks.

② Use Updates or deletes:

⑴ declare update or delete a display cursor:

Cursor cursor name is SELECT statement