PROCEDIMIENTOS ALMACENADOS Taller de base de datos
PROCEDIMIENTOS ALMACENADOS
Un Stored Procedure o Procedimiento Almacenado es un programa (o función) que se almacena físicamente en una base de datos. La implementación de un stored procedure varía de un gestor de bases de datos a otro.
2
PROCEDIMIENTOS ALMACENADOS
Los procedimientos almacenados son un método para encapsular tareas repetitivas. Admiten variables declaradas por el usuario, ejecución condicional y otras características de programación muy eficaces.
3
PROCEDIMIENTOS ALMACENADOS Implementación Se usan a menudo para realizar consultas SQL, insertar, actualizar o eliminar registros sobre los objetos de la base de datos de una manera transparente, desde el punto de vista del cliente de la aplicación. Un Stored Procedure, permite agrupar en forma exclusiva parte de una tarea específica que se desee realizar o mejor dicho el SQL apropiado para dicha acción. Son implementados desde la aplicación mediante CALL procedure (…). 4
PROCEDIMIENTOS ALMACENADOS El procesamiento de un procedimiento almacenado conlleva crearlo y ejecutarlo la primera vez. Cuando se crea un procedimiento almacenado, las instrucciones que hay en él se analizan para ver si son correctas desde el punto de vista sintáctico. Si se detecta un error de sintaxis, se devuelve un error y no se crea el procedimiento almacenado.
5
PROCEDIMIENTOS ALMACENADOS VENTAJAS
Seguridad: Es mucho mejor usar Stored procedure por seguridad. Los procedimientos almacenados facilitan algunas tareas de administración de seguridad y asignación de permisos. Por ejemplo, se puede conceder permiso a un usuario para ejecutar un determinado procedimiento almacenado, aunque el usuario no disponga de los permisos necesarios sobre los objetos afectados por las acciones individuales de dicho procedimiento. Centralización de la definición: al formar parte de la base de datos los procedimientos almacenados están en un lugar centralizado y pueden ser ejecutados por cualquier aplicación que tenga acceso a la misma. 6
PROCEDIMIENTOS ALMACENADOS VENTAJAS
Reducción del tráfico de red: una sentencia formada por decenas, cientos o incluso miles de líneas de código SQL puede escribirse como un procedimiento almacenado en el servidor y ejecutarse simplemente mediante el nombre de dicho procedimiento, en lugar de enviar todas las líneas de código por la red desde el cliente hasta el servidor (esta reducción del tráfico de red será especialmente significativa en redes no muy veloces, como por ejemplo, algunas redes WAN). Encapsulamiento: los procedimientos almacenados encapsulan gran parte de la lógica del negocio a las aplicaciones que los utilizan. Por ejemplo, una aplicación puede llamar al procedimiento almacenado sin conocer cómo funciona internamente este proceso.
7
PROCEDIMIENTOS ALMACENADOS
EJEMPLO EN SQL SERVER:
CREATE PROCEDURE miProcedimiento (IN vEdad INTEGER) BEGIN SELECT * from User where edad > vEdad; END
CALL miProcedimiento(20); 8
PROCEDIMIENTOS ALMACENADOS PRACTICA 1. Elaborar un procedimiento almacenado que consulte los datos de la tabla artículos contenida en la base de datos de tienda.
9
PROCEDIMIENTOS ALMACENADOS EJEMPLO EN POSTGRESQL: CREATE FUNCTION consulta(id1 int) RETURNS TABLE(name1 CHARACTER VARYING(30), carrera1 INT) AS $$ BEGIN RETURN QUERY SELECT name,carrera FROM alumno WHERE id = id1; END; $$ LANGUAGE plpgsql; Llamada del procedimiento: SELECT consulta(3); SELECT * FROM consulta(3);
10
PROCEDIMIENTOS ALMACENADOS PRACTICA 2. Elaborar un procedimiento almacenado que inserte datos de la tabla artículos.
11
PROCEDIMIENTOS ALMACENADOS CREATE FUNCTION agregar(id1 INT, name1 TEXT, carrera1 INT) RETURNS VOID AS $BODY$ BEGIN INSERT INTO alumno VALUES(id1,name1,carrera1); RETURN; END; $BODY$ LANGUAGE plpgsql;
12
PROCEDIMIENTOS ALMACENADOS PRACTICA 3. Elaborar un procedimiento almacenado que actualice datos de la tabla artículos.
13
PROCEDIMIENTOS ALMACENADOS CREATE FUNCTION actualizar(id1 INT) RETURNS VOID AS $$ BEGIN UPDATE alumno SET name='Juan' WHERE id = id1; RETURN; END; $$ LANGUAGE plpgsql;
14
PROCEDIMIENTOS ALMACENADOS PRACTICA 4. Elaborar un procedimiento almacenado que elimine datos de la tabla artículos.
15
PROCEDIMIENTOS ALMACENADOS CREATE FUNCTION borrar(id1 INT) RETURNS VOID AS $BODY$ BEGIN DELETE FROM alumno WHERE id=id1; RETURN; END; $BODY$ LANGUAGE plpgsql;
16
TRIGGERS (DISPARADORES) Taller de base de datos
TRIGGERS (DISPARADORES) Un Trigger es una porción de código que se “dispara” al ocurrir un evento, es decir que se ejecuta cuando se cumple una condición establecida al realizar una operación de inserción (INSERT), actualización (UPDATE) o borrado (DELETE). Son usados para mejorar la administración de la Base de datos, sin necesidad de contar con el usuario para que ejecute una sentencia de SQL. Además, pueden generar valores de columnas, prevenir errores de datos, sincronizar tablas, modificar valores de una vista, etc.
18
TRIGGERS (DISPARADORES) Permiten implementar programas basados en paradigma lógico (sistemas expertos, deducción). Suelen ser implementados para realizar tareas relacionadas con cambios en las tablas; como auditorías, búsqueda de límites de los valores, o establecimiento de valores por omisión en columnas.
19
TRIGGERS (DISPARADORES) Permiten implementar programas basados en paradigma lógico (sistemas expertos, deducción). Suelen ser implementados para realizar tareas relacionadas con cambios en las tablas; como auditorías, búsqueda de límites de los valores, o establecimiento de valores por omisión en columnas.
20
TRIGGERS (DISPARADORES) Para crear un trigger se usa la sentencia CREATE TRIGGER, junto a cláusulas que le indican cuando activarse y que hacer cuando se active. Si por alguna razón, quisiéramos que el trigger no se ejecute más, debemos eliminarlo. Para eliminar un trigger, se utiliza la sentencia DROP TRIGGER indicándole el nombre del trigger a borrar; DROP TRIGGER set_date;
21
TRIGGERS (DISPARADORES) SINTAXIS: CREATE TRIGGER nombre_trigger [AFTER|BEFORE] [INSERT | UPDATE | DELETE] ON nombre_tabla FOR EACH [ROW | STATEMENT] EXECUTE PROCEDURE nombre_funcion;
22
TRIGGERS (DISPARADORES) Crear una tabla de eventos en la base de datos con los campos id(int), transacción(char varying 30), fecha(timestamp), usuario(char varying 20). Crear las funciones de los triggers. Crear los triggers utilizando la tabla de eventos y los procedimientos de triggers.
23
TRIGGERS (DISPARADORES) CREATE OR REPLACE FUNCTION eventos1() RETURNS TRIGGER AS $BODY$ DECLARE r eventos%rowtype; BEGIN INSERT INTO eventos VALUES(1,‟actualizacion‟,now(), current_user); RETURN r; END; $BODY$ LANGUAGE 'plpgsql„;
24
TRIGGERS (DISPARADORES) CREATE OR REPLACE FUNCTION eventos2() RETURNS TRIGGER AS $BODY$ DECLARE r eventos%rowtype; BEGIN INSERT INTO eventos VALUES(2,‟insercion‟,now(), current_user); RETURN r; END; $BODY$ LANGUAGE 'plpgsql„;
25
TRIGGERS (DISPARADORES) CREATE OR REPLACE FUNCTION eventos3() RETURNS TRIGGER AS $BODY$ DECLARE r eventos%rowtype; BEGIN INSERT INTO eventos VALUES(3,‟eliminacion‟,now(), current_user); RETURN r; END; $BODY$ LANGUAGE 'plpgsql„;
26
TRIGGERS (DISPARADORES) EJEMPLO: CREATE TRIGGER actualizacion AFTER UPDATE ON articulos FOR EACH STATEMENT EXECUTE PROCEDURE eventos1( );
27
TRIGGERS (DISPARADORES) EJEMPLO: CREATE TRIGGER insercion AFTER INSERT ON articulos FOR EACH STATEMENT EXECUTE PROCEDURE eventos2();
28
TRIGGERS (DISPARADORES) EJEMPLO: CREATE TRIGGER eliminacion AFTER DELETE ON articulos FOR EACH STATEMENT EXECUTE PROCEDURE eventos3();
29
