/* Sesión12 – PaquetesEstudiante: José Luis Toro AlcarrazCurso: Base de Datos Avanzado IICorreo:i201010865@cibertec.edu.pe*/

Objetivos de la sesión.

Usar paquetes predefinidos por Oracle Empaquetar funciones y procedimientos en paquetes Invocar a un subprograma que reside en un paquete

1) Definición de paquete2) Especificación y Cuerpo de un paquete3) Ámbito para los paquetes.4) Sobrecarga de los subprogramas de un paquete5) Paquetes en Oracle

1) Definición de paquete

Es una estructura de PL/SQL que permite almacenar de manera agrupada varios objetos relacionados.

Un paquete consta de dos partes, la especificación y el cuerpo. Cada una de ellas se almacena independientemente en el diccionario de datos.

Un paquete no puede ser local, debe estar almacenado en la base de datos. Presentan una serie de ventajas de rendimiento.

2) Especificación y Cuerpo de un paquete

a) Especificación de un paquete

La especificación del paquete, también conocida como cabecera del paquete, incluye información acerca del contenido del paquete.

No contiene código ninguno de los procedimientos. Los elementos dentro del paquete (especificaciones de procedimientos y funciones,

variables, etc.) son idénticos a cómo serían si estuviesen en la sección declarativa de un bloque anónimo.

Las reglas sintácticas para la cabecera de un paquete son las mismas que para la sección declarativa de un bloque PL/SQL, excepto en el caso de declaraciones de procedimientos y funciones.

Los elementos del paquete pueden aparecer en cualquier orden. Sin embrago, como en una sección declarativa, un objeto debe declararse antes de hacer referencia al mismo.

No es necesario que aparezcan todos los tipos de elementos. Cualquier declaración de procedimientos y funciones debe ser formal. Un declaración

formal describe el subprograma y sus argumentos pero no incluye el código. Este se incluirá en el cuerpo del paquete.

SINTAXIS:CREATE [OR REPLACE] PACKAGE Nombre_paquete {IS|AS}

Especificación_procedimiento |Especificación_función |Especificación_variable |Definición_tipo |Declaración_excepción |Declaración_cursor

END [nombre_paquete];

Ejemplo:

CREATE OR REPLACE PACKAGE PQ_DEPTAS

-- Procedimiento que inserta un nuevo departamentoPROCEDURE SP_INSERTA_DEPT( P_NOMBRE IN DEPT.DNAME%TYPE,

P_LOCAL IN DEPT.LOC%TYPE, P_CODIGO OUT DEPT.DEPTNO%TYPE );

-- Procedimiento que elimina un departamentoPROCEDURE SP_ELIMINA_DEPT( P_CODIGO IN DEPT.DEPTNO%TYPE);-- Función que muestra el número de empleados por departamentoFUNCTION SF_NUM_EMP( P_CODIGO IN DEPT.DEPTNO%TYPE)

RETURN NUMBER;-- ExcepcionesE_DEPTNOREGISTRADO EXCEPTION;

END PQ_DEPT;/Paquete creado.

b) Cuerpo de un paquete

El cuerpo de un paquete es un objeto independiente de la especificación del paquete en el diccionario de datos.

El cuerpo del paquete no puede compilarse hasta que la especificación del paquete se haya compilado correctamente.

Contiene el código correspondiente a las declaraciones formales de los subprogramas que aparecen en la especificación del paquete.

Ejemplo:

-- Creamos una sequenciaCREATE SEQUENCE SEQ_DEPTSTART WITH 50INCREMENT BY 10;

Secuencia creada.

-- Creamos el cuerpo del paqueteCREATE OR REPLACE PACKAGE BODY PQ_DEPT AS

-- Procedimiento que inserta un nuevo departamento PROCEDURE SP_INSERTA_DEPT( P_NOMBRE IN DEPT.DNAME%TYPE, P_LOCAL IN DEPT.LOC%TYPE, P_CODIGO OUT DEPT.DEPTNO%TYPE) IS BEGIN SELECT SEQ_DEPT.NEXTVAL INTO P_CODIGO FROM DUAL;

INSERT INTO DEPT VALUES (P_CODIGO, P_NOMBRE, P_LOCAL);

COMMIT; END SP_INSERTA_DEPT; -- Procedimiento que elimina un departamento PROCEDURE SP_ELIMINA_DEPT(P_CODIGO IN DEPT.DEPTNO%TYPE) IS BEGIN DELETE FROM DEPT

WHERE DEPTNO = P_CODIGO; COMMIT; END SP_ELIMINA_DEPT; -- Función que muestra el número de empleados por departamento FUNCTION SF_NUM_EMP(P_CODIGO IN DEPT.DEPTNO%TYPE) RETURN NUMBER IS V_RESUL NUMBER(4); BEGIN SELECT COUNT(1) INTO V_RESUL

