Prova Supero, Oracle

Prova Supero

DECLARE
  --
    input_1 CLOB := 
'4733251368
33251368
5547984461240
554784461240
047984461240
04784461240
47984461240
4784461240
984461240
84461240
190
10321
1052
10625';
    input_2 CLOB := 
'4733251368
33251368
5547984461240
554784461240
047984461240
04784461240
47984461240
4784461240
984461240
84461240
190
10321
1052
10625
554733251368
04733251368
0003';
    input_3 CLOB := 
'4733251368
33251368
5547984461240
554784461240
047984461240
04784461240
47984461240
4784461240
984461240
84461240
190
10321
1052
10625
554733251368
04733251368
0003
08007294568';
  --
    vr_pos       PLS_INTEGER := 1;
    vr_linha     VARCHAR2(32767);
    vr_tam_arq   PLS_INTEGER;
    vr_tam_linha PLS_INTEGER;
    vr_dserro   VARCHAR2(4000);
    --
    PROCEDURE pr_ret_tel_com_mascara(pr_nrtel    IN  VARCHAR2
                                      ,pr_nrtelatu OUT VARCHAR2
                                      ,pr_dserro   OUT VARCHAR2
                                      ) IS
    --
        vr_tamnrtel NUMBER;
        vr_nr_tel   NUMBER;
        --
    BEGIN
        --
        vr_tamnrtel := LENGTH(trim(replace(pr_nrtel, chr(13), '')));
        --
        CASE vr_tamnrtel 
            WHEN 13 THEN 
                --
                pr_nrtelatu := regexp_replace(pr_nrtel,'([0-9]{2})([0-9]{2})([0-9]{1})([0-9]{4})([0-9]{4})','MOB: +\1 (\2) \3 \4-\5');
                --
            WHEN 12 THEN
                --
                IF SUBSTR(pr_nrtel, 0, 1) = '0' THEN
                    --
                  pr_nrtelatu := regexp_replace(SUBSTR(pr_nrtel,2,LENGTH(pr_nrtel)),'([0-9]{2})([0-9]{1})([0-9]{4})([0-9]{4})','MOB: (\1) \2 \3-\4');
                    --
                ELSE
                    --
                    pr_nrtelatu := regexp_replace(pr_nrtel,'([0-9]{2})([0-9]{2})([0-9]{4})([0-9]{4})','MOB: +\1 (\2) \3-\4');
                    --
                END IF;
                --
            WHEN 11 THEN
                --
                IF SUBSTR(pr_nrtel, 0, 1) = '0' THEN
                    --
                  pr_nrtelatu := regexp_replace(SUBSTR(pr_nrtel,2,LENGTH(pr_nrtel)),'([0-9]{2})([0-9]{4})([0-9]{4})','MOB: (\1) \2-\3');
                    --
                ELSE
                    --
                    pr_nrtelatu := regexp_replace(pr_nrtel,'([0-9]{2})([0-9]{1})([0-9]{4})([0-9]{4})','MOB: (\1) \2 \3-\4');
                    --
                END IF;
                --
            WHEN 10 THEN
                --
                IF SUBSTR(pr_nrtel, 3, 1) = '8' OR SUBSTR(pr_nrtel, 3, 1) = '9' THEN
                    --
                  pr_nrtelatu := regexp_replace(pr_nrtel,'([0-9]{2})([0-9]{4})([0-9]{4})','MOB: (\1) \2-\3');
                    --
                ELSE
                    --
                    pr_nrtelatu := regexp_replace(pr_nrtel,'([0-9]{2})([0-9]{4})([0-9]{4})','RES: (\1) \2-\3');
                    --
                END IF;
                --
            WHEN 9 THEN
                --
                pr_nrtelatu := regexp_replace(pr_nrtel,'([0-9]{1})([0-9]{4})([0-9]{4})','MOB: \1 \2-\3');
                --
            WHEN 8 THEN
                --
                IF SUBSTR(pr_nrtel, 0, 1) = '3' THEN
                    --
                  pr_nrtelatu := regexp_replace(pr_nrtel,'([0-9]{4})([0-9]{4})','RES: \1-\2');
                    --
                ELSE
                    --
                    pr_nrtelatu := regexp_replace(pr_nrtel,'([0-9]{4})([0-9]{4})','MOB: \1-\2');
                    --
                END IF;
                --
            WHEN 5 THEN
                --
                IF SUBSTR(pr_nrtel, 0, 3) = '103' THEN
                    --
                  pr_nrtelatu := regexp_replace(pr_nrtel,'([0-9]{3})([0-9]{2})','ETF: \1+\2');
                    --
                ELSIF SUBSTR(pr_nrtel, 0, 3) = '106' THEN
                    --
                    pr_nrtelatu := regexp_replace(pr_nrtel,'([0-9]{5})','ETV: \1');
                    --
                ELSE
                    --
                    pr_nrtelatu := regexp_replace(pr_nrtel,'([0-9]{1})([0-9]{4})([0-9]{4})','MOB: \1 \2-\3');
                    --
                END IF;
                --
            WHEN 4 THEN
                --
                pr_nrtelatu := regexp_replace(pr_nrtel,'([0-9]{4})','ETM: \1');
                --
            WHEN 3 THEN
                --
                pr_nrtelatu := regexp_replace(pr_nrtel,'([0-9]{3})','SUP: \1');
                --
            ELSE 
                --
                pr_nrtelatu := 'N/A ' || pr_nrtel;
                --
        END CASE;
        --
    EXCEPTION
        WHEN OTHERS THEN
            pr_dserro := 'Erro na pr_ret_tel_com_mascara: ' || SQLERRM;
    END pr_ret_tel_com_mascara;
    --
