What is the purpose of the orcacle cursor(select ..) query in Oracle?
oracle, plsql
Solution
CURSOR expressions are a seldom-used feature that enable you to pass sets of data to a PL/SQL procedure.
This allows advanced functionality such as chaining parallel pipelined functions. That provides a way to quickly process multiple stages of procedural code. See the PL/SQL Language Reference for an example.
It is an interesting feature but using CURSOR expressions is often a huge mistake. It means that most of your processing will be done in PL/SQL instead of SQL. PL/SQL is great for controlling SQL but it is generally not where you want to do the heavy lifting.
I've only seen CURSOR expressions used for two reasons:
- Developers were unaware of intermediate or advanced SQL features, such as analytic functions, MODEL, etc.
- Building an enterprise rules engine.
Problem
I am trying to understand the purpose of using cursor(select..) queries. For example, I got this from the oracle doc. I can just join employees with departments table. What's the deal with the cursor? ``` SELECT department_name, CURSOR(SELECT salary, commission_pct FROM employees e WHERE e.department_id = d.department_id) FROM departments d ORDER BY department_name; ```