What are set at a time operations and in what situation would processing a set of data be needed to be done by the application program?
Cheers.
If all the relevant data is in an Oracle database then the processing never requires procedural code.
Oracle SQL has many methods for processing data, such as inline views, analytic functions, MODEL clauses, WITH clauses, etc. Combining those can solve any programming problem. That doesn't necessarily mean everything should be done in a single SQL statement. As you get comfortable with those features your SQL statements will get larger and faster, and the overall amount of code will get smaller. But there's always a point where regular old procedural code makes more sense.
Your original statement is partially correct: "cursors work at a 'set at a time process' for the DBMS." Row-by-row processing with a cursor is usually the opposite of set-based processing. But Oracle can still do a few things to make parts of the process set-based.
For example, look at this simple code to join an EMPLOYEE and DEPARTMENT tables and print the results.
begin
for employee_and_departments in
(
select employee.employee_name, department.department_name
from employee
join department
on employee.department_id = department.department_id
order by employee_name
) loop
dbms_output.put_line(employee.employee_name||', '||
department.department_name);
end loop;
end;
/
This is not how the anonymous block is processed:
For each row:
1) Use index to retrieve one employee record, in order
2) Use index to lookup relevant row in department record
3) return one row
4) print one result
The details vary depending on the execution plan, but this is a more likely way to process the anonymous block:
1) Hash the department table and store it in memory
2) Join it with employee table
3) Order results
4) Retrieve 100 rows at a time, and for each row:
4a) Print one result
SQL operations and data retrieval are frequently done in bulk even when not requested. Part of the statement is still done row-by-row, but most of the heavy lifting is automatically done in batches. There is still room for massive improvement when procedural PL/SQL is converted to declarative SQL. But a lot of code benefits from set-based processing without knowing it.