-- 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
10624