Oracle Pl sql Continue in Cursor Loop
Summary: in this tutorial, you will learn about PL/SQL cursor and its usage.
A cursor is a pointer that points to a result of a query. PL/SQL has two types of cursors: implicit cursors and explicit cursors.
Implicit cursors
Whenever Oracle executes an SQL statement such as SELECT INTO, INSERT, UPDATE, and DELETE, it automatically creates an implicit cursor.
Oracle internally manages the whole execution cycle of implicit cursors and reveals only the cursor's information and statuses such as SQL%ROWCOUNT, SQL%ISOPEN, SQL%FOUND, and SQL%NOTFOUND.
The implicit cursor is not elegant when the query returns zero or multiple rows which cause NO_DATA_FOUND or TOO_MANY_ROWS exception respectively.
Explicit cursors
An explicit cursor is an SELECT statement declared explicitly in the declaration section of the current block or a package specification.
For an explicit cursor, you have control over its execution cycle from OPEN, FETCH, and CLOSE.
Oracle defines an execution cycle that executes an SQL statement and associates a cursor with it.
The following illustration shows the execution cycle of an explicit cursor:
Let's examine each step in detail.
Declare a cursor
Before using an explicit cursor, you must declare it in the declaration section of a block or package as follows:
Code language: SQL (Structured Query Language) ( sql )
CURSOR cursor_name IS query;
In this syntax:
- First, specify the name of the cursor after the
CURSORkeyword. - Second, define a query to fetch data after the
ISkeyword.
Open a cursor
Before start fetching rows from the cursor, you must open it. To open a cursor, you use the following syntax:
Code language: SQL (Structured Query Language) ( sql )
OPEN cursor_name;
In this syntax, the cursor_name is the name of the cursor declared in the declaration section.
When you open a cursor, Oracle parses the query, binds variables, and executes the associated SQL statement.
Oracle also determines an execution plan, associates host variables and cursor parameters with the placeholders in the SQL statement, determines the result set, and sets the cursor to the first row in the result set.
More about parameterized cursor in the next tutorial.
Fetch from a cursor
The FETCH statement places the contents of the current row into variables. The syntax of FETCH statement is as follows:
Code language: SQL (Structured Query Language) ( sql )
FETCH cursor_name INTO variable_list;
To retrieve all rows in a result set, you need to fetch each row till the last one.
Closing a cursor
After fetching all rows, you need to close the cursor with the CLOSE statement:
Code language: SQL (Structured Query Language) ( sql )
CLOSE cursor_name;
Closing a cursor instructs Oracle to release allocated memory at an appropriate time.
If you declare a cursor in an anonymous block, procedure, or function, the cursor will automatically be closed when the execution of these objects end.
However, you must explicitly close package-based cursors. Note that if you close a cursor that has not opened yet, Oracle will raise an INVALID_CURSOR exception.
Explicit Cursor Attributes
A cursor has four attributes to which you can reference in the following format:
Code language: SQL (Structured Query Language) ( sql )
cursor_name%attribute
where cursor_name is the name of the explicit cursor.
1) %ISOPEN
This attribute is TRUE if the cursor is open or FALSE if it is not.
2) %FOUND
This attribute has four values:
-
NULLbefore the first fetch -
TRUEif a record was fetched successfully -
FALSEif no row returned -
INVALID_CURSORif the cursor is not opened
3) %NOTFOUND
This attribute has four values:
-
NULLbefore the first fetch -
FALSEif a record was fetched successfully -
TRUEif no row returned -
INVALID_CURSORif the cursor is not opened
3) %ROWCOUNT
The %ROWCOUNT attribute returns the number of rows fetched from the cursor. If the cursor is not opened, this attribute returns INVALID_CURSOR.
PL/SQL cursor example
We will use the orders and order_items tables from the sample database for the demonstration.
The following statement creates a view that returns the sales revenues by customers:
CREATE VIEW sales AS SELECT customer_id, SUM(unit_price * quantity) total, ROUND(SUM(unit_price * quantity) * 0.05) credit FROM order_items INNER JOIN orders USING (order_id) WHERE status = 'Shipped' GROUP BY customer_id;
Code language: SQL (Structured Query Language) ( sql ) The values of the credit column are 5% of the total sales revenues.
Suppose you need to develop a anonymous block that:
- Reset credit limits of all customers to zero.
- Fetch customers sorted by sales in descending order and gives them new credit limits from a budget of 1 million.
The following anonymous block illustrates the logic:
DECLARE l_budget NUMBER := 1000000; -- cursor CURSOR c_sales IS SELECT * FROM sales ORDER BY total DESC; -- record r_sales c_sales%ROWTYPE; BEGIN -- reset credit limit of all customers UPDATE customers SET credit_limit = 0; OPEN c_sales; LOOP FETCH c_sales INTO r_sales; EXIT WHEN c_sales%NOTFOUND; -- update credit for the current customer UPDATE customers SET credit_limit = CASE WHEN l_budget > r_sales.credit THEN r_sales.credit ELSE l_budget END WHERE customer_id = r_sales.customer_id; -- reduce the budget for credit limit l_budget := l_budget - r_sales.credit; DBMS_OUTPUT.PUT_LINE( 'Customer id: ' ||r_sales.customer_id || ' Credit: ' || r_sales.credit || ' Remaining Budget: ' || l_budget ); -- check the budget EXIT WHEN l_budget <= 0; END LOOP; CLOSE c_sales; END;
Code language: SQL (Structured Query Language) ( sql ) In the declaration section, we declare three variables.
The first one is l_budget whose initial value is 1,000,000.
The second variable is an explicit cursor variable named c_sales whose SELECT statement retrieves data from the sales view:
CURSOR c_sales IS SELECT * FROM sales ORDER BY total DESC;
Code language: SQL (Structured Query Language) ( sql ) The third variable is a cursor-based record named c_sales.
In the execution section, we perform the following:
- First, reset credit limits of all customers to zero using an
UPDATEstatement. - Second, open the
c_salescursor. - Third, fetch each row from the cursor. In each loop iteration, we update the credit limit and reduced the budget. The loop terminates when there is no row to fetch or the budget is exhausted.
- Finally, close the cursor.
The following query retrieves data from thecustomers table to verify the update:
SELECT customer_id, name, credit_limit FROM customers ORDER BY credit_limit DESC;
Code language: SQL (Structured Query Language) ( sql ) Result:
As you can see clearly from the result, only the first few customers have the credit limits. If you sum up all credit limits, the total should be 1 million as shown follows:
SELECT SUM( credit_limit ) FROM customers;
Code language: SQL (Structured Query Language) ( sql )
SUM(CREDIT_LIMIT) ----------------- 1000000
Now, you should understand PL/SQL cursors including implicit and explicit cursors, and how to use them effectively to process data, row by row, from a table.
Was this tutorial helpful?
Source: https://www.oracletutorial.com/plsql-tutorial/plsql-cursor/
0 Response to "Oracle Pl sql Continue in Cursor Loop"
Post a Comment