Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
33Creación de Procedimientos
3-2 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
Visión General de los Procedimientos
• Un procedimiento es un bloque PL/SQL nombrado que realiza una acción.
• Un procedimiento puede estar almacenado en la base de datos (B.D.), como un objeto de la B.D, para ser ejecutado múltiples veces.
3-3 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
Sintaxis para la Creación Procedimientos
CREATE [OR REPLACE] PROCEDURE procedure_name (argument1 [mode1] datatype1, argument2 [mode2] datatype2, . . .IS parte declarativa (OPCIONAL)
BEGIN sentencias ejecutables [EXCEPTION]END;
3-4 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
Modos de Parámetros Procedurales
entornoentornode llamadade llamada
ProcedimientoProcedimiento
(DECLARE)(DECLARE)
BEGINBEGIN
EXCEPTIONEXCEPTION
END;END;
Parámetro INParámetro IN
Parámetro OUTParámetro OUT
Parámetro IN OUTParámetro IN OUT
3-5 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
IN OUTTiene que especif.Valor que se pasa al Subprograma; Devuelve al ent. de llamadaVariable InicializadaTiene que ser una variable
OUTTiene que especif.Devuelve al entorno de llamada
Variable no InicializadaTiene que ser una variable
Modos para Parámetros Formales
INPor DefectoValor que se pasa al Subprograma
Parámetro formal constanteParámetro Actualpuede ser un literal, expresión, cosntate o variable inicializada
3-6 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
Parámetros IN: Ejemplo
SQL> CREATE OR REPLACE PROCEDURE raise_salary 2 (v_id in emp.empno%TYPE) 3 IS 4 BEGIN 5 UPDATE emp 6 SET sal = sal * 1.10 7 WHERE empno = v_id; 8 END raise_salary; 9 /Procedure created.Procedure created.
SQL> EXECUTE raise_salary (7369)PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.
v_idv_id73697369
3-7 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
Parámetros OUT: Ejemplo
Entorno de LlamadaEntorno de Llamada Proc. QUERY_EMPProc. QUERY_EMP
76547654 v_idv_id
v_namev_name
v_salaryv_salary
v_ commv_ comm
MARTINMARTIN
12501250
14001400
3-8 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
Parámetros OUT: Ejemplo
SQL> CREATE OR REPLACE PROCEDURE query_emp 1 (v_id IN emp.empno%TYPE, 2 v_name OUT emp.ename%TYPE, 3 v_salary OUT emp.sal%TYPE, 4 v_comm OUT emp.comm%TYPE) 5 IS 6 BEGIN 7 SELECT ename, sal, comm 8 INTO v_name, v_salary, v_comm 9 FROM emp 10 WHERE empno = v_id; 11 END query_emp; 12 /
3-9 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
Parámetros OUT y SQL*Plus
SQL> START emp_query.sqlProcedure created.Procedure created.
SQL> VARIABLE g_name varchar2(15)SQL> VARIABLE g_salary numberSQL> VARIABLE g_comm number
SQL> EXECUTE query_emp (7654, :g_name, :g_salary, 2 :g_comm)PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.
SQL> PRINT g_nameG_NAME---------------MARTIN
3-10 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
Parámetros IN OUT
SQL> CREATE OR REPLACE PROCEDURE format_phone 2 (v_phone_no IN OUT VARCHAR2) 3 IS 4 BEGIN 5 v_phone_no := '(' || SUBSTR(v_phone_no,1,3) || 6 ')' || SUBSTR(v_phone_no,4,3) || 7 '-' || SUBSTR(v_phone_no,7); 8 END format_phone; 9 /
Calling environmentCalling environment FORMAT_PHONE procedureFORMAT_PHONE procedure
v_phone_nov_phone_no'(800)633-0575' '(800)633-0575' '(800)633-0575' '(800)633-0575'
3-11 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
Paso de Parámetros: Procedimiento de Ejemplo
SQL> CREATE OR REPLACE PROCEDURE add_dept 1 (v_name IN dept.dname%TYPE DEFAULT 'unknown', 2 v_loc IN dept.loc%TYPE DEFAULT 'unknown') 3 IS 4 BEGIN 5 INSERT INTO dept 6 VALUES (dept_deptno.NEXTVAL, v_name, v_loc); 7 END add_dept; 8 /
3-12 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
Ejemplos: Paso de Parámetros
SQL> exec add_dept-- inserta en tabla el valor ‘unknown’ en los camposPL/SQL procedure successfully completed.PL/SQL procedure successfully completed.SQL> exec add_dept(‘TRAINING’,’NEW YORK’);
SQL> SELECT * FROM dept;
DEPTNO DNAME LOC------ -------------- ------------- ... ... ... 41 unknown unknown 42 TRAINING NEW YORK 43 EDUCATION DALLAS 44 unknown BOSTON
3-13 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
Llamada a un Proc. desde un bloque Anónimo PL/SQL
DECLARE v_id NUMBER := 7900;BEGIN raise_salary(v_id); --invoke procedureCOMMIT;...
END;
3-14 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
Llamada a un Proc. desde un “Stored Procedure”
SQL> CREATE OR REPLACE PROCEDURE process_emps 2 IS 3 CURSOR emp_cursor IS 4 SELECT empno 5 FROM emp; 6 BEGIN 7 FOR emp_rec IN emp_cursor LOOP 8 raise_salary(emp_rec.empno); --invoke procedure 9 END LOOP; 10 COMMIT; 11 END process_emps; 12 /
3-15 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
Borrado de Procedimientos en el Servidor
Usando SQL*Plus:Usando SQL*Plus:• Sintaxis:
• Ejemplo:
DROP PROCEDURE procedure_name
SQL> DROP PROCEDURE raise_salary;Procedure dropped.
3-16 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
Resumen
• Un procedimiento es un bloque nombrado PL/SQL que realiza una acción.
• Use parámetros para pasar datos desde el entorno de llamada al procedimiento.
• Los Procedimientos pueden ser llamados desde cualquier herramienta o lenguaje que soporte PL/SQL.
• Los Procedimientos pueden servir como bloques de una aplicación.
Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
33Creación de Funciones
3-18 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
Visión General de las Funciones Almacenadas
• Una función es un bloque nombrado PL/SQL que devuelve un valor.
• Una función puede estar almacenada en la B.D, como objeto de la B.D, para repetidas ejecuciones.
• Una función puede ser llamada como parte de una expresión.
3-19 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
Sintaxis para la Creación de FuncionesCREATE [OR REPLACE] FUNCTION FUNCTION_name (argument1 [mode1] datatype1, argument2 [mode2] datatype2, . . .RETURN datatypeIS|ASPL/SQL Block;
3-20 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
Creación de una Función Almacenada Usando SQL*Plus:
EjemploSQL> CREATE OR REPLACE FUNCTION get_sal 2 (v_id IN emp.empno%TYPE) 3 RETURN NUMBER 4 IS 5 v_salary emp.sal%TYPE :=0; 6 BEGIN 7 SELECT sal 8 INTO v_salary 9 FROM emp 10 WHERE empno = v_id; 11 RETURN (v_salary); 12 END get_sal; 13 /
3-21 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
Ejecución de Funciones
• Llame a la función como parte de una expresión PL/SQL.
• Cree una variable host que recoja el valor devuelto.
• Ejecute la función. La variable host se volcará en valor de RETURN.
3-22 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
Ejecución de Funciones en SQL*Plus: Ejemplo
Entorno de LlamadaEntorno de Llamada Función GET_SALFunción GET_SALv_idv_id79347934
RETURN v_salaryRETURN v_salary
SQL> START get_salary.sqlSTART get_salary.sqlProcedure createdProcedure created.SQL> VARIABLE g_salary numberSQL> EXECUTE :g_salary := get_sal(7934)PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.
SQL> PRINT g_salary G_SALARY
------------------ 1300
3-23 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
Desde dónde llamar a una Función de Usuario
• Como columna de un SELECT• Condiciones en claúsulas WHERE y
HAVING• Claúsulas CONNECT BY, START WITH,
ORDER BY, y GROUP BY• Claúsula VALUES de un comando
INSERT• Claúsula SET de un comando UPDATE
3-24 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
Llamada a Funciones desde Expresiones SQL: Restricciones• Una función de usuario tiene que ser
una función almacenada.• Tiene que ser una función de registro,
no de grupo.• Sólo se sirve de comandos IN.• Los tipos de datos tienen que ser
CHAR, DATE, o NUMBER, no tipos PL/SQL types como BOOLEAN, RECORD, o TABLE.
• El tipo de “Return” tiene que ser un tipo interno del serividor Oracle.
3-25 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
Llamada a Funciones desde Expresiones SQL: Restricciones• No se permiten comandos INSERT,
UPDATE, o DELETE.• Las llamadas a subprogramas que
rompan estas restricciones, tampoco se permiten.
3-26 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
Borrando una Función del Servidor
Usando SQL*PlusUsando SQL*Plus• Sintaxis:
• Ejemplo:SQL> DROP FUNCTION get_salary;Function dropped.
DROP FUNCTION FUNCTION_name
3-27 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
¿Procedimiento o Función?
ProcedimientoProcedimiento
(DECLARE)(DECLARE)
BEGINBEGIN
EXCEPTIONEXCEPTION
END;END;
IN argumentoIN argumento
OUT argumentoOUT argumento
IN OUT argum.IN OUT argum.
Entorno Entorno de llamadade llamada
Entorno Entorno de llamadade llamada
FunciónFunción
(DECLARE)(DECLARE)
BEGINBEGIN
EXCEPTIONEXCEPTION
END;END;
IN argumentoIN argumento
3-28 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
Comparación entre Procedimientos y Funciones
ProcedimientoSe ejecuta como una sentencia PL/SQLNo devuelve un tipo de datoPueden devolver uno o más valores
FunciónSon llamadas como parte de una expresiónDeben contener RETURN tipo de datoTienen que devolver un valor
3-29 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
Beneficios de Procedimientos y Funciones Almacenadas
• Mejoran el Rendimiento• Mejoran el Mantenimiento• Mejoran la Seguridad e Integridad de los
datos.
3-30 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
Resumen
• Una función es un bloque nombrado PL/SQL que tiene que devolver un valor.
• Una función es llamada como parte de una expresión.
• Una función almacenada puede ser llamada en sentencias SQL.
Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
33Creación de Paquetes
3-32 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
Visión General sobre los Paquetes• Agrupan de forma lógica conceptos PL/SQL
relacionados; tipos PL/SQL, items y subprogramas
• Compuestos de dos partes:– Especificación– Cuerpo
• No pueden ser llamados, parametrizados o anidados
• Permiten a Oracle8 leer múltiples objetos en memoria, de una sola vez.
3-33 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
Ventajas de los Paquetes
• Modularidad• Diseño más sencilo de la aplicación• Información oculta• Funcionalidad añadida• Mejor rendimiento• Sobrecarga
3-34 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
Ventajas de los Paquetes
• Modularidad• Diseño más sencilo de la aplicación• Información oculta• Funcionalidad añadida• Mejor rendimiento• Sobrecarga
3-35 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
Desarrollo de un Paquete
Declaración del Declaración del Procedimiento AProcedimiento A
Definición del Definición del Procedimiento BProcedimiento B
EspecificaciónEspecificacióndel Paquetedel Paquete
CuerpoCuerpodel Paquetedel Paquete
1
2
3
4
2Definición del Definición del Procedimiento AProcedimiento A
5
3-36 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
Desarrollo de un Paquete1
2
3
4
25
EspecificaciónEspecificacióndel Paquetedel Paquete
CuerpoCuerpodel Paquetedel Paquete
Declaración del Declaración del Procedimiento AProcedimiento A
Definición del Definición del Procedimiento BProcedimiento B
Definición del Definición del Procedimiento AProcedimiento A
3-37 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
CREATE [OR REPLACE] PACKAGE package_nameIS | AS public type and item declarations subprogram specificationsEND package_name;
Creación de la Especificación de un Paquete
Sintaxis:Sintaxis:
3-38 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
Paquete COMM_PACKAGE Paquete COMM_PACKAGE
G_COMMG_COMM
Declaración Declaración del Procedimientodel ProcedimientoRESET_COMMRESET_COMM
Especificación Especificación del Paquetedel Paquete
1
2
Declaración de Partes Públicas
3-39 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
Creación de la Especificación de un Paquete: Ejemplo
SQL>CREATE OR REPLACE PACKAGE comm_package IS 2 g_comm NUMBER := 10; --initialized to 10 3 PROCEDURE reset_comm 4 (v_comm IN NUMBER); 5 END comm_package; 6 /
3-40 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
Declaración de una Variable Global o un Procedimiento Público
SQL>EXECUTE comm_package.g_comm := 5
SQL>EXECUTE comm_package.reset_comm(8)
3-41 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
Creación del Cuerpo de un Paquete
Sintaxis:Sintaxis:CREATE [OR REPLACE] PACKAGE BODY package_nameIS | AS private type and item declarations subprogram bodiesEND package_name;
3-42 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
G_COMMG_COMM
Declaración del ProcedimientoDeclaración del ProcedimientoRESET_COMMRESET_COMM
Definición de la FunciónDefinición de la FunciónVALIDATE_COMMVALIDATE_COMM
EspecificaciónEspecificacióndel Paquetedel Paquete
CuerpoCuerpodel Paquetedel Paquete
1
2
3
2Definición del ProcedimientoDefinición del ProcedimientoRESET_COMMRESET_COMM
Paquete COMM_PACKAGEPaquete COMM_PACKAGEPartes Públicas y Privadas
3-43 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
Creación del Cuerpo de un Paquete : Ejemplo
SQL>CREATE OR REPLACE PACKAGE BODY comm_package IS 2 FUNCTION validate_comm 3 (v_comm IN NUMBER) RETURN BOOLEAN 4 IS 5 v_max_comm NUMBER; 6 BEGIN 7 SELECT MAX(comm) 8 INTO v_max_comm 9 FROM emp; 10 IF v_comm > v_max_comm THEN RETURN(FALSE); 11 ELSE RETURN(TRUE); 12 END IF; 13 END validate_comm; 14 END comm_package; 15 /
3-44 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
SQL>PROCEDURE reset_comm 2 (v_comm IN NUMBER) 3 IS 4 v_valid BOOLEAN; 5 BEGIN 6 v_valid := validate_comm(v_comm); 7 IF v_valid = TRUE THEN 8 g_comm := v_comm; 9 ELSE 10 RAISE_APPLICATION_ERROR 11 (-20210,'Invalid commission'); 12 END IF; 13 END reset_comm; 14 END comm_package; 15 /
Creación del Cuerpo de un Paquete : Ejemplo
3-45 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
Guía para el Desarrollo de Paquetes
• Cree paquetes flexibles.• Defina la especificación del paquete
antes que el cuerpo.• La especificación del paquete sólo
debería de contener construcciones públicas
• La especificación del paquete debería contener el mínimo nº de construcciones.
3-46 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
Llamada a Programas de un PaqueteEjemplo 1: Llamada a una función desde Ejemplo 1: Llamada a una función desde un procedimiento del mismo paquete:un procedimiento del mismo paquete:
CREATE OR REPLACE PACKAGE BODY comm_package IS. . .
PROCEDURE reset_comm(v_comm IN NUMBER)IS v_valid BOOLEAN;BEGIN v_valid := validate_comm(v_comm); IF v_valid = TRUE THEN
g_comm := v_comm; ELSE
RAISE_APPLICATION_ERROR (-20210, 'Invalid comm'); END IF;END reset_comm;
END comm_package;
3-47 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
Ejemplo 2: Llamada a un procedimiento Ejemplo 2: Llamada a un procedimiento de un paquete desde SQL*Plus:de un paquete desde SQL*Plus:
Ejemplo 3: Llamada a un procedimiento Ejemplo 3: Llamada a un procedimiento de un paquete en un esquema diferente:de un paquete en un esquema diferente:
Ejemplo 4: Llamada a un procedimiento Ejemplo 4: Llamada a un procedimiento de un paquete en una B.D. remota:de un paquete en una B.D. remota:
Llamada a Programas de un Paquete
SQL> EXECUTE comm_package.reset_comm(1500);
SQL> EXECUTE scott.comm_package.reset_comm(1500);
SQL> EXECUTE comm_package.reset_comm@ny (1500);
3-48 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
Referencia a una Variable Global dentro del Paquete
Ejemplo 1:Ejemplo 1:CREATE OR REPLACE PACKAGE BODY comm_package IS
. . .PROCEDURE reset_comm(v_comm IN NUMBER)IS
v_valid BOOLEAN;BEGIN
v_valid := validate_comm(v_comm);IF v_valid = TRUE THEN
g_comm := v_comm;ELSE
RAISE_APPLICATION_ERROR (-20210,'Invalid comm');END IF;
END reset_comm;END comm_package;
3-49 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
Referencia a una Variable Global desde un Proc. Standalone
Ejemplo 2:Ejemplo 2:CREATE OR REPLACE PROCEDURE hire_emp (v_ename IN emp.ename%TYPE, v_mgr IN emp.mgr%TYPE, v_job IN emp.job%TYPE, v_sal IN emp.sal%TYPE)IS v_comm emp.comm%TYPE;. . .BEGIN. . .
v_comm := comm_package.g_comm;. . .END hire_emp;
3-50 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
SCOTT-09:00> EXECUTE comm_package.reset_comm(120); Initially g_comm equals 10After executing the procedure, g_comm equals 120
JONES-09:30> INSERT INTO emp (ename,..,comm) VALUES ('Madona',..,2000);
JONES-09:35> EXECUTE comm_package.reset_comm(170);Initially g_comm equals 10After executing the procedure, g_comm equals 170
SCOTT-10:00> EXECUTE comm_package.reset_comm(4000); Results in: 'Invalid commission'
JONES-11:00> ROLLBACK;JONES-11:01> EXIT;
JONES-12:00> EXECUTE comm_package.reset_comm(150); Initially g_comm equals 10
After executing the procedure, g_comm equals 150
Estado Persistente de las Variables de un Paquete
3-51 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
Estado Persistente de un Cursor en un Paquete
SQL> CREATE OR REPLACE PACKAGE pack_cur2 IS3 CURSOR c1 IS SELECT empno FROM emp
4 ORDER BY empno desc;5 PROCEDURE proc1_3rows;6 PROCEDURE proc4_6rows;7 END pack_cur;8 /
Ejemplo:Ejemplo:
3-52 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
SQL> CREATE OR REPLACE PACKAGE BODY pack_cur 2 IS 3 v_empno NUMBER; 4 PROCEDURE proc1_3rows IS 5 BEGIN OPEN c1; 6 LOOP FETCH c1 INTO v_empno; 7 DBMS_OUTPUT.PUT_LINE('Id :' ||(v_empno)); 8 EXIT WHEN c1%ROWCOUNT >= 3; 9 END LOOP; 10 END proc1_3rows; 11 PROCEDURE proc4_6rows IS 12 BEGIN 13 LOOP FETCH c1 INTO v_empno; 14 DBMS_OUTPUT.PUT_LINE('Id :' ||(v_empno)); 15 EXIT WHEN c1%ROWCOUNT >= 6; 16 END LOOP; 17 CLOSE c1; 18 END proc4_6rows; 19 END pack_cur; 20 /
Estado Persistente de un Cursor en un Paquete
3-53 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
SQL> SET SERVEROUTPUT ONSQL> EXECUTE pack1.proc1_3rows; Id : 7934 Id : 7902 Id : 7900SQL> EXECUTE pack1.proc4_6rows; Id : 7876 Id : 7844 Id : 7839
Estado Persistente de un Cursor en un Paquete
3-54 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
CREATE OR REPLACE PACKAGE emp_package IS TYPE emp_table_type IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER; PROCEDURE read_emp_table(emp_table OUT emp_table_type);END emp_package;
CREATE OR REPLACE PACKAGE BODY emp_package IS PROCEDURE read_emp_table(emp_table OUT emp_table_type) IS i BINARY_INTEGER:=0; BEGIN FOR emp_record IN (SELECT * FROM emp) LOOP emp_table(i):=emp_record; I:=I+1; END LOOP; END;END emp_package;
Estado Persistente de Tablas de Registros PL/SQL en un Paquete
3-55 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
DROP PACKAGE package_name
Eliminación de PaquetesPara borrar la especificación del Para borrar la especificación del paquete y el cuerpo:paquete y el cuerpo:
Para borrar el cuerpo del paquete:Para borrar el cuerpo del paquete:
DROP PACKAGE BODY package_name
3-56 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
Resumen• Mejoran la organización, gestión, seguridad
y rendimiento.• Agrupan procedimientos y funciones.• Se puede cambiar el cuerpo de un paquete
sin que afecte a la especificación.• Conceden acceso de seguridad al paquete
completo.
3-57 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
Resumen• Ocultan el código fuente a los usuarios.• Cargan todo el paquete en memoria en
la primera llamada.• Reducen el acceso a disco para
llamadas posteriores.• Ofrecen identificadores para las
sesiones de usuario.
3-58 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
Comando
CREATE [OR REPLACE] PACKAGE
CREATE [OR REPLACE] PACKAGE BODY
DROP PACKAGE
DROP PACKAGE BODY
Tarea
Crea o modifica la especificación de un paquete ya existente
Crea o modifica el cuerpo de un paquete ya existente
Borra tanto la especificación como el cuerpo de un paquete
Borra el cuerpo de un paquete
Resumen
Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
33Creación de Triggers en la
Base de Datos
3-60 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
Visión General de los Triggers
• Un trigger es un bloque PL/SQL que se ejecuta implícitamente cuando ocurre un evento.
• Un trigger puede ser “de la base de datos” o “de la aplicación”.
3-61 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
Guía para el Diseño de Triggers
• Realizar acciones relacionadas.• Utilizar triggers para operaciones
globales.• No “reinventar la rueda”• Prestar atención a los excesos (overkill)
3-62 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
AplicaciónAplicación
SQL> INSERT INTO EMP 2 . . .;
Tabla EMPTabla EMPEMPNO 7838 7698 7369 7788
ENAMEKINGBLAKESMITHSCOTT
JOBPRESIDENTMANAGERCLERKANALYST
SAL50002850 8003000
Trigger CHECK_SALTrigger CHECK_SAL
Ejemplo de Trigger de la B.D.
3-63 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
Creación de Disparadores• Momento: BEFORE o AFTER• Evento: INSERT o UPDATE o DELETE• Nombre Tabla: On table• Tipo de Trigger: Row o statement• Claúsula When: Condición • Cuerpo del Trigger: DECLARE BEGINBEGIN END; END;
3-64 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
Componentes de un Trigger
Momento: ¿Cuándo debería dispararse?Momento: ¿Cuándo debería dispararse?• BEFORE: El código del cuerpo del trigger
se ejecutará antes del evento DML• AFTER: El código del cuerpo del trigger se
ejecutará antes del evento DML
3-65 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
Componentes de un Trigger
Evento: Evento: ¿Qué operación DML provocará la ¿Qué operación DML provocará la ejecución trigger?ejecución trigger?• INSERT• UPDATE • DELETE• Cualquier combinación de las anteriores
3-66 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
Componentes de un TriggerTipo de Trigger:Tipo de Trigger:¿Cuántas veces debería ejecutarse el ¿Cuántas veces debería ejecutarse el cuerpo del trigger cuando ocurre el cuerpo del trigger cuando ocurre el evento?evento?• Statement: El cuerpo del trigger se
ejecuta sólo una vez para el evento. Este es el comportamiento por defecto.
• Row: El cuerpo del trigger se ejecuta una vez para cada registro afectado por el evento.
3-67 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
Componentes de un Trigger
Cuerpo del Trigger:Cuerpo del Trigger:¿Qué acción debería de realizar el trigger?¿Qué acción debería de realizar el trigger?• El cuerpo del trigger es definido con un
bloque PL/SQL anónimo[DECLARE][DECLARE]BEGINBEGIN[EXCEPTION][EXCEPTION]END;END;
3-68 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
Sintaxis para la Creación y Definición de Triggers
CREATE [OR REPLACE] TRIGGER trigger_nametiming event1 [OR event2 OR event3]ON table_name PL/SQL block;
3-69 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
Trigger “Before Statement”: Ejemplo
SQL> CREATE OR REPLACE TRIGGER secure_emp 2 BEFORE INSERT ON emp 3 BEGIN 4 IF (TO_CHAR (sysdate,'DY') IN ('SAT','SUN')) 5 OR (TO_CHAR(sysdate,'HH24')NOT BETWEEN 6 '08' AND '18' 7 THEN RAISE_APPLICATION_ERROR (-20500, 8 'You may only insert into EMP during normal 9 hours.'); 10 END IF; 11 END; 12 /
3-70 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
Ejemplo
SQL> INSERT INTO emp (empno, ename, deptno) 2 VALUES (7777, 'BAUWENS', 40);INSERT INTO emp (empno, ename, deptno) *ERROR at line 1:ORA-20500: You may only insert into EMP duringnormal hours.ORA-06512: at "SCOTT.SECURE_EMP", line 4ORA-04088: error during execution of trigger'SCOTT.SECURE_EMP'
3-71 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
Trigger “After Statement”: EjemploSQL>CREATE OR REPLACE TRIGGER check_salary_count 2 AFTER UPDATE OF sal ON emp 3 DECLARE 4 v_salary_changes NUMBER; 5 v_max_changes NUMBER; 6 BEGIN 7 SELECT upd, max_upd 8 INTO v_salary_changes, v_max_changes 9 FROM audit_table 10 WHERE user_name = user 11 AND table_name = 'EMP' 12 AND column_name = 'SAL'; 13 IF v_salary_changes > v_max_changes THEN 14 RAISE_APPLICATION_ERROR (-20501, 15 'You may only make a maximum of '|| 16 TO_CHAR (v_max_changes) || 17 ' changes to the SAL column'); 18 END IF; 19 END; 20 /
3-72 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
Creación de un Trigger a Nivel de Registro
CREATE [OR REPLACE] TRIGGER trigger_nametiming event1 [OR event2 OR event3]ON table_name [REFERENCING OLD AS old | NEW AS new]FOR EACH ROW[WHEN condition]PL/SQL block;
3-73 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
SQL>CREATE OR REPLACE TRIGGER audit_emp 2 AFTER DELETE OR INSERT OR UPDATE ON emp 3 FOR EACH ROW 4 BEGIN 5 IF DELETING THEN 6 UPDATE audit_table SET del = del + 1 7 WHERE user_name = user AND table_name = 'EMP' 8 AND column_name IS NULL; 9 ELSIF INSERTING THEN 10 UPDATE audit_table SET ins = ins + 1 11 WHERE user_name = user AND table_name = 'EMP' 12 AND column_name IS NULL; 13 ELSIF UPDATING ('SAL') THEN 14 UPDATE audit_table SET upd = upd + 1 15 WHERE user_name = user AND table_name = 'EMP' 16 AND column_name = 'SAL'; 17 ELSE /* The data manipulation operation is a general UPDATE. */ 18 UPDATE audit_table SET upd = upd + 1 19 WHERE user_name = user AND table_name = 'EMP' 20 AND column_name IS NULL; 21 END IF; 22 END; 23 /
Trigger “After Row”: Ejemplo
3-74 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
SQL>CREATE OR REPLACE TRIGGER audit_emp_values 2 AFTER DELETE OR INSERT OR UPDATE ON emp 3 FOR EACH ROW 4 BEGIN 5 INSERT INTO audit_emp_values (user_name, 6 timestamp, id, old_last_name, new_last_name, 7 old_title, new_title, old_salary, new_salary) 8 VALUES (USER, SYSDATE, :old.empno, :old.ename, 9 :new.ename, :old.job, :new.job, 10 :old.sal, :new.sal); 11 END; 12 /
Uso de los calificadores “OLD” y “NEW”
3-75 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
USER_NAMEEGRAVINA
NGREENBE
TIMESTAMP12-NOV-97
10-DEC-97
ID7950
7844
OLD_LAST_NAMENULL
MAGEE
NEW_LAST_NAMEHUTTON
TURNER
OLD_TITLENULL
CLERK
NEW_TITLEANALYST
SALESMAN
NEW_SALARY3500
1100
ContinuaciónOLD_SALARY
NULL
1100
Tabla de Auditoría “Audit_Emp_Values”
3-76 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
SQL>CREATE OR REPLACE TRIGGER derive_commission_pct 2 BEFORE INSERT OR UPDATE OF sal ON emp 3 FOR EACH ROW 4 WHEN (new.job = 'SALESMAN') 5 BEGIN 6 IF INSERTING THEN :new.comm := 0; 7 ELSE /* UPDATE of salary */ 8 IF :old.comm IS NULL THEN 9 :new.comm :=0; 10 ELSE 11 :new.comm := :old.comm * (:new.sal/:old.sal); 12 END IF; 13 END IF; 14 END; 15 /
Condicionando un Trigger de Registro
3-77 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
Diferenciando entre Triggers y Procedimientos Almacenados
Triggers
Usa CREATE TRIGGER
El Dic. de Datos contiene el código fuente y el p-code
Invocados implícitamente
NO se permite: COMMIT, SAVEPOINT, ROLLBACK
Procedure
Usa CREATE PROCEDURE
El Dic. de Datos contiene el código fuente y el p-code
Invocados explícitamente
Se permite: COMMIT, SAVEPOINT, ROLLBACK
3-78 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
ALTER TRIGGER trigger_name DISABLE | ENABLE
Gestión de los TriggersActivar o desactivar un trigger de la B.D.Activar o desactivar un trigger de la B.D.
ALTER TABLE table_name DISABLE | ENABLE ALL TRIGGERS
Activar o desactivar todos los trigs. de una tablaActivar o desactivar todos los trigs. de una tabla
ALTER TRIGGER trigger_name COMPILE
Recompilar un trigger para una tablaRecompilar un trigger para una tabla
3-79 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
Eliminación de Triggers
Para borrar un trigger de la B.D, utilice la Para borrar un trigger de la B.D, utilice la sintaxis DROP TRIGGER:sintaxis DROP TRIGGER:
DROP TRIGGER trigger_name
3-80 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
SQL> AUDIT INSERT, UPDATE, DELETE 2 ON emp 3 BY ACCESS 4 WHENEVER SUCCESSFUL;
Auditar por medio de la Utilidad “Audit”
3-81 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
SQL>CREATE OR REPLACE TRIGGER audit_emp_values 2 AFTER DELETE OR INSERT OR UPDATE ON emp 3 FOR EACH ROW 4 BEGIN 5 IF audit_emp_package.g_reason IS NULL THEN 6 RAISE_APPLICATION_ERROR (-20059, 'Specify a reason 7 for the data operation with the procedure 8 SET_REASON before proceeding.'); 9 ELSE 10 INSERT INTO audit_emp_values (user_name, timestamp, id, 11 old_last_name, new_last_name, old_title, new_title, 12 old_salary, new_salary, comments) 13 VALUES (user, sysdate, :old.empno,:old.ename, 14 :new.ename, :old.job, :new.job, :old.sal, 15 :new.sal, :audit_emp_package.g_reason); 16 END IF; 17 END; 18 /
SQL>CREATE TRIGGER cleanup_audit_emp 2 AFTER INSERT OR UPDATE OR DELETE ON emp 3 BEGIN 4 audit_emp_package.g_reason := NULL; 5 END; 6 /
Auditar por medio de Triggers
3-82 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
SQL> ALTER TABLE emp ADD 2 CONSTRAINT ck_salary CHECK (sal >= 500);
Forzar la Integridad de los Datos dentro del Servidor
3-83 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
SQL>CREATE OR REPLACE TRIGGER check_salary 2 BEFORE UPDATE OF sal ON emp 3 FOR EACH ROW 4 WHEN (new.sal < old.sal) OR 5 (new.sal > old.sal * 1.1) 6 BEGIN 7 RAISE_APPLICATION_ERROR (-20508, 8 'Do not decrease salary nor increase by 9 more than 10%.'); 10 END; 11 /
Protección de la Integridad de los Datos con un Trigger
3-84 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
SQL> ALTER TABLE emp 2 ADD CONSTRAINT emp_deptno_fk 3 FOREIGN KEY (deptno) REFERENCES dept(deptno) 4 ON DELETE CASCADE;
Forzar la Integridad Referencial dentro del Servidor
3-85 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
SQL>CREATE OR REPLACE TRIGGER cascade_updates 2 AFTER UPDATE OF deptno ON dept 3 FOR EACH ROW 4 BEGIN 5 UPDATE emp 6 SET emp.deptno = :new.deptno 7 WHERE emp.deptno = :old.deptno; 8 END; 9 /
Protección de la Integridad Referencial con un Trigger
3-86 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
SQL> CREATE SNAPSHOT emp_copy AS 2 SELECT * FROM emp@ny;
Replicación de una Tabla dentro del Servidor
3-87 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
SQL>CREATE OR REPLACE TRIGGER emp_replica 2 BEFORE INSERT OR UPDATE ON emp 3 FOR EACH ROW 4 BEGIN /*Only proceed if user init. data operation, 5 NOT the casc. trigger.*/ 6 IF INSERTING THEN 7 IF :new.flag IS NULL THEN 8 INSERT INTO emp@sf VALUES (:new.empno, 9 :new.ename,...,'B'); 10 :new.flag = 'A'; 11 ELSE /* Updating. */ 12 IF :new.flag = :old.flag THEN 13 UPDATE emp@sf SET ename = :new.ename, ..., 14 FLAG = :new.flag 15 WHERE empno = :new.empno; 16 END IF; 17 IF :old.flag = 'A' THEN :new.flag := 'B'; 18 ELSE :new.flag := 'A'; 19 END IF; 20 END IF; 21 END; 22 /
Replicación de una Tabla con un Trigger
3-88 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
Cálculo de Datos Derivados dentro del Servidor
SQL> UPDATE dept 2> SET total_sal = (SELECT SUM(salary) 3> FROM emp 4> WHERE emp.deptno = dept.deptno);
3-89 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
SQL>CREATE OR REPLACE PROCEDURE increment_salary 2 (v_id IN dept.deptno%TYPE, 3 v_salary IN dept.total_salary%TYPE) 4 IS 5 BEGIN 6 UPDATE dept 7 SET total_sal = NVL (total_sal,0)+ v_salary 8 WHERE deptno = v_id; 9 END increment_salary; 10 /
SQL>CREATE OR REPLACE TRIGGER compute_salary 2 AFTER INSERT OR UPDATE OF sal OR DELETE ON emp 3 FOR EACH ROW 4 BEGIN 5 IF DELETING THEN increment_salary(:old.deptno, -1 * :old.sal); 6 ELSIF UPDATING THEN increment_salary(:new.deptno, 7 :new.sal-:old.sal); 8 ELSE /*inserting*/ increment_salary(:new.deptno, :new.sal); 9 END IF; 10 END; 11 /
Cálculo Datos Derivados con Triggers
3-90 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
SQL>CREATE OR REPLACE TRIGGER notify_reorder_rep 2 AFTER UPDATE OF amount_in_stock, reorder_point ON inventory 3 FOR EACH ROW 4 WHEN new.amount_in_stock <= new.reorder_point 5 DECLARE 6 v_descrip product.descrip%TYPE; 7 v_msg_text VARCHAR2(2000); 8 BEGIN 9 SELECT descrip INTO v_descrip 10 FROM PRODUCT WHERE prodid = :new.product_id; 11 v_msg_text := 'It has come to my personal attention that, 12 due to recent ' 13 CHR(10) || 'transactions, our inventory for product # '|| 14 TO_CHAR(:new.product_id)||'--' 15 || v_name ||'-- has fallen' || CHR(10) || CHR(10) || 16 'Yours,' ||CHR(10) ||user || '.'; 17 dbms_mail.send ('Inventory', user,null,null,'Low 18 Inventory',null,v_msg_text); 19 END; 20 /
Control de Eventos con un Trigger
3-91 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
Ventajas de los Triggers de la B.D.Seguridad de los datos mejorada:Seguridad de los datos mejorada:• Ofrecen chequeos de seguridad basada
en valores.• Ofrecen auditorías basadas en valores.Integridad de los datos mejorada:Integridad de los datos mejorada:• Fuerzan restricciones dinámicas de
integridad de datos.• Fuerzan restricciones complejas de
integridad referencial.• Aseguran que las operaciones
relacionadas se realizan juntas de forma impícita.
3-92 Javier Balbuena / José Manuel Ferrer. Curso 2.001 / 2.002
ProcedimientoProcedimiento PaquetePaquete TriggerTriggerxxxxxxxxxxxxxxxxxxvvvvvvvvvvvvvvvvvvxxxxxxxxxxxxxxxxxxvvvvvvvvvvvvvvvvvvxxxxxxxxxxxxxxxxxxvvvvvvvvvvvvvvvvvvxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxvvvvvvvvvvvvvvvvvvxxxxxxxxxxxxxxxxxxvvvvvvvvvvvvvvvvvvxxxxxxxxxxxxxxxxxxvvvvvvvvvvvvvvvvvvxxxxxxxxxxxxxxxxxx
Declaración delDeclaración delProcedimiento AProcedimiento A
Definición del Definición del Procedimiento BProcedimiento B
Resumen
Definición del Definición del Procedimiento AProcedimiento A
VariableVariableLocalLocal