Procedimientos almacenados y funciones

Definición: Un procedimiento almacenado es un conjunto de comandos SQL que pueden almacenarse en el servidor. Un procedimiento almacenado es un programa que se guarda físicamente en una base de datos. Su implementación varía de un gestor de bases de datos a otro. Este programa esta hecho con un lenguaje propio de cada Gestor de BD y esta compilado, por lo que la velocidad de ejecución es muy rápida.

Ventajas: El S.G.B.D. es capaz de trabajar más rápido con los datos que cualquier programa externo, ya que posee acceso directo a los datos a manipular y sólo necesita enviar el resultado final al usuario. Sólo realizamos una conexión al servidor y este ya es capaz de realizar todas las comprobaciones sin tener que volver a establecer una conexión. Podemos reutilizar el procedimiento y este puede ser llamado desde diferentes aplicaciones y lenguajes. Sólo lo programaremos una vez.

Desventajas: Los procedimientos almacenados se guardan en la BD por lo que si ésta se corrompe perderemos todos los procedimientos almacenados.

Utilidad de los procedimientos almacenados: Cuando múltiples aplicaciones cliente se escriben en distintos lenguajes o funcionan en distintas plataformas, pero necesitan realizar la misma operación en la base de datos. Cuando la seguridad es muy importante. Los bancos, por ejemplo, usan procedimientos almacenados para todas las operaciones comunes. Esto proporciona un entorno seguro y consistente, y los procedimientos pueden asegurar que cada operación se loguea apropiadamente. En tal entorno, las aplicaciones y los usuarios no obtendrían ningún acceso directo a las tablas de la base de datos, sólo pueden ejectuar algunos procedimientos almacenados.

Procedmientos almacenados: Los procedimientos almacenados pueden mejorar el rendimiento ya que se necesita enviar menos información entre el servidor y el cliente. El intercambio que hay es que aumenta la carga del servidor de la base de datos ya que la mayoría del trabajo se realiza en la parte del servidor y no en el cliente.

Que contiene un Procedimiento Almacenado: Un nombre. Puede tener una lista de parámetros. Tiene un contenido (también llamada definición del procedimiento). Ese contenido puede estar compuesto por instrucciones sql, estructuras de control, declaración de variables locales, control de errores, etcétera.

Sintaxis de procedimientos almacenados: Los procedimientos almacenados y rutinas se crean con comandos CREATE PROCEDURE y CREATE FUNCTION. Una rutina es un procedimiento o una función. Un procedimiento se invoca usando un comando CALL , y sólo puede pasar valores usando variables de salida. Una función puede llamarse desde dentro de un comando como cualquier otra función (esto es, invocando el nombre de la función), y puede retornar un valor escalar. Las rutinas almacenadas pueden llamar otras rutinas almacenadas.

Estructura:

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

DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

Parametros:

• IN: pasa un valor en un procedimiento. El procedimiento podría modificar el valor, pero la modificación no es visible para la persona que llama.
• OUT: Su valor inicial es NULL en el procedimiento, y su valor es visible para la persona que llama.
• INOUT: es inicializado por la persona que llama, puede ser modificada por el procedimiento, y cualquier cambio realizado por el procedimiento es visible para la persona que llama cuando el procedimiento retorna.

Exemple

delimiter //

CREATE PROCEDURE simpleproc (OUT param1 INT)

BEGIN

SELECT COUNT(*) INTO param1 FROM t;

END//

SHOW CREATE {PROCEDURE | FUNCTION} sp_name

La sentencia ‘Show create procedure o function' retorna la cadena exacta que puede usarse para recrear la rutina nombrada.

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

Retorna características de rutinas, como el nombre de la base de datos, nombre, tipo, creador y fechas de creación y modificación. Si no se especifica un patrón, le lista la información para todos los procedimientos almacenados, en función del comando que use.

El comando CALL invoca un procedimiento definido préviamente con CREATE PROCEDURE.

CALL puede pasar valores al llamador usando parámetros declarados como OUT o INOUT.

La sentencia BEGIN ... END se emplea para delimitar subsentencias compuestas que aparecen en los procedimientos. Cada subsentencia dentro del BEGIN ... END debe acabar con el delimitador ‘ ; ’. Puede haber una sentencia BEGIN ... END sin subsentencias.

FUNCIONS

Què és CREATE PROCEDURE i CREATE FUNCTION?

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

La comanda CREATE FUNCTION s’utilitza en versions anteriors de MySQL per soportar 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. Ha de tenir el permís ALTER ROUTINE per les rutines fins 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 ocurreixi 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

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

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, nombre, tipus, creador i dates de creació i modificació. Si no s’especifica un patró, li llista la informació per tots els procediments emmagatzemats, en funció de la comanda que utilitzi.

LA SENTENCIA CALL

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

CALL pot passar valors al cridador 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 la API C mysql_affected_rows() .

BEGIN...END

Begin SQL es una paraula clau que permet indicar en el editor de métodes l’escomençament d’una sequencia de comandes SQL que ha de ser interpretada per la font de dades actual del procés.

Una secuencia de comandes SQL comença por Begin SQL y ha d’acabar amb la paraula clau End SQL.

Aquestas 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 diferentes línies separades per punt i coma ";".

PARA CREAR UNA FUNCIÓN

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:

procedimientos almacenados o comandos SQL válidos

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_inicio:] BEGIN

[lista_sentencias]

END [etiqueta_fin]

FUNCIONAMENT

Aquestes commandes permeten l’enmagatzenament de funcions en el servidor MySQL:

CREATE FUNCTION

Aquesta comanda afegeix una funció definida per l’usuari, normalment asociada amb la base de dades en la que 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 borrar una funció emmagatzemada (es borra 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.