In PL/SQL, you can use the SQL%ROWCOUNT attribute to determine the number of rows affected by an UPDATE statement. SQL%ROWCOUNT is a built-in attribute that returns the number of rows affected by the last executed SQL statement.
Here is an example of how to use SQL%ROWCOUNT in a PL/SQL block to determine the number of rows affected by an UPDATE statement:
DECLARE
rows_affected INTEGER;
BEGIN
UPDATE employees
SET salary = salary * 1.1
WHERE department_id = 30;
rows_affected := SQL%ROWCOUNT;
DBMS_OUTPUT.PUT_LINE('Number of rows affected: ' || rows_affected);
END;
In this example, the UPDATE statement updates the salary of all employees in department 30 by increasing it by 10%. The SQL%ROWCOUNT attribute is then used to store the number of rows affected by the UPDATE statement in the variable "rows_affected".
Finally, the number of rows affected is displayed using the "DBMS_OUTPUT.PUT_LINE" procedure.
Please note that if you use SQL%ROWCOUNT after a SELECT statement, it will return the number of rows selected by the query. Also, if you use SQL%ROWCOUNT after a DDL statement like CREATE, ALTER, or DROP it will return 0.
Please note that if you use SQL%ROWCOUNT after a SELECT statement, it will return the number of rows selected by the query. Also, if you use SQL%ROWCOUNT after a DDL statement like CREATE, ALTER, or DROP it will return 0.
You can also use the RETURNING clause in the update statement to return the number of rows affected.
BEGIN
UPDATE employees
SET salary = salary * 1.1
WHERE department_id = 30
RETURNING employee_id BULK COLLECT INTO employee_ids;
DBMS_OUTPUT.PUT_LINE('Number of rows affected: ' || employee_ids.COUNT);
END;
In this example, the RETURNING clause returns the employee_id of all the rows that were updated by the statement, and the COUNT function is used to count the number of the returned employee_ids.
0 Komentar