CREATE PROCEDURE

Sintassi

CREATE [OR REPLACE]
    [DEFINER = { utente | CURRENT_USER | role | CURRENT_ROLE }]
    PROCEDURE nome_sp ([parametro_sp[, ...]])
    [caratteristica ...] corpo_routine

CREATE
    [DEFINER = { utente | CURRENT_USER }]
    FUNCTION nome_sp ([parametro_funz[, ...]])
    RETURNS tipo
    [caratteristica ...] corpo_routine
    
parametro_sp:
    [ IN | OUT | INOUT ] nome_param tipo
    
parametro_funz:
    nome_param tipo

tipo:
    Qualsiasi tipo valido di MariaDB

characteristic:
    LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
  | COMMENT 'stringa'

corpo_routine:
    Istruzione SQL valida

Spiegazione

Queste istruzioni creano le Stored Routine. Per default, una routine viene associata al database corrente. Per associarla esplicitamente ad un altro database, nel crearla si può specificare un nome qualificato con la sintassi nome_db.nome_routine.

L'istruzione CREATE FUNCTION può essere usata anche per creare le UDF (User Defined Function, funzioni definite dall'utente). Si veda la pagina http://dev.mysql.com/doc/refman/5.1/en/adding-functions.html. Una UDF può essere considerata come una Stored Function esterna. Si noti che le Stored Function condividono lo stesso namespace delle UDF. Si veda http://dev.mysql.com/doc/refman/5.1/en/function-resolution.html per sapere in che modo il server interpreta i riferimenti a diversi tipi di funzioni.

Quando la routine viene invocata, viene eseguito un comando USE nome_db implicito, che viene annullato quando al termine della procedura. In questo modo il database di default usato dalle istruzioni contenute nella procedura, sarà quello a cui appartiene la procedura stessa. L'istruzione USE non è permessa all'interno del corpo della routine.

Le Stored Function si invocano facendo riferimento ad essere come ad un'espressione SQL. Quando vengono elaborate restituiscono un valore, come le espressioni. Le Stored Procedure si invocano invece con l'istruzione CALL (si veda CALL).

Per eseguire le istruzioni CREATE PROCEDURE e CREATE FUNCTION occorre disporre del privilegio CREATE ROUTINE. Per default, MariaDB assegna i permessi ALTER ROUTINE e EXECUTE al creatore delle routine. Si veda la pagina http://dev.mysql.com/doc/refman/5.1/en/stored-routines-privileges.html. Se il log binario è abilitato, l'istruzione CREATE FUNCTION potrebbe richiedere anche il privilegio SUPER, come spiegato alla pagina http://dev.mysql.com/doc/refman/5.1/en/stored-programs-logging.html.

Le clausole DEFINER e SQL SECURITY specificano il contesto di sicurezza da usare quando vengono verificati i permessi di accesso al momento dell'esecuzione della routine, come spiegato più avanti.

Se il nome di una routine è uguale a quello di una funzione SQL built-in, occorre inserire uno spazio tra il nome e le parentesi che seguono, si al momento della creazione sia quando la si invoca, altrimenti si ottiene un errore di sintassi. Per questo motivo si suggerisce di evitare di utilizzare per le Stored Routine i nomi delle funzioni SQL esistenti.

La modalità SQL IGNORE_SPACE si applica solo alle funzioni built-in, e non alle Stored Procedure. Dopo il nome della routine è sempre permesso inserire uno spazio, indipendentemente dal fatto che IGNORE_SPACE sia abilitata o meno.

La lista dei parametri deve sempre essere presente, tra le parentesi. Se non vi sono parametri, occorre specificare una lista vuota, in questo modo: (). I nomi dei parametri non sono case sensitive, cioè non vi è differenza tra le lettere maiuscole e le minuscole.

Ogni parametro deve essere associato a un tipo di dato valido e non è permesso utilizzare l'attributo COLLATE.

Ogni parametro per default è IN. Per specificare diversamente, si usano le parole chiave OUT o INOUT prima del suo nome.

Nota: Solo nelle PROCEDURE è possibile specificare un parametro come IN, OUT o INOUT. I parametri delle FUNCTION sono sempre considerati IN.

Ogni parametro IN passa un valore alla procedura. Essa può modificarlo, ma la modifica non è visibile al chiamante al termine della proceduta. Ogni parametro OUT passa un valore dalla procedura al chiamante. Il suo valore iniziale nella procedura è NULL, e questo valore sarà visibile al chiamante al termine della procedura. Ogni parametro INOUT è inizializzato dal chiamante, può essere modificato dalla procedura e qualsiasi cambiamento sarà visibile al termine della stessa.