FROM EMP WHERE DEPTNO = P_CODIGO;

RETURN V_RESUL; END SF_NUM_EMP;END PQ_DEPT;/

c) Llamada a un paquete

DECLAREV_NUMEMP NUMBER(4);V_CODIGO DEPT.DEPTNO%TYPE;

BEGIN-- Procedimiento que inserta un nuevo departamentoPQ_DEPT.SP_INSERTA_DEPT('MANTENIMIENTO','LIMA',V_CODIGO);DBMS_OUTPUT.PUT_LINE('Nuevo departamento: ' || V_CODIGO);-- Función que muestra el número de empleados por departamentoV_NUMEMP := PQ_DEPT.SF_NUM_EMP(20);DBMS_OUTPUT.PUT_LINE('Departamento 20 tiene ' || V_NUMEMP || ' empleados');

END;/Insertamos el nuevo registro.Nuevo departamento: 50Departamento 20 tiene 0 empleados

Procedimiento PL/SQL terminado correctamente.

3) Ámbito para los paquetes.

Cualquier objeto que se declare en la cabecera del paquete se encuentra dentro de ámbito y es visible desde fuera del paquete siempre que el objeto se distinga precediéndolo con el nombre del paquete donde se encuentra.

La llamada al procedimiento es la misma que sería en el caso de un procedimiento independiente. La única diferencia es el uso del nombre del paquete como prefijo.

Cuando un objeto solo es declarado dentro del cuerpo del paquete, su ámbito es el propio cuerpo del paquete y puede llamarse desde otros procedimientos que se encuentren en el cuerpo, pero no puede verse fuera del mismo.

Ejemplo:

-- Especificación nuevo paqueteCREATE OR REPLACE PACKAGE PQ_DEPT_2AS

-- Funcion1FUNCTION SF_DESC_LOCAL(P_CODIGO IN DEPT.DEPTNO%TYPE) RETURN VARCHAR2;

END PQ_DEPT_2;/Paquete creado.

-- Cuerpo del paquete con una función localCREATE OR REPLACE PACKAGE BODY PQ_DEPT_2 AS -- Funcion2 FUNCTION SF_FORMATEA(P_NOMBRE IN DEPT.DNAME%TYPE, P_LOCAL IN DEPT.LOC%TYPE)RETURN VARCHAR2 IS BEGIN RETURN 'El departamento ' || P_NOMBRE || ' se encuentra en el local ' || P_LOCAL; END SF_FORMATEA; -- Funcion1 FUNCTION SF_DESC_LOCAL(P_CODIGO IN DEPT.DEPTNO%TYPE) RETURN VARCHAR2 IS V_NOMBRE DEPT.DNAME%TYPE; V_LOCAL DEPT.LOC%TYPE;

BEGIN SELECT DNAME,LOC INTO V_NOMBRE, V_LOCAL FROM DEPT WHERE DEPTNO = P_CODIGO; RETURN SF_FORMATEA(V_NOMBRE, V_LOCAL); END SF_DESC_LOCAL;END PQ_DEPT_2;/Cuerpo del paquete creado.

-- Llamada a las funciones de los paquetesDECLARE

V_RESUL VARCHAR2(500);V_NOMBRE DEPT.DNAME%TYPE;V_LOCAL DEPT.LOC%TYPE;

BEGIN-- Función 1V_RESUL := PQ_DEPT_2.SF_DESC_LOCAL(30);DBMS_OUTPUT.PUT_LINE(V_RESUL);-- Función 2SELECT DNAME,LOC INTO V_NOMBRE,V_LOCALFROM DEPT WHERE DEPTNO = 30;V_RESUL := PQ_DEPT.SF_FORMATEA(V_NOMBRE,V_LOCAL);

DBMS_OUTPUT.PUT_LINE(V_RESUL);END;/V_RESUL := PQ_DEPT.SF_FORMATEA(V_NOMBRE,V_LOCAL); *ERROR en lÝnea 12:ORA-06550: lÝnea 12, columna 20:PLS-00302: el componente 'SF_FORMATEA' se debe declararORA-06550: lÝnea 12, columna 1:PL/SQL: Statement ignored

4) Sobrecarga de los subprogramas de un paquete

Los procedimientos y funciones dentro de un paquete pueden sobrecargarse, lo que significa que puede haber más de un procedimiento o función con el mismo nombre, pero con diferentes parámetros.

No pueden sobrecargarse dos subprogramas si sus parámetros difieren únicamente en el nombre o en el modo.

No pueden sobrecargarse dos funciones que únicamente difieren en tipo de dato que se devuelve.

Para que una función pueda sobrecargarse los tipos de los parámetros deben pertenecer a familias diferentes.

Ejemplo:

