Mysql

y resultados intermedios se procesan completamente del lado del servidor. Mejor seguridad: Una rutina puede ser escrita para acceder a información sensible. Esta se puede configurar para que el que la invoca o ejecuta, no vea información que no debe. Un SP puede ser utilizado para modificar tablas en modo seguro sin darle al usuario acceso a la tabla. Diferencias entre SP y funciones Un SP no retorna solo un valor. Es invocado con una sentencia CALL. ?? una función se invoca dentro de una expresión y retorna un valor simple directamente al invocador para ser utilizada en sa expresión • No se puede invocar una función con CALO y tampoco se puede invocar un SP en una expresión Cada rutina almacenada esta asociada con una base en particular. • Cuando se invoca la rutina, se realiza implícitamente USE db_name. No se permite cambiar la base dentro de un SP. • Se interpreta una referencia, nombre_rutina, en la base de datos por defecto. Para referirse a una rutina en una base de datos especifica hay que hacerlo de la manera: CALL nombre base de datos. ombre rutina • Cuando una rutina se ejecuta,se cambia la base de datos a la que esta asociada la utina y cuando termina setea como base por defecto la base anterior. Debido a esto, se debe tener acceso a esa base de datos para poder invocar la rutina. • Cuando se borra una base de datos, todos los procedimientos almacenados asociados con ella también se borran Invocacion de rutinas asociadas a la BD Definición 20F 18 CREATE PROCEDURE n borran. CREATE PROCEDURE nombre_procedure [características. ] CREATE FUNCTION nombre_funcion ([Parámetros[,. RETURNS tipo_dato [Características.. Cuerpo_de_la rutina Puede darse como un bloque BEGIN / END que contiene varias sentencias. Cada eclaración debe terminar con punto y coma Si usa el cliente de consola de mysql, el es ambiguo entre la definición de la rutina y el final de sentencia. para esto existe el comando delimiter que nos permite definir como queremos marcar el final de una rutina. La clausula opcional [Características] contiene los siguientes valores, estos pueden aparecer en cualquier orden: SQL SECURITY {DEFINER I INVOKER} IJn SP corre con los privilegios del usuario que los creo o el que lo invoca. DEFINER es el valor por default.

DETERMINISTIC o NOT DETERMINISTIC COMMENT ‘Cadena’ Este valor indica si la rutina produce siempre el mismo resultado no. El default es NOT DETERMINISTIC. Especifica una descripción para la rutina. Esta descripción es mostrada junto con la info efinición de una rutina. 8 F_MontoAlquiler(p_CantPelicula INT, p_importe RETURNS DECIMALO 0,2) RETURN p_cantpeljcu a * p_importe; SELECT Parámetros en los SP El nombre en la declaración de una variable debe ser precedido por una de las siguientes palabras para indicar en que dirección fluye la información a través de ese parámetro: • IN : Indica un parámetro de entrada.

El valor del parámetro es pasado al SP. El SP puede asignarle diferentes valores al parámetro, pero el ambio es visible solamente dentro del SP. • OUT : Indica un parámetro de salida. Si se pasa un valor al parámetro, es ignorado por el SP, y su valor inicial es NULI— El SP setea su valor y cuando termina el valor del parámetro es pasado por el SP al que lo llama. Este valor se ve accediendo a la variable. • INOCJT : Indica un parámetro que puede ser tanto de entrada como de salida. El SP recibe el parámetro tal como lo pasa el invocador y cuando termina vuelve a pasar su estado final.

Ejemplo: Parámetros de SP DELIMITER $$ CREATE PROCEDURE (IN p_in INT, OUT p_out INT, INOI_JT p_inout INT) BEGIN SELECT _in , p_out , p_inout; SET p_in = 100 , p_out = 200 , p_inout — 300; END; $$ SET O , = O , = O CALL @v_out, @v_inout mysql> SET @v_in = O , @v_out=O. @v_inout = O; Query OK O rows affected (0. 00 sec) mysq > CALL PRC_testeo_parametros @v_inout); | p_in p_out I p_inout IOI 10 sec) Querv OK, O rows affected 18 __+ 1 row in set (0. 01 sql> SELECT —+ 1 row in set (0. 01 sec) Query OK, 0 rows affected (0. 01 sec) mysql> SELECT @v in, @v_out, @v_in I I +– o 200 300 | set (0. 0 sec) La sentencia DECLARE 1 row in La sentencia DECLARE es usada para declarar distintos tipos de items en rutinas almacenadas: • Variables locales • Condiciones, ales como alertas o excepciones • Manejadores de error • Cursores Solo puede ser usada en un bloque BEGIN/END y debe aparecer en el bloque antes que cualquier otra sentencia. Si se declaran distintos tipos de items, se deben declarar variables y condiciones primero, luego cursores y finalmente manejadores de error. Los items creados por DECLARE dentro de un bloque son locales a este.

Cuando un bloque termina, cualquier cursor abierto se cierra y las variables dejan de ser accesibles. Variables dentro de Rutinas Almacenadas DECLARE nombre_variable tipoDato [DEFAULT valor] A una ariable se le pueden asignar valores usando SET, FETCH INTO y SELECT INTO. Las variables locales difieren de las variables de usuario, estas no contienen un «@» adelante. DECLARE v_prueba INT DEFAULT O Asignando valores a Variables con SET La sentencia SET asigna valores a variables, estas pueden ser vanables de sistema o variables de usuario.

Se pueden setear variables fuera de las rutinas o pueden ser variables dentro de la rutina previamente declaradas con DECLARE. La sentencia SET puede realizar asignaciones simples o múltiples: DECLARE varl , var2, vara; SET varl 1 v ar3 varl + var2 ; s 8 SET var3 varl + var2 ; Asignando valores con SELECT… INTO Asigna el resultado de una sentencia SELECT a una variable. Fuera de la rutina deben ser variables de usuario. Dentro de las rutinas, la sentencia puede utilizarse también para asignarle valores a variables locales, las cuales fueron declaradas previamente con DECLARE.

DECLARE first_name_var CHAR(45); DECLARE last_name_var CHAR(45); SELECT first_name, last_name INTO v_first_name, v last name FROM actor a WHERE actor id=l La sentencia SELECT debe traer como máximo una fila, sino ocurrirá un error. Si no trae ninguna fila, las variables utilizadas en el INTO permanecerán sin cambio. También se puede usar para asignar valores a parámetros de una rutina. Ejemplo Asignación de Variables DELIMITA $$ DROP PROCEDURE IF EXISTS ‘sakila PRC_MejorCliente $$ CREATE PROCEDURE . ?? PRC_MejorCliente OUT p_cliente SMALLINT, OUT p_pay ) BEGIN DECLARE DATE; DECLARE DATE; Determine start and end time periods SET = STR SET – LAST_DAY(last_month_start); SET p_cliente SELECT customer_id, SUM(amount) pago INTO p_cliente,p_pay FROM payment WHERE BEW,/EEN start AND v_last month end GROUP BY customer_id ORDER BY pago DESC LIMIT 1; END $$ CALL Recuperación múltiples c Una extensión del MySQL res es que la sentencia onjuntos de resultados que son retornados directamente al cliente sin proceso Intermediario.

EL cliente recibe los resultados como si ejecutara la sentencia SELECT el mismo. Esto no aplica a las funciones almacenadas. DELIMITER // CREATE PROCEDURE O BEGIN SELECT ‘Film’, FROM film; SELECT ‘Actor’, FROM actor; SELECT ‘Film_Actor•, FROM film_actor; END; // cuando se lo invoca retorna tres conjuntos de resultados de un registro Recuperación múltiples conjuntos de datos CALL Pelicula_Actor_Contador() mysql> CALL Pelicula_Actor_Contador(); +– Film COUNT(*) I __+ I Film | 1000 | 1 row in set (0. 1 — I Film_Actor _+ Film_Actor | 5462 | +- 1 row in set (0. 01 seo Query OK, O rows affected (0. 01 seo – + + Actor I I +- 1 row in set (0. 01 sec) __+ Actor 200 +- La sentencia IF y CASE nos permiten evaluar condiciones. Notese que tienen sintaxis diferente que la función IF() y la expresión CASE. Las ultimas producen un valor que es utilizado en una expresión Estas no son sentencias por si mismas. También terminan con END en lugar de END CASE.

Gracias a la sintaxis diferente, es posible utilizar las funciones IF() y las expresiones CASE dentro de las rutinas almacenadas sin tener ambigüedad, incluso si se utilizan dentro de una sentencia IF o CASE. IF expr THEN statement_list [ELSEIF expr THEN statement_list] [ELSE statement_list] END IF IF val IS NULL THEN SELECT val is NULL’; ELSE SELECT ‘val is not NULL’; END IE Contr val IS NULL THEN SELECT ‘val is NULL’; ELSESE ECT ‘val is not NULL’; END IE Control de Flujo: pruebas Condlcionales Condicional CASE CASE es la otra sentencia que evalúa condiciones.

Tiene dos modos. La primera sintaxis es la siguiente: CASE case_expr WHEN when_expr THEN statement_list [WHEN when_expr THEN statement_list] [ELSE statement_list] END CASE La expresón case_expr se evalúa y suele determinar cual de las iguientes clausulas en el resto de la sentencia debe ejecutarse. La when_expr en la clausula inicial del WHEN se evalúa y compara con la case_expr. Si son iguales, la lista de sentencias siguiente al THEN se ejecuta.

Si when_expr no es igual a case_expr, y existen otras clausulas WHEN, se manejan de forma similar a su turno. Si ninguna clausula WHEN tiene una when_expr igual a case_expr, y hay una clausula ELSE, la lista de sentencias de de la clausula ELSE es ejecutada. Cada comparación tiene el formato case_expr = when_expr. El significado de esto es que la comparación nunca s verdadera si el operando es NULO no importa el valor del operando.

La siguiente sentencia CASE evalúa si un valor dado es 0, 1, o diferente: Ejemplo CASE I er Forma CASE val WHEN O THEN SELECT ‘val es 0′; WHEN 1 THEN SELECT val es V; ELSE SELECT val no es O or V; END CASE; CASE 2da Forma CASE WHEN when_expr THEN statement_list [WHEN when expr THEN statement_list] [ELSE statement_listl END CASE Ejemplo CASE 2da Forma CASE WHEN val IS T al is NULL’; WHEN val < O 0'; ELSE SELECT 'val es 0'; END CASE; Ciclos La slntaxis de sentencias en MySQL provee tres tipos distintos de ciclo: •LOOP construye un ciclo incondicional sin sintaxis de erminación por esta razón, debe contener una sentencia que especifique la salida del ciclo. •REPEAT y WHILE, las otras dos construcciones de ciclo, son condicionales. Incluyen una clausula que determina si continua o termina la ejecución del ciclo. •Los SQL estándar incluyen un ciclo FOR también. MySQL no lo soporta. CiC10 LOOP La sentencia LOOP crea un ciclo incondicional con la siguiente sintaxis: LOOP statement list END LOOP La lista de sentencias dentro del ciclo se ejecuta repetidamente. El ciclo iterara por siempre a menos que la lista de sentencias contenga alguna entencia que genera la salida del ciclo. La salida puede hacerse efectiva con una sentencia LEAVE o (en una función) una sentencia de retorno.

El siguiente LOOP itera mientras la variable i sea menor que 10: Construcción Ciclo LOOP DECLARE i INT DEFAULT o; rny_loop: oop SET i = 1; IFi= 10 THEN LEAVE my_loop; END IF; END LOOP my_loop; Ciclo REPEAT La sentencia REPEAT crea un ciclo condicional. Tiene la siguiente sintaxis: REPEAT lista_sentencias UNTIL expr END REPEAT Las sentencias dentro del CICIO se ejecutan y luego se evalúa la expresión condicional expr. Si la expresion es verdadera, el ciclo termina. De otro modo, comienza nuevamente. Notese que no se utiliza punto y coma entre la expresión y END REPEAT. El siguiente ciclo REPEAT itera mientra ea menor a 10: O; REPEAT SET i -i + 1; UNTIL i 10 END REPEAT; Ciclo WHILE La sentencia WHILE crea un ciclo condicional. Es similar al REPEAT a excepción que la expresión de condición aparece al principio del ciclo en vez de al final.

También, un ciclo WHILE continua mientra la condición sea verdadera, mientras que el ciclo REPEAT termina tan pronto como la condición se vuelve verdadera. La sintaxis de WHILE es la siguiente: WHILE expr DO statement_list END WHILE La expresión de condición se evalúa y el ciclo finaliza si la la condición no es verdadera. De otra forma, la lista de sentencias dentro del ciclo se ejecuta, el control se transfiere al comienzo, y la expresión es evaluada nuevamente. El siguiente ciclo WHILE itera mientras la variable sea menor a 10(diez): DECLARE i INT DEFAULT O; WHILE 10 DO SET i – 1; END WHILE; Diferencia REPEAT y WHILE Debido a que la evaluación en un REPEAT se encuentra al final del ciclo, las sentencias dentro del ciclo siempre se ejecutan al menos una vez.

Con WHILE, la evaluaclón se realiza al principio, por lo ue es posible que las sentencias dentro del ciclo no se ejecuten ni siquiera una vez. por ejemplo, el siguiente ciclo WHII_E no ejecutara nunca las sentencias dentro del ciclo: WHILE 1 = O DO SET x — 1; END WHII_E; Así como los bloques BEGIN/END pueden ser anidados, los ciclos también En tales casos, es útil etiquetarlos por si es necesario salir de mas de un nivel de ciclo a la vez. Transferencia de Control Dentro de una rutina existen dos sentenclas que transfieren el control. Cada sentencia requiere una etiqueta que indica a que construcción etiquetada debe aplicarse: LEAVE label ITERATE label LEAVE transfiere el contro