Shulou(Shulou.com)06/01 Report--

Cursor definition:

Cursor is a method of processing data. In order to view or process the data in the result set, the cursor provides the ability to browse the data forward or backward one or more rows at a time. You can use the cursor as a pointer to return the row record it currently points to (only one row can be returned). If you want to return multiple rows, you need to constantly scroll through all the data you want. Cursors can specify any location in the result and then allow the user to process the data at the specified location.

Advantages of cursors:

1) allow programs to perform the same or different operations on each row in the set of rows returned by the query statement select

Instead of performing the same operation on the entire rowset

2) provides the ability to delete and update rows in a table based on cursor location.

Disadvantages of cursors:

When creating cursors, the most important thing to consider is, "is there any way to avoid using cursors?" Because cursors are inefficient, they should be rewritten if they contain more than 10,000 rows of data; if cursors are used, table joins in the cursor loop should be avoided as much as possible.

Static cursors are divided into:

Explicit and implicit cursors

Show the cursor section:-- >

1. Syntax for cursors:

CURSOR cursor_name (parameter_name datatype) IS select...

two。 Show the steps for using the cursor:

1. Declare cursor 2. Open cursor 3. Read cursor 4. Close the cursor

3. Displays the four properties of the cursor:

1.Cursorname%found

2.Cursorname%notfound

3.Cursorname%isopen

4.Cursorname%rowcount

4. Display an example of a cursor:

Example 1:

Declare

Cursor c is select * from dj_djb where slbh like '201708%'

V_djb dj_djb%rowtype

Begin

Open c

Loop

-- exit when c%notfound

If c%isopen then

Dbms_output.put_line ('cursor is 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 ('cursor not open!') ; end if; end loop

End

Example 2

Use: cursor for loop.

Declare

Cursor c is select * from dj_djb where slbh like '201708%'

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.... Extract data in batches

Declare

Cursor c is select * from dj_djb where slbh like '201708%'

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 number of rows:' | | I)

End loop

Dbms_output.put_line (c%rowcount)

Exit when c%notfound

End loop

Close c

End

Implicit cursor part-- >

Implicit cursors are defined automatically by the system, and DML statements that do not display definition cursors are assigned implicit cursor attributes. The process is controlled by oracle and fully automated. The name of the implicit cursor is SQL, and you cannot explicitly execute OPEN,FETCH,CLOSE statements on SQL cursors.

Properties of implicit cursors:

Similar to display cursors, implicit cursors have four properties, except that implicit cursors start with SQL% and display cursors start with Cursor_name%. Through SQL%, you can only access the cursor properties of the previous DML operation or single-line SELECT operation, which can be used to judge the state and result of DML execution, and then control the flow of the program.

SQL%ISOPEN:

Whether the cursor is open. When select into or insert update,delete is executed, Oracle implicitly opens the cursor and closes the cursor after the statement is executed or implicitly. Because it is an implicit cursor, SQL%ISOPEN is always false

SQL%FOUND:

Determines whether the SQL statement was executed successfully. True is successfully executed when a row is active, otherwise it is false. SQL%NOTFOUND:

Determines whether the SQL statement was executed successfully. Whether the value of an active row is false, otherwise its value is true.

SQL%ROWCOUNT:

Before any DML statement is executed, the value of SQL%ROWCOUNT is NULL.

By wolihaito 2018.03.26

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.