Cursors for databases

One, what is a cursor?

A cursor is a private SQL workspace, a section of memory that is used to temporarily hold data that is affected by an SQL statement. The popular understanding is that the affected data is temporarily placed in a virtual table in a memory area, and this virtual table is a cursor.

Second, what is the role?

1, we all know that the database of things can be rolled back, and cursors play a very important role, because the operation of the database we will be temporarily placed in the cursor, as long as we do not commit, we can be based on the content of the cursor rollback, in a sense to facilitate the security of the database.

2, in addition, PL/SQL can only return single row of data in Oracle, and cursors compensate for this deficiency. It's equivalent to the DataTable in ADO.

Three, type:

1, implicit cursor: additions and deletions, etc. oracle automatically creates cursors, temporarily saving the results of operations, that is, actions that can be rolled back will cause the creation of the cursor.

2, display cursors: explicitly controlled by the developer through the program for fetching multiple rows of data from a table, and processing multiple rows of data on a single line.

Four, Attributes:

Property

Comments

%rowcount

Number of rows affected by SQL

%found

Boolean value, whether or not there is data

%notfound

Boolean value, whether there is no data

%isopen

Whether the cursor is open

Of course, if we want to get the properties of an implicit cursor, we can get it by adding SQL to the front. such as Sql%rowcount.

Five, cursors are simple to understand, let's look at the specific use of cursors:

1, first look at the simple use of the cursor four steps:

Steps

Keywords

Description

1

Cursor in DECLARE

Declaring a cursor, creating a Named query statement

2

Open

Open cursor

3

Fetch

Remove a record in a cursor load variable

4

Close

Releasing cursors

2, of course, the cursor can be stored in one piece of data, can also hold multiple data, the latter is we use a cursor, the former we can do with PL/SQL statements, so we have to use the loop structure here, in the Oracle database we can use the While......loop......end loop , for ... loop......end Loop,loop......end Loop. What needs to be put forward here is that the for loop structure is simplified in Oracle, and we just need to declare and use it. Let's look at the example below:

A,whlie Loop structure:

Declare

--Define variables for record types

V_user User%rowtype;

--1, using the CURSOR keyword to declare a cursor

Cursor User_cur is

select * from user;

Begin

--2, opening the cursor

Open user_cur;

--3, fetching data using fetch

Fetch User_cur

into V_user;

While User_cur%found loop

Dbms_output.put_line (V_user.username);

Fetch User_cur

into V_user;

End Loop;

--4, releasing the cursor

Close user_cur;

End

b, simplified for Structure loop:

Declare

--1, using the CURSOR keyword to declare a cursor

Cursor User_cur is

select * from user;

Begin

--2, direct use, Oracle will automatically open and close operations.

For V_user in User_cur loop

Dbms_output.put_line (V_user.username);

End Loop

End

The two types are described here, and loop is the same as this.

3, finally here to learn the parameters of the cursor, but also with us but the parameters of the class is the same, but one used in the database, one used in the programming language.

Declare

--Define variables for record types

V_user User%rowtype;

--1, using the CURSOR keyword to declare a cursor with parameters

Cursor User_cur (v_userno number) is

SELECT * from user where userno=v_userno;

Begin

--2, open a cursor with parameters to make it more flexible.

Open User_cur (1012);

--3, fetching data using fetch

Loop

Fetch user_cur into V_user;

Exit when User_cur%notfound;

Dbms_output.put_line (V_user.username);

End Loop;

--4, releasing the cursor

Close user_cur;

End

In the light of the simple learning of cursors, recalling past learning, cursors can be found to be similar to a DataTable, and in order to increase flexibility, it has very similar functions to classes.

Cursors for databases