Creación de Procedimientos

1. Creación de Procedimientos

2. 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. 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;

4. Procedimiento Parámetro IN Parámetro OUT Parámetro IN OUT (DECLARE) BEGIN EXCEPTION END; Modos de Parámetros Procedurales entorno de llamada

5. Modos para Parámetros Formales IN Por Defecto Valor que se pasa al Subprograma Parámetro formal constante Parámetro Actualpuede ser un literal, expresión, cosntate o variable inicializada OUT Tiene que especif. Devuelve al entorno de llamada Variable no Inicializada Tiene que ser una variable IN OUT Tiene que especif. Valor que se pasa al Subprograma; Devuelve al ent. de llamada Variable Inicializada Tiene que ser una variable

6. Parámetros IN: Ejemplo 7369 v_id 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. SQL> EXECUTE raise_salary (7369) PL/SQL procedure successfully completed.

7. Parámetros OUT: Ejemplo Entorno de Llamada Proc. QUERY_EMP 7654 v_id MARTIN v_name v_salary 1250 v_ comm 1400

8. 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 /

9. Parámetros OUT y SQL*Plus SQL> START emp_query.sql Procedure created. SQL> VARIABLE g_name varchar2(15) SQL> VARIABLE g_salary number SQL> VARIABLE g_comm number SQL> EXECUTE query_emp (7654, :g_name, :g_salary, 2 :g_comm) PL/SQL procedure successfully completed. SQL> PRINT g_name G_NAME --------------- MARTIN

10. Parámetros IN OUT FORMAT_PHONE procedure Calling environment v_phone_no '(800)633-0575' '(800)633-0575' 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 /

11. 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 /

12. Ejemplos: Paso de Parámetros SQL> exec add_dept -- inserta en tabla el valor ‘unknown’ en los campos 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

13. Llamada a un Proc. desde un bloque Anónimo PL/SQL DECLARE v_id NUMBER := 7900; BEGIN raise_salary(v_id); --invoke procedure COMMIT; ... END;

14. 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 /

15. Borrado de Procedimientos en el Servidor • Usando SQL*Plus: • Sintaxis: • Ejemplo: DROP PROCEDURE procedure_name SQL> DROP PROCEDURE raise_salary; Procedure dropped.

16. 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.

17. Creación de Funciones

18. 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.

19. Sintaxis para la Creación de Funciones CREATE [OR REPLACE] FUNCTION FUNCTION_name (argument1 [mode1] datatype1, argument2 [mode2] datatype2, . . . RETURN datatype IS|AS PL/SQL Block;

20. Creación de una Función Almacenada Usando SQL*Plus: Ejemplo SQL> 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 /

21. 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.

22. SQL> START get_salary.sql Procedure created. SQL> VARIABLE g_salary number SQL> EXECUTE :g_salary := get_sal(7934) PL/SQL procedure successfully completed. SQL> PRINT g_salary G_SALARY ------------------ 1300 Ejecución de Funciones en SQL*Plus: Ejemplo Entorno de Llamada Función GET_SAL v_id 7934 RETURN v_salary

23. 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

24. 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.

25. 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.

26. Borrando una Función del Servidor • Usando SQL*Plus • Sintaxis: • Ejemplo: DROP FUNCTION FUNCTION_name SQL> DROP FUNCTION get_salary; Function dropped.

27. Función Procedimiento IN argumento IN argumento OUT argumento IN OUT argum. (DECLARE) BEGIN EXCEPTION END; (DECLARE) BEGIN EXCEPTION END; ¿Procedimiento o Función? Entorno de llamada Entorno de llamada

28. Comparación entre Procedimientos y Funciones Procedimiento Se ejecuta como una sentencia PL/SQL No devuelve un tipo de dato Pueden devolver uno o más valores Función Son llamadas como parte de una expresión Deben contener RETURN tipo de dato Tienen que devolver un valor

29. Beneficios de Procedimientos y Funciones Almacenadas • Mejoran el Rendimiento • Mejoran el Mantenimiento • Mejoran la Seguridad e Integridad de los datos.

30. 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.

31. Creación de Paquetes

32. 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.

33. Ventajas de los Paquetes • Modularidad • Diseño más sencilo de la aplicación • Información oculta • Funcionalidad añadida • Mejor rendimiento • Sobrecarga

34. Ventajas de los Paquetes • Modularidad • Diseño más sencilo de la aplicación • Información oculta • Funcionalidad añadida • Mejor rendimiento • Sobrecarga

35. Desarrollo de un Paquete 1 Especificación del Paquete Declaración del Procedimiento A 2 4 Definición del Procedimiento B 3 Cuerpo del Paquete Definición del Procedimiento A 2 5

36. Desarrollo de un Paquete 1 Especificación del Paquete Declaración del Procedimiento A 2 4 Definición del Procedimiento B 3 Cuerpo del Paquete Definición del Procedimiento A 2 5

37. Creación de la Especificación de un Paquete Sintaxis: CREATE [OR REPLACE] PACKAGE package_name IS | AS public type and item declarations subprogram specifications END package_name;

38. Declaración de Partes Públicas Paquete COMM_PACKAGE G_COMM 1 Especificación del Paquete Declaración del Procedimiento RESET_COMM 2

39. 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 /

40. 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)

41. Creación del Cuerpo de un Paquete Sintaxis: CREATE [OR REPLACE] PACKAGE BODY package_name IS | AS private type and item declarations subprogram bodies END package_name;

42. Partes Públicas y Privadas Paquete COMM_PACKAGE 1 G_COMM Especificación del Paquete Declaración del Procedimiento RESET_COMM 2 Definición de la Función VALIDATE_COMM 3 Cuerpo del Paquete Definición del Procedimiento RESET_COMM 2

43. 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 /

44. Creación del Cuerpo de un Paquete : Ejemplo 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 /

45. 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.

46. Llamada a Programas de un Paquete • Ejemplo 1: Llamada a una función desde 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;

47. Llamada a Programas de un Paquete • Ejemplo 2: Llamada a un procedimiento de un paquete desde SQL*Plus: • Ejemplo 3: Llamada a un procedimiento de un paquete en un esquema diferente: • Ejemplo 4: Llamada a un procedimiento de un paquete en una B.D. remota: SQL> EXECUTE comm_package.reset_comm(1500); SQL> EXECUTE scott.comm_package.reset_comm(1500); SQL> EXECUTE comm_package.reset_comm@ny (1500);

48. Referencia a una Variable Global dentro del Paquete 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;

49. Referencia a una Variable Global desde un Proc. Standalone • 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;

50. Estado Persistente de las Variables de un Paquete SCOTT-09:00> EXECUTE comm_package.reset_comm(120); Initially g_comm equals 10 After 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 10 After 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