37.11. Portage d'Oracle PL/SQL

Exemple 37-2. Portage d'une Fonction Simple de PL/SQL vers PL/pgSQL

Voici une fonction en PL/SQL Oracle :

CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name IN varchar, v_version IN varchar)
RETURN varchar IS
BEGIN
    IF v_version IS NULL THEN
        RETURN v_name;
    END IF;
    RETURN v_name || '/' || v_version;
END;
/
show errors;

Parcourons cette fonction et voyons les différences avec PL/pgSQL :

• PostgreSQL n'a pas de paramètres nommés. Vous devez les expliciter ou les aliaser dans votre fonction.

• Oracle peut avoir des paramètres IN, OUT, et INOUT passées aux fonctions. INOUT, par exemple, signifie que le paramètre recevra une valeur et en renverra une autre. PostgreSQL n'a que les paramètres IN.

• Le mot clé RETURN dans le prototype de la fonction (pas dans le corps de la fonction) devient RETURNS dans PostgreSQL.

• Dans PostgreSQL, les fonctions sont créées en utilisant des guillemets simples comme délimiteurs du corps de la fonction, vous devez donc échapper les guillemets simples dans le corps de la fonction.

• La commande /show errors n'existe pas dans PostgreSQL.

Voici de quoi aurait l'air cette fonction portée sous PostgreSQL :

CREATE OR REPLACE FUNCTION cs_fmt_browser_version(varchar, varchar)
RETURNS varchar AS '
DECLARE
    v_name ALIAS FOR $1;
    v_version ALIAS FOR $2;
BEGIN
    IF v_version IS NULL THEN
        return v_name;
    END IF;
    RETURN v_name || ''/'' || v_version;
END;
' LANGUAGE plpgsql;

Exemple 37-3. Portage d'une fonction qui crée une autre fonction de PL/SQL vers PL/pgSQL

La procédure suivante récupère des lignes d'une instruction SELECT et construit une grande fonction dont les résultats sont dans une instruction IF pour favoriser l'efficacité. Notez particulièrement les différences dans le curseur et la boucle FOR.

Voici la version Oracle :

CREATE OR REPLACE PROCEDURE cs_update_referrer_type_proc IS
    CURSOR referrer_keys IS 
        SELECT * FROM cs_referrer_keys 
        ORDER BY try_order;

    a_output VARCHAR(4000); 
BEGIN 
    a_output := 'CREATE OR REPLACE FUNCTION cs_find_referrer_type(v_host IN VARCHAR, v_domain IN VARCHAR, 
v_url IN VARCHAR) RETURN VARCHAR IS BEGIN'; 

    FOR referrer_key IN referrer_keys LOOP 
        a_output := a_output || ' IF v_' || referrer_key.kind || ' LIKE ''' || 
referrer_key.key_string || ''' THEN RETURN ''' || referrer_key.referrer_type || 
'''; END IF;'; 
    END LOOP; 

    a_output := a_output || ' RETURN NULL; END;'; 
    EXECUTE IMMEDIATE a_output; 
END; 
/ 
show errors;

Voici comment la fonction serait dans PostgreSQL :

CREATE FUNCTION cs_update_referrer_type_proc() RETURNS integer AS '
DECLARE
    referrer_keys RECORD;  -- Declare a generic record to be used in a FOR
    a_output varchar(4000);
BEGIN 
    a_output := ''CREATE FUNCTION cs_find_referrer_type(varchar, varchar, varchar) 
                  RETURNS varchar AS '''' 
                     DECLARE 
                         v_host ALIAS FOR $1; 
                         v_domain ALIAS FOR $2; 
                         v_url ALIAS FOR $3;
                     BEGIN ''; 

    -- Remarquez comment nous parcourons les résultats d'une requête dans une boucle FOR
    -- en utilisant la construction FOR <record>.

    FOR referrer_keys IN SELECT * FROM cs_referrer_keys ORDER BY try_order LOOP
        a_output := a_output || '' IF v_'' || referrer_keys.kind || '' LIKE '''''''''' 
                 || referrer_keys.key_string || '''''''''' THEN RETURN '''''' 
                 || referrer_keys.referrer_type || ''''''; END IF;''; 
    END LOOP; 
  
    a_output := a_output || '' RETURN NULL; END; '''' LANGUAGE plpgsql;''; 
 
    -- EXECUTE fonctionnera parce que nous ne substituons aucune variable.
    -- Autrement cela échouerait.  Regardez PERFORM pour d'autres façons d'exécuter une fonction.
    
    EXECUTE a_output; 
END; 
' LANGUAGE plpgsql;

Exemple 37-4. Portage d'une Procédure avec Manipulation de Chaînes et Paramètres OUT de PL/SQL vers PL/pgSQL

La procédure Oracle suivante est utilisées pour analyser une URL et renvoyer plusieurs éléments (hôte, chemin et requête). Les fonctions PL/pgSQL ne peuvent renvoyer qu'une seule valeur. Dans PostgreSQL, un moyen de contourner cela est de décomposer la procédure en trois fonctions différentes : la première pour renvoyer l'hôte, une autre pour le chemin et la denière pour la requête.

