PL/SQL para principiantes: Tu primer procedimiento almacenado paso a paso

-- ❌ SQL simple (múltiples llamadas desde la app)
SELECT * FROM libros WHERE disponible = 'Y';
INSERT INTO prestamos VALUES (1, 101, SYSDATE);
UPDATE libros SET disponible = 'N' WHERE id = 1;

-- ✅ PL/SQL (una sola llamada, lógica en el servidor)
CREATE OR REPLACE PROCEDURE registrar_prestamo(
    p_libro_id IN NUMBER,
    p_usuario_id IN NUMBER
) AS
BEGIN
    -- Verificar disponibilidad
    IF libro_disponible(p_libro_id) THEN
        INSERT INTO prestamos VALUES (p_libro_id, p_usuario_id, SYSDATE);
        UPDATE libros SET disponible = 'N' WHERE id = p_libro_id;
        COMMIT;
    END IF;
END;

# 1. Descargar la imagen oficial de Oracle XE
docker pull container-registry.oracle.com/database/express:latest

# 2. Crear y ejecutar el contenedor
docker run -d \
  --name oracle-xe \
  -p 1521:1521 \
  -p 5500:5500 \
  -e ORACLE_PWD=MiPassword123 \
  container-registry.oracle.com/database/express:latest

# 3. Verificar que está corriendo (tarda 2-3 min en iniciar)
docker logs -f oracle-xe

# Cuando veas "DATABASE IS READY TO USE!" ya puedes conectar

# 1. Descargar imagen con Podman
podman pull container-registry.oracle.com/database/express:latest

# 2. Ejecutar contenedor (sintaxis idéntica a Docker)
podman run -d \
  --name oracle-xe \
  -p 1521:1521 \
  -p 5500:5500 \
  -e ORACLE_PWD=MiPassword123 \
  container-registry.oracle.com/database/express:latest

# 3. Ver logs
podman logs -f oracle-xe

# Ventaja: Podman corre rootless por defecto (más seguro)

# Rancher usa Docker por debajo, mismo comando
docker pull container-registry.oracle.com/database/express:latest

docker run -d \
  --name oracle-xe \
  -p 1521:1521 \
  -p 5500:5500 \
  -e ORACLE_PWD=MiPassword123 \
  container-registry.oracle.com/database/express:latest

# Ventaja: Ver el contenedor corriendo en la GUI de Rancher

# Entrar al contenedor
docker exec -it oracle-xe bash

# Conectar como SYSTEM (administrador)
sqlplus system/MiPassword123@localhost:1521/XE

# O conectar como SYSDBA
sqlplus sys/MiPassword123@localhost:1521/XE as sysdba

-- Conectado como SYSTEM, crea tu usuario
CREATE USER biblioteca IDENTIFIED BY biblioteca123;

-- Dar permisos necesarios
GRANT CONNECT, RESOURCE TO biblioteca;
GRANT UNLIMITED TABLESPACE TO biblioteca;

-- Ahora desconecta y reconecta como 'biblioteca'
CONNECT biblioteca/biblioteca123@localhost:1521/XE;

-- Tabla de libros
CREATE TABLE libros (
    libro_id NUMBER PRIMARY KEY,
    titulo VARCHAR2(200) NOT NULL,
    autor VARCHAR2(100),
    isbn VARCHAR2(20) UNIQUE,
    disponible CHAR(1) DEFAULT 'Y' CHECK (disponible IN ('Y','N'))
);

-- Tabla de usuarios
CREATE TABLE usuarios (
    usuario_id NUMBER PRIMARY KEY,
    nombre VARCHAR2(100) NOT NULL,
    email VARCHAR2(100) UNIQUE,
    fecha_registro DATE DEFAULT SYSDATE
);

-- Tabla de préstamos
CREATE TABLE prestamos (
    prestamo_id NUMBER PRIMARY KEY,
    libro_id NUMBER REFERENCES libros(libro_id),
    usuario_id NUMBER REFERENCES usuarios(usuario_id),
    fecha_prestamo DATE DEFAULT SYSDATE,
    fecha_devolucion DATE,
    dias_retraso NUMBER DEFAULT 0
);

