GUÍA Nº7 PL/SQL CREACIÓN DE PROCEDIMIENTOS ALMACENADOS

GUÍA Nº7 PL/SQL

CREACIÓN DE PROCEDIMIENTOS ALMACENADOS

1.- Debido al cambio de Gerencia en  la empresa TOP-ONE, unos de los cambios que se desean concretar es la de efectuar la reingeniería de sus procesos para poder efectuar de manera eficiente su negocio. Por esta razón, la Gerencia decidió reestructurar el departamento de informática y al que Ud. ha postulado. Después de las pruebas de selección, se le ha informado que a contar del mes de Mayo comienza a trabajar en la empresa para poder solucionar los requerimientos de información que se requieren con urgencia.

El  requerimiento con mayor urgencia a solucionar, y del cual Ud. será el responsable, tiene relación a las ventas efectuadas en la empresa. Por ello, a partir del siguiente Modelo, generado por una empresa de asesoría informática,  efectúe lo siguiente:

[pic 2]

a) Conéctese a la base de datos como usuario SYSTEM y ejecute el archivo script_creación_usuario_guía_PLSQL_N°7_ejercicio_1  que creará el usuario p_plsq7_1 password practica.

b) Conéctese posteriormente como usuario p_plsq7_1 y ejecute el archivo script_creacion_tablas_guía_PLSQL_N°7_ejercicio_1 para crear y poblar las tablas del Modelo con los siguientes datos:

TABLA CLIENTE        

        [pic 3]

TABLA EMPLEADO

        [pic 4]

TABLA FACTURA

        [pic 5]

TABLA CUOTA

        [pic 6]

c)  La Gerencia desea contar con un proceso que permita obtener el total de ventas que hasta la fecha han efectuado los empleados de la empresa. Para el desarrollo de este proceso y de acuerdo a los requerimientos del usuario, se deben considerar las especificaciones entregadas por el ingeniero jefe del departamento de informática:

• Se deben considerar TODOS los empleados y las ventas que han efectuado. En el caso de que un empleado no haya efectuado ventas se debe mostrar valor cero para el total de ventas y monto de las ventas.
• Se deben controlar a los menos tres excepciones estándar.
• El resultado del proceso se debe almacenar en la tabla VENTAS_EMPLEADO.
• Al finalizar la ejecución del proceso, la información  almacenada en la tabla VENTA_EMPLEADO debería ser como se muestra en el ejemplo: 

[pic 7]

d) Como una forma de premiar a sus clientes, la empresa desea implementar la acumulación de pesos por compras efectuadas por cada cliente.  En primera instancia, se desea que Ud. implemente un proceso que permita obtener el cálculo detalla y total de acumulación de pesos por compras de acuerdo a las siguientes especificaciones entregadas:

• Los pesos que acumule el cliente corresponden al 1,2% del total de cada compra que ha realizado. Su valor se debe redondear
• El proceso debe controlar a los menos tres excepciones estándar
• Se debe grabar el detalle de los pesos acumulados por cada compra en la tabla DETALLE_PESOS_COMPRAS.
• El total de pesos acumulados por las compras efectuadas por cada cliente se deben grabar en la tabla TOTAL_PESOS_COMPRA.
• Al finalizar la ejecución del proceso, las tablas mencionadas deberían tener la siguiente información:

DETALLE_PESOS_COMPRAS

[pic 8]

TOTAL_PESOS_COMPRAS

[pic 9]

2.- La empresa de retail DUMBO ha visto incrementado sus ventas durante este año 2014. Debido a esto, se contrató a una empresa informática para que les diseñara una Base de Datos que permita almacenar la información necesaria para automatizar todos sus procesos. La Base de Datos considera las tablas que se presentan en el Modelo:

        [pic 10]

Para esta segunda etapa, la empresa lo ha contratado a Ud. para que automatice ciertos procesos a través de PL/SQL. Para ello, conéctese a la base de datos como usuario SYSTEM y ejecute el archivo script_creación_usuario_guía_PLSQL_N°7_ejercicio_2  que creará el usuario p_plsq7_2 password practica.

Posteriormente conéctese como usuario p_plsq7_1 y ejecute el archivo script_creacion_tablas_guía_PLSQL_N°7_ejercicio_2 para crear y poblar las tablas del Modelo con los siguientes datos:

TABLA CLIENTE

[pic 11]

TABLA EMPLEADO

[pic 12]

TABLA FACTURA

[pic 13]

Una vez creadas las tablas del Modelo, desarrollar los procesos que den solución a los requerimientos planteados.

a) El servicio de impuestos internos detectó que los impuestos informados para el año 2013 fueron mal calculados. Por esta razón, se le ha solicitado a la empresa volver a efectuar el recálculo de los impuestos de todos sus empleados, proceso que Ud. deberá simular. Para ello debe considerar lo siguiente:

• El empleado estará afecto al pago de impuesto si su sueldo base anual es mayor o igual a  $2.000.000.
• El valor del impuesto corresponde a un 10% de su sueldo base anual.
• Si el sueldo base anual del empleado en menor a $2.000.000 el valor de del impuesto será cero.
• Insertar en tabla IMPUESTO el rut del empleado (completo), sueldo base anual y valor del impuesto.
• Controlar las excepciones para que a pesar de que ocurra un error durante la ejecución del proceso pueda seguir su ejecución normal y en forma completa. Los errores deben ser grabados en tabla ERRORES_PROCESOS_DUMBO que debe tener la siguiente estructura:

• El procedimiento deberá retornar el total de empleados afectos a impuestos. Para ello, el bloque anónimo que ejecute el procedimiento deberá mostrar los mensaje:

• Total empleados procesados: total_de_empleados_procesados
• Total empleados afectos a impuestos: total_de_empleados.

• Al finalizar la ejecución del procedimiento, los resultaron deberían ser los que se muestran:

TABLA IMPUESTO

                        [pic 14]

MENSAJE DEL BLOQUE QUE EJECUTÓ EL PROCEDIMIENTO:

...