Introduction to the Table set returned by Oracle Functions

When calling the Oracle function, in order for the PL/SQL function to return multiple rows of data, a REF CURSOR or a data set must be returned. Ref cursor is limited to the data that can be selected from the query. The entire set must be specific before it can be returned. 9i corrected the latter situation by introducing the pipeline table function in Oracle functions. A table function is a function that returns a set of the entire row. It can be queried directly from an SQL statement, just as if it is a real database table. Pipeline table functions are similar, but they return data as they are during build, rather than all at once. Pipeline table functions are more effective because data can be returned as quickly as possible.

The canonicalized table function must return a set. In a function, the pipe row statement is used to RETURN a single element of the set. The function must end with an empty RETURN statement to indicate that it has been completed. Once we create the above function, we can use the TABLE operator to call it from the SQL query, so that the Oracle function returns the Table set.

1. Use a custom type

 
 

  
  
/* Formatted on 2010/02/26 08:42 (Formatter Plus v4.8.8) */  
  
  
CREATE OR REPLACE TYPE objemp AS OBJECT (  
  
  
   maxsal   NUMBER,  
  
  
   minsal   NUMBER  
  
  
);  
  
  
/* Formatted on 2010/02/26 08:43 (Formatter Plus v4.8.8) */  
  
  
CREATE OR REPLACE TYPE tabemp AS TABLE OF objemp;  
  
  
 
  
  
   
 
 

2. Use the Pipeline Function and Pipe row ()

 
 

  
  
CREATE OR REPLACE FUNCTION getmaxminsalary (department NUMBER)  
  
  
   RETURN tabemp PIPELINED  
  
  
AS  
  
  
   maximum_salary   NUMBER;  
  
  
   minimum_salary   NUMBER;  
  
  
   v_errorcode      NUMBER;  
  
  
   v_errortext      VARCHAR2 (200);  
  
  
   v                objemp;  
  
  
BEGIN  
  
  
   FOR myrow IN (SELECT MAX (sal) m_sal, MIN (sal) min_sal  
  
  
                   FROM emp  
  
  
                  WHERE deptno = departmnet)  
  
  
   LOOP  
  
  
      v := (myrow.m_sal, myrow.min_sal);  
  
  
      PIPE ROW (v);  
  
  
   END LOOP;  
  
  
   RETURN;  
  
  
EXCEPTION  
  
  
   WHEN OTHERS  
  
  
   THEN  
  
  
      v_errorcode := SQLCODE;  
  
  
      v_errortext := SUBSTR (SQLERRM, 1, 200);  
  
  
      INSERT INTO log_table  
  
  
                  (code, MESSAGE, info  
  
  
                  )  
  
  
           VALUES (v_errorcode, v_errortext, 'getMaxMinSalary'  
  
  
                 );  
  
  
END;  
  
  
 
 
 

3. Use the Table Operator

 
 

  
  
SELECT * FROM TABLE(getMaxMinSalary(10)); 
 
 

The above is an example of a good Oracle function that returns the Table set. If you are interested, try it.