Plsql basics (iii) cursor

There are two types of cursors:

Display cursor, implicit cursor

 

Cursor is used to display the cursor... the is command defines the cursor, which can process multiple records returned by the query statement (select), while the implicit cursor is executing insert, delete ), PLSQL automatically defines the update and select statements that return a single record.

 

Show cursor operations

1) Open the cursor 2) Push the cursor 3) Close the cursor

 

 

Declared cursor:

Declare

V_auths auths % rowtype;

V_code auths. author_code % type;

Cursor c_auths is

Select * From auths where author_code = v_code;

 

Open cursor:

V_code = 'a00001 ';

Open c_auths;

 

Declared cursor

Declare

Cursor c_auths (p_code auths. author_code % type) is

Select * From auths where author_code = p_code;

Open cursor

Open c_auths ('a0000001 ');

 

When the display cursor is opened, you can use the fetch statement to push the cursor and return a row in the query result. After a fetch statement is not executed, the display cursor automatically points to the next row of the query result set.

Fetch c_auths into c_auths;

 

When the entire result set is retrieved, the cursor should be closed to notify the PLSQL cursor operation has ended and the resources occupied by the cursor will be released.

Close cursor_name;

 

 

Cursor attributes

The cursor has four attributes: % found, % notfound, % isopen, % rowcount

 

Explicit cursor recommendation cycle

Declare

V_salary auths. Salary % type;

V_code auths. author_code % type;

Cursor c_salary is

Select salary, author_code from auths where author_code <= 'a00006 ';

Begin

Open c_salary;

Loop

Fetch c_salary into v_salary, v_code;

Exit when c_salary % notfount;

If v_salary <= 2000 then

Update auths set salary = salary + 50 where author_code = v_code;

End if;

End loop;

 

Close c_salary;

Commit;

End;

 

Whether using loop... the end loop statement still uses the while... loop statements must use open, fetch, and close statements to control the opening, pushing, and closing of a cursor. PLSQL also provides a simple type of loop, you can automatically control the opening, pushing, and closing of the cursor. This is called the for loop of the cursor.

Declare

Cursor c_salary is

Select salary from auths where author_code <= 'a00006 ';

Begin

-- Start the cursor for loop and implicitly open the c_salary cursor

For v_salary in c_calary Loop

If v_salary.salary <= 200 then

Update auths set salary = salary + 50 where salary = v_salary.salary;

End if;

End loop;

Commit;

End;

 

In the above example, v_salary is not declared in the definition part of the block. This variable is implicitly declared by the PLSQL compiler. The type of this variable is c_salary % rowtype, and its scope is only within the loop.

 

 

 

Implicit cursor

The display cursor is only used to control the select statements that return multiple rows. The implicit cursor is a pointer to the environment area where all SQL statements are processed. An implicit cursor is also called an SQL cursor, unlike the display cursor, an SQL cursor cannot be opened or closed by using special commands. PLSQL implicitly opens an SQL cursor, processes an SQL statement internally, and closes it.

SQL cursors are used to process insert, update, delete, and select... into statements that return a row. An SQL cursor cannot be operated by the open, fetch, and close commands.

The SQL cursor is similar to the display cursor, and also has the % found, % notfound, % isopen, % rowtype attribute. The SQL cursor attribute is usually returned for executing insert, delete, update or select... into statement. Before an SQL cursor is opened, the attribute of the SQL cursor is null.

% Found

When one or more rows are processed using insert, delete, or update statements, or a select into statement is executed to return a row, the % found attribute returns true; otherwise, false is returned.

NOTE: If multiple rows are returned when the select into statement is executed, the too_maney_rows exception is generated and the control is transferred to the Exception Handling Section. The % found attribute does not return true. if 0 rows are returned when the select into statement is executed, a no_date_found exception occurs. The % found attribute does not return false.

 

% Notfound

% Notfound is exactly the opposite of % found. When the function processed by the insert, delete, or update statement is 0, % notfound returns true; otherwise, false.

 

% Isopen

Because after the DML statement is executed, Oracle will automatically close the SQL cursor, so % isopen will always be false.

 

% Rowcount

This attribute returns the number of rows returned by the insert, delete, or update statement, or the number of rows queried when the select into statement is executed. If the number of rows returned by the insert, delete, update, or select into statement is 0, then the % rowtype property returns 0.

Begin

Update auths set entry_date_time = sysdate where author_code = 'a00007 ';

-- If the row modified in the update statement does not exist (SQL % notfound returns true)

If SQL % notfound then

Insert into auths values (......)......

End if;

End;

We use SQL % rowcount to complete the same functions as the previous example.

Begin

Update auths set entry_date_time = sysdate where author_code = 'a00007 ';

If SQL % rowcount = 0 then

Insert ......

End if;

End;

 

 

 

Cursor variable

So far, all the previous examples of displaying cursors are static curs---- that is, the cursor is associated with an SQL statement, and the SQL statement has been determined during compilation, the cursor variable is a variable of the reference type (REF). When the program runs, you can use the cursor variable to specify different queries. Therefore, the usage of the cursor variable is more flexible than that of the static cursor.

You can use a cursor variable that does not specify the result set type to specify multiple different types of queries.

Declare

-- Defines the cursor variable type t_curref. This variable type does not specify the result set type. Therefore, variables of this cursor variable type can return different PLSQL record types.

Type t_curref is ref cousor

-- Declare a variable of the cursor variable type

C_cursorref t_curref;

-- Define PLSQL record types

Tpye t_authorrec is record

(

Authorcode auths. author_code % type;

Name auths. name % Type

);

Type t_articlerec is recode

(

Authorcode article. author_code % Type

Title article. Title % Type

);

-- Declare two record type variables

V_author t_authorrec;

V_ariticle t_articlerec;

Begin

-- Open the cursor variable c_cursorref and return a t_authorrec record.

Open c_cursorref

Select author_code, name from auths;

Fectch c_cursorref into v_author;

While c_cursorref % found Loop

...

Fetch c_cursorref into v_author;

End loop;

Close c_cursorref;

 

Open c_cursorref

Select author_code, title from ariticle

Fetch c_cursorref into v_article;

While c_cursorref % found Loop

...

Fetch c_cursorref into v_article;

End loop;

Close c_cursorref;

End;

 

 

Note: In the preceding example, you can omit the first time you close the cursor variable, because the second time you open the cursor variable, the first query is lost.