-- Tabla de multas
CREATE TABLE multas (
    multa_id NUMBER PRIMARY KEY,
    prestamo_id NUMBER REFERENCES prestamos(prestamo_id),
    monto NUMBER(10,2),
    pagada CHAR(1) DEFAULT 'N'
);

-- Secuencias para auto-incremento
CREATE SEQUENCE seq_libros START WITH 1;
CREATE SEQUENCE seq_usuarios START WITH 1;
CREATE SEQUENCE seq_prestamos START WITH 1;
CREATE SEQUENCE seq_multas START WITH 1;

-- Insertar libros
INSERT INTO libros VALUES (seq_libros.NEXTVAL, 'Clean Code', 'Robert Martin', '978-0132350884', 'Y');
INSERT INTO libros VALUES (seq_libros.NEXTVAL, 'The Pragmatic Programmer', 'Hunt & Thomas', '978-0135957059', 'Y');
INSERT INTO libros VALUES (seq_libros.NEXTVAL, 'Design Patterns', 'Gang of Four', '978-0201633612', 'Y');

-- Insertar usuarios
INSERT INTO usuarios VALUES (seq_usuarios.NEXTVAL, 'Juan Pérez', 'juan@email.com', SYSDATE);
INSERT INTO usuarios VALUES (seq_usuarios.NEXTVAL, 'María García', 'maria@email.com', SYSDATE);

COMMIT;

DECLARE
    -- Variables (opcional)
    v_mensaje VARCHAR2(100);
    v_total NUMBER;
BEGIN
    -- Código ejecutable (obligatorio)
    v_mensaje := 'Hola desde PL/SQL!';
    SELECT COUNT(*) INTO v_total FROM libros;

    DBMS_OUTPUT.PUT_LINE(v_mensaje);
    DBMS_OUTPUT.PUT_LINE('Total de libros: ' || v_total);
EXCEPTION
    -- Manejo de errores (opcional)
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

DECLARE
    -- Tipos básicos
    v_numero NUMBER := 100;
    v_decimal NUMBER(10,2) := 99.99;
    v_texto VARCHAR2(100) := 'Texto variable';
    v_fecha DATE := SYSDATE;
    v_booleano BOOLEAN := TRUE;

    -- Tipo basado en columna (%TYPE) - ⭐ MUY ÚTIL
    v_titulo libros.titulo%TYPE;
    v_libro_id libros.libro_id%TYPE := 1;

    -- Tipo basado en fila completa (%ROWTYPE)
    v_libro_completo libros%ROWTYPE;
BEGIN
    -- Cargar datos en la variable
    SELECT titulo INTO v_titulo
    FROM libros
    WHERE libro_id = v_libro_id;

    DBMS_OUTPUT.PUT_LINE('Libro encontrado: ' || v_titulo);

    -- Cargar fila completa
    SELECT * INTO v_libro_completo
    FROM libros
    WHERE libro_id = v_libro_id;

    DBMS_OUTPUT.PUT_LINE('Autor: ' || v_libro_completo.autor);
END;
/

DECLARE
    v_dias_retraso NUMBER := 5;
    v_multa NUMBER;
    v_contador NUMBER := 1;
BEGIN
    -- IF/ELSIF/ELSE
    IF v_dias_retraso < 3 THEN
        v_multa := 0;
    ELSIF v_dias_retraso < 7 THEN
        v_multa := v_dias_retraso * 1.5;  -- $1.5 por día
    ELSE
        v_multa := v_dias_retraso * 3;    -- $3 por día después de 7
    END IF;

    -- CASE (similar a switch)
    CASE
        WHEN v_multa = 0 THEN
            DBMS_OUTPUT.PUT_LINE('Sin multa');
        WHEN v_multa < 10 THEN
            DBMS_OUTPUT.PUT_LINE('Multa leve: $' || v_multa);
        ELSE
            DBMS_OUTPUT.PUT_LINE('Multa grave: $' || v_multa);
    END CASE;

    -- LOOP básico
    LOOP
        DBMS_OUTPUT.PUT_LINE('Iteración: ' || v_contador);
        v_contador := v_contador + 1;
        EXIT WHEN v_contador > 3;  -- Salir del loop
    END LOOP;

    -- FOR loop
    FOR i IN 1..5 LOOP
        DBMS_OUTPUT.PUT_LINE('FOR i = ' || i);
    END LOOP;

    -- WHILE loop
    v_contador := 1;
    WHILE v_contador <= 3 LOOP
        DBMS_OUTPUT.PUT_LINE('WHILE contador = ' || v_contador);
        v_contador := v_contador + 1;
    END LOOP;
