Oracle Cursors (i)

1. Cursor Concepts

When you execute a SELECT, INSERT, delete, and UPDATE statement in a PL/SQL block, Oracle assigns it a context area, or buffer, in memory. A cursor is a pointer to the area, or a structured data type. It provides a method for individually processing each row of data in a query result set with multiple rows of data, and is a common programming method for applications that design Embedded SQL statements.

2. Cursor use

Cursors are divided into two types;

1, implicit cursor

2, display the cursor,

A, an implicit cursor

An implicit cursor is created automatically when an SQL statement is executed, and the program cannot control an implicit cursor when there is no declaration to display the cursor.

When DML statements (Insert,update and delete) are executed, the implicit cursor is automatically created, for insert operations, the cursor controls the inserted data, and for Delete,update, the cursor records the number of rows affected

Note: Any cursor can access the sql%attribute_name.

Cursor Properties

Property	Describe
%found	Returns true if the Insert,update,delete execution affects the number of rows, or if select into has a returned number of rows, meaning that a record is updated, or false otherwise.
%notfound	In contrast to the above, no record is updated to return true, otherwise false is returned.
%isopen	False is always returned for implicit cursors because Oracle shuts down automatically after executing the associated SQL statement. For a display cursor, returns TRUE if it is open, otherwise false.
%rowcount	Returns the number of rows affected. When you execute a insert,update or DELETE statement, or SELECT INTO

Implicitly-cursor Properties

Property	Value	SELECT	INSERT	UPDATE	DELETE
Sql%isopen		FALSE	FALSE	FALSE	FALSE
Sql%found	TRUE	With results		Success	Success
Sql%found	FALSE	No results.		Failed	Failed
Sql%notfuond	TRUE	No results.		Failed	Failed
Sql%notfound	FALSE	With results		Success	Failed
Sql%rowcount		Returns the number of rows, only 1	Number of rows inserted	Number of rows Modified	Number of rows deleted

Look at the premise of the example or prepare the data first

drop table customer;create table customer (name varchar ( -) Primary Key,ageint, address varchar ( -), Salarydecimal) Delete fromCustomer;insert into customer values ('Jack', A,'Singapore',5000.00) insert into customer values ('Rose', A,'Japan',2000.00) insert into customer values ('Jet', +,'HongKong',7000.00) insert into customer values ('John', -,'American',5000.00) insert into customer values ('Merry', -,'Singapore',3000.00) insert into customer values ('Peter', -,' China',1000.00) insert into customer values ('Adi', -,'India',2400.00);Select* fromCustomerSelect* fromCustomerwhereSalary < the

View Code

Example 1;

Example 1,Setserveroutput on;declare total_rows number (2); BEGIN UPDATE Customer SET Salary= Salary + -   whereSalary < the; IF SQL%NotFound then Dbms_output.put_line ('No customers selected'); elsif SQL%found then total_rows:= sql%rowcount; Dbms_output.put_line (Total_rows||'Customers selected'); END IF; END;/

View Code

Results:

3 Customers selected

Viewing the data will reveal that the database has updated 3 records.

Most of the time we still use the display cursor, which makes it easier to control.

B, display cursor

Declaration of the cursor:

    CURSOR cursor_name[(parameter[, parameter               ] ...) [RETURN datatype]     is          select_statement;

The following 4 steps are available for a practical display cursor;

• Declaring Cursors initializing memory
• Open Cursor Allocation memory
• FETCH CURSOR FETCH record
• Close cursor Frees memory

1. declaring cursors

CURSOR  is Select  from Customer;

2. Open Cursor Allocation memory

OPEN C1;    

3, Get Records

FETCH  into C_name, c_salary;

4, close the cursor

CLOSE c_1;

Here's a complete example of a simple use of a display cursor

Example 2;

SetServeroutput on;DECLAREc_salary customer.salary%TYPE; C_name Customer.name%TYPE; CURSORC1 is   SelectName,salary fromcustomer;BEGIN  OpenC1; LoopFetchC1 intoc_name,c_salary; Exit  whenC1%NotFound; Dbms_output.put_line (C_name||' '||c_salary); EndLoop; CloseC1;END;/

View Code

Results

 2500 7000   the  2900

View Code

The PL /SQL language provides a cursor for loop statement that automatically executes the function of the cursor's open, FETCH, CLOSE, and loop statements, and when the loop is entered, the cursor is automatically opened by a For loop statement. and extracts the first row of cursor data, when the program finishes processing the currently extracted data and enters the next loop, the cursor for Loop statement automatically extracts the next row of data for the program to process, when all the data rows in the result set are finished, and the cursor is closed automatically.

   for  in cursor_name[(value[, Value]...)] Loop    --  cursor Data processing code  END LOOP;

Using the For loop, you can accomplish the same effect as in Example 2 above.

Example 3;

　　

SetServeroutput on;DECLAREc_salary customer.salary%TYPE; C_name Customer.name%TYPE; CURSORC1 is   SelectName,salary fromcustomer;BEGIN  forFinchC1 Loop Dbms_output.put_line (F.name||' '||f.salary); EndLoop;END;/

View Code

Cursors with parameters

SetServeroutput on;DECLAREc_salary customer.salary%TYPE; C_name Customer.name%TYPE; CURSORC1 (p_salarydecimal default  the) is   SelectName,salary fromCustomerwhereSalary>=p_salary;BEGIN  --Open C1 (p_salary=>5000);  OpenC1 ( the); LoopFetchC1 intoc_name,c_salary; Exit  whenC1%NotFound; Dbms_output.put_line (C_name||' '||c_salary); EndLoop; CloseC1;END;

View Code

This is the first thing to see the basics, the cursor knowledge is more, and later updated.

This document is more in-depth for reference

Http://www.codeproject.com/Articles/580628/OracleplusPL-fSQLplusCursor

Oracle Cursors (i)