Objetivos:• Conocer que es un Paquete.
• Ver las ventajas de su uso.
• Crear la especificación y el cuerpo de un Paquete.
• Las Variables y Subprogramas Privados de un Paquete.
• Ver ejemplos de algunos Paquetes.
NOTA: Usamos como ejemplo la Base de Datos: ORCL, la cual viene por defecto en cualquier versión de ORACLE.
____________________________________________________________________________________
Los Paquetes de PL/SQL .
Los Paquetes de PL/SQL .
Como su nombre infiere, es una envoltura que agrupa un conjunto de objectos relacionados hasta cierto punto. Enfocado en PL/SQL un paquete puede ser definido como:
Un objeto de esquema que agrupa objectos relacionados(PL/SQL types, variables, y subprogramas) de forma lógica. Por lo general los Paquetes contienen dos partes, una especificación y un cuerpo, aunque a veces el cuerpo es innecesario. La especificación es donde se define la interfaz de tus aplicaciones; en la cual se declaran los tipos(types), variables, constantes, excepciones, cursores y subprogramas. En el cuerpo es donde se le da uso a los objectos antes declarados, ademas de ser donde se desarrolla/implementa la lógica completa del paquete.[1]
Sintaxis:
--Especificación
CREATE [ OR REPLACE ] PACKAGE [ schema. ]package
[ invoker_rights_clause ]
{ IS | AS } pl/sql_package_spec;
--Cuerpo
CREATE [ OR REPLACE ] PACKAGE BODY [ schema. ]package
{ IS | AS } pl/sql_package_body;
____________________________________________________________________________________
Ventajas de los Paquetes.
Modularidad: Los Paquetes permiten encapsular tipos(types), objectos y subprogramas relacionados lógicamente en un módulo de PL/SQL. Esto los hace fácil de entender, con interfaces simples, claras y bien definidas, para así facilitar el desarrollo de aplicaciones.
Fácil diseño de aplicaciones: Al diseñar una aplicación, todo lo que necesita inicialmente es la información de interfaz en la especificación del paquete. Puede codificar y compilar una especificación sin su cuerpo. Luego, puede compilar los subprogramas independientes(standalone) que hacen referencia el paquete. No es necesario definir el cuerpo del paquete completo hasta que esté listo para completar la aplicación.
Ocultación de información: Con los paquetes, puede especificar cuales objectos son públicos (visibles y accesibles) y cuales son privados (ocultos e inaccesible). Por ejemplo, si un paquete contiene cuatro subprogramas, tres podrían ser públicos y uno privado. El paquete oculta la implementación del subprograma privado para que sólo el paquete (no su aplicación) se vea afectada si la implementación cambia. Esto simplifica el mantenimiento y los cambios para mejora. Además, al ocultar los detalles de implementación de los usuarios, se protege la integridad del paquete.
Funcionalidad Añadida: Los cursores y variables públicas del paquete persisten durante la duración de una sesión. Por lo tanto, pueden ser compartidas por todos los subprogramas que se ejecutan en el ambiente. Además, le permiten mantener datos a través de transacciones sin tener que almacenarla en la Base de Datos.
Mejor desempeño: Cuando por primera vez se llama un subprograma de un paquete, todo el paquete se carga en memoria. Por lo tanto, las llamadas posteriores a subprogramas relacionados en el paquete no requieren I/O de disco.
Además, los paquetes impiden las dependencias en cascada y la recompilación innecesaria. Por ejemplo, si cambia el cuerpo de una función en un paquete, Oracle no recompila otros subprogramas que invocan la función, Esto porque dichos subprogramas sólo dependen de los parámetros y valor de retorno declarados en la especificación.
____________________________________________________________________________________
Especificación y Cuerpo del Paquete.
Especificación: Como ya especificamos, en la Especificación se declaran los objectos públicos. El alcance de un objecto público es el esquema del paquete. Un objecto público es visible en todas partes del esquema. Para hacer referencia a un objecto público que está en su alcance pero no es visible, debe preceder el nombre del objecto con el nombre del paquete. Eje: package_name.object_name.
La Especificación enumera los recursos del paquetes disponibles para la aplicación. Toda la información o recursos que su aplicación necesita utilizar se encuentran en la Especificación.
Cuerpo: es donde se implementa la Especificación del Paquete. Es decir, el Cuerpo del Paquete contiene la implementación de cada cursor y subprograma declarado en la Especificación del Paquete. Tenga en cuenta que los subprogramas definidos en el Cuerpo del Paquete son accesibles fuera del mismo sólo si sus características/especificación también aparecen en la Especificación del Paquete.
El Cuerpo del Paquete también puede contener declaraciones privadas, que definen tipos y objetos necesarios para el funcionamiento interno del paquete. El alcance de estas declaraciones es local en el cuerpo del paquete. Por lo tanto, los objectos declarados son inaccesibles, excepto desde el interior del Cuerpo del Paquete. A diferencia de la Especificación, el Cuerpo del Paquete puede contener la implementecion completa de los subprogramas.
Después de la parte declarativa del Cuerpo del Paquete está la parte de inicialización opcional, que típicamente contiene declaraciones que inicializan algunas de las variables previamente declaradas en el paquete.
La parte de inicialización de un paquete juega un papel menor, ya que, a diferencia de los subprogramas, un paquete no se puede llamar o pasar parámetros. Como resultado, la parte de inicialización de un paquete se ejecuta sólo una vez, la primera vez que se hace referencia al paquete.
Recuerde, si la Especificación de un paquete sólo declara tipos(types), constantes, variables y excepciones, el Cuerpo del Paquete es innecesario. Sin embargo, el Cuerpo todavía se puede utilizar para inicializar objectos declarados en la Especificación del Paquete.
____________________________________________________________________________________
Ejemplos Prácticos.
Un objeto de esquema que agrupa objectos relacionados(PL/SQL types, variables, y subprogramas) de forma lógica. Por lo general los Paquetes contienen dos partes, una especificación y un cuerpo, aunque a veces el cuerpo es innecesario. La especificación es donde se define la interfaz de tus aplicaciones; en la cual se declaran los tipos(types), variables, constantes, excepciones, cursores y subprogramas. En el cuerpo es donde se le da uso a los objectos antes declarados, ademas de ser donde se desarrolla/implementa la lógica completa del paquete.[1]
Sintaxis:
--Especificación
CREATE [ OR REPLACE ] PACKAGE [ schema. ]package
[ invoker_rights_clause ]
{ IS | AS } pl/sql_package_spec;
--Cuerpo
CREATE [ OR REPLACE ] PACKAGE BODY [ schema. ]package
{ IS | AS } pl/sql_package_body;
____________________________________________________________________________________
Ventajas de los Paquetes.
Modularidad: Los Paquetes permiten encapsular tipos(types), objectos y subprogramas relacionados lógicamente en un módulo de PL/SQL. Esto los hace fácil de entender, con interfaces simples, claras y bien definidas, para así facilitar el desarrollo de aplicaciones.
Fácil diseño de aplicaciones: Al diseñar una aplicación, todo lo que necesita inicialmente es la información de interfaz en la especificación del paquete. Puede codificar y compilar una especificación sin su cuerpo. Luego, puede compilar los subprogramas independientes(standalone) que hacen referencia el paquete. No es necesario definir el cuerpo del paquete completo hasta que esté listo para completar la aplicación.
Ocultación de información: Con los paquetes, puede especificar cuales objectos son públicos (visibles y accesibles) y cuales son privados (ocultos e inaccesible). Por ejemplo, si un paquete contiene cuatro subprogramas, tres podrían ser públicos y uno privado. El paquete oculta la implementación del subprograma privado para que sólo el paquete (no su aplicación) se vea afectada si la implementación cambia. Esto simplifica el mantenimiento y los cambios para mejora. Además, al ocultar los detalles de implementación de los usuarios, se protege la integridad del paquete.
Funcionalidad Añadida: Los cursores y variables públicas del paquete persisten durante la duración de una sesión. Por lo tanto, pueden ser compartidas por todos los subprogramas que se ejecutan en el ambiente. Además, le permiten mantener datos a través de transacciones sin tener que almacenarla en la Base de Datos.
Mejor desempeño: Cuando por primera vez se llama un subprograma de un paquete, todo el paquete se carga en memoria. Por lo tanto, las llamadas posteriores a subprogramas relacionados en el paquete no requieren I/O de disco.
Además, los paquetes impiden las dependencias en cascada y la recompilación innecesaria. Por ejemplo, si cambia el cuerpo de una función en un paquete, Oracle no recompila otros subprogramas que invocan la función, Esto porque dichos subprogramas sólo dependen de los parámetros y valor de retorno declarados en la especificación.
____________________________________________________________________________________
Especificación y Cuerpo del Paquete.
Especificación: Como ya especificamos, en la Especificación se declaran los objectos públicos. El alcance de un objecto público es el esquema del paquete. Un objecto público es visible en todas partes del esquema. Para hacer referencia a un objecto público que está en su alcance pero no es visible, debe preceder el nombre del objecto con el nombre del paquete. Eje: package_name.object_name.
La Especificación enumera los recursos del paquetes disponibles para la aplicación. Toda la información o recursos que su aplicación necesita utilizar se encuentran en la Especificación.
Cuerpo: es donde se implementa la Especificación del Paquete. Es decir, el Cuerpo del Paquete contiene la implementación de cada cursor y subprograma declarado en la Especificación del Paquete. Tenga en cuenta que los subprogramas definidos en el Cuerpo del Paquete son accesibles fuera del mismo sólo si sus características/especificación también aparecen en la Especificación del Paquete.
El Cuerpo del Paquete también puede contener declaraciones privadas, que definen tipos y objetos necesarios para el funcionamiento interno del paquete. El alcance de estas declaraciones es local en el cuerpo del paquete. Por lo tanto, los objectos declarados son inaccesibles, excepto desde el interior del Cuerpo del Paquete. A diferencia de la Especificación, el Cuerpo del Paquete puede contener la implementecion completa de los subprogramas.
Después de la parte declarativa del Cuerpo del Paquete está la parte de inicialización opcional, que típicamente contiene declaraciones que inicializan algunas de las variables previamente declaradas en el paquete.
La parte de inicialización de un paquete juega un papel menor, ya que, a diferencia de los subprogramas, un paquete no se puede llamar o pasar parámetros. Como resultado, la parte de inicialización de un paquete se ejecuta sólo una vez, la primera vez que se hace referencia al paquete.
Recuerde, si la Especificación de un paquete sólo declara tipos(types), constantes, variables y excepciones, el Cuerpo del Paquete es innecesario. Sin embargo, el Cuerpo todavía se puede utilizar para inicializar objectos declarados en la Especificación del Paquete.
____________________________________________________________________________________
Ejemplos Prácticos.
CREATE OR REPLACE PACKAGE hr.pkg_empleado IS
--Sección de Variables y Tipos Records
TYPE typ_emp_details IS RECORD
(
nombre VARCHAR2(60),
tiempo_vig NUMBER(2), --Tiempo Vigente en la empresa
departamento_act hr.departments.department_name%TYPE,
empleo hr.jobs.job_title%TYPE,
direccion VARCHAR2(150)
);
TYPE typ_empleo_hist IS TABLE OF
hr.jobs.job_title%TYPE
INDEX BY pls_integer;
TYPE typ_dept_hist IS TABLE OF
hr.departments.department_name%TYPE
INDEX BY pls_integer;
PROCEDURE proc_emp_details
(
p_cod_emp IN hr.employees.employee_id%TYPE,
p_emp_details OUT typ_emp_details
);
FUNCTION func_emp_hist
(
p_cod_emp IN hr.employees.employee_id%TYPE
) RETURN typ_empleo_hist;
END pkg_empleado;
/*Este SCRIPT muestra como crear la Especificación de un Paquete de PL/SQL.*/
---
----Objetos Privados del Paquete
v_valid_emp NUMBER := 0;
CURSOR cur_valid_emp --Cursor para validar si el empleado existe.
(
p_emp hr.employees.employee_id%TYPE
) IS
SELECT 1
FROM hr.employees
WHERE employee_id = p_emp;
---Implementación de los procedimientos y funciones definidas en la Especificación del Paquete.
PROCEDURE proc_emp_details
(
p_cod_emp IN hr.employees.employee_id%TYPE,
p_emp_details OUT typ_emp_details
) IS
CURSOR cur_emp_details IS
SELECT
e.first_name||' '||e.last_name AS nombre,
TRUNC(MONTHS_BETWEEN(SYSDATE, e.hire_date)/12) AS tiempo,
d.department_name AS departamento,
j.job_title AS empleo,
NVL(l.street_address, 'No Especificado')||', '||l.city||', '||
NVL(l.state_province, ' ')||', '||c.country_name AS direccion
FROM hr.employees e, hr.departments d, hr.jobs j, hr.locations l, hr.countries c
WHERE e.employee_id = p_cod_emp
AND e.department_id = d.department_id
AND e.job_id = j.job_id
AND d.location_id = l.location_id
AND l.country_id = c.country_id;
BEGIN
OPEN cur_valid_emp(p_cod_emp);
FETCH cur_valid_emp INTO v_valid_emp;
CLOSE cur_valid_emp;
IF v_valid_emp < 1 THEN
RAISE_APPLICATION_ERROR(-20001, 'El Código de Empleado '||p_cod_emp||' no es valido. Favor Validar!!!');
END IF;
OPEN cur_emp_details;
FETCH cur_emp_details INTO p_emp_details;
CLOSE cur_emp_details;
END proc_emp_details;
FUNCTION func_emp_hist
(
p_cod_emp IN hr.employees.employee_id%TYPE
) RETURN typ_empleo_hist IS
rec_empleo_hist typ_empleo_hist;
v_count NUMBER(2) := 1;
CURSOR cur_emp_hist
(
p_emp hr.employees.employee_id%TYPE
) IS
SELECT
j.job_title AS empleo
FROM hr.job_history jh, hr.jobs j
WHERE employee_id = p_emp
AND j.job_id = jh.job_id
ORDER BY start_date ASC;
BEGIN
OPEN cur_valid_emp(p_cod_emp);
FETCH cur_valid_emp INTO v_valid_emp;
CLOSE cur_valid_emp;
IF v_valid_emp < 1 THEN
RAISE_APPLICATION_ERROR(-20002, 'El Código de Empleado '||p_cod_emp||' no es valido. Favor Validar!!!');
END IF;
FOR i IN cur_emp_hist(p_cod_emp) LOOP
rec_empleo_hist(v_count) := i.empleo;
v_count := v_count+1;
END LOOP;
RETURN rec_empleo_hist;
END func_emp_hist;
END pkg_empleado;
/
SHOW ERROR
/*Este SCRIPT muestra como crear El Cuerpo de un Paquete de PL/SQL.*/
---
• En el Cuerpo de Paquete: pkg_empleado notamos el desarrollo de los objetos definidos en la Especificación del mismo, ademas de algunos objectos definidos solo en el Cuerpo (Variable: v_valid_emp y Cursor: cur_valid_emp), los cuales son privados ya que solo es posible usarlos dentro del paquete.
SET SERVEROUTPUT ON
DECLARE
rec_emp_details hr.pkg_empleado.typ_emp_details;
BEGIN
hr.pkg_empleado.proc_emp_details
(
100,
rec_emp_details
);
DBMS_OUTPUT.PUT_LINE
(
'Empleado: '||rec_emp_details.nombre||CHR(10)||
'Años en la Empresa: '||rec_emp_details.tiempo_vig||CHR(10)||
'Departamento Actual: '||rec_emp_details.departamento_act||CHR(10)||
'Empleo Actual: '||rec_emp_details.empleo||CHR(10)||
'Dirección Actual: '||rec_emp_details.direccion
);
END;
/*Creamos este bloque anónimo para dar uso al paquete: pkg_empleado, notar como debemos crear una variable tipo Record: typ_emp_details el cual pertenece al mismo paquete y es del tipo de retorno del procedimiento: proc_emp_details; Mas adelante con PUT_LINE mostramos los datos extraídos.*/
---OUTPUT:
SET SERVEROUTPUT ON
DECLARE
rec_empleo_hist hr.pkg_empleado.typ_empleo_hist;
BEGIN
rec_empleo_hist := hr.pkg_empleado.func_emp_hist(101);
IF rec_empleo_hist.COUNT > 0 THEN
FOR i IN rec_empleo_hist.FIRST..rec_empleo_hist.LAST LOOP
DBMS_OUTPUT.PUT_LINE
(
'Empleo '||i||' : '||rec_empleo_hist(i)
);
END LOOP;
ELSE
DBMS_OUTPUT.PUT_LINE('El Empleado no ha cambiado de Empleo.');
END IF;
END;
--Sección de Variables y Tipos Records
TYPE typ_emp_details IS RECORD
(
nombre VARCHAR2(60),
tiempo_vig NUMBER(2), --Tiempo Vigente en la empresa
departamento_act hr.departments.department_name%TYPE,
empleo hr.jobs.job_title%TYPE,
direccion VARCHAR2(150)
);
TYPE typ_empleo_hist IS TABLE OF
hr.jobs.job_title%TYPE
INDEX BY pls_integer;
TYPE typ_dept_hist IS TABLE OF
hr.departments.department_name%TYPE
INDEX BY pls_integer;
PROCEDURE proc_emp_details
(
p_cod_emp IN hr.employees.employee_id%TYPE,
p_emp_details OUT typ_emp_details
);
FUNCTION func_emp_hist
(
p_cod_emp IN hr.employees.employee_id%TYPE
) RETURN typ_empleo_hist;
END pkg_empleado;
/*Este SCRIPT muestra como crear la Especificación de un Paquete de PL/SQL.*/
---
• La Especificación del paquete pkg_empleado define 1 tipo Record: typ_emp_details, dos tipos Table: typ_empleo_hist y typ_dept_hist, un Procedimiento: proc_emp_details y una Función: func_emp_hist.
• Notar que el Procedimiento: proc_emp_details recibe como parámetro de entrada: p_cod_emp, que seria un numero de empleado: employee_id y retorna un parámetro de salida: p_emp_details, el cual es tipo: typ_emp_details definido en el mismo paquete.
• La función: func_emp_hist por igual recibe el numero de empleado y retorna un tipo: typ_empleo_hist definido también en el paquete.
CREATE OR REPLACE PACKAGE BODY hr.pkg_empleado IS• Notar que el Procedimiento: proc_emp_details recibe como parámetro de entrada: p_cod_emp, que seria un numero de empleado: employee_id y retorna un parámetro de salida: p_emp_details, el cual es tipo: typ_emp_details definido en el mismo paquete.
• La función: func_emp_hist por igual recibe el numero de empleado y retorna un tipo: typ_empleo_hist definido también en el paquete.
----Objetos Privados del Paquete
v_valid_emp NUMBER := 0;
CURSOR cur_valid_emp --Cursor para validar si el empleado existe.
(
p_emp hr.employees.employee_id%TYPE
) IS
SELECT 1
FROM hr.employees
WHERE employee_id = p_emp;
---Implementación de los procedimientos y funciones definidas en la Especificación del Paquete.
PROCEDURE proc_emp_details
(
p_cod_emp IN hr.employees.employee_id%TYPE,
p_emp_details OUT typ_emp_details
) IS
CURSOR cur_emp_details IS
SELECT
e.first_name||' '||e.last_name AS nombre,
TRUNC(MONTHS_BETWEEN(SYSDATE, e.hire_date)/12) AS tiempo,
d.department_name AS departamento,
j.job_title AS empleo,
NVL(l.street_address, 'No Especificado')||', '||l.city||', '||
NVL(l.state_province, ' ')||', '||c.country_name AS direccion
FROM hr.employees e, hr.departments d, hr.jobs j, hr.locations l, hr.countries c
WHERE e.employee_id = p_cod_emp
AND e.department_id = d.department_id
AND e.job_id = j.job_id
AND d.location_id = l.location_id
AND l.country_id = c.country_id;
BEGIN
OPEN cur_valid_emp(p_cod_emp);
FETCH cur_valid_emp INTO v_valid_emp;
CLOSE cur_valid_emp;
IF v_valid_emp < 1 THEN
RAISE_APPLICATION_ERROR(-20001, 'El Código de Empleado '||p_cod_emp||' no es valido. Favor Validar!!!');
END IF;
OPEN cur_emp_details;
FETCH cur_emp_details INTO p_emp_details;
CLOSE cur_emp_details;
END proc_emp_details;
FUNCTION func_emp_hist
(
p_cod_emp IN hr.employees.employee_id%TYPE
) RETURN typ_empleo_hist IS
rec_empleo_hist typ_empleo_hist;
v_count NUMBER(2) := 1;
CURSOR cur_emp_hist
(
p_emp hr.employees.employee_id%TYPE
) IS
SELECT
j.job_title AS empleo
FROM hr.job_history jh, hr.jobs j
WHERE employee_id = p_emp
AND j.job_id = jh.job_id
ORDER BY start_date ASC;
BEGIN
OPEN cur_valid_emp(p_cod_emp);
FETCH cur_valid_emp INTO v_valid_emp;
CLOSE cur_valid_emp;
IF v_valid_emp < 1 THEN
RAISE_APPLICATION_ERROR(-20002, 'El Código de Empleado '||p_cod_emp||' no es valido. Favor Validar!!!');
END IF;
FOR i IN cur_emp_hist(p_cod_emp) LOOP
rec_empleo_hist(v_count) := i.empleo;
v_count := v_count+1;
END LOOP;
RETURN rec_empleo_hist;
END func_emp_hist;
END pkg_empleado;
/
SHOW ERROR
/*Este SCRIPT muestra como crear El Cuerpo de un Paquete de PL/SQL.*/
---
• En el Cuerpo de Paquete: pkg_empleado notamos el desarrollo de los objetos definidos en la Especificación del mismo, ademas de algunos objectos definidos solo en el Cuerpo (Variable: v_valid_emp y Cursor: cur_valid_emp), los cuales son privados ya que solo es posible usarlos dentro del paquete.
SET SERVEROUTPUT ON
DECLARE
rec_emp_details hr.pkg_empleado.typ_emp_details;
BEGIN
hr.pkg_empleado.proc_emp_details
(
100,
rec_emp_details
);
DBMS_OUTPUT.PUT_LINE
(
'Empleado: '||rec_emp_details.nombre||CHR(10)||
'Años en la Empresa: '||rec_emp_details.tiempo_vig||CHR(10)||
'Departamento Actual: '||rec_emp_details.departamento_act||CHR(10)||
'Empleo Actual: '||rec_emp_details.empleo||CHR(10)||
'Dirección Actual: '||rec_emp_details.direccion
);
END;
/*Creamos este bloque anónimo para dar uso al paquete: pkg_empleado, notar como debemos crear una variable tipo Record: typ_emp_details el cual pertenece al mismo paquete y es del tipo de retorno del procedimiento: proc_emp_details; Mas adelante con PUT_LINE mostramos los datos extraídos.*/
---OUTPUT:
DECLARE
rec_empleo_hist hr.pkg_empleado.typ_empleo_hist;
BEGIN
rec_empleo_hist := hr.pkg_empleado.func_emp_hist(101);
IF rec_empleo_hist.COUNT > 0 THEN
FOR i IN rec_empleo_hist.FIRST..rec_empleo_hist.LAST LOOP
DBMS_OUTPUT.PUT_LINE
(
'Empleo '||i||' : '||rec_empleo_hist(i)
);
END LOOP;
ELSE
DBMS_OUTPUT.PUT_LINE('El Empleado no ha cambiado de Empleo.');
END IF;
END;
/*Por igual, en este bloque anónimo creamos una variable tipo tabla: typ_empleo_hist, definido en el paquete, esto porque la Función: func_emp_hist retorna ese tipo de dato.*/
---OUTPUT:
---OUTPUT:
_____________________________________________________________________________________
