39.12. Portage d'Oracle PL/SQL

Voici une fonction en PL/SQL OracleTM :

CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name varchar, v_version 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 :

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

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

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é.

Voici la version Oracle :

CREATE OR REPLACE PROCEDURE cs_update_referrer_type_proc IS
 referrer_keys CURSOR IS
 SELECT * FROM cs_referrer_keys 
 ORDER BY try_order;
 func_cmd VARCHAR(4000); 
BEGIN 
 func_cmd := '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 
 func_cmd := func_cmd ||
 ' IF v_' || referrer_key.kind
 || ' LIKE ''' || referrer_key.key_string
 || ''' THEN RETURN ''' || referrer_key.referrer_type
 || '''; END IF;'; 
 END LOOP; 
 func_cmd := func_cmd || ' RETURN NULL; END;'; 
 EXECUTE IMMEDIATE func_cmd; 
END; 
/ 
show errors;

Voici comment la fonction serait dans PostgreSQLTM :

CREATE OR REPLACE FUNCTION cs_update_referrer_type_proc() RETURNS void AS $func$
DECLARE
 CURSOR referrer_keys IS
 SELECT * FROM cs_referrer_keys
 ORDER BY try_order;
 func_body text;
 func_cmd text;
BEGIN 
 func_body := 'BEGIN' ;
 FOR referrer_key IN SELECT * FROM cs_referrer_keys ORDER BY try_order LOOP
 func_body := func_body ||
 ' IF v_' || referrer_key.kind
 || ' LIKE ' || quote_literal(referrer_key.key_string)
 || ' THEN RETURN ' || quote_literal(referrer_key.referrer_type)
 || '; END IF;' ;
 END LOOP; 
 func_body := func_body || ' RETURN NULL; END;';
 func_cmd :=
 'CREATE OR REPLACE FUNCTION cs_find_referrer_type(v_host varchar,
 v_domain varchar,
 v_url varchar) 
 RETURNS varchar AS '
 || quote_literal(func_body)
 || ' LANGUAGE plpgsql;' ;
 EXECUTE func_cmd;
END;
$func$ LANGUAGE plpgsql;

Notez comment le corps de la fonction est construit séparément et est passé au travers de quote_literal pour doubler tout symbole guillemet qu'il peut contenir. Cette technique est nécessaire parce que nous ne pouvons pas utiliser à coup sûr les guillemets dollar pour définir la nouvelle fonction : nous ne sommes pas sûr de savoir quelle chaîne sera interpolée à partir du champ referrer_key.key_string (nous supposons ici que ce referrer_key.kind vaut à coup sûr host, domain ou url mais referrer_key.key_string pourrait valoir autre chose, il pourrait contenir en particulier des signes dollar). Cette fonction est en fait une amélioration de l'original Oracle parce qu'il ne génèrera pas de code cassé quand referrer_key.key_string ou referrer_key.referrer_type contient des guillemets.

La procédure OracleTM suivante est utilisée 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.

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 une traduction possible en PL/pgSQL :

CREATE OR REPLACE FUNCTION cs_parse_url(
 v_url IN VARCHAR,
 v_host OUT VARCHAR, -- This will be passed back
 v_path OUT VARCHAR, -- This one too
 v_query OUT VARCHAR) -- And this one
AS $$
DECLARE
 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;
$$ LANGUAGE plpgsql;

Cette fonction pourrait être utilisée ainsi :

SELECT * FROM cs_parse_url('http://foobar.com/query.cgi?baz');

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; 
 SELECT count(*) INTO a_running_job_count FROM cs_jobs WHERE end_stamp IS NULL;
 IF a_running_job_count > 0 THEN
 COMMIT; -- free lock3
 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à
 END;
 COMMIT;
END;
/
show errors

Les procédures comme celles-ci peuvent être aisément converties en fonctions PostgreSQLTM renvoyant un void. 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(v_job_id integer) RETURNS void AS $$
DECLARE
 a_running_job_count 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';1
 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, now());
 EXCEPTION
 WHEN unique_violation THEN 2
 -- ne vous inquietez pas si cela existe déjà
 END;
END;
$$ LANGUAGE plpgsql;

La principale différence fonctionnelle entre cette procédure et l'équivalent Oracle est que le verrou exclusif sur la table cs_jobs sera détenu jusqu'à la fin de la transaction appelante. De plus, si l'appelant annule plus tard (par exemple à cause d'une erreur), les effets de cette procédure seront annulés.