Definició: Un procediment emmagatzemat és un conjunt de comandes SQL que poden emmagatzemar-se al servidor. Un procediment emmagatzemat és un programa que es guarda físicament en una base de dades. La seva implementació varia d'un gestor de bases de dades a un altre. Aquest programa està fet amb un llenguatge propi de cada Gestor de BD i està compilat, per la qual cosa la velocitat d'execució és molt ràpida.

Avantatges: El S.G.B.D. és capaç de treballar més ràpid amb les dades que qualsevol programa extern, ja que té accés directe a les dades a manipular i només necessita enviar el resultat final a l'usuari. Només realitzem una connexió al servidor i aquest ja és capaç de realitzar totes les comprovacions sense haver de tornar a establir una connexió. Podem reutilitzar el procediment i aquest pot ser cridat des de diferents aplicacions i llenguatges. Només el programarem una vegada.

Inconvenients: Els procediments emmagatzemats es guarden a la BD, per la qual cosa si aquesta es corromp perdrem tots els procediments emmagatzemats.

Utilitat dels procediments emmagatzemats: Quan múltiples aplicacions client s'escriuen en diferents llenguatges o funcionen en diferents plataformes, però necessiten realitzar la mateixa operació a la base de dades. Quan la seguretat és molt important. Els bancs, per exemple, utilitzen procediments emmagatzemats per a totes les operacions comunes. Això proporciona un entorn segur i consistent, i els procediments poden assegurar que cada operació es registra apropiadament. En aquest entorn, les aplicacions i els usuaris no obtendrien cap accés directe a les taules de la base de dades, només poden executar alguns procediments emmagatzemats.

Procediments emmagatzemats: Els procediments emmagatzemats poden millorar el rendiment ja que es necessita enviar menys informació entre el servidor i el client. La contrapartida és que augmenta la càrrega del servidor de la base de dades ja que la major part del treball es realitza a la part del servidor i no al client.

Què conté un procediment emmagatzemat: Un nom. Pot tenir una llista de paràmetres. Té un contingut (també anomenat definició del procediment). Aquest contingut pot estar compost per instruccions SQL, estructures de control, declaració de variables locals, control d'errors, etzetera.

Sintaxi de procediments emmagatzemats: Els procediments emmagatzemats i rutines es creen amb comandes CREATE PROCEDURE i CREATE FUNCTION. Una rutina és un procediment o una funció. Un procediment s'invoca utilitzant una comanda CALL , i només pot passar valors utilitzant variables de sortida. Una funció pot cridar-se des de dins d'una comanda com qualsevol altra funció (és a dir, invocant el nom de la funció), i pot retornar un valor escalar. Les rutines emmagatzemades poden cridar altres rutines emmagatzemades.

Estructura:

CREATE PROCEDURE sp_name ([parameter[,...]]) [characteristic ...] routine_body

DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

Paràmetres:

  • IN: passa un valor en un procediment. El procediment podria modificar el valor, però la modificació no és visible per a qui fa la crida.
  • OUT: El seu valor inicial és NULL al procediment, i el seu valor és visible per a qui fa la crida.
  • INOUT: és inicialitzat per qui fa la crida, pot ser modificat pel procediment, i qualsevol canvi realitzat pel procediment és visible per a qui fa la crida quan el procediment retorna.

Exemple

delimiter //

CREATE PROCEDURE simpleproc (OUT param1 INT)

BEGIN

SELECT COUNT(*) INTO param1 FROM t;

END//

SHOW CREATE {PROCEDURE | FUNCTION} sp_name

La sentència ‘Show create procedure o function' retorna la cadena exacta que pot utilitzar-se per recrear la rutina anomenada.

SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']

Retorna característiques de rutines, com el nom de la base de dades, nom, tipus, creador i dates de creació i modificació. Si no s'especifica un patró, llista la informació per a tots els procediments emmagatzemats, en funció de la comanda que utilitzi.

El comando CALL invoca un procediment definit prèviament amb CREATE PROCEDURE.

CALL pot passar valors a qui fa la crida utilitzant paràmetres declarats com OUT o INOUT.

La sentència BEGIN ... END s'empra per delimitar subsentències compostes que apareixen en els procediments. Cada subsentència dins del BEGIN ... END ha d'acabar amb el delimitador ‘ ; ’. Pot haver-hi una sentència BEGIN ... END sense subsentències.

FUNCIONS

Què és

CREATE PROCEDURE i CREATE FUNCTION?

Aquestes comandes creen una rutina emmagatzemada. Des de MySQL 5.0.3, per crear una rutina, és necessari tenir el permís CREATE ROUTINE, i els permisos ALTER ROUTINE i EXECUTE s’assignen automàticament al seu creador.

