Funzioni e procedure

Oltre ai trigger, un altro modo per estendere le funzionalità del linguaggio SQL "tradizionale" è la definizione di funzioni e procedure. In questa lezione vedremo come fare.

Funzioni

Nella lezione sul comando SELECT abbiamo visto come si possono usare le funzioni nelle interrogazioni per operare con valori numerici, testuali o booleani.

Quasi tutti i database SQL permettono di estendere l’insieme delle funzioni utilizzabili creandone di nuove. Ad esempio, per il nostro database, possiamo creare una funzione per verificare se il codice ISBN è corretto. In Oracle possiamo usare la sintassi seguente, che verifica se la cifra di controllo del codice è valida, restituendo Y in caso affermativo, o altrimenti il codice di controllo calcolato:

CREATE OR REPLACE FUNCTION CHECK_ISBN (  ISBN IN VARCHAR2  )
 RETURN VARCHAR2 IS
   ACC INTEGER := 0;
   CURR_CHAR VARCHAR2(2);
BEGIN
  FOR I IN 1..9 LOOP
    CURR_CHAR := SUBSTR(ISBN,I,1);
    ACC := ACC + (i) * TO_NUMBER(CURR_CHAR);
  END LOOP;
  ACC := MOD(ACC, 11);
  IF ACC = 10 THEN CURR_CHAR := 'X'; ELSE CURR_CHAR := TO_CHAR(ACC); END IF;
  IF CURR_CHAR = SUBSTR(ISBN,LENGTH(ISBN),1) THEN RETURN 'Y'; ELSE RETURN CURR_CHAR; END IF;
END CHECK_ISBN;

Il lavoro viene svolto tra le istruzioni BEGIN ed END: qui viene eseguito un ciclo sui caratteri presenti nel codice e viene calcolata la cifra di controllo. Nella penultima riga si verifica se l’ultimo carattere del codice ISBN corrisponde al valore calcolato. Questo blocco di codice è stato scritto in PL/SQL, l’estensione di SQL realizzata per Oracle proprio per la programmabilità: per altri database la sintassi sarebbe stata diversa.

Una volta creata, si può invocare la funzione in una normale istruzione interrogazione:

SELECT title, CHECK_ISBN(isbncode)
FROM Books

Procedure

Una procedura, a differenza di una funzione, non può essere usata in una semplice istruzione SELECT, ma può essere invocata con una sintassi ad hoc, ed è usata, generalmente, per incapsulare del codice che ha qualche effetto collaterale, ad esempio inserire, modificare o eliminare righe dal database.

Per mostrare un esempio, la seguente procedura, scritta per SQL Server (quindi in T-SQL), inserisce simultaneamente oltre al libro, anche il suo autore ed il suo editore, se non esistono nel databas; altrimenti li collega:

CREATE PROCEDURE insert_book
	@title varchar(200),
	@author_surname varchar(32),
	@author_given_names varchar(32),
	@publisher varchar(64)
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @idPerson AS INT;
	DECLARE @idPublisher AS INT;
	DECLARE @idBook AS INT;
	SELECT @idPerson = ID From Person
	   WHERE surname = @author_surname AND given_names = @author_given_names;
	IF @@ROWCOUNT = 0 BEGIN
		SELECT @idPerson = MAX(ID) + 1 FROM Person;
		INSERT INTO Person(ID, surname, given_names)
		     VALUES(@idPerson, @author_surname, @author_given_names);
	END
	SELECT @idPublisher = ID From Publisher WHERE name = @publisher;
	IF @@ROWCOUNT > 0 BEGIN
		SELECT @idPublisher = COALESCE(MAX(ID)+1, 1) FROM Publisher;
		INSERT INTO Publisher(ID, name) VALUES(@idPublisher, @publisher);
	END
	SELECT @idBook = COALESCE(MAX(ID)+1, 1) FROM Publication;
	INSERT INTO Publication(ID, type) VALUES(@idBook, 'Book');
	INSERT INTO Book(ID, title, publisher, pub_year)
         VALUES (@idBook, @title, @idPublisher, YEAR(getdate()));
	INSERT INTO Author(personID, publicationID)
         VALUES (@idPerson, @idBook);
END

Per ulteriori dettagli sulle stored procedure in T-SQL rimandiamo all'apposita guida.

Tipi definiti dall’utente

Oracle (ma anche PostgreSQL) permette di creare tipi definiti dall’utente che incapsulano dati e procedure proprio come nella programmazione ad oggetti. Si tratta di una funzionalità molto potente perché, oltre a dare maggior chiarezza al database, evita il moltiplicarsi di campi simili in diverse tabelle, e stesse logiche in varie procedure.

Il seguente esempio in PL/SQL mostra la definizione di un oggetto contenente coordinate di localizzazione e una funzione per calcolare la distanza (in questo caso per semplicità il calcolo viene effettuato come se si trattasse di coordinate piane):

CREATE OR REPLACE TYPE geoloc AS OBJECT (
   latit NUMBER,
   longt NUMBER,
   MEMBER FUNCTION distanza(altra geoloc) RETURN NUMBER
);
/
CREATE TYPE BODY geoloc IS
  MEMBER FUNCTION distanza(altra geoloc) RETURN NUMBER IS
  BEGIN
    RETURN SQRT(POWER(altra.latit-latit,2)+POWER(altra.longt-longt,2));
  END;
END;

Si può notare che con la parola chiave MEMBER si dichiara solo la firma della funzione. Il corpo delle funzioni, invece, va definito a parte all’interno di un blocco CREATE TYPE BODY. Una volta definito, il tipo si può usare nella creazione delle tabelle, e le sue funzioni possono essere usate nelle interrogazioni, come in questi esempi:

CREATE TABLE Indirizzi(ID INT, indirizzo VARCHAR2(500), posizione geoloc);
INSERT INTO Indirizzi(ID, indirizzo, posizione)
     VALUES(1, 'Lungomare Vanvitelli', geoloc(43.6251419,13.5049253);
SELECT ind.indirizzo FROM Indirizzi ind
 WHERE ind.posizione.distanza(geoloc(43.6224239,13.5071353)) < 100;