The example below shows embedding a function (subfunction) within a table function.

Table Function To Get Prime Numbers
-- Create Type
CREATE OR REPLACE TYPE typ_num_tab IS TABLE OF NUMBER; 
/

-- Table Function To Get Prime Numbers
CREATE OR REPLACE FUNCTION pf_get_prime(i_num IN PLS_INTEGER)
 RETURN typ_num_tab DETERMINISTIC PIPELINED 
AS 

FUNCTION is_prime(i_num IN PLS_INTEGER)
RETURN PLS_INTEGER AS
   v_div    PLS_INTEGER := 1;
   o_int    PLS_INTEGER;
BEGIN  
  WHILE v_div < i_num LOOP
   v_div := v_div+1;
   IF MOD(i_num,v_div) = 0 AND v_div < i_num THEN 
      EXIT;
   END IF;
  END LOOP;
  IF v_div = i_num THEN
     o_int := i_num;
  END IF;
  RETURN o_int;
END is_prime;

BEGIN 
  FOR idx IN 1..i_num LOOP
    IF is_prime(idx) IS NOT NULL THEN
      PIPE ROW(is_prime(idx));
    END IF;
  END LOOP; 
  RETURN; 
EXCEPTION 
  WHEN OTHERS THEN RAISE; 
END pf_get_prime;
/

-- Query To Get Prime Numbers
SELECT ROWNUM ref_number, q_tab.*
FROM TABLE(pf_get_prime(&i_val)) q_tab;
 
Query Using Pipeline Function:
Prime Numbers between 1 and 20
Table Function Output - Prime Numbers

Pipeline Function to get factorial values

Abbreviations and Math Reference

Oracle registered trademark of Oracle Corporation.

Last Revised On: July 06th, 2014

  1157