La comanda CREATE FUNCTION s’utilitza en versions anteriors de MySQL per suportar UDFs (User Defined Functions) (Funcions Definides per l’Usuari).

ALTER PROCEDURE I ALTER FUNCTION

Aquesta comanda pot utilitzar-se per canviar les característiques d’un procediment o funció emmagatzemada.

DROP PROCEDURE I DROP FUNCTION

Aquesta comanda s’utilitza per esborrar un procediment o funció emmagatzemada. Això és, la rutina especificada s’esborra del servidor. S'ha de tenir el permís ALTER ROUTINE per a les rutines fins a MySQL 5.0.3. Aquest permís s’atorga automàticament al creador de la rutina.

La clàusula IF EXISTS és una extensió de MySQL. Evita que ocorri un error si la funció o procediment no existeix. Es genera una advertència que pot veure’s amb SHOW WARNINGS.

SHOW CREATE PROCEDURE I SHOW CREATE FUNCTION

Aquesta comanda és una extensió de MySQL. Similar a SHOW CREATE TABLE, retorna la cadena exacta que pot utilitzar-se per recrear la rutina anomenada.

SHOW PROCEDURE STATUS I SHOW FUNCTION STATUS

Aquesta comanda és una extensió de MySQL. Retorna característiques de rutines, com el nom de la base de dades, nom, tipus, creador i dates de creació i modificació. Si no s’especifica un patró, llista la informació per a tots els procediments emmagatzemats, en funció de la comanda que utilitzi.

LA SENTÈNCIA CALL

La comanda CALL invoca un procediment definit prèviament amb CREATE PROCEDURE.

CALL pot passar valors a qui fa la crida utilitzant paràmetres declarats com OUT o INOUT.

També “retorna” el número de registres afectats, que amb un programa client pot obtenir-se a nivell SQL cridant la funció ROW_COUNT() i des de C cridant la funció de l’API C mysql_affected_rows() .

BEGIN...END

Begin SQL és una paraula clau que permet indicar en l'editor de mètodes el començament d’una seqüència de comandes SQL que ha de ser interpretada per la font de dades actual del procés.

Una seqüència de comandes SQL comença per Begin SQL i ha d’acabar amb la paraula clau End SQL.

Aquestes paraules clau funcionen d’aquesta forma:

• Pot posar un o més blocs d’etiquetes Begin SQL/End SQL en el mateix mètode.

• Pot escriure diverses instruccions SQL en la mateixa línia o en diferents línies separades per punt i coma ";".

PER A CREAR UNA FUNCIÓ

CREATE FUNCTION sp_name ([parameter[,...]])

RETURNS type

[characteristic ...] routine_body

parameter:

[ IN | OUT | INOUT ] param_name type

type:

Any valid MySQL data type

features:

LANGUAGE SQL

| [NOT] DETERMINISTIC

| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }

| SQL SECURITY { DEFINER | INVOKER }

| COMMENT 'string'

routine_body:

procediments emmagatzemats o comandes SQL vàlides

ALTER FUNCTION

nom_funció [characteristic ...]

characteristic:

{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }

| SQL SECURITY { DEFINER | INVOKER }

| COMMENT 'string'

DROP FUNCTION

DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

SHOW CREATE FUNCTION

SHOW CREATE {PROCEDURE | FUNCTION} sp_name

[etiqueta_inici:] BEGIN

[llista_sentències]

END [etiqueta_fi]

FUNCIONAMENT

Aquestes comandes permeten l’emmagatzematge de funcions en el servidor MySQL:

CREATE FUNCTION

Aquesta comanda afegeix una funció definida per l’usuari, normalment associada amb la base de dades en la qual l’usuari està treballant.

ALTER FUNCTION

La comanda “ALTER” és utilitzada per a canviar les característiques d’una funció emmagatzemada.

DROP FUNCTION

La comanda “DROP FUNCTION” s’utilitza per a esborrar una funció emmagatzemada (s'esborra del servidor). L’usuari que ho fa ha de tenir el permís “ALTER ROUTINE”, que és atorgat automàticament al creador de la rutina (una rutina és un terme que engloba els procediments i les funcions).

La clàusula “IF EXISTS” és una extensió de MySQL que evita que ocorri un error si la funció no existeix. Així es genera una advertència que pot veure’s amb “SHOW WARNINGS”.

SHOW CREATE FUNCTION

Aquesta comanda és una extensió de MySQL, semblant a “SHOW CREATE TABLE”, que retorna la cadena exacta que pot fer-se servir per a recrear la funció anomenada.

SHOW FUNCTION STATUS

Aquesta comanda és una extensió de MySQL que retorna característiques de funcions, com el nom de la base de dades, el propi nom, el tipus, el creador i les dates de creació i modificació. Si no s’especifica un patró, llista la informació per a totes les funcions, segons la comanda que es faci servir.