PL/SQLPL/SQLFrancisco Moreno

Universidad Nacional

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] FUNCTIONFUNCTION nombre_función[(arg1 [modo] tipo [,arg2 [modo] tipo...])]RETURN tipo_de_datoRETURN tipo_de_datoISBloque de PL/SQL

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);

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;/

SELECT cod, sumaant(cod) AS sFROM emp;

--Aunque también se puede solucionar sin PL/SQL:

SELECT cod, (SELECT NVL(SUM(sal),0) FROM emp WHERE cod < e.cod) sFROM emp e;

¿Cuál solución es más rápida?

RecursividadCREATE OR REPLACE FUNCTION sumadigit(n IN NUMBER) RETURN NUMBER ISsuma NUMBER(3); tamano NUMBER(38);BEGIN suma := SUBSTR(n,1,1); tamano := LENGTH(n); IF tamano > 1 THEN suma := suma + sumadigit(SUBSTR(n,2)); END IF; RETURN suma; EXCEPTION WHEN OTHERS THEN RETURN 0;END;/

Límite: 38 dígitos (NUMBER)Ejercicio: pulir para decimales y negativos.

Casting implícito a caracteres

SUBSTR(cad, pos. ini, [nro. car. a extraer])

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 suminFROM numero;

Otro ejemplo:

• Elaborar una función llamada crea_sub que 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 ¡un programa que crea otros programas!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.

CREATE OR REPLACE …

END;

El usuario ingresa, por ejemplo, en un campo de texto el código del programa que desea crear

Se le envía la cadena de texto con el código fuente a la función crea_sub

crea_sub recibe el código fuente y crea el programa que el usuario desea

CREATE OR REPLACE FUNCTION crea_sub(codigo_fuente IN VARCHAR) RETURN VARCHAR ISBEGIN EXECUTE IMMEDIATE codigo_fuente; RETURN 'Creación exitosa';EXCEPTIONWHEN OTHERS THEN RETURN 'Error fatal: ' || SQLERRM; --Hubo

erroresEND;/

Ejemplo de invocación:

BEGINDBMS_OUTPUT.PUT_LINE(crea_sub('CREAT

E OR REPLACE FUNCTION hoy RETURN DATE IS BEGINRETURN 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.

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

CREATE PACKAGE nom_paquete IS -- Variables públicas -- Declaración de subprogramas

(públicos)END;/

CREATE PACKAGE BODYBODY nom_paquete IS

-- Variables privadas /* Implementación de subprogramas privados */ /* Implementación de subprogramas declarados en la especificación */END;/

SintaxisEESSPPEECCFFIICCAACCIIÓÓNN

CCUUEERRPPOO

Los subprogramas Los subprogramas privados se privados se debendeben implementar implementarantes que los públicosantes que los públicos

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/

Ejemplo: CuerpoCREATE OR REPLACE PACKAGE BODY

mat ISPROCEDURE mulvec(vec IN OUT t_num) ISi NUMBER := vec.FIRST;BEGIN WHILE i IS NOT NULL LOOP vec(i) := vec(i) * i; i := vec.NEXT(i); END LOOP;END; Continúa

Cont. cuerpoFUNCTION cadvalores(vec IN t_num) RETURN VARCHAR ISk NUMBER; cad VARCHAR(2000);BEGINk := 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/

DECLAREmi_vec mat.t_num;BEGINmi_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;/