FACULTAD DE INGENIERIA INDUSTRIAL, SISTEMAS EINFORMATICA
ESCUELA ACADEMICO PROFESIONAL DE INGENIERIA DE INFORMATICA
CURSO BASE DE DATOS
ORACLE 11g - IMPLEMENTACION SEPARATA Nº 07 PROCEDIMIENTOS ALMACENADOS Y DISPARADORES
DOCENTE MG ING. EDWIN IVAN FARRO PACIFICO
HUACHO-LIMA PERU
PROCEDIMIENTOS ALMACENADOS Son un conjunto de instrucciones en PL/SQL, que puede ser llamado usando el nombre que se la haya asignado. Ejercicio01 Crear un procedimiento almacenado que permita ingresar registros a la tabla ciudad que debe crear a continuación: Crear la tabla ciudad Campo Idciudad Nombciud postciud
Tipo de dato Number Varchar2 char
Longitud 4 50 3
Restricciones PK
CREATE TABLE ciudad (idciudad number(4) PRIMARY KEY, nombciud varchar2(50), p os t ci ud c ha r( 3 )) Crear la secuencia para generar el código del distrito CREATE SEQUENCE xidciudad INCREMENT BY 10 START WITH 10 MAXVALUE 9999 NOCYCLE NOCACHE Crear procedimiento almacenado CREATE OR REPLACE PROCEDURE sp_insert_ciudad (v_nombciud ciudad.nombciud%type, v_postciud ciudad .postciud%type) AS BEGIN INSERT INTO ciudad(idciudad,nombciud,postciud) VALUES (xidciudad.nextval,v_nombciud ,v_postciud); END sp_insert_ciudad; Llamado al procedimiento BEGIN sp_insert_ciudad('HUACHO','1 0'); END;
Ing.CIP Edwin Iván Farro Pacífico
Pag 2
Ejercicio 02 Crear un procedimiento almacenado que permita actualizar registros de la tabla ciudad
CREATE OR REPLACE PROCEDURE sp_update_ciudad (v_idciudad ciudad.idciudad%type, v_nombciud ciudad.nombciud%type, v_postciud ciudad .postciud%type) AS BEGIN UPDATE ciudad SET nombciud=v_nombciud ,postciud=v_postciud WHERE idciudad=v_idciudad; END sp_update_ciudad; Ejercicio 03 Crear un procedimiento almacenado que permita eliminar registros de la tabla ciudad CREATE OR REPLACE PROCEDURE sp_delete_ciudad (v_idciudad ciudad.idciudad%type) AS BEGIN DELETE FROM ciudad WHERE idciudad=v_idciudad; END sp_delete_ciudad; Ejercicio 04 Crear un procedimiento almacenado que permita agregar, actualizar o borrar registros.
CREATE OR REPLACE PROCEDURE sp_mante_ciudad (v_condicion varchar2, v_idciudad ciudad.idciudad%type, v_nombciud ciudad.nombciud%type, v_postciud ciudad .postciud%type) AS BEGIN IF v_condicion='I' THEN INSERT INTO CIUDAD (idciudad,nombciud,postciud) VALUES(xidciudad .nextval,v_nombciud ,v_postciud); dbms_output.put_line('Inserto' | |v_nombciud); ELSIF v_condicion='U' THEN UPDATE ciudad SET nombciud=v_nombciud,postciud=v_postciud WHERE idciudad=v_idciudad; dbms_output.put_line('Actualizo el registro' | |v_idciudad); ELSIF v_condicion='D' THEN DELETE FROM ciudad WHERE idciudad=v_idciudad; dbms_output.put_line('Eliminó el registro' ||v_idciudad||' '||v_nombciud); END IF; END;
Ing.CIP Edwin Iván Farro Pacífico
Pag 3
TRIGGERS El triggers es una clase especial de procedimiento almacenado que se ejecuta automáticamente cuando un usuario intenta la instrucción especificada de modificación de datos en la tabla indicada. SQL Server permite la creación de varios desencadenadores para cualquier instrucción INSERT, UPDATE o DELETE. A continuación se indican algunos usos habituales para los desencadenadores.
Mantener la regla de integridad de los datos Mantener actualizados totales dinámicos Mantener actualizada una columna calculada Implementar una acción referencial, como eliminación en cascada Mantener un registro de auditoria de los cambios.
Un desencadenador puede configurarse para que se active cuando cambien los datos de alguna manera, es decir, mediante una instrucción INSERT, UPDATE o DELETE. Para el siguiente ejemplo crearemos una tabla Historial_cliente, para realizar la auditoria a la tabla Cliente, de la base de datos Nwind. Esta tabla guarda información del cliente, además el tipo de transacción (I,U,D), la fecha de transacción, el usuario que realizó la transacción, y la estación de trabajo que ejecuto la transacción. Sintaxis para controlar operaciones de insert o update CREATE OR REPLACE TRIGGER TR_AUDIT_PROG AFTER INSERT OR UPDATE OR DELETE ON PROGRAMADOR FOR EACH ROW BEGIN IF INSERTING THEN INSERT INTO HISTORIAL VALUES(: NEW. IDPROGRAMADOR,: NEW. NOMBPROG,'I',SYSDATE,USER); ELSIF UPDATING THEN INSERT INTO HISTORIAL VALUES(: NEW. IDPROGRAMADOR,: NEW. NOMBPROG,'U',SYSDATE,USER); ELSE INSERT INTO HISTORIAL VALUES(:OLD. IDPROGRAMADOR,:OLD. NOMBPROG,'D',SYSDATE, USER); END IF; END TR_AUDIT_PROG;
FUNCIONES Una función es un conjunto de instrucciones en PL/SQL, que pueden ser llamados usando el nombre con que se le haya creado. Se diferencian de los procedimientos, en que las funciones retornan un valor al entorno desde donde fueron llamadas. Sintaxis: CREATE OR REPLACE FUNCTION nombre [(param [IN] datatype...] RETURN tipodato [IS | AS] pl/sql_subprograma
Ing.CIP Edwin Iván Farro Pacífico
Pag 4
Ejemplo Crear una función que reciba como parámetro el número de departamento y devuelva el máximo salario del departamento. CREATE OR REPLACE FUNCTION f_maxsal (xdeptno dept.deptno%type) RETURN NUMBER IS xsal emp.sal%type; BEGIN SELECT MAX(sal) I NTO(xsal) FROM emp WHERE deptno=xdeptno; RETURN xsal; END;
Ejecutar la función: SQL> SELECT f_maxsal(10) FROM dual;
Ing.CIP Edwin Iván Farro Pacífico
Pag 5
