Monsieur Deividson et ses bases de données : les procédures stockées.

Les procédures stockées sont des fonctions précompilées stockées dans la base de données. Elles permettent de sélectionner et de manipuler des données, et grâce à des structures de contrôle et des boucles, de faire des calculs complexes et d'en renvoyer le résultat au client. Cela économise beaucoup de communications client/serveur.

Les procédures stockées de PostgreSQL peuvent être écrites dans différents langages, incluant Perl, Python, TCL et pgSQLmdash;le langage propre à PostgreSQL. Des langages procéduraux définis par l'utilisateur peuvent aussi être utilisés, et plusieurs de ces langages sont facilement téléchargeables, par exemple Java.

Dans cet article, nous utiliserons PL/pgSQL. PL/pgSQL ressemble beaucoup à du SQL normal, mais il ajoute beaucoup de fonctionnalités à ce dernier, comme des structures de contrôle, des types de données et des fonctions définis par l'utilisateur.

Commençons par une procédure stockée basique qui renvoie « Hello World! »—pas très utile, je sais, mais cela nous permettra de débuter avec la syntaxe de base de PL/pgSQL. Voici le code :

   create or replace function hello() RETURNS text AS $$
DECLARE
  hello text;
begin
  hello := 'Hello World!';
  return hello;	
END;
$$ LANGUAGE plpgsql;

Voici ce que cela fait :

create or replace function hello() RETURNS text AS $$

Crée la fonction nommée hello qui n'a pas de paramètres et renvoie du texte. Vous devez toujours déclarer ce que la fonction renvoie. Utilisez VOID si vous ne voulez rien renvoyer du tout.

DECLARE

Ouvre le bloc de déclaration de variables.

hello text;

Déclare une variable appelée hello de type text. Pour définir plusieurs variables, utilisez « ; » comme séparateur. Vous pouvez utilisez n'importe quels types standards employés dans les tables, comme les integer ou les float, et même des types utilisateurs et des domaines.

	BEGIN

Débute le code de la fonction.

	hello := 'Hello World!';

Initialise la variable hello avec 'Hello World!'. Notez que vous devez utiliser des guillemets simples pour les valeurs de type chaîne de caractères.

return hello; 

Renvoie notre valeur.

END;

Termine la fonction.

$$ LANGUAGE plpgsql;	

Définit quel langage nous avons utilisé— 'plpgsql' dans ce cas. Pour invoquer cette fonction, nous utilisons le code SQL suivant :

select * from hello();

La sortie sera un champ texte appelé « hello », de valeur « Hello World! ».

Voici un autre exemple d'utilisation des procédures stockées (que nous abrégerons SP dès maintenant) —: générer des données de test pour vos tables. Utilisons l'article du mois dernier comme exemple—nous avions utilisé une procédure stockée pour générer 500 000 lignes de données pour une de nos tables. Voici le code :

  create or replace function test_data_computer()
RETURNS integer AS $$
DECLARE
  count integer;
  sql text;
begin
  count = 1;
  LOOP 
    sql = 'insert into computer(computer_id, computer_ram, cpu_id, video_id) values';
    sql = sql || '('|| count ||', ' || random()*1024 || ', ' || (random()*49999)+1 || ', ' || (random()*49999)+1 || ')';
    EXECUTE sql;
    count = count + 1;
    EXIT WHEN count > 500000;
  END LOOP;  
  return count;	
END;
$$ LANGUAGE plpgsql;
  

Cela commence pratiquement comme notre exemple précédent, mais cette fois nous déclarons 2 variables au lieu d'une seule. Il y a du nouveau à la ligne 8, où nous introduisons l'instruction LOOP. La boucle (loop) est une instruction de base ; elle réitère le code indéfiniment, jusqu'à ce qu'elle trouve une instruction EXIT ou EXIT WHEN.

Les lignes 9 et 10 sont utilisées pour générer du code SQL qui introduit un simple enregistrement dans notre table. La double barre verticale est l'opérateur de concaténation. Random() génère un nombre réél aléatoire entre 0 et 1 (ainsi random()*49999)+1 va générer un nombre aléatoire entre 0 et 50 000).

La ligne 11 exécute le code SQL stocké dans la variable sql, ajoutant la ligne à la table.

Les lignes 12 et 13 contrôlent l'exécution de la boucle : sans celles-ci, la boucle devient une boucle infinie. EXIT WHEN count > 500 000; arrête la boucle quand la condition est remplie (lorsque « count » dépasse 500 000 dans ce cas).

La ligne 14 termine le bloc de la boucle (LOOP), de telle sorte que la fonction revient à la ligne 8, et réexécute à nouveau ce qui est dans le corps de la boucle (encore et encore).

La ligne 15 renvoie le nombre d'enregistrements ajoutés, plus un dans ce cas.

Faisons un scénario pour cet exemple. Imaginez que vous concevez une application pour un médecin, et une des informations qu'il souhaite est le temps exact passé avec ses patients (et pas le temps passé à ne rien faire dans son bureau). De plus, il souhaite pouvoir sélectionner les informations d'une date donnée, ou pour une période donnée, et il veut choisir entre sélectionner les lignes d'un seul patient, ou bien de tous les patients. Le scénario est complexe, n'est-ce pas ? Et bien, nous pouvons le résoudre par une seule procédure stockée. Voici les tables que notre base de donnée comportera :

		create table patient (
		patient_id serial primary key, 
		patient_name text );
		
		create table visits (
		v_id serial  primary key, 
		patient_id integer references patient,
		v_date date,
		v_time_start time,
		v_time_end time );

	

Une table pour les patients, une autre pour les visites, avec la date, l'heure de début et l'heure de fin. Maintenant, chargeons la table avec des données :

		insert into patient (patient_name) values ('Deividson');
		insert into patient (patient_name) values ('John');
		insert into patient (patient_name) values ('Benjamin');
		insert into visits (patient_id, v_date, v_time_start, v_time_end) values (1, '10/04/2008', '08:00', '09:00');
		insert into visits (patient_id, v_date, v_time_start, v_time_end) values (1, '14/04/2008', '13:00', '13:45');
		insert into visits (patient_id, v_date, v_time_start, v_time_end) values (1, '18/04/2008', '10:00', '10:15');
		insert into visits (patient_id, v_date, v_time_start, v_time_end) values (2, '11/04/2008', '14:00', '15:00');
		insert into visits (patient_id, v_date, v_time_start, v_time_end) values (2, '12/04/2008', '14:00', '15:45');
		insert into visits (patient_id, v_date, v_time_start, v_time_end) values (2, '17/04/2008', '14:00', '15:15');
		insert into visits (patient_id, v_date, v_time_start, v_time_end) values (3, '15/04/2008', '08:00', '12:00');

	

Trois patients, 7 visites, en voila assez pour tester notre procédure stockée. Voici le code :

		CREATE OR REPLACE FUNCTION total( date1 date, date2 date, patient integer ) 
		RETURNS interval AS $$
		DECLARE
		total interval;
		rec record;
		sql text;
		BEGIN
		total = '00:00:00'::time;
		sql = 'select * from visits';
		
		if date1 is not null OR patient is not null then
		sql = sql || ' where ';
		end if;
		
		if patient is not null then
		sql = sql || '(patient_id = ' || patient || ')';
		end if;
		
		if date2 is not null AND date1 is not null then
		if patient is not null then
		sql = sql || ' AND ';
		end if;
		sql = sql || '(v_date between ''' || date1 || ''' and ''' || date2 || ''')';	
		else
		if date1 is not null then
		if patient is not null then
		sql = sql || ' AND ';
		end if;
		sql = sql || '(v_date = ''' || date1 || ''')';	
		end if;  
		end if;
		
		for rec in EXECUTE sql loop
		total = total + (rec.v_time_end - rec.v_time_start);
		end loop;
		
		return total;
		END;
		$$ LANGUAGE plpgsql;

	

Regardons de plus près. Le début du code ressemble beaucoup à celui des autres exemples, mais nous avons 3 variables cette fois-ci. total stocke le temps total à renvoyer au client, et rec (de type record) est la variable qui va porter le résultat de la requête.

A la ligne 8, nous initialisons la variable total avec la valeur 00:00:00—le « :: » est un transtypage—:: time signifie que la chaîne de caractère ("00:00:00") doit être convertie en temps.

De la ligne 9 à la ligne 31, tout ce que nous faisons est de créer le code de la requête SQL qui va sélectionner les données que nous voulons. Ici, nous utilisons un autre type de structure de contrôle : le IF. les IF sont des structures de contrôle basiques, et leur syntaxe est (comme dans la plupart des langages de programmation) :

		IF (condition) THEN (commands) [ELSE (commands)] END IF;

	

La condition peut être n'importe quelle comparaison logique (< > , =, IS NULL, ou IS NOT NULL), et vous pouvez combiner plusieurs conditions en utilisant les opérateurs logiques (AND, OR, etc...) Si la condition est vraie, les commandes dans la clause THEN seront exécutées. Si elle est fausse, les commandes de la clause ELSE seront exécutées (si cette dernière existe), ou bien le flot d'exécution reprendra après le ELSE IF.

C'est dans ces blocs IF que nous créons les conditions (une seule date, intervalle de dates, un seul patient, etc ...)

A la ligne 33, nous exécutons le code SQL que nous avons généré. Nous faisons une boucle parcourant chacun des enregistrements du résultat. C'est alors que nous ajoutons la durée de chaque visite dans la variable total, et nous renvoyons le résultat lorsqu'il n'y a plus d'enregistrements à lire.

Nous pouvons appeler la procédure stockée de plusieurs façons différentes : chacune d'elle sélectionne un différent jeu de données, et nous donne un résultat différent :

		
		# All the records, from all patients
		select * from total(NULL, NULL, NULL);
	

		# All the records, from patient #3 only
		select * from total(NULL, NULL, 3);
		
	

		# Records from '14/04/2008', all patients
		select * from total('14/04/2008', NULL, NULL);
	

		# Records from '14/04/2008', patient #1 only
		select * from total('14/04/2008', NULL, 1);
	

		# Records from '14/04/2008' through '17/04/2008', all patients
		select * from total('14/04/2008', '17/04/2008', NULL);
	

		
		# Records from '14/04/2008' through '17/04/2008', patient #2 only.
		select * from total('14/04/2008', '17/04/2008', 2);
	

Les procédures stockées sont puissantes et souples, et peuvent être un bon moyen de pré-selectionner et pré-traiter les données. Elles vous permettent également de manipuler des données et d'exécuter du code directement sur le serveur.

PostgreSQL fournit un manuel complet sur son site, comprenant un chapitre sur PL/pgSQL. Vous le trouverez ici : http://www.postgresql.org/docs/8.3/static/plpgsql.html

C'est tout pour les procédures stockées—rendez-vous le mois prochain, où nous parlerons des triggers!