Saltar a contenido

BD2 · S1 · PL/SQL aplicado al negocio

SGE-JD · JD Refrigeración S.A.C. · Equipo CherryTree · Yoel Wagner Chambi Sirena · 10/11/2026

Trabajo autónomo de la sesión 1: el esquema y el primer motor transaccional PL/SQL de la entidad transaccional del proyecto, la venta (VENTAS–DETALLE_VENTA).

Se ejecutó sobre la base de práctica sge-jd-bd2, una copia de los datos de desarrollo del 10/11/2026 con 237 ventas, 712 líneas, 39 productos y 37 clientes (ver ../README.md). Las salidas completas quedan en bd2/salidas/ al correr los scripts.

Orden Script Se ejecuta como Qué hace
1 S01_01_esquema.sql SYSTEM (-Dba -ClaveApp) Verifica el propietario JDREFRIGERACION y crea el usuario técnico JD_APP
2 S01_02_tablas.sql JDREFRIGERACION Revisa las tablas y agrega las reglas críticas como CHECK
3 S01_03_plsql.sql JDREFRIGERACION Dos funciones y el procedimiento de registro; permiso de ejecución para JD_APP
4 S01_04_pruebas.sql JDREFRIGERACION Compara con Java, prueba un caso válido y tres rechazos; se puede repetir
cd C:\SGE-JD\bomerp-backend
.\bd2\herramientas\sql.ps1 bd2\S01\S01_01_esquema.sql -Dba -ClaveApp
.\bd2\herramientas\sql.ps1 bd2\S01\S01_02_tablas.sql
.\bd2\herramientas\sql.ps1 bd2\S01\S01_03_plsql.sql
.\bd2\herramientas\sql.ps1 bd2\S01\S01_04_pruebas.sql

1. Esquema y tablas del proyecto

Usuario Rol Privilegios
JDREFRIGERACION Propietario de las 29 tablas de SGE-JD CREATE SESSION, TABLE, VIEW, PROCEDURE, TRIGGER, SEQUENCE
JD_APP (nuevo) Usuario técnico de la aplicación Solo CREATE SESSION y EXECUTE sobre SP_REGISTRAR_VENTA y FN_TOTAL_VENTA: no es dueño de nada ni toca las tablas directamente

Las tablas de la operación ya existían, porque las creó LP2 con JPA. Su DDL está en scripts/prod/esquema_jdrefrigeracion.sql:

erDiagram
    CLIENTES ||--o{ VENTAS : "compra"
    VENTAS ||--|{ DETALLE_VENTA : "tiene"
    PRODUCTOS ||--o{ DETALLE_VENTA : "se vende en"
    VENTAS ||--o| COMPROBANTES : "se factura en"
    VENTAS {
        NUMBER ID PK
        NUMBER CLIENTE_ID FK
        DATE FECHA_VENTA
        VARCHAR2 MONEDA "SOLES o DOLARES"
        VARCHAR2 TIPO_PAGO "CONTADO o CREDITO"
        VARCHAR2 ESTADO_PAGO
        VARCHAR2 ESTADO "REGISTRADA o ANULADA"
    }
    DETALLE_VENTA {
        NUMBER ID PK
        NUMBER VENTA_ID FK
        NUMBER PRODUCTO_ID FK
        NUMBER CANTIDAD "CHECK mayor que 0 (nuevo)"
        NUMBER PRECIO_UNITARIO "CHECK mayor o igual a 0 (nuevo)"
        NUMBER DESCUENTO_PORCENTAJE "CHECK 0 a 100 (nuevo)"
        NUMBER COSTO_UNITARIO
    }
    PRODUCTOS {
        NUMBER ID PK
        VARCHAR2 SECTOR
        NUMBER COSTO_BASE
        NUMBER MARGEN_PORCENTAJE
        NUMBER STOCK_ACTUAL "CHECK mayor o igual a 0 (nuevo)"
    }

Antes de agregar las reglas se comprobó que ningún dato existente las rompiera. Las seis cuentas dieron 0, así que los cinco ALTER TABLE pasaron sin ORA-02293:

CANTIDAD_MALA PRECIO_MALO DESCUENTO_MALO      STOCK_NEGATIVO COSTO_NEGATIVO MARGEN_NEGATIVO
            0           0              0                   0              0               0
Table altered. (x5)
CK_DETALLE_VENTA_CANTIDAD · CK_DETALLE_VENTA_DESCUENTO · CK_DETALLE_VENTA_PRECIO · CK_PRODUCTO_COSTO_MARGEN · CK_PRODUCTO_STOCK

2. Operación transaccional

Elemento Respuesta
Proceso Registrar una venta directa al contado de un producto
Entidad transaccional VENTAS (cabecera) + DETALLE_VENTA (línea) + PRODUCTOS.STOCK_ACTUAL
Acción PL/SQL FN_SUBTOTAL_LINEA y FN_TOTAL_VENTA (cálculo) + SP_REGISTRAR_VENTA (registro, IN y OUT)
Reglas críticas Cliente y producto existen; descuento dentro del tope del rol (TOPES_DESCUENTO); stock suficiente solo para MATERIAL_SERVICIO; nada negativo

Todo pasa en una sola transacción: la cabecera, la línea y el descuento de stock se confirman juntos o no se confirma nada. El procedimiento no hace COMMIT, porque confirma quien lo llama, igual que una transacción JPA. La fila del producto se bloquea con SELECT … FOR UPDATE, para que dos ventas simultáneas no usen el mismo stock. Es la misma protección que ProductoService.descontarStock en Java.

Las fórmulas son las del backend, para que Oracle y Java den el mismo número:

Cálculo PL/SQL Java
Importe de una línea ROUND(precio × cantidad × (1 − descuento/100), 2) DetalleVenta.calcularSubtotalDetalle()
IGV y total IGV = ROUND(subtotal × 0.18, 2); total = subtotal + IGV Venta.calcularIgv(), calcularTotal()
Precio de catálogo costo_base × (1 + margen/100) Producto.calcularPrecioVenta()
Costo de la línea NVL(ultimo_costo_compra, costo_base) Producto.costoVigente()

3. Funciones PL/SQL

  • FN_SUBTOTAL_LINEA(p_cantidad IN, p_precio IN, p_descuento IN): valida los tres valores (ORA-20001 a 20003) y devuelve el importe de la línea sin IGV.
  • FN_TOTAL_VENTA(p_venta_id IN): suma las líneas de una venta registrada y devuelve el total con IGV (ORA-20004 si la venta no existe). Solo lee.

Hay una función además del procedimiento porque el cálculo se necesita en más lugares que el registro. Se usa en consultas (SELECT fn_total_venta(id) …), en el propio procedimiento y, más adelante, en reportes y triggers. Un procedimiento no se puede usar dentro de un SELECT.

Prueba de que es correcta: se comparó FN_TOTAL_VENTA con COMPROBANTES.TOTAL, el total que calculó y guardó el backend Java, en todas las ventas:

VENTAS_COMPARADAS  COINCIDEN   DIFIEREN
              237        237          0

4. Procedimiento PL/SQL

SP_REGISTRAR_VENTA(p_cliente_id IN, p_producto_id IN, p_cantidad IN, p_descuento IN, p_rol IN, p_vendedor IN,
                   p_venta_id OUT, p_total OUT)
Paso Regla Si falla
1 El cliente existe ORA-20010
2 El producto existe; se bloquea su fila (FOR UPDATE) ORA-20015
3 Producto en soles (S1 no maneja tipo de cambio) ORA-20011
4 Descuento ≤ tope del rol: TOPES_DESCUENTO, o 30 % ADMIN y 10 % ASISTENTE si nadie lo cambió ORA-20012
5 Stock suficiente si el producto es MATERIAL_SERVICIO ORA-20013
6 Total ≤ 700 soles (sobre ese monto corresponden detracción o retención, que S1 no calcula) ORA-20014
7 Inserta cabecera (CONTADO, PAGADO, REGISTRADA) y línea con precio y costo del día; descuenta el stock del material —

Los dos parámetros OUT devuelven el ID de la venta, que quien llama necesita para seguir su flujo (comprobante, PDF), y el total con IGV. Compilación:

Function created. · Function created. · Procedure created. · No errors.
FN_SUBTOTAL_LINEA  FUNCTION   VALID
FN_TOTAL_VENTA     FUNCTION   VALID
SP_REGISTRAR_VENTA PROCEDURE  VALID
Grant succeeded. (x2: EXECUTE a JD_APP)

5. Evidencia de ejecución

Caso válido. Tres unidades de Cinta vinílica blanca (producto 27, precio 7.00, stock 124), con 5 % de descuento, por la asistente:

Venta registrada: 1015 | total devuelto (OUT): 23.54 | total recalculado por FN_TOTAL_VENTA: 23.54

  ID FECHA_VENTA TIPO_PAGO ESTADO_PAGO ESTADO     VENDEDOR_NOMBRE PRODUCTO_ID CANTIDAD PRECIO DESC SUBTOTAL_LINEA TOTAL_CON_IGV
1015 10-NOV-26   CONTADO   PAGADO      REGISTRADA Prueba BD2 S1            27        3      7    5          19.95         23.54

  ID STOCK_DESPUES
  27           121

La cuenta a mano coincide: 3 × 7 × 0.95 = 19.95; IGV 3.59; total 23.54. El stock bajó de 124 a 121.

Casos rechazados. Cada uno hace ROLLBACK y no deja nada a medias:

Rechazado (descuento sobre el tope del asistente): ORA-20012: El descuento de 15 % supera el tope de 10 %.
Rechazado (más unidades que el stock): ORA-20013: Stock insuficiente: hay 121 y se piden 100000.
Rechazado (producto inexistente): ORA-20015: El producto -1 no existe.

La regla protege incluso sin el procedimiento. Un UPDATE directo, como el que haría alguien desde SQL Developer:

UPDATE productos SET stock_actual = -1 WHERE id = :v_producto
ORA-02290: check constraint (JDREFRIGERACION.CK_PRODUCTO_STOCK) violated

Al final queda 1 venta de prueba. El script empieza deshaciendo las ventas de corridas anteriores y devuelve su stock, así que se puede repetir sin acumular datos.

6. Error o hallazgo

Hallazgo: Oracle no protegía ninguna regla de la venta. Al revisar USER_CONSTRAINTS, DETALLE_VENTA no tenía ninguna CHECK, y PRODUCTOS solo tenía las de sector y moneda. En la base se podía guardar stock negativo, una línea con cantidad 0 o un descuento de 150 %. Esas reglas solo las cuidaba el backend Java, con if en VentaService ("cantidad mayor que cero", tope de descuento) y en ProductoService.descontarStock. JPA crea las tablas con ddl-auto=update a partir de las entidades, y esas validaciones escritas en el servicio no se convierten en CHECK. Cualquier script de mantenimiento, migración o consulta manual podía saltárselas. Corrección: cinco CHECK, verificando antes que los datos existentes las cumplían.

Error encontrado al probar: el primer S01_04_pruebas.sql no ejecutó la consulta final (SP2-0734: unknown command beginning "d.producto..."). Causa: la línea PROMPT --- … --- terminaba en guion, y para SQL*Plus un guion al final de línea significa "la línea continúa". La consulta siguiente se pegó al PROMPT y se perdió, igual que el UPDATE de la CHECK. Se cambió el texto del PROMPT y se volvió a correr.

7. Mejora para S2 o S3

Sesión Mejora
S2 Tabla de auditoría y trigger: registrar quién anula una venta y cuándo (cambio de ESTADO con :OLD/:NEW), y un trigger BEFORE que impida volver a REGISTRADA una venta ANULADA
S3 Tabla de registro de errores y excepciones con nombre (PRAGMA EXCEPTION_INIT) en lugar de capturar WHEN OTHERS; ventas de varias líneas (colección) y en dólares con tipo de cambio

8. Relación con ADS y LP2

Objeto BD2 Decisión ADS (S1) Servicio LP2
SP_REGISTRAR_VENTA Integridad: reglas transaccionales también en Oracle (ADR-S1-02) POST /api/v1/admin/ventas/directa → VentaService.registrarVentaDirecta
FN_TOTAL_VENTA Una sola fórmula de total, verificada contra Java Venta.calcularTotal()
CK_PRODUCTO_STOCK Integridad del inventario ProductoService.descontarStock (409 si no alcanza)
JD_APP Seguridad de mínimo privilegio Hoy el backend se conecta como el propietario; en S8 pasará a un usuario técnico

9. Reflexión

¿Qué regla del negocio conviene proteger en Oracle y por qué no debería quedar solo en el backend?

Conviene proteger en Oracle las reglas que, si se rompen, dejan datos imposibles de explicar: stock negativo, cantidades en cero, descuentos fuera de rango y la inmutabilidad de un comprobante aceptado por SUNAT. El backend no es el único que escribe en la base. También escriben las migraciones, los scripts de limpieza, el respaldo que se restaura y quien abre SQL Developer para corregir algo a mano. Si la regla vive solo en Java, cualquiera de esos caminos la salta sin aviso. En la base se cumple venga de donde venga, como mostró el ORA-02290 del UPDATE directo. El costo es mantener la regla en dos lugares. Por eso FN_TOTAL_VENTA se comparó con los 237 totales de Java: si algún día difieren, la prueba lo dice.