Download - PL/SQL*

Transcript

25/11/07 Laboratorio de Bases de Datos

PL/SQL*PL/SQL*

*Tomado del curso de Francisco Moreno*Tomado del curso de Francisco Moreno

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

25/11/07 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;

25/11/07 Laboratorio de Bases de Datos

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

Recursividad

25/11/07 Laboratorio de Bases de Datos

Solución recursiva

CREATE OR REPLACE FUNCTION factorialr(n NATURAL) RETURN NUMBER ISBEGIN IF n=0 THEN RETURN 1; ELSE RETURN n*factorialr(n-1); END IF;END;/

Rango: 0,1,…2147’

25/11/07 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;/

25/11/07 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 factFROM numero;

Incluso se puede hacer algo como:

SELECT 'El factorial de ' ||factorialr(num) || ' es '|| factorialnr(factorialr(num)) AS factFROM numero;

25/11/07 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

25/11/07 Laboratorio de Bases de Datos

CREATE PACKAGE nom_paquete IS -- Variables públicas -- Declaración de subprogramasEND;/

CREATE PACKAGE BODYBODY nom_paquete IS -- Variables privadas /* Implementación de subprogramas privados */ /* Implementación de subprogramas declarados en la especificación */END;/

Sintaxis

EESSPPEECC IIFF IICCAACC IIÓÓNN

CCUUEERRPPOO Los subprogramas Los subprogramas

privados privados deben deben implementarse implementarseantes que los otrosantes que los otros

25/11/07 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;/

25/11/07 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;

Continúa

Subprogramas privadosSubprogramas privados

25/11/07 Laboratorio de Bases de Datos

Cont. ImplementaciónFUNCTION 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úblicosSubprogramas Públicos

25/11/07 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 ac) EXECUTE :a := matematico.factorial(-5);Errord) EXECUTE matematico.error('Mensaje');Error, el subprograma es privado

Imprime 6

Imprime 1.4142…

25/11/07 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);

25/11/07 Laboratorio de Bases de Datos

SELECT 'La distancia del origen a (' || coordx || ',' || coordy || ')' || ' es ' || matematico.dist_puntos(0,0,coordx,coordy) AS distanciaFROM 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 distanciaFROM punto p1, punto p2ORDER 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.

25/11/07 Laboratorio de Bases de Datos

Sobrecarga de Subprogramas

Es posible tener dentro de un paquetedentro 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

25/11/07 Laboratorio de Bases de Datos

No puede haber sobrecarga entre:

PROCEDURE xxx(a VARCHAR)

PROCEDURE yyy(a IN NUMBER)

FUNCTION zzz(a IN NUMBER)

RETURN NUMBER

PROCEDURE xxx(b CHAR)

PROCEDURE yyy(b OUT NUMBER)

FUNCTION zzz(b IN NUMBER) RETURN VARCHAR

25/11/07

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

25/11/07 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ónInvocación

25/11/07 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ón son

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)

25/11/07 Laboratorio de Bases de Datos

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é?

Ejemplo

Subprogramas públicos

25/11/07 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;/

25/11/07 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;

De nuevo: