Updating multiple rows in pl sql
I have a PL/SQL function (running on Oracle 10g) in which I update some rows.Is there a way to find out how many rows were affected by the UPDATE?As you can probably guess, there are situations where using PL/SQL can be the better option.
The basic problem is that SQL%ROWCOUNT is a PL/SQL variable (or function), and cannot be directly accessed from an SQL command. 1 client updated for 10 After running update statement if you check the count on what you actually updated - This doesn't give any generic solution.In Example 6-1, a PL/SQL anonymous block declares three PL/SQL variables and uses them in the static SQL statements DROP TABLE employees_temp; CREATE TABLE employees_temp AS SELECT employee_id, first_name, last_name FROM employees; DECLARE emp_id employees_temp.employee_id%TYPE := 299; emp_first_name employees_temp.first_name%TYPE := 'Bob'; emp_last_name employees_temp.last_name%TYPE := 'Henry'; BEGIN DROP TABLE employees_temp; CREATE TABLE employees_temp AS SELECT employee_id, first_name, last_name FROM employees; DROP TABLE employees_temp2; CREATE TABLE employees_temp2 AS SELECT employee_id, first_name, last_name FROM employees; DECLARE seq_value NUMBER; BEGIN .You can get information about any session cursor from its attributes (which you can reference in procedural statements, but not in SQL statements).The following is an example of a real-world scenario that I have encountered a few times already in my career and expect to see a few times more before I hang up the keyboard and mouse for a set of golf clubs.Consider the following two tables: You have been tasked with updating the employee salaries in the EMPLOYEES table with the maximum salary for the same employee, based on EMPLOYEE_ID, in the NEW_SALARY_INFO table. There is no index on the NEW_SALARY_INFO table so the database will have to full scan a four million row table for each of the one million employee records to find the maximum salary.