Procédures

(sujet préalablement traité avec la version 11 devel)


      Oracle Database peut embarquer et exécuter en base du Java (c’est très peu utilisé) et du PL/SQL (c’est très utilisé). Il est possible de créer des fonctions PL/SQL mais aussi des procédures si le programme ne doit pas renvoyer de résultat.
      En plus de la possibilité de créer des fonctions C et SQL, PostgreSQL offre de nombreux langages pour coder côté base. Quatre sont disponibles en standard : PL/pgSQL, un langage proche du PL/SQL, mais aussi PL/Perl, PL/Tcl, PL/Python.
      Il n’était pas possible jusqu’à la version 10 incluse de créer des procédures dans ces différents langages. Il était bien sûr possible de créer des fonctions retournant VOID mais, si vous le souhaitez, vous pouvez à présent créer directement des procédures avec CREATE PROCEDURE.
      Démonstration avec PostgreSQL 11 beta :

select version(); version --------------------------------------------------------------------------------------------------------------------------------------------------- PostgreSQL 11beta1 (Ubuntu 11~beta1-2.pgdg16.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.9) 5.4.0 20160609, 64-bit (1 ligne) start transaction; START TRANSACTION create table all_times(cu timestamp, st timestamp, cl timestamp); CREATE TABLE CREATE PROCEDURE pg_temp.insert_times() LANGUAGE SQL AS $$ INSERT INTO all_times(cu, st, cl) VALUES (current_timestamp, statement_timestamp(), clock_timestamp()); $$; CREATE PROCEDURE CREATE PROCEDURE pg_temp.pl_insert_times() AS $$ BEGIN EXECUTE 'INSERT INTO all_times(cu, st, cl) VALUES (current_timestamp, statement_timestamp(), clock_timestamp());'; END; $$ LANGUAGE plpgsql; CREATE PROCEDURE CALL pg_temp.insert_times(); CALL CALL pg_temp.pl_insert_times(); CALL select * from all_times; cu | st | cl ---------------------------+----------------------------+---------------------------- 2017-12-04 14:04:50.56782 | 2017-12-04 14:05:26.58376 | 2017-12-04 14:05:26.58455 2017-12-04 14:04:50.56782 | 2017-12-04 14:05:33.441893 | 2017-12-04 14:05:33.442741 (2 lignes) rollback; ROLLBACK

      Une procédure est appelée via CALL et non SELECT comme une fonction.
      Un aspect essentiel est la possibilité nouvelle qu'offrent les procédures d'embarquer des transactions (en clair intégrer des COMMIT et des ROLLBACK). Démonstration :

create table t1(c1 timestamp); CREATE TABLE create table logbat(nmb text, ddb timestamp, dfb timestamp, perfok boolean, res boolean); CREATE TABLE create or replace function pg_temp.traitement(duree_en_s integer) returns void as $$ begin insert into t1 values(current_timestamp); perform pg_sleep(duree_en_s); end; $$ language plpgsql; CREATE FUNCTION create or replace function pg_temp.traitement_ok() returns boolean as $$ begin return true; end; $$ language plpgsql; CREATE FUNCTION CREATE OR REPLACE PROCEDURE bilan(traitement text, duree_en_s integer, duree_normale interval) AS $$ BEGIN perform pg_temp.traitement(duree_en_s); IF pg_temp.traitement_ok() THEN INSERT INTO logbat(nmb, ddb, dfb, perfok, res) SELECT traitement, clock_timestamp(), current_timestamp, case when clock_timestamp()-current_timestamp > duree_normale then false else true end, true; COMMIT; ELSE ROLLBACK; INSERT INTO logbat(nmb, ddb, dfb, perfok, res) SELECT traitement, clock_timestamp(), current_timestamp, case when clock_timestamp()-current_timestamp > duree_normale then false else true end, false; COMMIT; END IF; END; $$ LANGUAGE plpgsql; CREATE PROCEDURE call bilan('essai',1, '1 minute'::interval); CALL table t1; c1 ---------------------------- 2018-06-05 14:18:25.176019 (1 ligne) table logbat; nmb | ddb | dfb | perfok | res -------+----------------------------+----------------------------+--------+----- essai | 2018-06-05 14:18:26.179503 | 2018-06-05 14:18:25.176019 | t | t (1 ligne) call bilan('essai',2, '1 second'::interval); CALL table t1; c1 ---------------------------- 2018-06-05 14:18:25.176019 2018-06-05 14:19:51.16889 (2 lignes) table logbat; nmb | ddb | dfb | perfok | res -------+----------------------------+----------------------------+--------+----- essai | 2018-06-05 14:18:26.179503 | 2018-06-05 14:18:25.176019 | t | t essai | 2018-06-05 14:19:53.17168 | 2018-06-05 14:19:51.16889 | f | t (2 lignes) create or replace function pg_temp.traitement_ok() returns boolean as $$ begin return false; end; $$ language plpgsql; CREATE FUNCTION call bilan('essai',1, '2 seconds'::interval); CALL table t1; c1 ---------------------------- 2018-06-05 14:18:25.176019 2018-06-05 14:19:51.16889 (2 lignes) table logbat; nmb | ddb | dfb | perfok | res -------+----------------------------+----------------------------+--------+----- essai | 2018-06-05 14:18:26.179503 | 2018-06-05 14:18:25.176019 | t | t essai | 2018-06-05 14:19:53.17168 | 2018-06-05 14:19:51.16889 | f | t essai | 2018-06-05 14:21:44.790985 | 2018-06-05 14:21:43.788711 | t | f (3 lignes) start transaction; START TRANSACTION call bilan('essai',1, '2 seconds'::interval); ERREUR: invalid transaction termination CONTEXTE : fonction PL/pgsql bilan(text,integer,interval), ligne 14 à ROLLBACK

      Ici, une procédure BILAN intègre un traitement et son contrôle. En fonction du résultat du contróle, le traitement est validé ou annulé. Mais, dans tous les cas, le résultat du contrôle est enregistré dans une table logbat.
      Attention, si la procédure embarque des transactions, elle ne doit pas elle-même être appelée depuis une transaction existante comme l'illustre l'erreur obtenue à la fin de la démonstration. Une procédure doit être exécutée dans sa propre transaction.

Mise à jour : 17/06/2018