In this section, we will examine the cursors in PL/SQL. Prophet makes a memory region, known as the setting zone, for handling a SQL explanation, which contains all the data required for preparing the assertion; for instance, the quantity of lines prepared, and so forth
A cursor is a pointer to this setting zone. PL/SQL controls the setting territory through a cursor. A cursor holds the columns (at least one) returned by a SQL proclamation. The arrangement of lines the cursor holds is alluded to as the dynamic set.
You can name a cursor so it very well may be alluded to in a program to get and handle the columns returned by the SQL proclamation, each in turn. There are two kinds of cursors −
- Implied cursors
- Express cursors
Implicit Cursors
Certain cursors are naturally made by Oracle at whatever point a SQL explanation is executed, when there is no express cursor for the assertion. Software engineers can't handle the implied cursors and the data in it.
At whatever point a DML proclamation (INSERT, UPDATE and DELETE) is given, a verifiable cursor is related with this assertion. For INSERT activities, the cursor holds the information that should be embedded. For UPDATE and DELETE tasks, the cursor distinguishes the columns that would be influenced.
In PL/SQL, you can allude to the latest verifiable cursor as the SQL cursor, which consistently has characteristics, for example, %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT. The SQL cursor has extra credits, %BULK_ROWCOUNT and %BULK_EXCEPTIONS, intended for use with the FORALL explanation. The accompanying table gives the portrayal of the most utilized credits −
S.No | Attribute & Description |
---|---|
1 |
%FOUND Returns TRUE if an INSERT, UPDATE, or DELETE statement affected one or more rows or a SELECT INTO statement returned one or more rows. Otherwise, it returns FALSE. |
2 |
%NOTFOUND The logical opposite of %FOUND. It returns TRUE if an INSERT, UPDATE, or DELETE statement affected no rows, or a SELECT INTO statement returned no rows. Otherwise, it returns FALSE. |
3 |
%ISOPEN Always returns FALSE for implicit cursors, because Oracle closes the SQL cursor automatically after executing its associated SQL statement. |
4 |
%ROWCOUNT Returns the number of rows affected by an INSERT, UPDATE, or DELETE statement, or returned by a SELECT INTO statement. |
Any SQL cursor trait will be gotten to as sql%attribute_name as demonstrated beneath in the model.
Example
We will utilize the CUSTOMERS table we had made and utilized in the past sections.
Select * from customers;
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
+----+----------+-----+-----------+----------+
The accompanying system will refresh the table and increment the compensation of every client by 500 and utilize the SQL%ROWCOUNT characteristic to decide the quantity of lines influenced −
DECLARE
total_rows number(2);
BEGIN
UPDATE customers
SET salary = salary + 500;
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;
/
At the point when the above code is executed at the SQL brief, it delivers the accompanying outcome −
6 customers selected
PL/SQL procedure successfully completed.
On the off chance that you check the records in clients table, you will locate that the lines have been refreshed −
Select * from customers;
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2500.00 |
| 2 | Khilan | 25 | Delhi | 2000.00 |
| 3 | kaushik | 23 | Kota | 2500.00 |
| 4 | Chaitali | 25 | Mumbai | 7000.00 |
| 5 | Hardik | 27 | Bhopal | 9000.00 |
| 6 | Komal | 22 | MP | 5000.00 |
+----+----------+-----+-----------+----------+
Explicit Cursors
Unequivocal cursors are software engineer characterized cursors for dealing with the setting region. An unequivocal cursor ought to be characterized in the affirmation segment of the PL/SQL Block. It is made on a SELECT Statement which returns more than one column.
The grammar for making an express cursor is −
CURSOR cursor_name IS select_statement;
Working with an unequivocal cursor incorporates the accompanying advances −
- Proclaiming the cursor for introducing the memory
- Opening the cursor for distributing the memory
- Getting the cursor for recovering the information
- Shutting the cursor to deliver the distributed memory
Declaring the Cursor
Proclaiming the cursor characterizes the cursor with a name and the related SELECT assertion. For instance −
CURSOR c_customers IS
SELECT id, name, address FROM customers;
Opening the Cursor
Opening the cursor allots the memory for the cursor and prepares it for getting the columns returned by the SQL explanation into it. For instance, we will open the above characterized cursor as follows −
OPEN c_customers;
Fetching the Cursor
Bringing the cursor includes getting to each column in turn. For instance, we will get columns from the above-opened cursor as follows −
FETCH c_customers INTO c_id, c_name, c_addr;
Closing the Cursor
Shutting the cursor implies delivering the allotted memory. For instance, we will close the above-opened cursor as follows −
CLOSE c_customers;
Example
Following is a finished guide to show the ideas of unequivocal cursors &minua;
DECLARE
c_id customers.id%type;
c_name customer.name%type;
c_addr customers.address%type;
CURSOR c_customers is
SELECT id, name, address FROM customers;
BEGIN
OPEN c_customers;
LOOP
FETCH c_customers into c_id, c_name, c_addr;
EXIT WHEN c_customers%notfound;
dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr);
END LOOP;
CLOSE c_customers;
END;
/
At the point when the above code is executed at the SQL brief, it delivers the accompanying outcome −
1 Ramesh Ahmedabad
2 Khilan Delhi
3 kaushik Kota
4 Chaitali Mumbai
5 Hardik Bhopal
6 Komal MP
PL/SQL procedure successfully completed.