Guía de Laboratorio
Programación en Base de Datos – Programación PL/SQL II 1
________________________________________________________________________
________________________
Programación en Base de Datos
PROGRAMACIÓN PL/SQL II
Semana 7
OBJETIVO DEL LA EXPERIENCIA
Construye cursores explícitos e implícitos, y de actualización, utilizando la programación PL/SQL.
MARCO TEÓRICO
El lenguaje utilizado para acceder a las bases de datos relacionales es el llamado Lenguaje Estructurado de Consulta (SQL,
Structured Query Language), que es muy flexible y transparente, es decir, sencillo y poderoso. SQL es un lenguaje de cuarta
generación, lo cual quiere decir que describe lo que quiere hacerse. Ayudará de manera factible al estudiante a relacionar el
mundo real con la base de datos a desarrollar usando programación orientado a negocio.
PL / SQL es un lenguaje de procedimiento diseñado específicamente para incluir declaraciones SQL dentro de su sintaxis. Las
unidades de programa PL / SQL son compiladas por el servidor de Oracle Database y almacenadas dentro de la base de
datos. Y en tiempo de ejecución, tanto PL / SQL como SQL se ejecutan dentro del mismo proceso del servidor, brindando una
eficiencia óptima. PL / SQL hereda automáticamente la solidez, seguridad y portabilidad de la base de datos Oracle.
RECURSOS
Hardware
Sistema. Oracle en Windows requiere un PC Intel x86, AMD64 o Intel EM64T
Memoria. Al menos 1 GB de RAM y el doble en virtual.
Espacio en disco duro. Al menos 6 GB para la instalación (algunas instalaciones requieren menos). Además, necesitamos
poder almacenar 500 MB en la carpeta TEMP del sistema.
Tarjeta gráfica. Debe de ser capaz de mostrar 1024 por 768 píxeles como mínimo y 256 colores.
Software
Sistema Operativo.
Windows 2003 Server y 2003 Server R2.
Windows XP Professional.
Windows Vista, pero no la versión Home Edition.
Windows Server 2008 y 2012. No la versión Server Core.
Compiladores. Se usan para la gente que crean aplicaciones en Oracle usando lenguajes como Pro C, Pro COBOL,
JAVA.
o Visual C++.NET 2005 8.0 o Intel 10.1 C, .Net Express.
Navegador. Para configurar algunos servicios de Oracle. Debe de ser navegador moderno (Internet Explorer 6 o
superior, Firefox 2.0 o superior, Safari 3.1 o superior, Chrome 3.0 o superior)
Usar dirección IP única en la máquina en la que se instala Oracle. Es decir, no usar DHCP para direccionar la IP en el
servidor de Oracle. No es un requisito obligatorio, pero es muy recomendable.
Guía de Laboratorio
Programación en Base de Datos – Programación PL/SQL II 2
________________________________________________________________________
________________________
Implementa cursores explícitos e implícitos, y de actualización, utilizando la programación PL/SQL, programación
estructurada basada en reglas de negocio.
CURSORES IMPLICITOS Y EXPLICITOS.
FAVOR DE DESCARGAR EL ARCHIVO Semana09_PL_SQL_1.sql,
ESTE SCRIPT CONTIENE LA ESTRUCTURA DE TABLAS DEL MODELO RELACIONAL.
CURSORES IMPLICITOS y EXPLICITOS USANDO VARIAS TABLAS.
CASO PRÁCTICO PARA DESARROLLAR
Se tiene el siguiente modelo relacional. COMPROBANTE DE PAGO
Modelo Relacional en Oracle, Diagrama de Base de Datos:
Guía de Laboratorio
Programación en Base de Datos – Programación PL/SQL II 3
________________________________________________________________________
________________________
Cuyos datos son:
TABLA CLIENTE:
TABLA EMPLEADO:
TABLA PRODUCTO:
TABLA CABECERA_PEDIDO:
TABLA DETALLE_PEDIDO:
Guía de Laboratorio
Programación en Base de Datos – Programación PL/SQL II 4
________________________________________________________________________
________________________
TABLA COMPROBANTE:
Ejercicio 1:
Se pide crear un cursor que sea capaz de mostrar los pedidos realizados, según la tabla CABECERA_PEDIDO esta tiene 3
pedidos estos son: ‘00001’, ‘00002’ y ‘00003’, para ello se debe ingresar el número del pedido, por intermedio de variables de
sustitución &, y brindar la información solicitada, tal como se muestran en las siguientes imágenes.
NUMERO DE PEDIDO 00001
NUMERO DE PEDIDO 00002
NUMERO DE PEDIDO 00003
Guía de Laboratorio
Programación en Base de Datos – Programación PL/SQL II 5
________________________________________________________________________
________________________
Cada vez que ejecute el cursor, este pedirá el número del pedido, en este ejercicio se usan todas las tablas, realizando diferentes
consultas y obteniendo los registros solicitados.
Guía de Laboratorio
Programación en Base de Datos – Programación PL/SQL II 6
________________________________________________________________________
________________________
SOLUCION:
SET SERVEROUTPUT ON;
DECLARE
It DETALLE_PEDIDO.ITEM%TYPE;
CodigoP CABECERA_PEDIDO.IDPEDIDO%TYPE:=&CodigoP;
CodigoC CABECERA_PEDIDO.IDCLI%TYPE;
CodigoE CABECERA_PEDIDO.IDEMP%TYPE;
Est CABECERA_PEDIDO.ESTADO%TYPE;
NombreCli CLIENTE.NOMBRE%TYPE;
NombreEmp EMPLEADO.NOMBRE%TYPE;
NombreProd PRODUCTO.NOMBRE%TYPE;
CantProd DETALLE_PEDIDO.CANT%TYPE;
PreProd PRODUCTO.PRECIO%TYPE;
ImpCompra NUMBER (10,2);
TOTAL_IMPORTE NUMBER (10,2):=0;
CURSOR DETALLE IS (SELECT D.ITEM, P.NOMBRE, D.CANT,
P.PRECIO, (D.CANT* P.PRECIO) IMPORTE
FROM DETALLE_PEDIDO D, PRODUCTO P
WHERE D.IDPROD = P.IDPROD AND
D.IDPEDIDO = CodigoP);
BEGIN
SELECT IDPEDIDO,IDCLI, IDEMP, ESTADO
INTO CodigoP, CodigoC, CodigoE, Est
FROM CABECERA_PEDIDO WHERE IDPEDIDO=CodigoP;
SELECT NOMBRE INTO NombreCli FROM CLIENTE WHERE IDCLI=CodigoC;
SELECT NOMBRE INTO NombreEmp FROM EMPLEADO WHERE IDEMP=CodigoE;
DBMS_OUTPUT.PUT_LINE('==================================================');
DBMS_OUTPUT.PUT_LINE('IMPORTADORA - LAS CAMELIAS S.A');
DBMS_OUTPUT.PUT_LINE('==================================================');
DBMS_OUTPUT.PUT_LINE('PEDIDO Nº : ' || CodigoP);
DBMS_OUTPUT.PUT_LINE('CLIENTE : ' || NombreCli);
DBMS_OUTPUT.PUT_LINE('EMPLEADO : ' || NombreEmp);
DBMS_OUTPUT.PUT_LINE('==================================================');
OPEN DETALLE;
FETCH DETALLE INTO It, NombreProd, CantProd, PreProd, ImpCompra;
WHiLE DETALLE%Found
LOOP
DBMS_OUTPUT.PUT_LINE(It || CHR(9) || NombreProd || CHR(9));
DBMS_OUTPUT.PUT_LINE(CHR(9) || 'Cantidad: ' ||CantProd || CHR(9) ||
'Precio: ' ||PreProd || CHR(9) || 'Importe: ' ||ImpCompra);
DBMS_OUTPUT.PUT_LINE('==================================================');
FETCH DETALLE INTO It, NombreProd, CantProd, PreProd, ImpCompra;
END LOOP;
SELECT SUM(D.CANT* P.PRECIO) INTO TOTAL_IMPORTE
FROM DETALLE_PEDIDO D, PRODUCTO P
WHERE D.IDPROD = P.IDPROD AND D.IDPEDIDO = CodigoP;
DBMS_OUTPUT.PUT_LINE('TOTAL IMPORTE: ' || TOTAL_IMPORTE);
DBMS_OUTPUT.PUT_LINE('==================================================');
CLOSE DETALLE;
END;
Guía de Laboratorio
Programación en Base de Datos – Programación PL/SQL II 7
________________________________________________________________________
________________________
El programa esta dividido en 5 secciones
SECCION 1:
En esta sección se declaran las variables y cursor necesario para los procesos a realizar, lea con detenimiento que variables son,
en especial la variable &CodigoP (Numero de Pedido) que recibe un valor desde teclado, El cursor DETALLE
proporcionara al programa el detalle de productos que se han solicitado. Las tablas que se están usando en este cursor son:
DETALLE_PEDIDO y PRODUCTO. Las variables que usan %TYPE heredan el tipo de dato que corresponde al campo de
una determinada tabla.
SECCION 2:
Al inicial el programa, se realiza una consulta implícita, el cual captura: IDPEDIDO, IDCLI, IDEMP y ESTADO, y
almacenarlos en variables temporales INTO CodigoP, CodigoC, CodigoE, Est consecutivamente, dependiendo del
IDPEDIDO, los datos obtenidos serán usado en el proceso de la sección 3.
SECCION 3:
En esta sección se realizan dos consulta implícitas, la primera es para captura el nombre del cliente y la segunda es para
captura el nombre del empleado, que se usaran para mostrar en la cabecera del documento.
Guía de Laboratorio
Programación en Base de Datos – Programación PL/SQL II 8
________________________________________________________________________
________________________
SECCION 4:
En esta sección se abre el cursor DETALLE y se procede a leer la data almacenada en el primer registro del cursor, y a
continuación se usa un bucle con la finalidad de leer todo el cursor la cantidad de veces que se requiera (DETALLE%Found),
según la cantidad de vueltas que realice el bucle se irán imprimiendo los datos (DBMS_OUTPUT.PUT_LINE) del ítem,
Nombre del producto, Precio, Cantidad y el importe de la compra.
SECCION 5:
En esta sección se realizar una consulta implícita, obteniendo el total del importe y mostrando dicho resultado en el
comprobante. Finalmente cerrando el cursor.
FIN DEL EJERCICIO.
Ejercicio 2:
Se pide crear un cursor que sea capaz de mostrar datos de las facturas que han sido realizadas como PAGADAS, según la tabla
COMPROBANTE, esta tiene 3 documentos (1 factura y 2 boletas) estos son: ‘F0001’, ‘B0001’ y ‘B0002’, tal como se
muestra en la siguiente consulta:
Para consultar el comprobante se debe ingresar el número del comprobante, por intermedio de variables de sustitución &, y
brindar la información solicitada, tal como se muestran en las siguientes imágenes.
Guía de Laboratorio
Programación en Base de Datos – Programación PL/SQL II 9
________________________________________________________________________
________________________
Por ejemplo, si ingresamos ‘F0001’, mediante una variable de sustitución, se debe obtener el siguiente comprobante:
Guía de Laboratorio
Programación en Base de Datos – Programación PL/SQL II
10
________________________________________________________________________
________________________
SOLUCION:
Guía de Laboratorio
Programación en Base de Datos – Programación PL/SQL II
11
________________________________________________________________________
________________________
SET SERVEROUTPUT ON;
DECLARE
It DETALLE_PEDIDO.ITEM%TYPE;
CodigoF COMPROBANTE.NCOMP%TYPE:= &CodigoF;
CodigoP CABECERA_PEDIDO.IDPEDIDO%TYPE;
CodigoC CABECERA_PEDIDO.IDCLI%TYPE;
CodigoE CABECERA_PEDIDO.IDEMP%TYPE;
Est CABECERA_PEDIDO.ESTADO%TYPE;
NombreCli CLIENTE.NOMBRE%TYPE;
NombreEmp EMPLEADO.NOMBRE%TYPE;
NombreProd PRODUCTO.NOMBRE%TYPE;
CantProd DETALLE_PEDIDO.CANT%TYPE;
PreProd PRODUCTO.PRECIO%TYPE;
ImpCompra NUMBER(10,2);
TOTAL_IMPORTE NUMBER(10,2):=0;
IGV NUMBER(10,2):=0;
TOTAL_GENERAL NUMBER(10,2):=0;
CURSOR DETALLE IS (SELECT D.ITEM, P.NOMBRE, D.CANT,
P.PRECIO, (D.CANT* P.PRECIO) IMPORTE
FROM DETALLE_PEDIDO D, PRODUCTO P, COMPROBANTE C
WHERE D.IDPROD = P.IDPROD AND
D.IDPEDIDO = C.IDPEDIDO AND
C.TIPCOM ='FACTURA' AND
C.NCOMP = CodigoF AND
C.ESTADO ='PAGADO');
BEGIN
SELECT CABECERA_PEDIDO.IDPEDIDO,CABECERA_PEDIDO.IDCLI,
CABECERA_PEDIDO.IDEMP,COMPROBANTE.ESTADO
INTO CodigoP, CodigoC,CodigoE, Est
FROM CABECERA_PEDIDO, COMPROBANTE
WHERE CABECERA_PEDIDO.IDPEDIDO = COMPROBANTE.IDPEDIDO AND
COMPROBANTE.TIPCOM='FACTURA' AND
COMPROBANTE.NCOMP= CodigoF;
SELECT NOMBRE INTO NombreCli FROM CLIENTE WHERE IDCLI=CodigoC;
SELECT NOMBRE INTO NombreEmp FROM EMPLEADO WHERE IDEMP=CodigoE;
DBMS_OUTPUT.PUT_LINE('==================================================');
DBMS_OUTPUT.PUT_LINE('IMPORTADORA - LAS CAMELIAS S.A');
DBMS_OUTPUT.PUT_LINE('==================================================');
DBMS_OUTPUT.PUT_LINE('FACTURA Nº : ' || CodigoF);
DBMS_OUTPUT.PUT_LINE('PEDIDO Nº : ' || CodigoP);
DBMS_OUTPUT.PUT_LINE('CLIENTE : ' || NombreCli);
DBMS_OUTPUT.PUT_LINE('EMPLEADO : ' || NombreEmp);
DBMS_OUTPUT.PUT_LINE('==================================================');
OPEN DETALLE;
FETCH DETALLE INTO It, NombreProd, CantProd, PreProd, ImpCompra;
WHiLE DETALLE%Found
LOOP
DBMS_OUTPUT.PUT_LINE(It || CHR(9) || NombreProd || CHR(9));
DBMS_OUTPUT.PUT_LINE(CHR(9) || 'Cantidad: ' ||CantProd || CHR(9) ||
'Precio: ' ||PreProd || CHR(9) || 'Importe: ' ||ImpCompra);
DBMS_OUTPUT.PUT_LINE('==============================================');
FETCH DETALLE INTO It, NombreProd, CantProd, PreProd, ImpCompra;
END LOOP;
SELECT SUM(D.CANT* P.PRECIO) INTO TOTAL_IMPORTE FROM DETALLE_PEDIDO D,
PRODUCTO P
WHERE D.IDPROD = P.IDPROD AND D.IDPEDIDO = CodigoP;
IGV := TOTAL_IMPORTE * 0.18;
TOTAL_GENERAL := TOTAL_IMPORTE + IGV;
DBMS_OUTPUT.PUT_LINE('TOTAL IMPORTE: ' || TOTAL_IMPORTE);
DBMS_OUTPUT.PUT_LINE('IGV (18%) : ' || IGV);
DBMS_OUTPUT.PUT_LINE('TOTAL IMPORTE: ' || TOTAL_GENERAL);
DBMS_OUTPUT.PUT_LINE('==================================================');
CLOSE DETALLE;
END;
Guía de Laboratorio
Programación en Base de Datos – Programación PL/SQL II
12
________________________________________________________________________
________________________
El programa está dividido en 5 secciones
SECCION 1:
En esta sección se declaran las variables y cursor necesario para los procesos a realizar, lea con detenimiento que variables son,
en especial la variable &CodigoF (Numero de Factura o Comprobante) que recibe un valor desde teclado, El cursor
DETALLE proporcionara al programa el detalle de productos que se han solicitado (Cursor Explicito). Las tablas que se están
usando en este cursor son: DETALLE_PEDIDO, PRODUCTO y COMPROBANTE. Las variables que usan %TYPE
heredan el tipo de dato que corresponde al campo de una determinada tabla.
SECCION 2:
Al iniciar el programa, se realiza una consulta implícita, el cual captura: IDPEDIDO, IDCLI, IDEMP y ESTADO, y
almacenarlos en variables temporales INTO CodigoP, CodigoC, CodigoE, Est consecutivamente, dependiendo del Ncomp
(Numero de Comprobante), los datos obtenidos serán usado en el proceso de la sección 3.
SECCION 3:
Esta sección se realiza dos consulta implícitas, la primera es para captura el nombre del cliente y la segunda es para captura
el nombre del empleado, que se usaran para mostrar en la cabecera del documento.
Guía de Laboratorio
Programación en Base de Datos – Programación PL/SQL II
13
________________________________________________________________________
________________________
SECCION 4:
En esta sección se abre el cursor DETALLE y se procede a leer la data almacenada en el primer registro del cursor (FETCH),
y a continuación se usa un bucle con la finalidad de leer todo el cursor la cantidad de veces que se requiera (DETALLE
%Found), según la cantidad de vueltas que realice el bucle se irán imprimiendo los datos (DBMS_OUTPUT.PUT_LINE)
de: ítem, Nombre del producto, Precio, Cantidad y el importe de la compra.
SECCION 5:
En esta sección se realizar una consulta implícita, obteniendo el TOTAL IMPORTE calculando IGV y
TOTAL_GENERAL, mostrando dichos resultados en el comprobante. Finalmente cerrando el cursor.
FINDEL EJERCICIO.
CONCLUSIONES Y RECOMENDACIONES DE LA EXPERIENCIA
La codificación PL/SQL de Oracle es tan amplia, que ofrece a los estudiantes una alta gama de alternativas para la resolución
de problemas, no difíciles de entender, con la prioridad de manejar los datos con suma responsabilidad por el programador
Oracle.
Si una base de datos es demasiado grande, se debe hacer un rediseño de base de datos centralizada, para que esta sea una base
de datos distribuida ya que se pueden almacenar los datos en localidades donde son utilizados con mayor frecuencia, de tal
manera que la mayor parte de las operaciones sean sólo locales lo cual reduce el tráfico en la red.
Guía de Laboratorio
Programación en Base de Datos – Programación PL/SQL II
14
________________________________________________________________________
________________________
ACTIVIDAD VIRTUAL
Ingresa a la plataforma virtual, revisa y analiza el tema desarrollado en la semana 8 y luego desarrolla las actividades
propuestas:
SE TIENE EL SIGUIENTE MODELO RELACIONAL:
IMPORTANTE
LA ESTRUCTURA Y DATOS DE LAS TABLAS DEL MODELO RELACIONAL SE ENCUENTRAN
EN EL ARCHIVO SCRIPT DE BASE DE DATOS.
S9_PL_SQL_1.sql,
FAVOR DE DESCARGAR DICHO ARCHIVO.
De acuerdo al material presentado Semana 08, Responda a las siguientes Preguntas:
RESOLVER:
Crear los siguientes programas anónimos PL/SQL:
1. Crear un cursor el cual permita mostrar un determinado comprobante de pago, con solamente ingresar el tipo de
comprobante (‘BOLETA’, ‘FACTURA’) y el número del comprobante, estos pueden ser: ‘F0001’, ‘B0001’ y
‘B0002’. Si en caso el comprobante no existe, agregue una excepción de no existencia.
2. Crear un cursor, el cual muestre los pedidos y sus montos correspondientes de la venta. Si en caso no existen pedidos,
agregar una excepción de no existencia.
