Use of oracle custom functions

Oracle user-defined functions are one of the most important functions. The following describes how to use oracle user-defined functions to implement the return table type, which is helpful to you.

Functions in oracle can return the table type. However, this table type is actually a collection type similar to an array) This type cannot be directly used as the from object.

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

When this 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);  
 
 

 

Oracle TRIM function syntax

Oracle date functions

Oracle trunc () function usage

Syntax for creating an Oracle package

Use of Oracle to_char Functions