BEGIN
    -- Manipula o arquivo input_1
    dbms_output.put_line('PROCESSAMENTO DO ARQUIVO INPUT_1');
    vr_tam_arq := LENGTH(input_1);
    --
    WHILE vr_pos <= vr_tam_arq LOOP
        --
    vr_tam_linha := INSTR(input_1
                              ,CHR(10)
                                                    ,vr_pos
                                                    ) - vr_pos;
      --
    IF vr_tam_linha < 0 THEN
            --
      vr_tam_linha := vr_tam_arq + 1 - vr_pos;
            --
    END IF;
        --
    vr_linha := SUBSTR(input_1
                        ,vr_pos
                                        ,vr_tam_linha
                                        );
    --
    dbms_output.put_line('Entrada: ' || vr_linha);
        --
        pr_ret_tel_com_mascara(pr_nrtel    => vr_linha  -- IN
                              ,pr_nrtelatu => vr_linha -- OUT
                                                    ,pr_dserro   => vr_dserro -- OUT
                                                    );
        IF vr_dserro IS NOT NULL THEN
         dbms_output.put_line('Erro ' || vr_dserro);
        END IF;
        --
        dbms_output.put_line('Saída: ' || vr_linha);
        --
    vr_pos:= vr_pos + vr_tam_linha + 1;
        --
  end loop;
    -- Manipula o arquivo input_2
    dbms_output.put_line('PROCESSAMENTO DO ARQUIVO INPUT_2');
    --
    vr_tam_arq := LENGTH(input_2);
    --
    vr_pos := 1;
    --
    WHILE vr_pos <= vr_tam_arq LOOP
        --
    vr_tam_linha := INSTR(input_2
                              ,CHR(10)
                                                    ,vr_pos
                                                    ) - vr_pos;
      --
    IF vr_tam_linha < 0 THEN
            --
      vr_tam_linha := vr_tam_arq + 1 - vr_pos;
            --
    END IF;
        --
    vr_linha := SUBSTR(input_2
                        ,vr_pos
                                        ,vr_tam_linha
                                        );
    --
    dbms_output.put_line('Entrada: ' || vr_linha);
        --
        pr_ret_tel_com_mascara(pr_nrtel    => vr_linha  -- IN
                              ,pr_nrtelatu => vr_linha  -- OUT
                                                    ,pr_dserro   => vr_dserro -- OUT
                                                    );
        IF vr_dserro IS NOT NULL THEN
         dbms_output.put_line('Erro ' || vr_dserro);
        END IF;
        --
        dbms_output.put_line('Saída: ' || vr_linha);
        --
    vr_pos:= vr_pos + vr_tam_linha + 1;
        --
  end loop;
    -- Manipula o arquivo input_3
    dbms_output.put_line('PROCESSAMENTO DO ARQUIVO INPUT_3');
    --
    vr_tam_arq := LENGTH(input_3);
    --
    vr_pos := 1;
    --
    WHILE vr_pos <= vr_tam_arq LOOP
        --
    vr_tam_linha := INSTR(input_3
                              ,CHR(10)
                                                    ,vr_pos
                                                    ) - vr_pos;
      --
    IF vr_tam_linha < 0 THEN
            --
      vr_tam_linha := vr_tam_arq + 1 - vr_pos;
            --
    END IF;
        --
    vr_linha := SUBSTR(input_3
                        ,vr_pos
                                        ,vr_tam_linha
                                        );
    --
    dbms_output.put_line('Entrada: ' || vr_linha);
        --
        pr_ret_tel_com_mascara(pr_nrtel    => vr_linha  -- IN
                              ,pr_nrtelatu => vr_linha -- OUT
                                                    ,pr_dserro   => vr_dserro -- OUT
                                                    );
        IF vr_dserro IS NOT NULL THEN
         dbms_output.put_line('Erro ' || vr_dserro);
        END IF;
        --c
        dbms_output.put_line('Saída: ' || vr_linha);
        --
    vr_pos:= vr_pos + vr_tam_linha + 1;
        --
  end loop;
    --
END;


run \| edit \| history \| help	0