Kayshav.com
About Developer Oracle 12c Oracle 19c Technology Information Sitemap

Oracle Table - New Features
-- In Oracle 12c, a function (or procedure) can be created in a WITH clause as 
-- shown below.

WITH
 FUNCTION f_num_type(i_num IN SIMPLE_INTEGER) RETURN VARCHAR2
 IS
  v_type   VARCHAR2(5) := 'ODD';
 BEGIN
  IF MOD(i_num, 2) = 0 THEN
     v_type := 'EVEN';
  END IF;
  RETURN v_type;
 END f_num_type;
SELECT
  level  idx,
  f_num_type(i_num => level) number_type
FROM DUAL
CONNECT BY level <=5;

-- In Oracle 12c, a function can be created with BOOLEAN input as shown below

WITH
 FUNCTION f_num_type(i_num IN SIMPLE_INTEGER,
     i_bol IN BOOLEAN DEFAULT TRUE) RETURN VARCHAR2
 IS
   v_type   VARCHAR2(5);
 BEGIN
  IF i_bol THEN
    IF MOD(i_num, 2) = 0 THEN
      v_type := 'EVEN';
    ELSE
      v_type :=  'ODD';
    END IF;
  ELSE
    v_type := '-';
  END IF;
  RETURN v_type;
 END f_num_type;
SELECT 
 level  idx, 
 f_num_type(i_num => level) number_type
FROM DUAL
CONNECT BY level <=5;

Function Output

--Cummulative Summing of Numbers

WITH
 FUNCTION f_cumm_sum(i_num IN SIMPLE_INTEGER
   i_bol IN BOOLEAN DEFAULT TRUE)  RETURN NUMBER
 IS
 BEGIN
  IF i_bol THEN
    IF i > 0 THEN
      RETURN i_num + f_cumm_sum(i_num - 1);
    ELSE
      RETURN 0;
    END IF;
  ELSE
    RETURN -1;
  END IF;
 END f_cumm_sum;
SELECT 
 level  idx, 
 f_cumm_sum(i_num => level) number_type
FROM DUAL
CONNECT BY level <=5;

Function Output


WITH syntax more ..

Execute Query within Query

Oracle 12c Index

Oracle registered trademark of Oracle Corporation.

Last Revised On: June 27th, 2016

  11061