PL/SQL*

1. Laboratorio de Bases de Datos PL/SQL* *Tomado del curso de Francisco Moreno

2. Laboratorio de Bases de Datos Funciones • Cuando un subprograma posea exactamente un argumento de salida, es mejor crear una función que un procedimiento y así se puede involucrar directamente en expresiones • Sintaxis: CREATE [OR REPLACE]FUNCTION nombre_función [ ( arg1 [modo] tipo [, arg2 [modo] tipo ...] ) ] RETURN tipo_de_dato IS Bloque de PL/SQL

3. Laboratorio de Bases de Datos Ejemplo CREATE OR REPLACE FUNCTION area_circulo (radio IN NUMBER) RETURN NUMBER IS pi CONSTANT NUMBER(5,4) := 3.1416; BEGIN RETURN ( radio * radio * pi ); END; / Invocación en SQL*Plus: VAR a NUMBER; EXECUTE :a := area_circulo(3); PRINT a;

4. Laboratorio de Bases de Datos Recursividad Ejercicio: Elaborar una función que devuelva el factorial de un número. n > 0  n*(n-1)! n! = n = 0  1 n < 0  error (-1)‏ - Elaborarlo recursivo - Elaborarlo no recursivo

5. Laboratorio de Bases de Datos Solución recursiva CREATE OR REPLACE FUNCTION factorialr (n NATURAL) RETURN NUMBER IS BEGIN IF n=0 THEN RETURN 1; ELSE RETURN n*factorialr(n-1); END IF; END; / Rango: 0,1,…2147’

6. Laboratorio de Bases de Datos Solución iterativa CREATE OR REPLACE FUNCTION factorialnr (n NATURAL) RETURN NUMBER IS fact NUMBER := 1; BEGIN FOR k IN 1..n LOOP fact := fact * k; END LOOP; RETURN fact; END; /

7. Laboratorio de Bases de Datos Las funciones se pueden usar en las consultas así: CREATE TABLE numero( num NUMBER(10) PRIMARY KEY); INSERT INTO numero VALUES(1); INSERT INTO numero VALUES(3); SELECT 'El factorial de ' || num || ' es ' || factorialr(num) AS fact FROM numero; Incluso se puede hacer algo como: SELECT 'El factorial de ' ||factorialr(num) || ' es '|| factorialnr(factorialr(num)) AS fact FROM numero;

8. Laboratorio de Bases de Datos Paquetes • Un paquete es una agrupación de funciones, procedimientos y variables. • Clasifican los subprogramas de acuerdo a una categoría elegida por el programador o diseñador • Los paquetes se dividen en 2 partes: • Especificación  Definiciones de vbles “públicas” y prototipos de los subprogramas • Cuerpo (BODY)  Implementación de los subprogramas descritos en la especificación y vbles privadas

9. Laboratorio de Bases de Datos Sintaxis E S P E C I F I C A C I Ó N CREATE PACKAGE nom_paquete IS -- Variables públicas -- Declaración de subprogramas END; / CREATE PACKAGE BODY nom_paquete IS -- Variables privadas /* Implementación de subprogramas privados */ /* Implementación de subprogramas declarados en la especificación */ END; / C U E R P O Los subprogramas privados deben implementarse antes que los otros

10. Laboratorio de Bases de Datos Ejemplo: Creación de la especificación: CREATE OR REPLACE PACKAGE matematico IS FUNCTION factorial(n NATURAL) RETURN NUMBER; FUNCTION dist_puntos( x1 NUMBER, y1 NUMBER, x2 NUMBER, y2 NUMBER) RETURN NUMBER; END; /

11. Laboratorio de Bases de Datos Ejemplo: Implementación CREATE OR REPLACE PACKAGE BODY matematico IS FUNCTION fact (n NATURAL) RETURN NUMBER IS BEGIN IF n=0 THEN RETURN 1; ELSE RETURN n*fact(n-1); END IF; END; PROCEDURE error(mensaje VARCHAR) IS BEGIN DBMS_OUTPUT.PUT_LINE(mensaje); END; Subprogramas privados Continúa

12. Laboratorio de Bases de Datos Cont. Implementación FUNCTION factorial(n NATURAL) RETURN NUMBER IS BEGIN IF (n>20) THEN error('Valor demasiado grande'); RETURN -1; ELSE RETURN fact(n); END IF; END; FUNCTION dist_puntos(x1 NUMBER, y1 NUMBER, x2 NUMBER, y2 NUMBER) RETURN NUMBER IS BEGIN RETURN (SQRT(POWER(x2 - x1,2) + POWER(y2 - y1,2) ) ); END; END;--Fin del paquete / Subprogramas Públicos

13. Laboratorio de Bases de Datos Para ejecutar: a) VAR a NUMBER; EXECUTE :a := matematico.factorial(3); PRINT a b) EXECUTE :a := matematico.dist_puntos(0,0,1,1); PRINT a c) EXECUTE :a := matematico.factorial(-5); Error d) EXECUTE matematico.error('Mensaje'); Error, el subprograma es privado  Imprime 6  Imprime 1.4142…

