In order to iterate over rows in a table or query result, you need to use a record variable or a list of scalar variables as the loop variable. A record variable is a variable that can hold multiple values of different data types.
Here is an example to help illustrate the concept:
“`sql
DECLARE
— Declaring a record variable
emp_row employees%ROWTYPE;
BEGIN
— Looping over rows in a table
FOR emp_row IN (SELECT * FROM employees) LOOP
— Accessing individual columns of the current row
DBMS_OUTPUT.PUT_LINE(‘Employee ID: ‘ || emp_row.employee_id);
DBMS_OUTPUT.PUT_LINE(‘First Name: ‘ || emp_row.first_name);
DBMS_OUTPUT.PUT_LINE(‘Last Name: ‘ || emp_row.last_name);
END LOOP;
END;
“`
In the example above, we declare a record variable `emp_row` of type `employees%ROWTYPE`. This allows us to hold a row from the `employees` table, which consists of multiple columns such as `employee_id`, `first_name`, and `last_name`.
Inside the loop, we can access the individual columns of the current row by using dot notation (`emp_row.column_name`). We can then perform any desired operations or manipulate the data as needed.
Note that the above example assumes the usage of Oracle PL/SQL.