END;
/

CREATE OR REPLACE PROCEDURE registrar_prestamo(
    p_libro_id IN NUMBER,
    p_usuario_id IN NUMBER,
    p_mensaje OUT VARCHAR2
) AS
    v_disponible CHAR(1);
BEGIN
    -- Verificar si el libro está disponible
    SELECT disponible INTO v_disponible
    FROM libros
    WHERE libro_id = p_libro_id;

    IF v_disponible = 'N' THEN
        p_mensaje := 'Error: El libro no está disponible';
        RETURN;
    END IF;

    -- Registrar el préstamo
    INSERT INTO prestamos (prestamo_id, libro_id, usuario_id, fecha_prestamo)
    VALUES (seq_prestamos.NEXTVAL, p_libro_id, p_usuario_id, SYSDATE);

    -- Marcar libro como no disponible
    UPDATE libros
    SET disponible = 'N'
    WHERE libro_id = p_libro_id;

    COMMIT;
    p_mensaje := 'Préstamo registrado exitosamente';

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        p_mensaje := 'Error: Libro no encontrado';
    WHEN OTHERS THEN
        ROLLBACK;
        p_mensaje := 'Error: ' || SQLERRM;
END registrar_prestamo;
/

DECLARE
    v_mensaje VARCHAR2(200);
BEGIN
    -- Registrar préstamo del libro 1 al usuario 1
    registrar_prestamo(
        p_libro_id => 1,
        p_usuario_id => 1,
        p_mensaje => v_mensaje
    );

    DBMS_OUTPUT.PUT_LINE(v_mensaje);
END;
/

CREATE OR REPLACE FUNCTION calcular_multa(
    p_prestamo_id IN NUMBER
) RETURN NUMBER
AS
    v_dias_retraso NUMBER;
    v_multa NUMBER;
    v_fecha_prestamo DATE;
    v_fecha_devolucion DATE;
    c_dias_limite CONSTANT NUMBER := 14;  -- 14 días para devolver
    c_multa_por_dia CONSTANT NUMBER := 2.5; -- $2.5 por día
BEGIN
    -- Obtener fechas del préstamo
    SELECT fecha_prestamo, fecha_devolucion
    INTO v_fecha_prestamo, v_fecha_devolucion
    FROM prestamos
    WHERE prestamo_id = p_prestamo_id;

    -- Si no se ha devuelto, usar fecha actual
    IF v_fecha_devolucion IS NULL THEN
        v_fecha_devolucion := SYSDATE;
    END IF;

    -- Calcular días de retraso
    v_dias_retraso := v_fecha_devolucion - v_fecha_prestamo - c_dias_limite;

    -- Si no hay retraso, multa = 0
    IF v_dias_retraso <= 0 THEN
        v_multa := 0;
    ELSE
        v_multa := v_dias_retraso * c_multa_por_dia;
    END IF;

    RETURN v_multa;

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN 0;
    WHEN OTHERS THEN
        RETURN -1;  -- -1 indica error
END calcular_multa;
/

-- Usar la función directamente en un SELECT
SELECT
    p.prestamo_id,
    l.titulo,
    u.nombre,
    p.fecha_prestamo,
    calcular_multa(p.prestamo_id) AS multa
FROM prestamos p
JOIN libros l ON p.libro_id = l.libro_id
JOIN usuarios u ON p.usuario_id = u.usuario_id
WHERE p.fecha_devolucion IS NULL;

-- Usar en un bloque PL/SQL
DECLARE
    v_multa NUMBER;
BEGIN
    v_multa := calcular_multa(1);
    DBMS_OUTPUT.PUT_LINE('Multa del préstamo 1: $' || v_multa);
END;
/