-- Especificación nuevo paqueteCREATE OR REPLACE PACKAGE PQ_EJEMPLO AS

-- Función1FUNCTION SF_SUMA(P_1 IN NUMBER, P_2 IN NUMBER) RETURN NUMBER;-- Función2FUNCTION SF_SUMA(P_1 IN NUMBER,P_2 IN NUMBER,P_3 IN NUMBER) RETURN NUMBER;

END PQ_EJEMPLO;/

Paquete creado.

-- Cuerpo del paquete con sobre cargaCREATE OR REPLACE PACKAGE BODY PQ_EJEMPLO AS

-- Función1FUNCTION SF_SUMA(P_1 IN NUMBER, P_2 IN NUMBER)RETURN NUMBERISBEGIN

RETURN P_1 + P_2;END SF_SUMA;-- Función2FUNCTION SF_SUMA(P_1 IN NUMBER, P_2 IN NUMBER, P_3 IN NUMBER ) RETURN NUMBERIS

BEGIN RETURN P_1 + P_2 + P_3; END SF_SUMA;END PQ_EJEMPLO; /

Cuerpo del paquete creado.

-- Llamada al subpogramaDECLARE

V_RESULTADO NUMBER;BEGIN

V_RESULTADO :=PQ_EJEMPLO .SF_SUMA(10,200);DBMS_OUTPUT.PUT_LINE(‘Resultado1: ’ || V_RESULTADO);V_RESULTADO :=PQ_EJEMPLO .SF_SUMA(10,200,100);DBMS_OUTPUT.PUT_LINE(‘Resultado2: ’ || V_RESULTADO);

END;/Resultado1: 210Resultado2: 310Procedimiento PL/SQL terminado correctamente.

5) Paquetes en Oracle

El motor de Base de Datos Oracle contiene un conjunto variado de paquetes preestablecidos que pueden ser usados para ejecutar diversas tareas. Entre los principales tenemos :

- DBMS_OUTPUT- UTL_FILE - DBMS_SQL - DBMS_APPLICATION_INFO

Listado de Paquetes en la base de datosSELECT object_name FROM dba_objects WHERE object_type=’PACKAGE’;

Contenido de un paqueteDESC nombre_paquete

Problema1: Implementar un paquete PQ_VENTAS que tenga los siguientes elementos:

- Función que reciba como parámetro el código de un empleado y devuelva el total de ventas realizado por ese empleado.

- Procedimiento que imprima en pantalla el ranking de ventas de empleados.

-- Cabecera del triggerCREATE OR REPLACE PACKAGE PQ_VENTAS AS

-- Función que retorna el total de ventas por empleadosFUNCTION SF_TOT_VENTAS( V_EMPNO IN EMP.EMPNO%TYPE)

RETURN NUMBER;-- Procedimiento que devuelve el ranking de ventasPROCEDURE SP_RANKING_VENTAS;E_SINVENTAS EXCEPTION;

END PQ_VENTAS;/Paquete creado.-- Cuerpo del triggerCREATE OR REPLACE PACKAGE BODY PQ_VENTAS AS

-- Función que retorna el total de ventas por empleadosFUNCTION SF_TOT_VENTAS(V_EMPNO IN EMP.EMPNO%TYPE)RETURN NUMBER ISV_RESUL NUMBER(10,2);BEGIN

SELECT SUM(TOTAL) INTO V_RESUL FROM ORD

WHERE EMPNO=V_EMPNO;RETURN V_RESUL;

EXCEPTIONWHEN OTHERS THENDBMS_OUTPUT.PUT_LINE('Error inesperado.' || SQLERRM);RETURN –1;

END SF_TOT_VENTAS; -- procedimiento que devuelve el ranking de ventasPROCEDURE SP_RANKING_VENTAS

CURSOR CUR_RANKING IS SELECT EMPNO,ENAME,SF_TOT_VENTAS(EMPNO) FROM EMP;REG_RANKING CUR_RANKING%ROWTYPE;V_POSICION NUMBER(4,2):=1;

BEGINDBMS_OUTPUT.PUT_LINE('ranking de empleados por venta');DBMS_OUTPUT.PUT_LINE(RPAD('*',100,'*'));

OPEN CUR_RANKING;LOOP

FETCH CUR_RANKING INTO REG_RANKING;EXIT WHEN CUR_RANKING%NOTFOUND;DBMS_OUTPUT.PUT_LINE(RPAD(V_POSICION,10) ||

RPAD(REG_RANKING.EMPNO,10) || RPAD(REG_RANKING.ENAME,20) || RPAD(REG_RANKING.TOTAL,10));

END LOOP;CLOSE CUR_RANKING;

EXCEPTIONWHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('EROR DESCONOSIDO');END SP_RANKING_VENTAS;

END PQ_VENTAS;/ Cuerpo del paquete creado.