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

Oracle 12c - WITH Syntax
-- In Oracle 12c, a function can be created with BOOLEAN input as shown below. 
-- The function is created with default value of TRUE so that the input is 
-- optional.

WITH
 FUNCTION fn_factorial(i_num IN SIMPLE_INTEGER,
     i_bol IN BOOLEAN DEFAULT TRUE) RETURN  NUMBER
 IS
 BEGIN
  IF i_num > 1 THEN
    RETURN i_num * fn_factorial(i_num -1);
  ELSE
    RETURN 1;
  END IF;
 END fn_factorial;
SELECT 
 level  idx, 
 fn_factorial(i_num => level) factorial_value
FROM DUAL
CONNECT BY level <=15;

Factorial

-- Pingala/Hemachandra/Fibonacci Series
WITH
 FUNCTION fn_fhseries(i_num IN SIMPLE_INTEGER,
     i_bol IN BOOLEAN DEFAULT TRUE) RETURN  NUMBER
 IS
   v_val   SIMPLE_INTEGER :=0;
 BEGIN
  v_val :=
    CASE
     WHEN i_num = 1 THEN 0
     WHEN i_num = 2 THEN 1
     ELSE fn_fhseries(i_num-1) + fn_fhseries(i_num-2)
    END;
  RETURN v_val;
 END fn_fhseries;
SELECT 
 level  idx, 
 fn_fhseries(i_num => level) fh_series
FROM DUAL
CONNECT BY level <=15;

Pingala/Hemachandra/Fibonacci Series


WITH syntax more ..

Execute Query within Query

Oracle 12c Index    Oracle 19c Index

Oracle registered trademark of Oracle Corporation.

Last Revised On: June 27th, 2020

  11054