Per ogni parametro OUT o INOUT, occorre passare una variabile definita dall'utente nell'istruzione CALL, per poter vedere il nuovo valore al termine della procedura. Se questa viene chiamata dall'interno di un'altra Stored Routine, è possibile anche passare un suo parametro, o una sua variabile locale, come parametro IN o INOUT.

La clausola RETURNS può essere specificata solo per le funzioni, ed è obbligatoria. Indica quale tipo di dato viene restituito e il corpo della funzione deve contenere un'istruzione RETURN. Se questa funzione restituisce un valore di tipo differente, verrà convertito nel tipo appropriato. Per esempio, se una funzione ha una clausola RETURNS che indica un tipo ENUM o SET, ma poi restituisce un intero, questo valore verrà convertito nel membro corrispondente della lista ENUM o SET.

Il corpo della routine deve essere un'istruzione SQL valida. Può essere un comando semplice come SELECT o INSERT, o un'istruzione composta che utilizza BEGIN e END. Le istruzioni composte possono contenere dichiarazioni, cicli e altre strutture di controllo. La sintassi di queste istruzioni è descritta alla pagina: http://dev.mysql.com/doc/refman/5.1/en/sql-syntax-compound-statements.html

MariaDB permette che le routine contengano istruzioni DDL, come CREATE e DROP. Permette inoltre che le Stored Procedure (ma non le Stored Function) contengano istruzioni per le transazioni, come COMMIT. Le funzioni non possono contenere istruzioni che effettuano esplicitamente o implicitamente un commit o un rollback. Il supporto per tali istruzioni non è richiesto dallo standard SQL, il quale afferma che ogni venditore di DBMS può decidere se permetterli o meno.

Le istruzioni che restituiscono un set di risultati non possono essere utilizzati all'interno di una Stored Function. Ad esempio, non si possono usare istruzioni SELECT che non hanno una clausola INTO, e altri comandi come SHOW, EXPLAIN e CHECK TABLE. Se viene utilizzata una funzione che restituisce un set di risultati e questo è rilevabile al momento della definizione, viene restituito un errore Not allowed to return a result set from a function (ER_SP_NO_RETSET). Se invece viene rilevato solo al momento dell'esecuzione, viene restituito un errore PROCEDURE %s can't return a result set in the given context (ER_SP_BADSELECT).

Per ulteriori informazioni sulle istruzioni che non sono ammesse nelle Stored Routine, si veda http://dev.mysql.com/doc/refman/5.1/en/stored-program-restrictions.html .

L'esempio seguente mostra una semplice Stored Procedure che fa uso di un parametro OUT:

MariaDB [test]> delimiter //

MariaDB [test]> CREATE PROCEDURE procSemplice (OUT param1 INT)
    -> BEGIN
    ->   SELECT COUNT(*) INTO param1 FROM t;
    -> END;
    -> //
Query OK, 0 rows affected (0.03 sec)

MariaDB [test]> delimiter ;

MariaDB [test]> CALL procSemplice(@a);
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> SELECT @a;
+------+
| @a   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

MariaDB [test]> 

L'esempio utilizza il comando delimiter del client mysql, per modificare il delimitatore delle istruzioni da ; a // durante la definizione della procedura. Questo permette di utilizzare ;, che verrà passato al server all'interno dell'istruzione, e non interpretato dal client come termine del comando. Si veda la pagina http://dev.mysql.com/doc/refman/5.1/en/stored-programs-defining.html

Per ulteriori informazioni su come invocare le Stored Procedure dall'interno dei programmi scritti in un linguaggio che abbia un'interfaccia a MySQL/MariaDB, si veda CALL.

Il seguente esempio accetta un parametro, esegue un'operazione usando una funzione SQL e restituisce il risultato. In questo caso, non è necessario usare il comando delimiter perché la funzione non contiene ';' al suo interno:

MariaDB [test]> CREATE FUNCTION hello (s CHAR(20))
    -> RETURNS CHAR(50) DETERMINISTIC
    -> RETURN CONCAT('Hello, ',s,'!');
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> SELECT hello('world');
+----------------+
| hello('world') |
+----------------+
| Hello, world!  |
+----------------+
1 row in set (0.00 sec)

MariaDB [test]> 

MariaDB ricorda il valore della variabile di sistema sql_mode che è in uso al momento della creazione delle routine, e lo utilizza sempre nell'esecuzione delle routine, indipendentemente dalla modalità SQL in uso al momento dell'invocazione.