DECLARE
    -- Declarar el cursor
    CURSOR c_prestamos IS
        SELECT
            p.prestamo_id,
            l.titulo,
            u.nombre,
            p.fecha_prestamo
        FROM prestamos p
        JOIN libros l ON p.libro_id = l.libro_id
        JOIN usuarios u ON p.usuario_id = u.usuario_id
        WHERE p.fecha_devolucion IS NULL;

    -- Variable para cada fila
    v_prestamo c_prestamos%ROWTYPE;
    v_multa NUMBER;
BEGIN
    -- Abrir el cursor
    OPEN c_prestamos;

    -- Procesar cada fila
    LOOP
        FETCH c_prestamos INTO v_prestamo;
        EXIT WHEN c_prestamos%NOTFOUND;

        -- Calcular multa para este préstamo
        v_multa := calcular_multa(v_prestamo.prestamo_id);

        -- Mostrar información
        DBMS_OUTPUT.PUT_LINE(
            'Libro: ' || v_prestamo.titulo ||
            ' | Usuario: ' || v_prestamo.nombre ||
            ' | Multa: $' || v_multa
        );
    END LOOP;

    -- Cerrar el cursor
    CLOSE c_prestamos;
END;
/

BEGIN
    -- ✅ Cursor FOR automatiza OPEN, FETCH, CLOSE
    FOR prestamo IN (
        SELECT
            p.prestamo_id,
            l.titulo,
            u.nombre
        FROM prestamos p
        JOIN libros l ON p.libro_id = l.libro_id
        JOIN usuarios u ON p.usuario_id = u.usuario_id
        WHERE p.fecha_devolucion IS NULL
    ) LOOP
        DBMS_OUTPUT.PUT_LINE(
            'Préstamo activo - Libro: ' || prestamo.titulo ||
            ' | Usuario: ' || prestamo.nombre
        );
    END LOOP;
END;
/

DECLARE
    v_titulo libros.titulo%TYPE;
    v_count NUMBER;
BEGIN
    -- Esto podría lanzar NO_DATA_FOUND
    SELECT titulo INTO v_titulo
    FROM libros
    WHERE libro_id = 999;  -- ID que no existe

    -- Esto podría lanzar TOO_MANY_ROWS
    SELECT COUNT(*) INTO v_count
    FROM libros;

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('⚠️ No se encontró ningún registro');

    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('⚠️ Se encontraron múltiples registros');

    WHEN ZERO_DIVIDE THEN
        DBMS_OUTPUT.PUT_LINE('⚠️ División por cero');

    WHEN DUP_VAL_ON_INDEX THEN
        DBMS_OUTPUT.PUT_LINE('⚠️ Valor duplicado (violación de UNIQUE)');

    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('❌ Error inesperado: ' || SQLERRM);
END;
/

DECLARE
    -- Declarar excepción personalizada
    libro_no_disponible EXCEPTION;
    usuario_con_multas EXCEPTION;

    v_disponible CHAR(1);
    v_multas_pendientes NUMBER;
BEGIN
    -- Verificar disponibilidad del libro
    SELECT disponible INTO v_disponible
    FROM libros WHERE libro_id = 1;

    IF v_disponible = 'N' THEN
        RAISE libro_no_disponible;  -- Lanzar excepción
    END IF;

    -- Verificar multas pendientes del usuario
    SELECT COUNT(*) INTO v_multas_pendientes
    FROM multas WHERE pagada = 'N';

    IF v_multas_pendientes > 0 THEN
        RAISE usuario_con_multas;
    END IF;

    DBMS_OUTPUT.PUT_LINE('✅ Préstamo autorizado');

EXCEPTION
    WHEN libro_no_disponible THEN
        DBMS_OUTPUT.PUT_LINE('❌ El libro no está disponible');

    WHEN usuario_con_multas THEN
        DBMS_OUTPUT.PUT_LINE('❌ El usuario tiene multas pendientes');
END;
/

-- Primero crear tabla de auditoría
CREATE TABLE auditoria_prestamos (
    auditoria_id NUMBER PRIMARY KEY,
    prestamo_id NUMBER,
    accion VARCHAR2(20),
    usuario_bd VARCHAR2(50),
    fecha DATE
);

