-- The example below shows the use of Oracle analytic function -- COUNT(*) OVER (PARTITION BY -- DECODE(MOD(level,2),0,'EVEN','ODD')) -- to get counts split on the number type (num_type). The -- number string is then dynamically created. -- Similar output can be obtained using -- Oracle function LISTAGG DECLARE CURSOR c_num IS SELECT level idx, DECODE(MOD(level,2),0,'EVEN','ODD') num_type, COUNT(*) OVER (PARTITION BY DECODE(MOD(level,2),0,'EVEN','ODD')) cnts FROM DUAL CONNECT BY level <=10 ORDER BY idx; v_oidx SIMPLE_INTEGER := 0; v_eidx SIMPLE_INTEGER := 0; v_odd VARCHAR2(30); v_even VARCHAR2(30); v_str VARCHAR2(30); FUNCTION str_cc(i_val IN SIMPLE_INTEGER, i_cnt IN SIMPLE_INTEGER, i_str IN VARCHAR2) RETURN VARCHAR2 AS o_str VARCHAR2(30); BEGIN IF i_val < i_cnt THEN o_str := i_str||', '; ELSE o_str := i_str; END IF; RETURN o_str; END; BEGIN FOR num_rec IN c_num LOOP IF num_rec.num_type = 'ODD' THEN v_oidx := v_oidx +1; v_odd := v_odd|| str_cc(v_oidx, num_rec.cnts, num_rec.idx); ELSE v_eidx := v_eidx +1; v_even := v_even|| str_cc(v_eidx, num_rec.cnts, num_rec.idx); END IF; END LOOP; DBMS_OUTPUT.PUT_LINE('ODD -> '||v_odd); DBMS_OUTPUT.PUT_LINE('EVEN -> '||v_even); END; / => Analytic Function Output
52797