Better solution of switch case

Question

Hello I need help about a Switch case better solution on oracle . So I have a function

FUNCTION GETSTAT(i_json_stats clob)  
RETURN  CRESPONSE AS

BEGIN 

    IF JSON_GET_STAT IS NOT NULL THEN
    FOR i IN 1..JSON_GET_STAT.count LOOP
    V_FUNCTION := json_ext.get_string(json(JSON_GET_STAT.get(i)), 'CLE');
      O_RETURN_RESPONSE := new CRESPONSE;

THE FIRST / THE SECOND SOLUTION BELOW

    END LOOP;
    END IF;

I have two solution , the first is to use switch and case But I want to call dynamicly the function if not I have to make 50 case ..

FIRST SOLUTION:

    case V_FUNCTION   
    when 'COUVERTURE_MAGASIN' then  v_json_collect.put(V_FUNCTION,COUVERTURE_MAGASIN(i_json_stats));
    when 'COUVERTURE_VOLUMES' then v_json_collect.put(V_FUNCTION,COUVERTURE_VOLUMES(i_json_stats));
    end case;

SECOND SOLUTION : but I dont know how to insert it in my GETSTAT Function

    execute immediate' begin v_json_collect.put(V_FUNCTION,'||V_FUNCTION||'(i_json_stats)); end';

Thanks for help

You should use the `CASE` statement. Don't use `EXECUTE IMMEDIATE` as the PL/SQL compiler can't detect which branch you are going to take and optimise its execution; it has to dynamically evaluate everything. It will also help prevent SQL injection attacks as you can white-list the function call names and won't get [little bobby tables](https://xkcd.com/327/) running havoc with malicious JSON data. — MT0, Oct 18 '19 at 12:52

Popeye · Answer 1 · 2019-10-18T12:34:53.413

0

You can use CASE WHEN but inside your function call.

v_json_collect.put(
V_FUNCTION,
case V_FUNCTION   
    when 'COUVERTURE_MAGASIN' 
    then COUVERTURE_MAGASIN(i_json_stats)
    when 'COUVERTURE_VOLUMES' 
    then COUVERTURE_VOLUMES(i_json_stats) 
end
);

If you want to use dynamic query then use following:

execute immediate 
'SELECT v_json_collect.put('|| V_FUNCTION|| ',' ||V_FUNCTION||'('||i_json_stats||'))' into return_value;

Cheers!!

edited Oct 18 '19 at 12:34

answered Oct 18 '19 at 12:25

Popeye

35,427
4
10
31

Thanks for this solution , but I also have to write all my 50 case :/ – DEVLOGIN Oct 18 '19 at 12:31
Yes, I was writing other solution for it when you did comment.. haha – Popeye Oct 18 '19 at 12:35

Better solution of switch case

1 Answers1