How to choose between "pipelined table function" , view , and explicit cusor

Question

I'll start by saying what I think I have understood.

A explicit cursor is used because we need to reuse the query later. If an non-explicit cursor is used (i.e. for cs in (select .........)), the request is reexecuted each time the cursor is used.. Therefore an explicit cursor is used for efficienty.

To factor the code, we can use a "pipelined table function" or a "view" to create a cursor. I would to know like why should I use one solution over another.

Here is the pro and con that I know about these solutions:

neiter pro nor con

I can extract a part of the view or pipelined table function function with a select statement.

con

The record type, and table type used by the "pipelined table" must be declared. It takes time

pro: We can use all the possibilities of pl/sql statement inside a pipelined table such as a loop

Is all what I have said true? Is there other things that I should know?

Answer 1

With both cursor types, the database executes the statement within it when it opens. Provided the cursor remains open, you can fetch the results from it later without re-running it. So both are equally efficient in that respect.

An explicit cursor is one where you control its full lifecycle: open, fetch, and close. With an implicit cursor, PL/SQL handles this for you.

You use an explicit cursor when you want full control over the fetch process. The main use case for this bulk collection with a limit.

An explicit cursor can also be handy if you want to use the same query in many places in your application. Declare it at the package level and you can reference it anywhere else you like:

create or replace package pkg as 
  cursor common_cursor is 
    select ...
end;

This gives a single definition for the query, which can make your code more maintainable. The problem with this is you're on the hook for opening, fetching, and closing it wherever you use it. In most cases, this results in much more code for minimal benefit.

Which brings us to views. Instead of declaring the common cursor, you could place the common query in a view:

create or replace view common_query as 
  select ...;

You can then use this in any other SQL statement just like a regular table. So you can join to it, etc. You can't do this with an explicit cursor directly. You have to wrap it in a (pipelined) table function:

create or replace function pipetf
  return ...
  pipelined 
as
  retvals ...;
begin
  open pkg.common_cursor;
  loop
    fetch pkg.common_cursor
    bulk collect into retvals limit 100;
    
    exit when retvals.count = 0;
      
    for i in 1 .. retvals.count loop
      pipe row ( retvals ( i ) ) ;
    end loop;
  end loop;
  close pkg.common_cursor ;
  return;
end pipetf;
/

This allows you to use the cursor within another SQL statement like a view:

select * from pipetf;

At this point, a pipelined table function seems a lot more faff than a view. So why bother?

Well it allows you to do things views can't (easily):

• Generate new rows or manipulate the result set procedurally
• Create parameterized queries

In general you can't pass a variable to a query like this a view (there are ways, but they come with gotchas):

select c2 from ...
where  c1 = :var
group  by c2;

Whereas you can in an explicit cursor:

cursor common_cursor ( var int ) is 
  select c2 from ...
  where  c1 = var
  group  by c2;

So you could use this in a PTF to create a reusable, parameterized query:

create or replace function pipetf ( var int )
  return ...
  pipelined 
as
  retvals ...;
begin
  open pkg.common_cursor ( var );
  loop
    fetch pkg.common_cursor
    bulk collect into retvals limit 100;
    
    exit when retvals.count = 0;
      
    for i in 1 .. retvals.count loop
      pipe row ( retvals ( i ) ) ;
    end loop;
  end loop;
  close pkg.common_cursor ;
  return;
end pipetf;
/

So if you need to use PL/SQL to create new rows, manipulate a queries results, or want reusable parameterized queries, pipelined table functions were the way to go.

Why were?

Oracle Database 18c added polymorphic table functions, which covers many of the row generation/result manipulation examples. And from 19.6 you can create SQL macros, which you can use to emulate parameterized views. These features cover most (all?) the use cases for pipelined table functions (and more).

If you just need a reusable query with no extra processing, I'd stick with a view.