PAQUETES

gestor de base de datos Oracle, explicando estructura y funcionamiento. Por Sara Alvarez Atención:

Contenido exclusivo de DesarrolloWeb.com. No reproducir. Copyright.

En este artículo que pertenece al tutorial de Oracle trateremos el tema de los paquetes de forma detenida.

Los paquetes en Oracle se utilizan para guardar subprogramas y otros objetos de la base de datos. Un paquete consta de los siguientes elementos: y

y

Especificación o cabecera: contiene las declaraciones públicas (es decir, accesibles desde cualquier parte de la aplicación) de sus programas, tipos, constantes, variables, cursores, excepciones, etc. Cuerpo: contiene los detalles de implementación y declaraciones privadas, es decir, accesibles solamente desde los objetos del paquete.

La sintaxis de la cabecera es la siguiente:

create [or replace] pa ckage nombre_paquete nombre_paquete as end nombre_paquete; La sintaxis del cuerpo sería la siguiente: create [or r eplace] package body nombre_paquete as [begin ] end nombre_paquete; Como podéis observar la cabecera se compila independientemente del cuerpo. Os dejo un ejemplo de paquete para que lo veáis más claro. /* Cabecera */ create or replace package busar_emple as TYPE t_reg_emple is RECORD (num_empleado emple.emp_no%TYPE, apellido emple.apellido%TYPE, emple.apellido%TYPE, salario emple.salario%TYPE, departamento emple.dept_no%TYPE); emple.dept_no%TYPE);  procedure ver_por_numero(v_cod ver_por_numero(v_cod emple.emp_no%TYPE); emple.emp_no%TYPE);  procedure ver_por_apellido ver_por_apellido(v_ape (v_ape emple.apellido%TYPE); emple.apellido%TYPE); function datos (v_cod emple.emp_no%TYPE) return t_reg_emple; end buscar_emple;

/* Cuerpo */ create or replace package body buscar_emple as vg_emple t_reg_emple;  procedure ver_emple; /* procedimiento privado*/  procedure ver_por_numero (v_cod emple.emp_no%TYPE) is  begin select emp_no, apellido, salario, dept_no into vg_emple from emple where emp_no=v_cod; ver_emple; end ver_por_numero;  procedure ver_por_apellido (v_ape emple.apellido%TYPE) is  begin select emp_no,apellido,salario,dept_no into vg_emple from emple where apellido=v_apellido; ver_emple; end ver_por_apellido; function datos (v_cod emple.emp_no%TYPE) return t_reg_emple is  begin select emp_no,apellido,salario,dept_no into vg_emple from emple where emp_no=v_cod;  procedure ver_emple is  begin DBMS_OUTPUT.PUT_LINE(vg_emple.num_empleado || '*' || vg_emple.apellido || '*' || vg_emple.salario || '*'|| vg _emple.departamento); end ver_emple; end buscar_emple; Como podéis ver este paquete nos permite buscar un empleado de tres formas distintas y visualizar sus datos.

Utilización de los objetos definidos en los paquetes Podemos utilizar los objetos definidos en los paquetes básicamente de dos maneras distintas: y

y

Desde el mismo paquete: esto quiere decir que cualquier objeto puede ser utilizado dentro del paquete por otro objeto declarado en el mismo. Para utilizar un objeto dentro del paquete tan sólo tendríamos que llamarlo. La llamada sería algo así: v_emple :=buscar_emple.datos(v_n_ape); (como veis no utilizamos el execute ya que nos encontramos dentro del paquete). Desde fuera del paquete: Podemos utilizar los objetos de un paquete siempre y cuando haya sido declarado en la especificación del mismo. Para llamar a un objeto o procedimiento desde fuera del paquete utilizaríamos la siguiente notación: execute nombre_paquete.nombre_procedimiento(lista de parametros);

Declaración

de cursores en paquetes