CREATE SEQUENCE seq_auditoria START WITH 1;

-- Trigger que se ejecuta DESPUÉS de insertar un préstamo
CREATE OR REPLACE TRIGGER trg_auditoria_prestamo
AFTER INSERT ON prestamos
FOR EACH ROW
BEGIN
    INSERT INTO auditoria_prestamos VALUES (
        seq_auditoria.NEXTVAL,
        :NEW.prestamo_id,      -- :NEW = valores nuevos
        'INSERT',
        USER,                    -- Usuario de Oracle actual
        SYSDATE
    );
END;
/

-- Trigger que calcula días de retraso ANTES de actualizar
CREATE OR REPLACE TRIGGER trg_calcular_retraso
BEFORE UPDATE OF fecha_devolucion ON prestamos
FOR EACH ROW
WHEN (NEW.fecha_devolucion IS NOT NULL)
DECLARE
    v_dias_limite CONSTANT NUMBER := 14;
    v_dias_transcurridos NUMBER;
BEGIN
    -- Calcular días transcurridos
    v_dias_transcurridos := :NEW.fecha_devolucion - :OLD.fecha_prestamo;

    -- Auto-calcular días de retraso
    IF v_dias_transcurridos > v_dias_limite THEN
        :NEW.dias_retraso := v_dias_transcurridos - v_dias_limite;

        -- Registrar multa automáticamente
        INSERT INTO multas VALUES (
            seq_multas.NEXTVAL,
            :NEW.prestamo_id,
            :NEW.dias_retraso * 2.5,  -- $2.5 por día
            'N'
        );
    ELSE
        :NEW.dias_retraso := 0;
    END IF;

    -- Marcar libro como disponible de nuevo
    UPDATE libros
    SET disponible = 'Y'
    WHERE libro_id = :NEW.libro_id;
END;
/

CREATE OR REPLACE PROCEDURE devolver_libro(
    p_prestamo_id IN NUMBER,
    p_mensaje OUT VARCHAR2
) AS
    v_ya_devuelto DATE;
BEGIN
    -- Verificar si ya fue devuelto
    SELECT fecha_devolucion INTO v_ya_devuelto
    FROM prestamos
    WHERE prestamo_id = p_prestamo_id;

    IF v_ya_devuelto IS NOT NULL THEN
        p_mensaje := 'Este libro ya fue devuelto el ' ||
                    TO_CHAR(v_ya_devuelto, 'DD/MM/YYYY');
        RETURN;
    END IF;

    -- Registrar devolución (el trigger calcula multa automáticamente)
    UPDATE prestamos
    SET fecha_devolucion = SYSDATE
    WHERE prestamo_id = p_prestamo_id;

    COMMIT;
    p_mensaje := '✅ Libro devuelto exitosamente';

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        p_mensaje := '❌ Préstamo no encontrado';
    WHEN OTHERS THEN
        ROLLBACK;
        p_mensaje := '❌ Error: ' || SQLERRM;
END devolver_libro;
/

CREATE OR REPLACE PROCEDURE reporte_biblioteca
AS
    v_total_libros NUMBER;
    v_libros_prestados NUMBER;
    v_multas_pendientes NUMBER;
    v_monto_multas NUMBER;