Voici la version Oracle :

CREATE OR REPLACE PROCEDURE cs_parse_url(
    v_url IN VARCHAR,
    v_host OUT VARCHAR,  -- Celle-ci sera passée en retour
    v_path OUT VARCHAR,  -- Celle-là aussi
    v_query OUT VARCHAR) -- Et celle-là
IS
    a_pos1 INTEGER;
    a_pos2 INTEGER;
BEGIN
    v_host := NULL;
    v_path := NULL;
    v_query := NULL;
    a_pos1 := instr(v_url, '//');

    IF a_pos1 = 0 THEN
        RETURN;
    END IF;
    a_pos2 := instr(v_url, '/', a_pos1 + 2);
    IF a_pos2 = 0 THEN
        v_host := substr(v_url, a_pos1 + 2);
        v_path := '/';
        RETURN;
    END IF;

    v_host := substr(v_url, a_pos1 + 2, a_pos2 - a_pos1 - 2);
    a_pos1 := instr(v_url, '?', a_pos2 + 1);

    IF a_pos1 = 0 THEN
        v_path := substr(v_url, a_pos2);
        RETURN;
    END IF;

    v_path := substr(v_url, a_pos2, a_pos1 - a_pos2);
    v_query := substr(v_url, a_pos1 + 1);
END;
/
show errors;

Voici à quoi ressemble la fonction PL/pgSQL renvoyant la partie hôte :

CREATE OR REPLACE FUNCTION cs_parse_url_host(varchar) RETURNS varchar AS '
DECLARE
    v_url ALIAS FOR $1;
    v_host varchar;
    v_path varchar;
    a_pos1 integer;
    a_pos2 integer;
    a_pos3 integer;
BEGIN 
    v_host := NULL; 
    a_pos1 := instr(v_url, ''//'');

    IF a_pos1 = 0 THEN 
        RETURN '''';  -- Renvoie un blanc
    END IF; 

    a_pos2 := instr(v_url,''/'',a_pos1 + 2);
    IF a_pos2 = 0 THEN 
        v_host := substr(v_url, a_pos1 + 2); 
        v_path := ''/''; 
        RETURN v_host; 
    END IF; 

    v_host := substr(v_url, a_pos1 + 2, a_pos2 - a_pos1 - 2 ); 
    RETURN v_host; 
END; 
' LANGUAGE plpgsql;

Exemple 37-5. Portage d'une procédure de PL/SQL vers PL/pgSQL

La version Oracle :

CREATE OR REPLACE PROCEDURE cs_create_job(v_job_id IN INTEGER) IS
    a_running_job_count INTEGER;
    PRAGMA AUTONOMOUS_TRANSACTION;(1)
BEGIN
    LOCK TABLE cs_jobs IN EXCLUSIVE MODE;(2)

    SELECT count(*) INTO a_running_job_count FROM cs_jobs WHERE end_stamp IS NULL;

    IF a_running_job_count > 0 THEN
        COMMIT; -- free lock(3)
        raise_application_error(-20000, 'Unable to create a new job: a job is currently running.');
    END IF;

    DELETE FROM cs_active_job;
    INSERT INTO cs_active_job(job_id) VALUES (v_job_id);

    BEGIN
        INSERT INTO cs_jobs (job_id, start_stamp) VALUES (v_job_id, sysdate);
        EXCEPTION WHEN dup_val_on_index THEN NULL; -- ne vous inquietez pas si cela existe déjà(4)
    END;
    COMMIT;
END;
/
show errors

Les procédures comme celles-ci peuvent être aisément converties en fonctions PostgreSQL renvoyant un integer. Cette procédure en particulier est intéressante parce qu'elle peut nous apprendre diverses choses :

Voici comment nous pourrions porter cette procédure vers PL/pgSQL :

CREATE OR REPLACE FUNCTION cs_create_job(integer) RETURNS integer AS '
DECLARE
    v_job_id ALIAS FOR $1;
    a_running_job_count integer;
    a_num integer;
BEGIN
    LOCK TABLE cs_jobs IN EXCLUSIVE MODE;
    SELECT count(*) INTO a_running_job_count FROM cs_jobs WHERE end_stamp IS NULL;

    IF a_running_job_count > 0
    THEN
        RAISE EXCEPTION ''Unable to create a new job: a job is currently running.'';
    END IF;

    DELETE FROM cs_active_job;
    INSERT INTO cs_active_job(job_id) VALUES (v_job_id);

    SELECT count(*) INTO a_num FROM cs_jobs WHERE job_id=v_job_id;
    IF NOT FOUND THEN  -- If nothing was returned in the last query
        -- This job is not in the table so lets insert it.
        INSERT INTO cs_jobs(job_id, start_stamp) VALUES (v_job_id, current_timestamp);
        RETURN 1;
    ELSE
        RAISE NOTICE ''Job already running.'';(1)
    END IF;

    RETURN 0;
END;
' LANGUAGE plpgsql;