En los paquetes también podemos introducir cursores, para ello debemos declararlo en la cabecera del paquete indicando su nombre, los parámetros y tipo devuelto. Para que lo veáis más claro os dejo un ejemplo a continuación:

CREATE or REPLACE PACKAGE empleados AS ..... CURSOR a RETURN empleado%ROWTYPE; ... END empleados; CREATE or REPLACE PACKAGE BODY empleados AS .... CURSOR a RETURN empleado%ROWTYPE SELECT * FROM empleado WHERE salario < 10000; .... END empleados; Los paquetes suministrados por Oracle son: Standard : tiene la función to_char y abs dbms_standard: tiene la función raise_aplication_error  dbms_output: con la función put_line dbms_sql: que utiliza sql de forma dinámica. NOTA:

sql dinámico significa que el programa es capaz de ejecutar órdenes de definición y

manipulación sobre objetos que sólo se conocen al ejecutar el paquete. Un ejemplo de la utilización de dbms_sql es el siguiente: BEGIN ...... id_cursor := DBMS_SQL.OPEN_CURSOR; DMBS_SQL.PARSE(id_cursor, instrucción,DMBS_SQL.V3); v_dum :=DMBS_SQL.EXECUTE(id_cursor); DMBS_SQL.CLOSE_CURSOR(id_cursor); ......

Lo que hacemos es abrir el cursor y nos devuelve el id del mismo para poder trabajar  con él. Después tenemos el DMBS_SQL.PARSE que analiza la instrucción que se va a ejecutar. Ya en la siguiente linea ejecutamos la sentencia y por último cerramos el cursor.  No os preocupéis si no habéis ter minado de entenderlo ya que dedicaremos un articulo completo a la utilización de DBSM_SQL.

E

JERCICIOS

CR EATE OR R EPLACE PACK AGE... 1.- Ejemplo de como crear un paquete. 6) Escribir un paquete completo para gestionar los departamentos. El paquete se llamará gest_depart y deberá incluir, al menos, los siguientes subprogramas: - insertar_nuevo_depart: permite insertar un departamento nuevo. El procedimiento recibe el nombre y la localidad del nuevo departamento. Creará el nuevo departamento comprobando que el nombre no se duplique y le asignará como número de departamento la decena siguiente al último número de departamento utiliza do. - borrar_depart: permite borrar un departamento. El procedimiento r ecibirá dos números de departamento de los cuales el primero corresponde al departamento que queremos  borrar y el segundo al departamento al que pasarán los empleados del departamento que se va eliminar. El procedimiento se encargará de realizar los cambios oportunos en los números de departamento de los empleados correspondientes. - modificar_loc_depart: modifica la localidad del departamento. El procedimiento recibirá el número del departamento a modificar y la nueva localidad, y realizará el cambio solicitado. - visualizar_datos_depart: visualizará los datos de un departamento cuyo número se  pasará en la llamada. Además de los dat os relativos al departamento, se visualizará el número de empleados que pertenecen actual mente al departamento. - visualizar_datos_depart: versión sobrecargada del procedimiento anterior que, en lugar  del número del departamento, recibirá el nombre del departamento. Realizará una llamada a la función buscar_depart_por_nombre que se indica en el apartado siguiente. - buscar_depart_por_nombre: función local al paquete. Recibe el nombre de un departamento y devuelve el número del mismo. /************* Cabecera o especificación del paquete **********/ CREATE OR REPLACE PACKAGE gest_depart AS PROCEDURE insert_depart (v_nom_dep VARCHAR2, v_loc VARCHAR2); PROCEDURE borrar_depar  (v_dep_borrar NUMBER, v_dep_nue NUMBER); PROCEDURE cambiar_localidad (v_num_dep NUMBER, v_loc VARCHAR2); PROCEDURE visualizar_datos_depart (v_num_dep NUMBER); PROCEDURE visualizar_datos_depart (v_nom_dep VARCHAR2); END gest_depart; / /******************* Cuerpo del paquete **********************/ CREATE OR REPLACE PACKAGE BODY gest_depart AS FUNCTION buscar_depart_por_nombre /* Función privada */ (v_nom_dep VARCHAR2)

