Oracle uses two types of cursor: explicit cursor and implicit cursor. No matter how many records are returned by the statement, PLSQL implicitly
Oracle uses two types of cursor: explicit cursor and implicit cursor. No matter how many records are returned by the statement, PL/SQL is implicitly used by each SQL command, such as UPDATE, DELETE, and INSERT.
How to count the total number of records deleted after execution of the delete statement in PLSQL.
Oracle uses two types of cursor: explicit cursor and implicit cursor. No matter how many records are returned by the statement, PL/SQL implicitly declares a cursor for each SQL command, such as UPDATE, DELETE, and INSERT. (To manage SQL statement processing, you must implicitly define a cursor for it .)
In PL/SQL, an implicit cursor is automatically opened when a DML statement is executed (the cursor is similar to the ResultSet in JDBC ), the data to be operated will be put into the implicit cursor first. Implicit cursors are automatically managed by ORACLE. If you want to perform some manual operations, you can use the attributes of implicit cursors. For example:
SQL % FOUND returns true if the record is obtained successfully; otherwise, false is returned.
SQL % NOTFOUND returns true if the record is obtained successfully; otherwise, false is returned.
SQL % ROWCOUNT returns the number of records retrieved from the cursor
SQL % ISOPEN always returns false
Using the implicit cursor attribute SQL % ROWCOUNT, you can calculate the number of records deleted.
The available DML SQL statements include INSERT/UPDATE/DELETE/MERGER.
For more information, see the following example:
Create or replace procedure test_02 is
Cnt int;
Begin
Delete from TEST_01;
Cnt: = SQL % rowcount;
Commit;
End;
Create or replace procedure test_03 is
Cnt int;
N_1 int;
N_2 int;
N_3 int;
Begin
Merge into test_04
Using (select * from test_09)
Merger_subquery
On (test_04.object_id = merger_subquery.object_id)
When not matched then
Insert (object_id, OBJECT_NAME)
Values (merger_subquery.object_id, merger_subquery.object_name)
When matched then
Update set object_name = merger_subquery.object_name;
Cnt: = SQL % rowcount;
N_1: = cnt;
Commit;
End;