Implementation of oracle function return table

How should we implement the return table through the oracle function? The following describes how to implement an oracle function return table for your reference.

Use a stored procedure in the package and return the cursor.

> Package Definition
1) Baotou

 
 

  
  
create or replace package mypk  
  
  
as  
  
  
type t_cursor is ref cursor;  
  
  
procedure proc(name varchar2,c out t_cursor,a number);  
  
  
end; 
 
 

2) package body

 
 

  
  
create or replace package body mypk  
  
  
as  
  
  
procedure proc(name varchar2,c out t_cursor,a number)  
  
  
as   
  
  
begin  
  
  
open c for select * from test where id=a and namename=name;  
  
  
end proc;  
  
  
end;  
 
 

This solution has too many limitations to meet the requirements of select * from function ().

Since oracle 9i, a concept called "pipeline table function" is provided to solve this problem.
This type of function must return a set type and indicate pipelined
This oracle function cannot return specific variables and must return an empty return
In this oracle function, each row in the table to be returned is sent using the pipe row () statement.

When this oracle function is called, the pipeline stream is simulated as a dataset using the table () keyword.

The following is a simple example:

 
 

  
  
create table tb1(k number, v varchar2(10));  
  
  
 
  
  
insert into tb1(k, v) values(100,'aaa');  
  
  
insert into tb1(k, v) values(200,'bbb');  
  
  
insert into tb1(k, v) values(200,'ccc');  
  
  
 
  
  
select * from tb1;  
  
  
 
  
  
create type row_type1 as object(k number, v varchar2(10));  
  
  
 
  
  
create type table_type1 as table of row_type1;  
  
  
 
  
  
create or replace function fun1 return table_type1 pipelined as  
  
  
v row_type1;  
  
  
begin  
  
  
for myrow in (select k, v from tb1) loop  
  
  
v := row_type1(myrow.k, myrow.v);  
  
  
pipe row (v);  
  
  
end loop;  
  
  
return;  
  
  
end;  
  
  
 
  
  
select * from table(fun1);  
  
  
 
 
 

If an oracle function has parameters, you can write the following statement:

 
 

  
  
create or replace function fun1(i_v Int) return table_type1 pipelined as  
  
  
v1 row_type1;  
  
  
begin  
  
  
for myrow in (select k, v from tb1 Where k = i_v) loop  
  
  
v1 := row_type1(myrow.k, myrow.v);  
  
  
pipe row (v1);  
  
  
end loop;  
  
  
return;  
  
  
end;  
  
  
 
  
  
select * from table(fun1(100));  
 
 

This scheme can basically meet the requirements of the returned table, but it should be noted that the application of too many set objects is not conducive to management.
 
 

Learn about the Oracle FBI Index

How to uninstall an Oracle database in Windows

How to install Oracle as a Linux Service

Multiple table Connection Methods in Oracle

Example of using SQL recursive statements in oracle