RETURN NUMBER; /********************************************** ***************/ PROCEDURE insert_depart( v_nom_dep VARCHAR2, v_loc VARCHAR2) AS ultimo_dep DEPART.DEPT_NO%TYPE; nombre_repetido EXCEPTION; BEGIN /*Comprobar dpt repetido(Puede levantar NO_DATA_FOUND)*/ DECLARE nom_dep depart.DNOMBRE%TYPE; nombre_repetido EXCEPTION; BEGIN SELECT dnombre INTO nom_dep FROM depart WHERE dnombre = v_nom_dep; RAISE insert_depart.nombre_repetido; EXCEPTION WHEN NO_DATA_FOUND THEN  NULL; WHEN TOO_MANY_ROWS THEN RAISE insert_Depart.nombre_repetido; END; /* Fin del bloque de comprobación de departamento repetido */ /* Calcular el número de departamento e insertar */ SELECT MAX(DEPT_NO) INTO ultimo_dep FROM DEPART; INSERT INTO DEPART VALUES ((TRUNC(ultimo_dep, -1) +10), v_nom_dep,v_loc); EXCEPTION WHEN nombre_repetido THEN DBMS_OUTPUT.PUT_LINE ('Err. Nombre de departamento duplicado'); WHEN NO_DATA_FOUND THEN /* Si no había ningún departamento */ INSERT INTO DEPART VALUES (10,v_nom_dep,v_loc); END insert_depart; /**************************************************************/ PROCEDURE borrar_depar  (v_dep_borrar NUMBER, v_dep_nue NUMBER) AS BEGIN UPDATE emple SET dept_no = v_dep_nue WHERE DEPT_NO=v_dep_borrar; DELETE FROM depart WHERE dept_no = v_dep_borrar; END borrar_depar; /***************************************************** ********/

PROCEDURE visualizar_datos_depart (v_num_dep NUMBER) AS vr_dep depart%ROWTYPE; v_num_empleados NUMBER(4); BEGIN SELECT * INTO vr_dep FROM depart WHERE DEPT_NO=v_num_dep; SELECT COUNT(*) INTO v_num_empleados FROM EMPLE WHERE DEPT_NO=v_num_dep; DBMS_OUTPUT.PUT_LINE ('Número de departamento: '||vr_dep.dept_no); DBMS_OUTPUT.PUT_LINE ('Nombre del departamento: '||vr_dep.dnombre); DBMS_OUTPUT.PUT_LINE ('Localidad : '||vr_dep.loc); DBMS_OUTPUT.PUT_LINE ('Numero de empleados : '||v_num_empleados); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Err departamento no encontrado'); END visualizar_datos_depart; /*************************************************************/ PROCEDURE visualizar_datos_depart /* Versión sobrecargada */ (v_nom_dep VARCHAR2) AS v_num_dep depart.dept_no%TYPE; vr_dep depart%ROWTYPE; v_num_empleados NUMBER(4); BEGIN v_num_dep:=buscar_depart_por_nombre(v_nom_dep); SELECT * INTO vr_dep FROM depart WHERE dept_no=v_num_dep; SELECT COUNT(*) INTO v_num_empleados FROM EMPLE WHERE dept_no=v_num_dep; DBMS_OUTPUT.PUT_LINE ('Número de departamento: '||vr_dep.dept_no); DBMS_OUTPUT.PUT_LINE ('Nombre del departamento: '||vr_dep.dnombre); DBMS_OUTPUT.PUT_LINE ('Localidad : '||vr_dep.loc); DBMS_OUTPUT.PUT_LINE ('Numero de empleados : '||v_num_empleados); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Err departamento no encontrado');