PL/SQL Francisco Moreno Universidad Nacional

1. PL/SQL Francisco Moreno Universidad Nacional

2. Funciones • Si un procedimiento tiene solo un parámetro de salida, se puede remplazar por una función y esta se puede involucrar directamente en expresiones y en consultas SQL. • Sintaxis: CREATE [OR REPLACE]FUNCTION nombre_función [(arg1 [modo] tipo [,arg2 [modo] tipo...])] RETURN tipo_de_dato IS Bloque de PL/SQL

3. Ejemplo 1 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. Ejemplo 2 DROP TABLE emp; CREATE TABLE emp( cod NUMBER(8) PRIMARY KEY, nom VARCHAR2(15), sal NUMBER(8)); INSERT INTO emp VALUES(12,'María',100); INSERT INTO emp VALUES(15,'Ana',500); INSERT INTO emp VALUES(76,'Lisa',150); INSERT INTO emp VALUES(33,'Cheryl',100);

5. CREATE OR REPLACE FUNCTION sumaant(code IN NUMBER) RETURN NUMBER IS suma NUMBER(8); BEGIN SELECT NVL(SUM(sal),0) INTO suma FROM emp WHERE cod < code; RETURN suma; END; /

6. SELECT cod, sumaant(cod) AS s FROM emp; --Aunque también se puede solucionar sin PL/SQL: SELECT cod, (SELECT NVL(SUM(sal),0) FROM emp WHERE cod < e.cod) s FROM emp e; ¿Cuál solución es más rápida?

7. Recursividad CREATE OR REPLACE FUNCTION sumadigit(n IN NUMBER) RETURN NUMBER IS suma NUMBER(3); tamano NUMBER(38); BEGIN suma := SUBSTR(n,1,1); tamano := LENGTH(n); IF tamano > 1 THEN suma := suma + sumadigit(SUBSTR(n,2,tamano)); END IF; RETURN suma; EXCEPTION WHEN OTHERS THEN RETURN 0; END; / Límite: 38 dígitos (NUMBER) Ejercicio: pulir para decimales y negativos.

8. Invocación de la función desde una consulta: CREATE TABLE numero( num NUMBER(30) PRIMARY KEY); INSERT INTO numero VALUES(1); INSERT INTO numero VALUES(111); INSERT INTO numero VALUES(123456789); SELECT num, sumadigit(num) AS sumin FROM numero;

9. Otro ejemplo: • Elaborar una función llamada crea_subque recibe como parámetro el código fuente de un subprograma (en una cadena de caracteres) y lo crea (o lo remplaza si ya existía). Es decir, ¡un programa que crea otros programas! • La función retorna “Creación exitosa”si no hubo problemas en la creación del subprograma o el mensaje de error de lo contrario.

10. El usuario ingresa en un campo de texto el código del programa que desea crear CREATE OR REPLACE … END; Se le envía la cadena de texto con el código fuente a la función crea_sub crea_subrecibe el código fuente y crea el programa que el usuario desea

11. CREATE OR REPLACE FUNCTION crea_sub(codigo_fuente IN VARCHAR) RETURN VARCHAR IS BEGIN EXECUTE IMMEDIATE codigo_fuente; RETURN 'Creación exitosa'; EXCEPTION WHEN OTHERS THEN RETURN 'Error mortal: ' || SQLERRM; --Hubo errores END; /

12. Ejemplo de invocación: BEGIN DBMS_OUTPUT.PUT_LINE(crea_sub('CREATE OR REPLACE FUNCTION hoy RETURN DATE IS BEGIN RETURN SYSDATE; END;')); END; / El código en verde sería el que el usuario ingresaría a través de una interfaz en un campo de texto. Más adelante en el curso se verá como hacerlo, por ejemplo, desde Java.

13. Paquetes • Un paquete es una agrupación de funciones, procedimientos y variables. • Clasifican los subprogramas de acuerdo con una categoría elegida por el programador o diseñador • Un paquete se compone de dos partes: • Especificación Definiciones de variables “públicas” y prototipos de los subprogramas • Cuerpo (BODY) Implementación de los subprogramas declarados en la especificación más subprogramas y variables privadas

14. Sintaxis E S P E C F I C A C I Ó N CREATE PACKAGEnom_paquete IS -- Variables públicas -- Declaración de subprogramas (públicos) END; / CREATE PACKAGE BODYnom_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 se deben implementar antes que los públicos

15. Ejemplo: Especificación CREATE OR REPLACE PACKAGE mat IS TYPE t_num IS TABLE OF NUMBER(3) INDEX BY BINARY_INTEGER; PROCEDURE mulvec(vec IN OUT t_num); FUNCTION cadvalores(vec IN t_num) RETURN VARCHAR; END; --Fin de la especificación /

16. Ejemplo: Cuerpo CREATE OR REPLACE PACKAGE BODY mat IS PROCEDURE mulvec(vec IN OUT t_num) IS i NUMBER := vec.FIRST; BEGIN WHILE i IS NOT NULL LOOP vec(i) := vec(i) * i; i := vec.NEXT(i); END LOOP; END; Continúa

17. Cont. cuerpo FUNCTION cadvalores(vec IN t_num) RETURN VARCHAR IS k NUMBER; cad VARCHAR(2000); BEGIN k := vec.FIRST; WHILE k IS NOT NULL LOOP cad := cad || ' ' || vec(k); k := vec.NEXT(k); END LOOP; RETURN cad; END; END; --Fin del cuerpo del paquete /

18. DECLARE mi_vec mat.t_num; k NUMBER; BEGIN mi_vec(1):= 5; mi_vec(2):= 10; mi_vec(3):= 80; DBMS_OUTPUT.PUT_LINE(mat.cadvalores(mi_vec)); mat.mulvec(mi_vec); DBMS_OUTPUT.PUT_LINE(mat.cadvalores(mi_vec)); END; /