Create a table for practice.
CREATE TABLE EMP
(
EID NUMBER(10) NOT NULL,
ENAME VARCHAR2(100 BYTE),
DID NUMBER(2)
);
Insert the following row;
Insert into EMP
(EID, ENAME, DID)
Values
(703, 'YK', 50);
Insert into EMP
(EID, ENAME, DID)
Values
(704, 'LK', 50);
Insert into EMP
(EID, ENAME, DID)
Values
(705, 'OP', 50);
Insert into EMP
(EID, ENAME, DID)
Values
(706, 'YU', 50);
Insert into EMP
(EID, ENAME, DID)
Values
(707, 'RTY', 50);
Insert into EMP
(EID, ENAME, DID)
Values
(708, 'ERT', 50);
Insert into EMP
(EID, ENAME, DID)
Values
(709, 'DCVG', 50);
Insert into EMP
(EID, ENAME, DID)
Values
(800, 'RET', 50);
Insert into EMP
(EID, ENAME, DID)
Values
(801, 'EEE', 50);
Insert into EMP
(EID, ENAME, DID)
Values
(802, 'EEE', 50);
Insert into EMP
(EID, ENAME, DID)
Values
(803, 'DFG', 50);
Insert into EMP
(EID, ENAME, DID)
Values
(804, 'AAA', 50);
Insert into EMP
(EID, ENAME, DID)
Values
(805, 'WW', 50);
Insert into EMP
(EID, ENAME, DID)
Values
(806, 'WW', 50);
Insert into EMP
(EID, ENAME, DID)
Values
(807, 'WR', 50);
Insert into EMP
(EID, ENAME, DID)
Values
(808, 'WER', 50);
Insert into EMP
(EID, ENAME, DID)
Values
(809, 'WER', 50);
Insert into EMP
(EID, ENAME, DID)
Values
(900, 'WER', 50);
Insert into EMP
(EID, ENAME, DID)
Values
(901, 'WER', 50);
Insert into EMP
(EID, ENAME, DID)
Values
(902, 'RAJ', 50);
Insert into EMP
(EID, ENAME, DID)
Values
(903, 'RAJ', 50);
COMMIT;
=========================================
What is cursor;
Oracle database creates a memory area to processing an SQL statement,
PL/SQL uses implicit and explicit cursors. PL/SQL declares a cursor implicitly for all
SQL data manipulation statements, including queries that return only one row.
Implicit cursors are called SQL cursors. If you want precise control over query
processing, you can declare an explicit cursor in the declarative part of any PL/SQL
block, subprogram, or package. You must declare explicit cursors for queries that
return more than one row.
Topics:
■ SQL Cursors (Implicit)
■ Explicit Cursors
Whenever a DML statement (INSERT, UPDATE and DELETE) is issued, an implicit cursor is associated with this statement. For INSERT operations, the cursor holds the data that needs to be inserted. For UPDATE and DELETE operations, the cursor identifies the rows that would be affected.
In PL/SQL, you can refer to the most recent implicit cursor as the SQL cursor, which always has the attributes like %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT. The SQL cursor has additional attributes, %BULK_ROWCOUNT and %BULK_EXCEPTIONS, designed for use with the FORALL statement. The following table provides the description of the most used attributes:
Any SQL cursor attribute will be accessed as sql%attribute_name as shown below in the example.
Practice:
SQL% found:
Begin
Delete from EMP where EID='704';
if sql%found then
Dbms_output.put_line ('The row to be deleted was not found');
else
Dbms_output.put_line ('The row was deleted');
end if;
End;
/
Result:
SQL>
The row to be deleted was not found
PL/SQL procedure successfully completed.
SQL%notfound;
Begin
Delete from EMP where EID='703';
if sql%notfound then
Dbms_output.put_line ('The row to be deleted was not found');
else
Dbms_output.put_line ('The row was deleted');
end if;
End;
/
Result:
SQL>
The row was deleted
PL/SQL procedure successfully completed.
SQL%ISOPEN
Begin
Delete from EMP where EID='704';
if sql%ISOPEN then
Dbms_output.put_line ('The row to be deleted was not found');
else
Dbms_output.put_line ('The row was deleted');
end if;
End;
/
Result:
SQL>
The row was deleted
PL/SQL procedure successfully completed.
SQL%ROWCOUNT
Begin
update EMP Set EID= EID+500;
if sql%notfound then
Dbms_output.put_line ('No rows Updated - Department not found');
else
Dbms_output.put_line (SQL%rowcount ||'Row (s) updated');
end if;
End;
/
Result:
SQL>
20Row (s) updated
PL/SQL procedure successfully completed.
=======================
The syntax for creating an explicit cursor is :
CREATE TABLE EMP
(
EID NUMBER(10) NOT NULL,
ENAME VARCHAR2(100 BYTE),
DID NUMBER(2)
);
Insert the following row;
Insert into EMP
(EID, ENAME, DID)
Values
(703, 'YK', 50);
Insert into EMP
(EID, ENAME, DID)
Values
(704, 'LK', 50);
Insert into EMP
(EID, ENAME, DID)
Values
(705, 'OP', 50);
Insert into EMP
(EID, ENAME, DID)
Values
(706, 'YU', 50);
Insert into EMP
(EID, ENAME, DID)
Values
(707, 'RTY', 50);
Insert into EMP
(EID, ENAME, DID)
Values
(708, 'ERT', 50);
Insert into EMP
(EID, ENAME, DID)
Values
(709, 'DCVG', 50);
Insert into EMP
(EID, ENAME, DID)
Values
(800, 'RET', 50);
Insert into EMP
(EID, ENAME, DID)
Values
(801, 'EEE', 50);
Insert into EMP
(EID, ENAME, DID)
Values
(802, 'EEE', 50);
Insert into EMP
(EID, ENAME, DID)
Values
(803, 'DFG', 50);
Insert into EMP
(EID, ENAME, DID)
Values
(804, 'AAA', 50);
Insert into EMP
(EID, ENAME, DID)
Values
(805, 'WW', 50);
Insert into EMP
(EID, ENAME, DID)
Values
(806, 'WW', 50);
Insert into EMP
(EID, ENAME, DID)
Values
(807, 'WR', 50);
Insert into EMP
(EID, ENAME, DID)
Values
(808, 'WER', 50);
Insert into EMP
(EID, ENAME, DID)
Values
(809, 'WER', 50);
Insert into EMP
(EID, ENAME, DID)
Values
(900, 'WER', 50);
Insert into EMP
(EID, ENAME, DID)
Values
(901, 'WER', 50);
Insert into EMP
(EID, ENAME, DID)
Values
(902, 'RAJ', 50);
Insert into EMP
(EID, ENAME, DID)
Values
(903, 'RAJ', 50);
COMMIT;
=========================================
What is cursor;
Oracle database creates a memory area to processing an SQL statement,
PL/SQL uses implicit and explicit cursors. PL/SQL declares a cursor implicitly for all
SQL data manipulation statements, including queries that return only one row.
Implicit cursors are called SQL cursors. If you want precise control over query
processing, you can declare an explicit cursor in the declarative part of any PL/SQL
block, subprogram, or package. You must declare explicit cursors for queries that
return more than one row.
Topics:
■ SQL Cursors (Implicit)
■ Explicit Cursors
Implicit Cursors
Implicit cursors are automatically created by Oracle whenever an SQL statement is executed, when there is no explicit cursor for the statement. Programmers cannot control the implicit cursors and the information in it.Whenever a DML statement (INSERT, UPDATE and DELETE) is issued, an implicit cursor is associated with this statement. For INSERT operations, the cursor holds the data that needs to be inserted. For UPDATE and DELETE operations, the cursor identifies the rows that would be affected.
In PL/SQL, you can refer to the most recent implicit cursor as the SQL cursor, which always has the attributes like %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT. The SQL cursor has additional attributes, %BULK_ROWCOUNT and %BULK_EXCEPTIONS, designed for use with the FORALL statement. The following table provides the description of the most used attributes:
| Attribute | Description |
|---|---|
| %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. |
| %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. |
| %ISOPEN | Always returns FALSE for implicit cursors, because Oracle closes the SQL cursor automatically after executing its associated SQL statement. |
| %ROWCOUNT | Returns the number of rows affected by an INSERT, UPDATE, or DELETE statement, or returned by a SELECT INTO statement. |
Practice:
SQL% found:
Begin
Delete from EMP where EID='704';
if sql%found then
Dbms_output.put_line ('The row to be deleted was not found');
else
Dbms_output.put_line ('The row was deleted');
end if;
End;
/
Result:
SQL>
The row to be deleted was not found
PL/SQL procedure successfully completed.
SQL%notfound;
Begin
Delete from EMP where EID='703';
if sql%notfound then
Dbms_output.put_line ('The row to be deleted was not found');
else
Dbms_output.put_line ('The row was deleted');
end if;
End;
/
Result:
SQL>
The row was deleted
PL/SQL procedure successfully completed.
SQL%ISOPEN
Begin
Delete from EMP where EID='704';
if sql%ISOPEN then
Dbms_output.put_line ('The row to be deleted was not found');
else
Dbms_output.put_line ('The row was deleted');
end if;
End;
/
Result:
SQL>
The row was deleted
PL/SQL procedure successfully completed.
SQL%ROWCOUNT
Begin
update EMP Set EID= EID+500;
if sql%notfound then
Dbms_output.put_line ('No rows Updated - Department not found');
else
Dbms_output.put_line (SQL%rowcount ||'Row (s) updated');
end if;
End;
/
Result:
SQL>
20Row (s) updated
PL/SQL procedure successfully completed.
=======================
Explicit Cursors
Explicit cursors are programmer defined cursors for gaining more control over the context area. An explicit cursor should be defined in the declaration section of the PL/SQL Block. It is created on a SELECT Statement which returns more than one row.The syntax for creating an explicit cursor is :
CURSOR cursor_name IS select_statement;Working with an explicit cursor involves four steps:
- Declaring the cursor for initializing in the memory
- Opening the cursor for allocating memory
- Fetching the cursor for retrieving data
- Closing the cursor to release allocated memory
Declaring the Cursor
Declaring the cursor defines the cursor with a name and the associated SELECT statement. For example:CURSOR c_customers IS SELECT id, name, address FROM customers;
Opening the Cursor
Opening the cursor allocates memory for the cursor and makes it ready for fetching the rows returned by the SQL statement into it. For example, we will open above-defined cursor as follows:OPEN c_customers;
Fetching the Cursor
Fetching the cursor involves accessing one row at a time. For example we will fetch rows from the above-opened cursor as follows:FETCH c_customers INTO c_id, c_name, c_addr;
Closing the Cursor
Closing the cursor means releasing the allocated memory. For example, we will close above-opened cursor as follows:CLOSE c_customers;
Practice:
0 Comments