BEGIN
    -- Estadísticas generales
    SELECT COUNT(*) INTO v_total_libros FROM libros;
    SELECT COUNT(*) INTO v_libros_prestados
    FROM libros WHERE disponible = 'N';

    SELECT COUNT(*), NVL(SUM(monto),0)
    INTO v_multas_pendientes, v_monto_multas
    FROM multas WHERE pagada = 'N';

    -- Mostrar resumen
    DBMS_OUTPUT.PUT_LINE('========== REPORTE DE BIBLIOTECA ==========');
    DBMS_OUTPUT.PUT_LINE('Total de libros: ' || v_total_libros);
    DBMS_OUTPUT.PUT_LINE('Libros prestados: ' || v_libros_prestados);
    DBMS_OUTPUT.PUT_LINE('Libros disponibles: ' ||
                        (v_total_libros - v_libros_prestados));
    DBMS_OUTPUT.PUT_LINE('Multas pendientes: ' || v_multas_pendientes);
    DBMS_OUTPUT.PUT_LINE('Monto total multas: $' || v_monto_multas);
    DBMS_OUTPUT.PUT_LINE('===========================================');

    -- Listar préstamos activos
    DBMS_OUTPUT.PUT_LINE('');
    DBMS_OUTPUT.PUT_LINE('PRÉSTAMOS ACTIVOS:');

    FOR prestamo IN (
        SELECT
            l.titulo,
            u.nombre,
            p.fecha_prestamo,
            TRUNC(SYSDATE - p.fecha_prestamo) AS dias_prestado
        FROM prestamos p
        JOIN libros l ON p.libro_id = l.libro_id
        JOIN usuarios u ON p.usuario_id = u.usuario_id
        WHERE p.fecha_devolucion IS NULL
    ) LOOP
        DBMS_OUTPUT.PUT_LINE(
            '- ' || prestamo.titulo ||
            ' | ' || prestamo.nombre ||
            ' | Días: ' || prestamo.dias_prestado
        );
    END LOOP;
END reporte_biblioteca;
/

SET SERVEROUTPUT ON;

DECLARE
    v_mensaje VARCHAR2(200);
BEGIN
    -- 1. Registrar un préstamo
    DBMS_OUTPUT.PUT_LINE('=== TEST 1: Registrar préstamo ===');
    registrar_prestamo(1, 1, v_mensaje);
    DBMS_OUTPUT.PUT_LINE(v_mensaje);
    DBMS_OUTPUT.PUT_LINE('');

    -- 2. Intentar prestar el mismo libro (debe fallar)
    DBMS_OUTPUT.PUT_LINE('=== TEST 2: Intentar duplicar préstamo ===');
    registrar_prestamo(1, 2, v_mensaje);
    DBMS_OUTPUT.PUT_LINE(v_mensaje);
    DBMS_OUTPUT.PUT_LINE('');

    -- 3. Ver reporte
    DBMS_OUTPUT.PUT_LINE('=== TEST 3: Reporte general ===');
    reporte_biblioteca;
    DBMS_OUTPUT.PUT_LINE('');

    -- 4. Simular retraso y devolver libro
    DBMS_OUTPUT.PUT_LINE('=== TEST 4: Simular retraso (20 días atrás) ===');
    UPDATE prestamos
    SET fecha_prestamo = SYSDATE - 20
    WHERE prestamo_id = 1;
    COMMIT;

    -- 5. Devolver libro (generará multa automática)
    DBMS_OUTPUT.PUT_LINE('=== TEST 5: Devolver libro ===');
    devolver_libro(1, v_mensaje);
    DBMS_OUTPUT.PUT_LINE(v_mensaje);
    DBMS_OUTPUT.PUT_LINE('');

    -- 6. Ver multas generadas
    DBMS_OUTPUT.PUT_LINE('=== TEST 6: Ver multas ===');
    FOR multa IN (
        SELECT m.*, p.dias_retraso
        FROM multas m
        JOIN prestamos p ON m.prestamo_id = p.prestamo_id
    ) LOOP
        DBMS_OUTPUT.PUT_LINE(
            'Multa ID ' || multa.multa_id ||
            ' | Días retraso: ' || multa.dias_retraso ||
            ' | Monto: $' || multa.monto
        );
    END LOOP;
END;
/

-- Ver todos los procedimientos y funciones
SELECT object_name, object_type, status
FROM user_objects
WHERE object_type IN ('PROCEDURE', 'FUNCTION', 'TRIGGER')
ORDER BY object_type, object_name;

-- Ver código fuente de un procedimiento
SELECT text
FROM user_source
WHERE name = 'REGISTRAR_PRESTAMO'
ORDER BY line;

-- Ver errores de compilación
SELECT line, position, text
FROM user_errors
WHERE name = 'REGISTRAR_PRESTAMO';

-- Eliminar objetos
DROP PROCEDURE registrar_prestamo;
DROP FUNCTION calcular_multa;
DROP TRIGGER trg_auditoria_prestamo;

-- Recompilar si hay dependencias rotas
ALTER PROCEDURE registrar_prestamo COMPILE;
ALTER FUNCTION calcular_multa COMPILE;