Showing posts with label for loop. Show all posts
Showing posts with label for loop. Show all posts

Wednesday, October 3, 2018

Oracle 12c parsing table row by row with for and switch case

Function that will be used:

CREATE OR REPLACE FUNCTION fun2( p_c_id IN CHAR, p_name IN VARCHAR2, p_regio IN NUMBER) 
RETURN VARCHAR2 
AS 
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO CON (COUNTRY_ID, COUNTRY_NAME, REGION_ID)
  VALUES ( p_c_id, p_name, p_regio);
COMMIT;
RETURN p_name || ' mod';
END;
/


Parse row by row using CASE SWITCH:

SELECT REGION_ID,
       CASE region_id
           WHEN 2 THEN fun2(country_id, country_name, region_id )
           WHEN 4 THEN 'four'
           ELSE 'not'
       END AS c
FROM countries


Parse row by row using loop

BEGIN
FOR c IN (SELECT field1, field2 FROM mytable) 
  LOOP 
    my_proc(c.field1, c.field2);
  END LOOP;
END;