14. Laboratorio de Bases de Datos Ahora se invocará desde un SELECT a la función de distancia entre puntos: Sea la tabla: CREATE TABLE punto( coordx NUMBER(10), coordy NUMBER(10), PRIMARY KEY(coordx, coordy) ); INSERT INTO punto VALUES(0,0); INSERT INTO punto VALUES(1,1); INSERT INTO punto VALUES(0,4);

15. Laboratorio de Bases de Datos SELECT 'La distancia del origen a (' || coordx || ',' || coordy || ')' || ' es ' || matematico.dist_puntos(0,0,coordx,coordy) AS distancia FROM punto; ¿Qué hace la siguiente consulta? SELECT p1.coordx p1x,p1.coordy p1y, p2.coordx p2x,p2.coordy p2y, matematico.dist_puntos(p1.coordx,p1.coordy, p2.coordx,p2.coordy) AS distancia FROM punto p1, punto p2 ORDER BY 1,2,3,4; Nota: En versiones anteriores a 9i para poder invocar funciones empaquetadas se requiere especificar el grado de “pureza” de la función mediante PRAGMA RESTRICT_REFERENCES.

16. Laboratorio de Bases de Datos Sobrecarga de Subprogramas • Es posible tener dentro de un paquete un subprograma declarado varias veces con el mismo nombre pero con diferentes tipos de parámetros • No puede haber sobrecarga si los parámetros sólo varían en el modo (IN, OUT)‏ • No puede haber sobrecarga si ésta se basa exclusivamente en el tipo de retorno de la función • Debe haber diferencia entre la familia de los tipos, ejemplo CHAR vs. VARCHAR se consideran iguales

17. Laboratorio de Bases de Datos No puede haber sobrecarga entre: PROCEDURE xxx(b CHAR)‏ PROCEDURE yyy(b OUTNUMBER)‏ FUNCTION zzz(b IN NUMBER)‏ RETURN VARCHAR PROCEDURE xxx(a VARCHAR)‏ PROCEDURE yyy(a IN NUMBER)‏ FUNCTION zzz(a IN NUMBER)‏ RETURNNUMBER

18. Ejemplo CREATE OR REPLACE PACKAGE insercion IS PROCEDURE insertar(n NUMBER); PROCEDURE insertar(cad VARCHAR); END; / Sean las tablas: CREATE TABLE numero(num NUMBER(10)); CREATE TABLE mensaje(texto VARCHAR2(10));

19. Laboratorio de Bases de Datos CREATE OR REPLACE PACKAGE BODY insercion IS PROCEDURE insertar(n NUMBER) IS BEGIN INSERT INTO numero VALUES(n); END; PROCEDURE insertar(cad VARCHAR) IS BEGIN INSERT INTO mensaje VALUES(cad); END; END; / Para Ejecutar: SQL> EXECUTE insercion.insertar('Hola'); SQL> EXECUTE insercion.insertar(8); Invocación

20. Laboratorio de Bases de Datos Variables en los Paquetes • Las variables en los paquetes son persistentes (durante la sesión)‏ • Las variables declaradas en la especificaciónson públicas, es decir, pueden ser accesadas y modificadas directamente desde otros subprogramas o bloques anónimos • Las variables declaradas en el cuerpo son privadas, sólo pueden ser accesadas dentro de éste. Si desean “verse” desde afuera del cuerpo se debe crear un subprograma (público) a través del cual se accesen (cf. encapsulamiento)‏

21. Laboratorio de Bases de Datos Ejemplo CREATE OR REPLACE PACKAGE vbles IS nro_veces NUMBER(3) := 0; -- Vble pública PROCEDURE incrementa_veces; -- Ver Nota PROCEDURE muestra_veces; -- Ver Nota PROCEDURE muestra_impresiones; -- Requerido END; / Nota: Estos procedimientos no son estrictamente necesarios aunque recomendables ¿por qué? Subprogramas públicos

22. Laboratorio de Bases de Datos CREATE OR REPLACE PACKAGE BODY vbles IS nro_impresiones NUMBER(3) :=0; --Variable privada PROCEDURE incrementa_veces IS BEGIN nro_veces := nro_veces + 1; END; PROCEDURE muestra_veces IS BEGIN DBMS_OUTPUT.PUT_LINE(nro_veces); nro_impresiones := nro_impresiones + 1; END; PROCEDURE muestra_impresiones IS BEGIN DBMS_OUTPUT.PUT_LINE(nro_impresiones); END; END; /

23. Laboratorio de Bases de Datos Para ejecutar: • EXECUTE vbles.muestra_veces; • EXECUTE vbles.muestra_impresiones; • EXECUTE vbles.incrementa_veces; • EXECUTE vbles.muestra_veces; • EXECUTE vbles.muestra_veces; • EXECUTE vbles.muestra_impresiones;  0  1  1 De nuevo:  1  3

24. Laboratorio de Bases de Datos Directamente se pueden accesar las variables públicas pero NO las privadas por ejemplo: BEGIN vbles.nro_veces := vbles.nro_veces +1; END; / BEGIN DBMS_OUTPUT.PUT_LINE(vbles.nro_veces); END; / BEGIN DBMS_OUTPUT.PUT_LINE(vbles.nro_impresiones); END; / Genera error porque nro_impresionesNO es pública